Deadlock due to keylock and page lock in SQL Server
We are facing deadlock for 2 to 3 times a day. When we analysed it is due to keylock and pagelock happening together.
We found deadlock is between the database replication job and other ETL job
Keylock is owned as Exclusive lock (x) by the database replication job on the clustered index column, and when ETL process is trying to get shared lock (s) on the same index it is failing as victim.
The page lock is owned by the ETL process as shared lock (s) and the replication job is getting an Intent开发者_JS百科 Exclusive lock (ix) on the same.
When we try re-running the failed ETL job after some time it is getting completed. ETL job has only select query and no updates, inserts or deletes. It is only truncate and load. Further it is not happening in all jobs which is hitting the same server, only few jobs are getting failed. The replication job is set to run all the time.
Will SELECT(NO LOCK)
help in this case? Or increasing the deadlock priority of ETL job?
Could anyone help how to resolve this deadlock?
Deadlock-graph
精彩评论