Esat Erkec
Deadlock graph

A SQL Interview Question: Define Deadlocks with an example

October 7, 2021 by

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.

Define Deadlock with a simple example

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.

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.

Simulate a deadlock

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.

Define deadlock with a simple graph

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.

Using system_health extended event

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.

Apply filter to the extended event

We can find the deadlock graph in this event details.

Deadlock graph

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.

Esat Erkec
1,280 Views