T-SQL query Advanced-Understanding locks in SQL Server

In SQL Server, each query will find the shortest path to achieve its own goal. If the database accepts only one connection at a time, only one query is executed. Then the query is, of course, M.F.B. s to complete the work. For most databases, however, multiple queries need to be processed at the same time. Instead of waiting for execution like a gentleman, these queries will find the shortest path to execute. So, just like a crossroads requires a traffic light, SQL Server also needs a traffic light to tell the query: When to go and when not to go. This traffic light is a lock.

Figure 1. Queries are not queued in order, as gentlemen do

Why do I need a lock

Before you begin to talk about locks, it is a simple matter of understanding the acid properties of transactions and transactions. You can see my previous article on acid. If you understand how transactions are effected, you should know that in the database, theoretically all transactions should be completely isolated. But in fact, the cost of full isolation is really too high (it must be a serialized isolation level to be completely isolated, a bit of concurrency ...). Therefore, SQL Server's default read commited is a good choice to strike a balance between isolation and concurrency.

SQL Server passes the lock, just like a traffic light at a crossroads, to tell all concurrent connections that at the same time those resources can be read and those resources can be modified. As mentioned earlier, the query itself is not a gentleman, so it needs to be supervised. When a transaction requires access to a resource that is not compatible with the lock, SQL Server blocks the current transaction to achieve the so-called isolation. Until the lock on the requested resource is freed, 2 is shown.

Figure 2. SQL Server implements concurrency by blocking

How to view locks

Understanding the locking of SQL Server at a certain point in time is undoubtedly an effective means of learning to lock and diagnose database deadlock and performance. The most common way to view database locks is in two ways:

Use sys.dm_tran_locks this DMV

SQL Server provides sys.dm_tran_locks this DMV to view the locks in the current database, as shown in the previous Figure 2 by this DMV.

