This, however, may impact other things that use the PK elsewhere. Would that resolve the deadlock Removing the PK and making UserID the (non-unique) clustered index. Since the two updates don't really rely on each other, that seems viable. There are a number of options, including retry on deadlock, a low-priorty process using SET LOCK_TIMEOUT to back out early, or serialisation with application locks. Possible solutions I've thought of are: Removing the explicit transaction control. How this should be resolved, I don't know (save that removing HOLDLOCK may help), because I don't how these tables are used. Which is already locked by the first process. But the other process comes in through the index on JOBSTATE and first takes a UPDATE lock here, and then tries to lock the data page. One process arrive to the row through the RECNO column which is the clustered index, so it starts with taking an UPDATE lock on this level, and then try to lock the index row. This means that they must both update the data page and the index page. Now there can be a clash also if they are updating adjacent rows.īoth processes are updating the JOBSTATE column, and this column is indexed. In terms of SQL Server, a deadlock occurs when two (or more) processes lock the separate resource. Note: To learn more about how to avoid deadlocks, please read The Anatomy of SQL Server Deadlocks and the Best Ways to Avoid Them article. With only row locks, there can only be a clash if they try to update the same row. This example mainly explained how deadlock occurs in a scenario. HOLDLOCK is a synonym to SERIALIZABLE, and this isolation level leads to range locks which are wider that strict row locks. As Tom points out, one of the processes uses the HOLDLOCK hint, which is dubious. On the other hand, it is perfectly understandable that these two UPDATE statements are clashing. INCLUDE(,)įirst of all, one of the processes is running a multi-statement transaction, so it is perfectly possible that locks in involved in the deadlock were taken prior to the statement displayed in the deadlock. ADD CONSTRAINT PRIMARY KEY CLUSTEREDĬREATE NONCLUSTERED INDEX ON. Jobqueue table structure on which deadlock is occurringĬREATE TABLE. UPDATE transactions.JobQueue with (rowlock,HOLDLOCK) SET WHERE Proc Update transactions.Jobqueue -with (UPDLOCK, HOLDLOCK) set JobState=31 where and Proc
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |