T-SQL query-understanding the lock in SQL Server

Introduction

In SQL Server, each query finds the shortest path to achieve its goal. If the database only accepts one connection, only one query is executed at a time. Therefore, queries must be completed quickly and easily. However, most databases need to process multiple queries at the same time. These queries will not wait in line like a gentleman, but will be executed in the shortest path. Therefore, like a traffic light at a crossroads, SQL Server also needs a traffic light to tell the query: when to go, and when not to go. The traffic light is the lock.

Figure 1. The query can not be queued in order as gentlemen do

 

Why do I need to lock

Before starting to talk about locks, we should first briefly understand the acid attributes of transactions and transactions. See my previous article on acid. If you understand how transactions affect each other, you should know that in the database, theoretically all transactions should be completely isolated. But in fact, the cost of achieving full isolation is too high (it must be a serialization isolation level to be completely isolated, this concurrency is a bit ....). Therefore, SQL Server's default read commited is a good choice to strike a balance between isolation and concurrency.

The SQL Server uses a lock to tell all concurrent connections, just like the traffic lights at the crossroads, that resources can be read and modified at the same time. As mentioned above, the query itself is not a gentleman, so it needs to be supervised. When a transaction needs to access resources with an incompatible lock, SQL Server will block the current transaction to achieve the so-called isolation. Wait until the lock on the requested resource is released, as shown in figure 2.

Figure 2. SQL server achieves concurrency through Blocking

 

How to view locks

Understanding how SQL Server locks at a certain point in time is undoubtedly an effective means to learn and diagnose database deadlocks and performance. The most common methods for viewing database locks are as follows:

Use the dmv sys. dm_tran_locks.

SQL Server provides the SYS. dm_tran_locks DMV to view the locks in the current database. Figure 2 above shows the DMV.

