What is a Deadlock in SQL Server | How a deadlock occur in SQL Server with DEMO | SQL Interview Q&A

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
   • Locking in SQL Server | with DEMO | W...  

2. Locking resources in SQL server
   • Locking Resources in SQL Server | Loc...  

3. Locking Modes in SQL server
   • What are the different locking modes ...  

4. Blockings in SQL server
   • What is a Blocking in SQL Server | Fi...  

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