Deadlock is a condition where two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs.
SQL Server database engine comes with a deadlock monitor thread that will periodically check for deadlock situations and it will choose one of the process as a deadlock victim and rollback that process. After it rollbacks the victim process, it allows other processes to execute.
In this video, you will learn what is a deadlock and a scenario on how a deadlock occur in SQL server that is explained with a Demo in SSMS.
1. What is Locking in SQL server
[ Ссылка ]
2. Locking resources in SQL server
[ Ссылка ]
3. Locking Modes in SQL server
[ Ссылка ]
4. Blockings in SQL server
[ Ссылка ]
================
--T-SQL scripts used in this video
================
select * from table1
select * from table2
--***Transaction 1
Begin tran
--//-- Statement1
update Table1 set name = 'Brownee'
where id = 101
--// -- Statement2
update Table2 set Salary = '60000'
where id = 102
commit
==============
---*** Transaction 2
Begin tran
--//-- Statement1
Update Table2 set Salary = '5000'
where id = 101
--// -- Statement2
update Table1 set name = 'Chris'
where id = 102
commit
===============
--To check Lock modes & resource
select request_session_id, request_mode, request_type,
resource_type, resource_description
from sys.dm_tran_locks
select * from sys.sysprocesses where blocked
