In this article, we will try to find out an answer to the “Could you Define Deadlocks with a Simple Example” question that might be asked for the database administrator interviews.
Define Deadlock with a Simple Example
Deadlock is a very complicated issue and sometimes it can be laborious to understand this concept. So let’s try to explain the deadlock with a more tangible example from real life. In the following image, each car’s intention is to cross the intersection but none of them gets an empty lane. This situation can continue forever if no driver is driving his car back.
A deadlock is a situation in which a set of transactions are blocked because each process is blocking a resource and waiting for another resource that is already acquired lock by another process. A deadlock may occur if the following four conditions (Coffman’s conditions) occur simultaneously.
- Mutual Exclusion: A resource can only be held by one process just a time
- Hold & Wait: Some processes may request new resources while holding some resources
- No preemption: No resource can be forcibly taken from the processes holding it, resources can only be released by the processes themselves
- Circular Wait: Two or more processes form a circular chain, where each process waits for the next process to release its resources
Define a deadlock for SQL Server
The main task of the lock mechanism is to protect the integrity of the data because databases are used by the users concurrently to read and modify the data. Simply put, the lock mechanism ensures only one user can modify the data at a time and when another user wants to access the same data the blocking occurs. As soon as the process blocking the resource is complete, the blocked process continues to execute its task.
Deadlock is closely related to these two notions, but it is a more complicated situation. The resources are locked by two or more processes, and then each process wants to acquire a lock on the resource that has already been locked by the other process. In this situation, neither of the processes can complete its task and start to wait for the other process to release the lock. However, this locked state will not be resolved unless SQL Server intervenes. After a deadlock is detected by the SQL Server, a victim is selected according to which session termination will use fewer resources than others. As the last action, the process selected as the victim is rolled back and the other process or processes can complete their piece of works. After defining deadlock, let’s reinforce this concept with an example. At first, we will create two tables and insert some sample data into them.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE CityListA ( ID INT IDENTITY PRIMARY KEY, CityName NVARCHAR(50) ) INSERT INTO CityListA VALUES ('Paris'),('London'),('NewYork') CREATE TABLE CityListB ( ID INT IDENTITY PRIMARY KEY, CityName NVARCHAR(50) ) INSERT INTO CityListB VALUES ('Helsinki'),('Istanbul'),('Warsaw') |
After creating the tables, we will open two different query windows in SQL Server Management Studio (SSMS) and then execute the queries in the separated windows at the same time. A short time later one of the queries will return a deadlock error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
--Query Window 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE CityListB Set CityName = 'Oslo' WHERE Id = 1 WAITFOR DELAY '00:00:10' UPDATE CityListA Set CityName = 'Moscow' WHERE Id = 1 ROLLBACK TRAN --Query Window 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE CityListA Set CityName = 'Berlin' WHERE Id = 1 WAITFOR DELAY '00:00:10' UPDATE CityListB Set CityName = 'Bucharest' WHERE Id = 1 ROLLBACK TRAN |
As we can see, the error message is very clear the Query-2 is selected as the victim and is terminated by the SQL Server because of the deadlock issue. Now let’s examine in detail how this deadlock occurs. In this deadlock scenario, we have two queries, and they want to modify some rows of the CityListA and CityListB tables. Query-1 acquires an exclusive lock on the CityListA table. Simultaneously Query-2 acquires an exclusive lock on the CityListB table. Until this time everything is on its way because they are working on the separate tables and can access the tables. However, after queries waiting for 10 seconds, Query-1 wants to access CityListB but this table had been already locked by Query-2. Meantime, Query-1 wants to access CityListA but this table had been already locked by Query-1. Just at this time, a deadlock occurs and two queries begin to wait for the other and this wait can go on forever unless someone intervenes. SQL Server chooses one of the queries as the victim and then rollback its processes. The following image defines deadlock how it occurs.
Monitoring Deadlocks
The system_health session is the default extended event of the SQL Server and it begins to collect data when the SQL Server engine starts. It helps to monitor some error logs, locks and latches issues and this event also captures when a deadlock occurs in SQL Server. For this reason, we can use this event to monitor the deadlocks. We can find the system_health event under the SQL Server Management Studio > Management > Extended Events -> system_health.
We can click the View Target Data… on package0.event_file to report all captured events. We click the Filters button and then apply a filter to the captured event name.
We can find the deadlock graph in this event details.
The graph shapes and edges define deadlock very clearly. The rectangles indicate the objects that are involved in the deadlocks. This object can be an index, table, or row. The edges indicate locks types that acquire and request to acquire to the which objects. In the circles, we can find the process details that are related to the deadlock. The circle that is crossed out with the blue line shows us the victim of this deadlock.
Conclusion
In this article, we defined deadlocks with a simple example, this is a question that might be placed in the SQL interview.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023