DeadLock

In database, a deadlock occurs when two or more process have a resource locked and each process request a lock on resource that another process already locked

when deadlock occur sql server choose one of process as deadlock victim and

 rollback that process.

Example:

Step 1:

begin tran

update employee  set salary=15000 where name='amit'

step 2

begin tran

update prod set price=15 where id=3

update employee  set salary=15000 where name='sweta'

 Step 3

update prod set price=15 where id=4

At this point SQL Server will select one of the process as a deadlock victim and roll back the statement

step 4

--issue this command in query window (window 1) to undo all of the changes 

rollback

Step 5

--issue this command in query window (window 2) to undo all of the changes 
rollback

how SQL server detect deadlock 

Lock monitor thread in sql server,runs every 5s by default to detect if there are

any deadlock. if lock monitor thread find deadlocks, the deadlock detectin interval

will drop from 5s to as low as 100ms depending on the frequency of deadlock.if the

lock monitor thread stops finding deadlock,the datadatabase search engine increae

the interval between searches to 5s.

what happpened when deadlock detected?

when deadlock detected the database engineend the deadlock by choosing one of the

thread as the deadlock victim.the deadlock viction transaction is then rolled back

and returns a 1205 error to the application.rolling back the transaction of the deadlock

victim release all lock held by that transaction.this allw the other transaction became

unblock and move forward.

What is deadlock priority?

by default sql server choose a transaction as the deadlock victim is least expensive

to rollback. however, a user can specify the priority of session in a deadlock situation

using the set deadlock priority statement. the session with the lowest deadlock priority

ex- set deadlock_priority normal

deadlck_priority

1.default normal

2.can be set to low normal high

3.can also be set to integer value in the range -10 to 10.

  *low: -5 

  *normal: 0 

  *high: 5

Deadlock victim criteria

1. if the deadlock priority is diffrent, the session with the lowest priority is

   selected as the victim.

2. if both the session have same priority, the transaction that is least expensive

   to rollback is selected as the victim.

3. if both session have the same priority and same expensive cost, a victim is 

chosen as randomly.