mardi 6 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?





Aucun commentaire:

Enregistrer un commentaire