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