question

shridhar avatar image
shridhar asked

database Transaction was deadlocked on lock resources with another process

I am inserting data into table1 from 2 services( windows services ). I have after Trigger (trigger1) on table1 .In trigger 1 I split data and store data into table2 again I have trigger (trigger2) on table2 for another process . But while inserting from 2 service I get error insert data into database Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
sql-server-2008inserttriggerdeadlockc#
10 |1200

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

jamesjames avatar image
jamesjames answered
I have also same problem
10 |1200

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

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
A deadlock means that two (or more) processes each lock a resource and request another resource, which one of the other processes involved in the deadlock chain has already locked. So if this is what's going on, in chronological order (very simplified, locks are generally not on table level unless the whole table is read): Process 1 starts a transaction Process 2 starts a transaction Process 1 reads Table A which places a shared lock on table A. Process 2 reads Table B which places a shared lock on table B Process 1 tries to update Table B (one of the rows locked by Process 2). Since there's a shared lock, Process 1 is suspended until the lock is released. Process 2 tries to update Table A (one of the rows locked by Process 2). Since there's a shared lock, Process 2 is suspended until the lock is released. Now we have two processes which are suspended until a lock is released. But since both are suspended, none of the locks involved in this locking thriller will ever be released. The deadlock mechanism in SQL Server will understand this and brutally pick one of the processes as a victim of the deadlock scenario. Let's say Process 1 is selected victim. Then the locks held by Process 1, namely the shared lock on table A, will be released and Process 2 can go on. There's a TechNet article named Minimizing Deadlocks: https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx
10 |1200

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

kevaburg avatar image
kevaburg answered
A deadlock is the result of a programming error and not a Problem associated with the administration of a database. SQL Server handles deadlocks quite well and resolves it by selecting a deadlock victim (as shown in your message above) so that at least one of the processes can continue to work. The deadlock victim will be shown an appropriate message explaining what has happened and will need to rerun the query that caused the 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.

shamim007 avatar image
shamim007 answered
use sp_whoisactive or sp_who2 to find the blocked session then kill it .just run "KILL session ID"
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.