How SQL server choose a Deadlock Victim | Deadlock Priority in SQL Server | SQL interview Q&A
When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions
as a deadlock victim and roll back its transaction (error 1205) and allows other session to move forward.
By default, SQL server will choose the least expensive transaction as deadlock victim and roll back its transaction.
Least expensive transaction is the one that makes fewest changes to the database.
However, user can specify the priority of sessions in a deadlock situation using deadlock_priority statement.
Watch the video to know more and for live Demo.
Deadlock in SQL Server and how do they occur
Video Link : • What is a Deadlock in SQL Server | Ho...
======================
select * from table1
select * from table2
SET DEADLOCK_PRIORITY HIGH
begin tran
--**Statement1
update table1 set name = 'Ramos'
where id = 103
--**Statement2
update table2 set salary = 50000
where id = 101
===============================
begin tran
--**Statement1
update table2 set salary = 50000
where id in (101,102,103)
--**Statement2
update table1 set name = 'Ramos'
where id = 103
===============================
SET DEADLOCK_PRIORITY HIGH --- priority of +5.
SET DEADLOCK_PRIORITY NORMAL --- priority of 0.
SET DEADLOCK_PRIORITY LOW --- priority of -5.
SET DEADLOCK_PRIORITY 10
SET DEADLOCK_PRIORITY -2
SELECT session_id, deadlock_priority
FROM sys.dm_exec_sessions
Microsoft document on deadlock priority
https://learn.microsoft.com/en-us/sql...
Watch video How SQL server choose a Deadlock Victim | Deadlock Priority in SQL Server | SQL interview Q&A online without registration, duration hours minute second in high quality. This video was added by user Right to Learn @BK 30 January 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,378 once and liked it 93 people.