x

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 '10 at 11:30 AM in Default

dosberg gravatar image

dosberg
122 9 10 12

(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 '10 at 11:54 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x38

asked: Sep 10 '10 at 11:30 AM

Seen: 1578 times

Last Updated: Sep 10 '10 at 11:30 AM