question

dosberg avatar image
dosberg asked

Deadlock involving same table but different indexes

Application has a table where all service calls are logged. App sends up ad hoc insert statement to insert data. Different spids doing the same insert are deadlocking on different indexes in the table. The fragmentation on the indexes looks good. Any suggestion on how to prevent deadlocks in this type of situation? The database is vendor schema but the application changes are possible.
deadlock
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Once had a very similar issue - with a very high concurrency, high throughput session data table - resolved it with covering indexes, as the updates where deadlocking with the clustered index seek and bookmark (or key) lookups against the non-clustered indexes for the reads. The covering index meant that the read only needed to use (shared lock) one index.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.