CODEWITHSUNDEEP
c#.netsqlsql-serverdeadlock
Sebastian
Sebastian

Reputation: 972

SQL Server Insert in several Threads Deadlock

I insert in one table with many concurrent threads, in this Table is a AFTER INSERT Trigger which updates one field on the inserted Row. When this is executed in one thread everything is fine, but with many ones I got a Deadlock. Is there something I can do?

Upvotes: 1

Views: 1358

Answers (5)

A-K
A-K

Reputation: 17080

The easiest way in the long run is to avoid high concurrency altogether. Can you insert rows in bulk, via staging tables, or table-valued parameters? Can you do it from one connection? That could be mush faster and deadlock-free.

Although our system has a lot of OLTP modification, we have been completely deadlock-free since April at least.

Also I wrote a canned answer with more approaches.

Upvotes: 0

Jason Meckley
Jason Meckley

Reputation: 7591

What if you update the value as part of the insert, rather than a post-insert trigger. You may also want to take a look at the db usage overall. there may be a way to reduce the chances of a deadlock by changing some of the queries.

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

I would suggest that you capture the Deadlock Event Graph in order to identify which database objects/resources are being contested for (and ultimately causing the Deadlocks).

See: Detecting and Ending Deadlocks

You should also look at the execution plan/s for your queries involved in this process. You may find that a particular column is the source of contention(often via a Table/Index scan) which can be addressed through the deployment of a suitable index (in order to encourage an Index Seek operation).

Upvotes: 1

Matt Fellows
Matt Fellows

Reputation: 6522

Could the Trigger be removed and the equivalent operation run by code, after all the updates have happened? Or a Periodic procedure gets run to do the equivalent of the trigger?

Other than that I guess looking up on locking hints might be a good idea? Maybe your trigger could make use of the nolock hint?

Upvotes: 0

Schroedingers Cat
Schroedingers Cat

Reputation: 3129

Either don't use multiple threads for this process - which I assume is not a reasonable option - or don't use the trigger, rather use a post-processing stored procedure to update all of the records at once.

Upvotes: 2

Related Questions