Suggested Videos
Part 75 - Snapshot isolation level in sql server
Part 76 - Read committed snapshot isolation level in sql server
Part 77 - Difference between snapshot isolation and read committed snapshot
In this video we will discuss a scenario when a deadlock can occur in SQL Server.
When can a deadlock occur
In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. The following diagram explains this.
When deadlocks occur, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process can move forward. The transaction that is chosen as the deadlock victim will produce the following error.
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Let us look at this in action. We will use the following 2 tables for this example.
SQL script to create the tables and populate them with test data
The following 2 transactions will result in a dead lock. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code.
Next Video : We will discuss the criteria SQL Server uses to choose a deadlock victim
Part 75 - Snapshot isolation level in sql server
Part 76 - Read committed snapshot isolation level in sql server
Part 77 - Difference between snapshot isolation and read committed snapshot
In this video we will discuss a scenario when a deadlock can occur in SQL Server.
When can a deadlock occur
In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. The following diagram explains this.
When deadlocks occur, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process can move forward. The transaction that is chosen as the deadlock victim will produce the following error.
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Let us look at this in action. We will use the following 2 tables for this example.
SQL script to create the tables and populate them with test data
Create table TableA
(
Id int identity
primary key,
Name nvarchar(50)
)
Go
Insert into TableA values ('Mark')
Go
Create table TableB
(
Id int identity
primary key,
Name nvarchar(50)
)
Go
Insert into TableB values ('Mary')
Go
The following 2 transactions will result in a dead lock. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code.
-- Transaction 1
Begin Tran
Update TableA Set Name = 'Mark Transaction 1' where
Id = 1
-- From
Transaction 2 window execute the first update statement
Update TableB Set Name = 'Mary Transaction 1' where
Id = 1
-- From
Transaction 2 window execute the second update statement
Commit Transaction
-- Transaction 2
Begin Tran
Update TableB Set Name = 'Mark Transaction 2' where
Id = 1
-- From
Transaction 1 window execute the second update statement
Update TableA Set Name = 'Mary Transaction 2' where
Id = 1
-- After a few
seconds notice that one of the transactions complete
-- successfully
while the other transaction is made the deadlock victim
Commit Transaction
Next Video : We will discuss the criteria SQL Server uses to choose a deadlock victim
No comments:
Post a Comment
It would be great if you can help share these free resources