jeudi 8 janvier 2015

SQL Server Index Update Deadlock


I have 2 queries which when run at the same time are causing a deadlock.


Query 1 - update a column which is included in an index (index1):



update table1 set column1 = value1 where id = @Id


Takes X-Lock on table1 then attempts an X-Lock on index1.


Query 2:



select columnx, columny, etc from table1 where {some condition}


Takes an S-Lock on index1 then attempts an S-Lock on table1.


Is there a way to prevent the deadlock while maintaining the same queries? For example can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order - which should prevent the deadlock?


Edit 1: Isolation level is Read Committed.


Edit 2: http://ift.tt/1FuODYa





Aucun commentaire:

Enregistrer un commentaire