T-SQL query-understanding the lock in SQL Server

T-SQL query advanced-understanding the lock introduction in SQL Server, each query will find the shortest path to achieve their goals. 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.

T-SQL query advanced-understanding the lock introduction in SQL Server, each query will find the shortest path to achieve their goals. 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.

T-SQLQueryAdvanced-UnderstandingSQL Server locks

Introduction

In SQL ServerQueryYou can find the shortest path to achieve your goal. If the database only accepts one connection and only executes oneQuery. SoQueryOf course, it is necessary to complete the work quickly and easily. However, most databases need to process multipleQuery. TheseQueryIt will not wait in line like a gentleman, but will find the shortest path for execution. Therefore, SQL Server also needs a traffic light to tellQuery: When to leave, and when to leave. The traffic light is the lock.

.QueryNot queuing 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,QueryHe is not a gentleman, so he 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.

. 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. The previous DMV is used to view the locks.

It is worth noting that the DMV sys. dm_tran_locks is displayed inQueryThe database lock at the time point does not contain any history lock records. YesUnderstandingFor databases inQueryA snapshot of the time point locking status. 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)

. 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.

. Write the statement to extract the lock information we need

Use Profiler to capture lock Information

We can use Profiler to capture information about locks and deadlocks, as shown in 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.

. Filter out database lock Information

The captured information is 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.

ForQueryIn itself, the lock issue is not concerned. 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. For example, key distribution, number of request rows, row density,QueryConditions. 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	Resources dedicated to applications.
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.

0. Replace 6000 key locks with one table lock

Although a table lock replaces the 6000 key locks, it affects concurrency.QueryUpdate the row (id is 50001, not in 0)QueryWithin the range of), and found to cause blocking, as shown in 11.

1. 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. IfQueryThe requested resource lock is found to be compatible with the lock applied by the user.QueryBut if it is not compatible,QueryWill 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. That's why I'm in Figure 9 and 0QueryThe isolation level needs to be set to Repeatable read. The S lock can continue until the transaction ends only when the isolation level above the Repeatable read level is set or the prompt is used. In fact, you can add countless S locks 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 a 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. At this time, you canUnderstandingS 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.

2. If there is no U Lock, the S lock and the 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. YesUnderstandingThe page containing the row and part of the table have been locked. When anotherQueryWhen you need to lock a 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 otherQueryA 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.

3. When a row is updated, 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.

4. Lock compatibility list

UnderstandingDeadlock

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.

5. Brief description of deadlocks

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

6. 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. ThoroughUnderstandingThe lock concept is the basis for database performance optimization and deadlock resolution.

Sample Code