SQL Server deadlock victim selection

Publicado em: 26 Agosto 2015
no canal de: kudvenkat

86,049

458

Text version of the video
http://csharp-video-tutorials.blogspo...

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
/ @aarvikitchen5572

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic

In this video we will discuss
1. How SQL Server detects deadlocks
2. What happens when a deadlock is detected
3. What is DEADLOCK_PRIORITY
4. What is the criteria that SQL Server uses to choose a deadlock victim when there is a deadlock

This is continuation to Part 78, please watch Part 78 before proceeding.

How SQL Server detects deadlocks
Lock monitor thread in SQL Server, runs every 5 seconds by default to detect if there are any deadlocks. If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks. If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.

What happens when a deadlock is detected
When a deadlock is detected, the Database Engine ends the deadlock by choosing one of the threads as the deadlock victim. The deadlock victim's transaction is then rolled back and returns a 1205 error to the application. Rolling back the transaction of the deadlock victim releases all locks held by that transaction. This allows the other transactions to become unblocked and move forward.

What is DEADLOCK_PRIORITY
By default, SQL Server chooses a transaction as the deadlock victim that is least expensive to roll back. However, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. The session with the lowest deadlock priority is chosen as the deadlock victim.

Example : SET DEADLOCK_PRIORITY NORMAL

DEADLOCK_PRIORITY
1. The default is Normal
2. Can be set to LOW, NORMAL, or HIGH
3. Can also be set to a integer value in the range of -10 to 10.
LOW : -5
NORMAL : 0
HIGH : 5

What is the deadlock victim selection criteria
1. If the DEADLOCK_PRIORITY is different, the session with the lowest priority is selected as the victim
2. If both the sessions have the same priority, the transaction that is least expensive to rollback is selected as the victim
3. If both the sessions have the same deadlock priority and the same cost, a victim is chosen randomly

SQL Script to setup the tables for the examples

Create table TableA
(
Id int identity primary key,
Name nvarchar(50)
)
Go

Insert into TableA values ('Mark')
Insert into TableA values ('Ben')
Insert into TableA values ('Todd')
Insert into TableA values ('Pam')
Insert into TableA values ('Sara')
Go

Create table TableB
(
Id int identity primary key,
Name nvarchar(50)
)
Go

Insert into TableB values ('Mary')
Go

Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code. We have not explicitly set DEADLOCK_PRIORITY, so both the sessions have the default DEADLOCK_PRIORITY which is NORMAL. So in this case SQL Server is going to choose Transaction 2 as the deadlock victim as it is the least expensive one to rollback.

-- Transaction 1
Begin Tran
Update TableA Set Name = Name + ' Transaction 1' where Id IN (1, 2, 3, 4, 5)

-- From Transaction 2 window execute the first update statement

Update TableB Set Name = Name + ' Transaction 1' where Id = 1

-- From Transaction 2 window execute the second update statement
Commit Transaction

-- Transaction 2
Begin Tran
Update TableB Set Name = Name + ' Transaction 2' where Id = 1

-- From Transaction 1 window execute the second update statement

Update TableA Set Name = Name + ' Transaction 2' where Id IN (1, 2, 3, 4, 5)

-- After a few seconds notice that this transaction will be chosen as the deadlock
-- victim as it is less expensive to rollback this transaction than Transaction 1
Commit Transaction

In the following example we have set DEADLOCK_PRIORITY of Transaction 2 to HIGH. Transaction 1 will be chosen as the deadlock victim, because it's DEADLOCK_PRIORITY (Normal) is lower than the DEADLOCK_PRIORITY of Transaction 2.

Nesta página do site você pode assistir ao vídeo on-line SQL Server deadlock victim selection duração hora minuto segundo em boa qualidade , que foi baixado pelo usuário kudvenkat 26 Agosto 2015, compartilhe o link com seus amigos e conhecidos, no youtube este vídeo já foi visto 86,049 vezes e gostou 458 espectadores. Boa visualização!

3,849

260