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

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