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