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...
En esta página del sitio puede ver el video en línea How SQL server choose a Deadlock Victim | Deadlock Priority in SQL Server | SQL interview Q&A de Duración hora minuto segunda en buena calidad , que subió el usuario Right to Learn @BK 30 enero 2023, comparta el enlace con amigos y conocidos, en youtube este video ya ha sido visto 4,378 veces y le gustó 93 a los espectadores. Disfruta viendo!