It is worth noting that the SYS. dm_tran_locks DMV shows the database lock at the query time point and does not contain any history lock records. It can be understood as the snapshot when the database is locked at the query time. SYS. dm_tran_locks contains two types of information: Information about the resource where the lock is located starting with resource, and information about the applied lock starting with request. 3. For more details, see msdn (http://msdn.microsoft.com/en-us/library/ms190345.aspx)

Figure 3. SYS. dm_tran_locks

 

This DMV contains a lot of information, so we usually write some statements to extract the information we need from this DMV. 4.

Figure 4. Write statements to extract the required lock Information

 

Use profiler to capture lock Information

We can use profiler to capture information about locks and deadlocks, as shown in Figure 5.

Figure 5. Capture lock information in profiler

 

However, if it is not filtered by default, the lock information captured by Profiler contains the internal lock of SQL Server, which is inconvenient for us to view the lock information, so we often need to filter the column, as shown in 6.

Figure 6. filter out database lock Information

 

The captured information is 7.

Figure 7. Information captured by Profiler

 

Lock Granularity

Locks are applied to database objects. The database objects are of a specific granularity. For example, the Unit is also 1. The data contained in one row, one page, one B tree, and one table is not of a specific granularity. Therefore, the so-called lock granularity is the granularity of the resource where the lock is located. The information of the resource to be created is the information starting with resource in figure 3.

The query itself is not concerned with the lock issue. Just like you don't care which intersection should have traffic lights when you drive. The granularity and type of the lock are controlled by SQL Server (you can also use the lock prompt, but it is not recommended ). The lock will block the database, so the larger the granularity of the lock will cause more blocking, but because the larger granularity of the lock requires fewer locks, it will improve the performance. A small-granularity lock reduces blocking because it locks fewer resources and improves concurrency. However, a large number of locks may also cause performance degradation. Therefore, the lock granularity shows the relationship between performance and concurrency.

Figure 8. Impact of lock Granularity on performance and concurrency

 

SQL Server determines the granularity of the lock depends on many factors. Such as key distribution, number of request rows, row density, and query conditions. However, the specific judgment condition is that Microsoft has not published a secret. Developers do not have to worry about how SQL Server decides which lock to use. Because SQL Server has made the best choice.

In SQL Server, the lock granularity is shown in table 1.

 

Resources	Description
RID	The row identifier used to lock a single row in the heap.
Key	The index is used to protect the row locks for key ranges in serializable transactions.
Page	8 KB pages in the database, such as data pages or index pages.
Extent	A group of eight consecutive pages, such as data pages or index pages.
HOBT	Heap or B-tree. Used to protect the locks of the B-tree (INDEX) or heap data pages in a table without clustered indexes.
Table	The entire table that includes all data and indexes.
File	Database files.
Application	ApplicationProgramDedicated resources.
Metadata	Metadata lock.
Allocation_unit	Allocation unit.
Database	The entire database.

Table 1. SQL server lock Granularity

 

Lock upgrade

We have discussed the relationship between the lock granularity and performance. In fact, each lock occupies 96 bytes of memory. If a large number of small-granularity locks exist, they occupy a large amount of memory.

Let's take an example. When we select several hundred rows of data (a total of rows), SQL Server will apply a key lock for the corresponding number of rows, as shown in figure 9.

Figure 9.341 requires 341 key locks.

 

However, when the number of rows obtained increases, for example, 6000 (more than 30000 data records in the table), if 6000 key locks are used, it will occupy about 96*6000 = 6000 KB of memory, so to balance the relationship between performance and concurrency, SQL server uses a table lock to replace key locks, this is the so-called lock upgrade. 10.

Figure 10. Replace the 6000 key locks with a table lock

 

Although a table lock replaces the 6000 key locks, it affects concurrency. We update the row that is not in the preceding query (the ID is 50001 and is not within the range of the query in figure 10 ), blocking may occur, as shown in Figure 11.

Figure 11. Lock upgrade improves performance at the cost of reducing concurrency

 

 

Lock mode

When SQL Server requests a lock, it selects a mode that affects the lock. The lock mode determines the Compatibility Level of the lock to any other locks. If a query finds that the lock on the requested resource is compatible with the lock applied by the user, the query can be executed. However, if the query is incompatible, the query will be blocked. Until the lock on the requested resource is released. SQL Server locks can be divided into the following categories:

Shared lock (s lock): used to read the lock applied to the resource. Resources with shared locks cannot be modified. By default, a shared lock reads resources and is immediately released. For example, if I read 100 data records, I can imagine that after I read the first data record, I will immediately release the first data record, lock the second data record, and lock the third data record. So on until 100th. This is also why I need to set the isolation level to repeated read for queries in Figure 9 and figure 10. Only when the isolation level above the Repeatable read level is set or the usage prompt is displayed, the S lock can continue until the transaction ends. Actually,Countless s locks can be added to the same resource..

Exclusive lock (x lock): it is incompatible with any other lock, including other exclusive locks. The exclusive lock is used for data modification. When an exclusive lock is applied to a resource, transactions that read or modify the resource from other requests will be blocked until the exclusive lock is released.

Update lock: The U lock can be seen as the combination of the S lock and the X lock. It is used to update data. When updating data, you must first find the updated data, it can be understood that the S lock is applied to the searched data. When you find the data to be modified, You need to lock X on the modified resource. SQL Server uses the U Lock to avoid deadlocks. Because s locks are compatible with S locks, u locks are compatible with S locks, so that the update lookup does not affect data lookup, but u locks and U locks are not compatible, this reduces the possibility of deadlocks. This concept 12 is shown in.

Figure 12. If the U Lock is not available, the S lock and X lock modify data can easily cause a deadlock.

 

Intention lock (is, IU, IX): Intention lock is more like an indicator than a lock. In SQL Server, resources are hierarchical. A table can contain N pages, and a page can contain N rows. When a lock is added to a row. It can be understood that the page containing this row and a part of the table have been locked. When another Query Needs to lock the page or table, it is a bit painful to check whether the page and the data contained in the table are locked in another row. Therefore, when SQL Server locks a resource with a low granularity, it will add an intention lock to its parent resource to tell other users that a part of the resource has been locked. For example, when we update a row in a table, the page and table where the row is located will get the intention exclusive lock, as shown in Figure 13.

Figure 13. When updating a row, the page and table of the row will get the intention lock.

 

Other types of Schema locks, key range locks and large capacity update locks are not discussed in detail, see msdn (http://msdn.microsoft.com/zh-cn/library/ms175519.aspx)

 

The compatibility between locks Microsoft provides a detailed table, as shown in figure 14.

Figure 14. Lock compatibility list

 

Understand deadlocks

When both processes hold one or more locks, and the lock held by another process is incompatible with the lock obtained by another process view. A deadlock occurs. This concept 15 is shown in.

Figure 15. Brief description of deadlocks

 

Next we will simulate a deadlock based on the concept in Figure 15, as shown in Figure 16.

Figure 16. simulate a deadlock

 

As you can see, after a deadlock occurs, SQL server does not stand by and let the two processes wait infinitely, but chooses a transaction that is easier to roll back as the victim, and the other transaction can be executed normally.

 

Summary

This article briefly introduces the concept and principle of locks in SQL Server, as well as the granularity, mode, compatibility, and deadlock of locks. A thorough understanding of the lock concept is the basis for database performance optimization and deadlock resolution.

Sample Code