CODEWITHSUNDEEP
sql-serverdeadlock
Mohammad Nadeem
Mohammad Nadeem

Reputation: 9392

Deadlock caused due to multiple inserts in a transaction

I have a sp which has insert statements like this:

begin transaction

insert into table1
insert into table2
.
.
.
insert into table n

commit transaction

This all is fine as long as the sp is not called concurrently. However when the sp is called concurrently I face deadlock issue sometimes on the primary key of the first table.

Upvotes: 0

Views: 871

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294277

There are a mirriad ways this can deadlock. Post the actual deadlock graph (the actual xml, not the picture of the dealock!).

If you truly have nothing but inserts (no reads) then the most likely cause is foreign key constraints. Post the exact schema definition (all table definition, all index definition, all constraints, including primary keys and foreign keys). BTW, if you have any trigger defined then all bets are off as the deadlock could be caused by arbitrary trigger code.

Upvotes: 1

IamDeveloper
IamDeveloper

Reputation: 5226

Have you set for your db some reliable isolation level, like ReadCommitted?

Upvotes: 0

Related Questions