It is worth noting that sys.dm_tran_locks this DMV sees a database lock at a point in time, and does not contain any history lock records. Can be understood as a snapshot of how the database locks up at the point of query time. The information contained in Sys.dm_tran_locks is divided into two categories, the information describing the resource in which the lock is located at the beginning of the resource, and another class of information that describes the requested lock itself, starting with request. As shown in 3. More detailed instructions can be viewed on MSDN (http://msdn.microsoft.com/en-us/library/ms190345.aspx)

Figure 3.sys.dm_tran_locks

This DMV contains more information, so usually we write statements to extract the information we need from this DMV. As shown in 4.

Figure 4. Write a statement to extract the lock information we need

Use Profiler to capture lock information

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

Figure 5 Capturing the lock information in the profiler

But by default, if not filtered, the lock information captured by the profiler contains locks inside SQL Server, which is very inconvenient for us to view the lock information, so we often need to filter the columns as shown in 6.

Figure 6: Filtering out the database lock information

The captured information is shown in 7.

Figure 7. Information captured by the profiler

the size of the lock

The lock is added to the database object. And the database object is granular, such as the same 1 units, 1 rows, 1 pages, 1 B-tree, 1 tables contain data is not a grain size at all. Therefore, the so-called lock granularity is the size of the resource in which the lock resides. The information for the resource is the information that starts with resource in Figure 3 earlier.

For the query itself, the problem of locks is not a concern. It's like you're driving and you don't care which intersection should have a traffic light. The granularity of the lock and the type of lock are controlled by SQL Server (you can use the lock hint, of course, but not recommended). Locks can clog the database, so larger locks cause more blocking, but because large-grained locks require fewer locks, they can improve performance. Small-grained locks reduce congestion because they lock in less resources, thereby increasing concurrency, while a large number of locks can cause performance degradation. So the granularity of the lock is shown in the relationship between performance and concurrency 8.

Figure 8: Effect of lock granularity on performance and concurrency

SQL Server determines the granularity of the locks that are added depends on many factors. such as the distribution of keys, the number of request lines, row density, query conditions and so on. But the specific condition is that Microsoft has not disclosed the secret. Developers don't have to worry about how SQL Server decides which locks to use. Because SQL Server has made the best choice.

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

is used to lock the row identifier of a single row in the heap.

Resource	Description
RID
Key	The row lock in the index that is used to protect the key ranges in a serializable transaction.
Page	8 KB pages in the database, such as data pages or index pages.
EXTENT	A contiguous set of eight pages, such as data pages or index pages.
HoBT	Heap or B-tree. A lock that is used to protect a B-tree (index) or heap data page in a table that does not have a clustered index.
Table	Includes the entire table for all data and indexes.
File	Database file.
Application	Application-specific resources.
METADATA	Metadata lock.
Allocation_unit	Allocation unit.
Database	Entire database.

Table 1. Granularity of locks in SQL Server

Upgrade of Locks

The relationship between the grain size and the performance of the lock is mentioned earlier. In fact, each lock accounts for 96 bytes of memory, and if there is a large number of small-grained locks, it takes up a lot of memory.

Let's take a look at an example, when we select hundreds of rows of data (a total of 3W rows), SQL Server adds a key lock corresponding to the number of rows, as shown in 9

Figure 9.341 Line, you need to use a 341 key lock

However, when the number of rows obtained increases, say 6000 (a total of more than 30,000 data in the table), if the use of 6,000 key locks, it will occupy about 96*6000=600k of memory, so in order to balance the performance and concurrency of the relationship between the SQL The server uses a table lock to replace 6,000 key locks, which is called lock escalation. As shown in 10.

Figure 10: Use a table lock instead of 6,000 key locks

Although the use of a table lock instead of 6,000 key locks, but it will affect the concurrency, we do not in the above query in the row update (ID is 50001, not in the scope of the query in Figure 10), found to cause blocking, 11 is shown.

Figure 11: Lock escalation improves performance to reduce concurrency at the expense of

Lock Mode

When SQL Server requests a lock, it chooses a pattern that affects the lock. The lock mode determines the level of compatibility of the lock with any other lock. If a query finds that the lock on the requested resource is compatible with the lock it is requesting, the query can be executed, but if it is incompatible, the query is blocked. Until the lock on the requested resource is freed. From a large class, locks in SQL Server can be categorized as follows:

Shared lock (S lock): used to read the lock added by the resource. A resource that has a shared lock cannot be modified. Shared locks are read by default and are released immediately. For example, if I read 100 data, I can imagine reading the first one, releasing the first one immediately, then locking the second data, releasing the second one, and locking the third one. And so on until the 100th article. This is why my query in Figures 9 and 10 requires that the isolation level be set to repeatable read, and that the S lock will last until the end of the transaction if you have set the level of isolation that can be read repeatedly or when using hints. In fact, you can add countless s locks on the same resource .

Exclusive lock (x Lock): incompatible with any other lock, including other exclusive locks. Exclusive locks are used for data modification, and when an exclusive lock is added to a resource, other transactions that request to read or modify the resource are blocked until the exclusive lock is released.

Update lock (U lock): U lock can be seen as a combination of S-lock and X-lock, used to update the data, update the data first need to find the updated data, at this time can be understood as the data found on the S lock. When the data that needs to be modified is found, the X lock on the resource being modified is required. SQL Server avoids deadlock problems with u locks. Because S-Lock and S-Lock are compatible, the U-Lock and S-lock are compatible to make the update lookup not affect the data lookup, and the U-Lock and U-Lock are not compatible, thus reducing the possibility of deadlock. This concept is shown in 12.

Figure 12: If you do not have a U lock, the S-Lock and X-Lock modification data can easily cause deadlocks

Intent Lock (IS,IU,IX): Intent lock is more like an indicator than a lock. In SQL Server, resources are hierarchical, one table can contain n pages, and one page can contain n rows. When we add a lock to a row. A page that can be understood as containing this line, and a part of the table has been locked. When another query needs to lock the page or table, it's a bit too painful to see whether the page and the data contained in the table are locked in a row. Therefore, when SQL Server locks a resource with a lower granularity, it adds an intent lock on its parent resource, telling other queries that a part of the resource is locked. For example, when we update a row in a table, both the page and the table are given an intent exclusive lock, as shown in 13.

Figure 13. When a row is updated, the page and table on which it is located get an intent lock

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

Compatibility between locks Microsoft provides a detailed table, shown in 14.

Figure 14. Lock Compatibility List

Understanding Deadlocks

When two processes hold one or a set of locks, while the lock held by another process is incompatible with the lock acquired by another process view. A deadlock will occur. This concept is shown in 15.

Figure 15: A simple schematic of a deadlock

Below we simulate a deadlock, as shown in Figure 15, 16.

Figure 16: Simulate a deadlock

As you can see, when a deadlock occurs, SQL Server does not stand idly by to let the two processes wait indefinitely, but instead chooses a more easily rollback transaction as the victim while another transaction executes normally.

Summary

This article briefly describes the concept of locks in SQL Server, the principles, and the granularity, pattern, compatibility, and deadlock of locks. A thorough understanding of the concept of locks is the basis of database performance tuning and the resolution of deadlocks.

T-SQL query Advanced-Understanding locks in SQL Server