![]() 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. With only row locks, there can only be a clash if they try to update the same row. 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. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |