Deadlocks in SQL Server — part1: What the locks are?
Introduction
Complex, distributed, high-load systems very often experience the concurrent data access issue which triggers various kinds of locks in database queries. Besides, deadlocks may also occur, and they need to be dealt with.
We have started a series of articles dedicated to locks in databases. In these articles, we’ll share our experience accumulated in our company on the following issues:
- What types of locks we used while developing our products;
- What diagnostic tools and means we used to reveal deadlocks;
- How we dealt with deadlocks.
In this article series, we’ll talk about locks we came across when developing software products.
Before we proceed, let’s briefly recollect why we need locks in the first place.
Why locks are necessary
In distributed systems used as database storage, various side effects with parallel data access may emerge:
- The lost update issue happens when two transactions simultaneously UPDATE the same row, and modifications introduced by one transaction are erased by another one;
- Dirty read is such a read when data from some other transaction, which is subsequently rolled back, may be read, added, or modified;
- Non-repeatable read manifests itself when the previously read data is modified during the rereading operation in a single transaction;
- Phantom reads can be witnessed when one transaction is executed selecting multiple rows several times according to the same criteria. At the same time, in the intervals between these selections, another transaction adds or deletes rows or modifies the columns of some rows, which are used in the selection criteria for the previous transaction, and ends successfully. As a result, the same selections in the previous transaction return different sets of rows.
Modern DBM Systems are capable of solving these issues, using built-in mechanisms. For example, MSSQL Server executes this operation, using different levels of transaction isolation:
- Read uncommitted;
- Read committed;
- Repeatable read;
- Serializable;
- Snapshot.
In turn, the locking mechanism is used to implement the transaction isolation requirement. Consequently, the locking mechanism is designed to solve concurrency problems. It’s also worth noting that locks can lead to performance degradation, deadlocks, and data loss if used improperly.
Locks
MS SQL Server includes many different types of locks, and many good articles and technical documentation have been written on this topic. In this article, we’ll cover only a part of them. They’re the locks we experienced in practice while developing our products.
The task of data extraction from the queue
The classic task when locking is used is the item extraction from the queue with which a limited number of clients can simultaneously work. Only one queuing client can gain access to such an element at a certain time.
In several of our products, we use a queue, stored in a database, access to which has to be synchronized among clients. Let’s see, how we solved this issue in our products.
Suppose we have a queue of tasks for clients, which is stored in the database. Each task can be simultaneously accessed by a maximum of N clients. Therefore, we have a connection of the task with its results on our hands — it’s a 1-N connection. This aspect brings in additional complexity since the data extracted is stored in two tables linked by a foreign key. The tables have a structure like this:
Task — the table with tasks
TaskResults — the table with task results
To solve this issue, we used the update lock (UPDLOCK) together with the ROWLOCK and READPAST hints applied to the Task root table entries.
For example:
Using the UPDLOCK operator guaranteed that other clients were unable to access the task. Using the ROWLOCK hint, we recommended MS SQL Server to use the lock at the row level, and the READPAST hint chose only those entries which weren’t blocked by other transactions. Besides data extraction, we also included the UPDLOCK and ROWLOCK hints in all data update operations for both root and child tables.
That’s the way we solved the issue of client concurrent access to tasks.
Extracting data modified simultaneously
In high-load systems developed by ITA Labs, it’s often necessary to monitor data modified by parallel transactions. If data condition only in a particular point in time matters for us, there’s no need to set any lock at all. In that case, there is a risk of reading inconsistent data at a particular moment, but it’s eventually updated after some time. In addition, we increase the displayed data reading speed instead of waiting for locks release. This is very important for the timely software response which increases the UI user-friendliness.
In one of the developed products, we had to display the history of the tasks performed by a specific operator, as well as select or filter instructions conforming to certain criteria. Because a number of clients with update operations accessed these data simultaneously, the table was locked, and there was no possibility to read data during the required time. Therefore, a timeout occurred. We solved this issue by reading the data without locking but using the NOLOCK hint. Though, we have to admit now that this method is not suitable if it’s necessary to take any decision based on the data read. For example:
The NOLOCK hint instructs SQL Server to skip using locks when executing this transaction, and as a result, data is read quickly. However one particular point is worth noting here — one type of lock will be actually set even when the NOLOCK hint is used. What we are talking about is a schema stability lock (Sch-S). Nevertheless, it’s actually a rare occasion when a database schema change is required during business logic execution affecting the work with the database, and therefore, this lock won’t have any effect. However, in the systems where the database schema is changed simultaneously with access to the data stored in the database, we should take this circumstance into account and use other locking mechanisms.
The task of synchronizing access to the data modified
In distributed systems, we often come across the task of synchronizing access to data and its subsequent modification. There are similar cases in our products as well. In these instances, we have to use the UPDLOCK hint in the data extraction operator to prevent updates. This way we ensure that the data, retrieved for subsequent modification, is locked until the transaction is complete.
Let’s consider the example shown in Fig. 2. Suppose we have two concurrently executed transactions selecting the second row from the Table for later modification. To prevent simultaneous access to this row for its modification, we applied the UPDLOCK hint on the SELECT operator. Under these circumstances, the transaction that used the update lock first gains access to the second row. The second transaction will wait until the applied lock is released.
Lock escalation management
Lock escalation is a process aimed at optimizing server operation, which allows replacing multiple low-level locks with one or more locks of the higher level.
For example, if we applied many row-level locks, and all the rows belong to one page, the server can replace them all with a single page lock and then with a table lock to save resources (see Fig. 3).
In our products, we used lock escalation option management where it was necessary as well. In one of the projects, we came across a lock escalation option going from the row level to upper levels like pages or extents. The escalations occurred when the load on several related tables became high. Transactions locked each other and were executed by mistake. To solve this issue, we disabled lock escalation for some tables referring to many other tables.
After disabling lock escalation, the issue with deadlocks was solved. The overall system throughput also increased because the total number of locks significantly decreased.
We also have to note that it would be a poor decision to abuse this option and disable escalation in the environment where it operates properly. Unreasonably applied lock escalation can adversely affect the SQL Server performance and reduce the speed at which SQL instructions are executed.
Deadlocks
A deadlock is a situation where one process requires the resource occupied by another process to continue its execution, and the latter process needs access to the resource used by the former process. In this situation, both processes are in a locked condition and cannot continue their execution.
Let’s consider the simplest example. Suppose we have a table listing some students and consisting of 2 rows:
Two parallel transactions are launched:
- The 1st transaction
- The 2nd transaction
The update operator gets a lock on both transactions. So, what’s going on here? The SELECT operator in both transactions applies the update lock to the second and first rows respectively. Then, executing the UPDATE operation in one transaction, the process tries to apply an exclusive lock to the first row but cannot do this because it’s locked by the other transaction and continues to wait until it’s released. The same way, executing the UPDATE operation, the latter transaction tries to apply an exclusive lock to the second row but it’s already locked by the former transaction and the process holds and waits until the lock is released. This case is a classic example of a deadlock. Both transactions wait for the resources locked by each other until they are released. However, these processes don’t actually freeze. Using the built-in lock manager, MS SQL Server detects deadlocks and resolves them the simple way — it sacrifices one of the transactions. The server just rolls it back and returns an error. The other transaction will continue. MS SQL Server itself selects the transaction to sacrifice, based on performance considerations or using the DEADLOCK_PRIORITY statement.
Summary
In this article, we’ve told you about locks and why we need them. In short, locks are a dedicated mechanism used to implement requirements set by different transaction isolation levels.
We’ve given consideration to the issues which had to be practically solved using locks in a multi-user concurrent environment:
- it’s the task of data extraction from the queue when only one client has access to data at a given time;
- it’s the task of modifiable data extraction with concurrent access when clients simultaneously access shared and often modified data but only for reading;
- it’s the task of modifiable data access synchronization when it’s necessary to access data with subsequent modification.
We’ve also mentioned the lock escalation issue when lower level locks are raised to the level of pages and tables upon exceeding the critical value.
And, to top it off, we’ve briefed you on deadlocks — the situations in which two processes are waiting for the resources locked by them from each other. This topic deserves special attention. So, in our following articles, we’ll discuss how to diagnose and deal with deadlocks.
Author: Nikolay Ivanov, Senior Developer, ITA Labs
