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