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.

more ▼

asked Sep 10, 2010 at 11:30 AM in Default

avatar image

132 10 10 14

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Sep 10, 2010 at 11:54 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 10, 2010 at 11:30 AM

Seen: 1947 times

Last Updated: Sep 10, 2010 at 11:30 AM

Copyright 2018 Redgate Software. Privacy Policy