Analysis of transaction lock mechanism in ms SQL Server database

Analysis of ms SQL Server database transaction lock mechanism ■ Liu Yongming, Beijing Normal University

Locks are a very important concept in network databases. They are mainly used to ensure database integrity and consistency in multi-user environments. The base theory of the locks used by various large databases is consistent, but there are differences in implementation. Currently, most database management systems have more or less self-regulation and self-management functions. Therefore, many users do not know the lock theory and the specific implementation of the locks in the databases they use.

Microsoft SQL Server (hereinafter referred to as SQL Server), as a small and medium-sized database management system, has been widely used. It emphasizes that the system manages locks. When a user has an SQL request, the system analysis request automatically adds an appropriate lock to the database between the locking condition and the system performance. At the same time, the system often performs optimization during operation, implement Dynamic locking. Generally, users can use the system's automatic lock management mechanism to meet usage requirements. However, if there are special requirements for data security, database integrity, and consistency, you must control the locking and unlocking of the database by yourself. Therefore, you need to understand the locking mechanism of SQL Server and master the locking method of the database.

Multi-granularity and lock upgrade

A lock in a database is a software mechanism used to indicate that a user (that is, a process session, the same below) has occupied a certain resource, this prevents other users from modifying data that affects the current user or resulting in database data integrity and non-consistency. Resources mainly refer to data rows, indexes, and data tables that users can operate on. Depending on the resource, the lock has the concept of multigranular, that is, the level of resources that can be locked. The granularity of resources that can be locked in SQL Server includes: database, table, region, page, key value (the row data with an index), row identifier (RID, that is, a single row of data in the table ).

Multi-granularity locks are used to support concurrent operations and ensure data integrity. SQL Server automatically adds a proper lock to the database after analysis based on the user's request. Assuming that a user only operates part of the row data in a table, the system may add only a few row locks (RID) or page locks, so as to support concurrent operations by multiple users as much as possible. However, if your transactions frequently operate on multiple records in a table, row-level locks will be applied to many record rows in the table, and the number of locks in the database system will increase dramatically, this increases the system load and affects system performance. Therefore, lock escalation is generally supported in database systems ). The so-called lock upgrade refers to adjusting the lock granularity and replacing multiple low-granularity locks with a few higher-granularity locks to reduce the system load. In SQL Server, when there are many locks in a transaction and the lock upgrade threshold is reached, the system automatically upgrades row-level locks and page locks to table-level locks. In SQL Server, the threshold for Lock upgrade and lock upgrade are automatically determined by the system and do not need to be set by the user.

Lock mode and compatibility

When locking a database, in addition to locking different resources, you can also use different degrees of locking methods, that is, there are multiple lock modes. The lock modes in SQL Server include:

1. Shared lock

In SQL Server, the shared lock is used for all read-only data operations. The shared lock is exclusive and allows multiple concurrent transactions to read the locked resources. By default, after data is read, SQL Server immediately releases the shared lock. For example, when "select * From my_table" is queried, the first page is locked first. After reading, the first page is released and the second page is locked. In this way, you can modify the first page that is not locked during the reading operation. However, both the transaction isolation level connection option setting and the lock setting in the SELECT statement can change the default setting of SQL Server. For example, "select * From my_table holdlock" requires that the table be locked during the entire query process until the query is complete.

2. Modify the lock

The modification lock is used to lock resources that may be modified in the initialization phase of the modification operation. This can avoid deadlocks caused by the use of shared locks. When a shared lock is used, the data modification operation is divided into two steps. First, a shared lock is obtained, data is read, the shared lock is upgraded to an exclusive lock, and then the modification is performed. In this way, if two or more transactions apply for a shared lock for one transaction at the same time, these transactions must upgrade the shared lock to an exclusive lock when modifying data. At this time, these transactions will not release the shared lock, but will wait for the other party to release, resulting in a deadlock. If a data lock is applied for modification before modification, and is upgraded to an exclusive lock when the data is modified, the deadlock can be avoided. Modification locks are compatible with shared locks. That is to say, a resource can be locked by a shared lock and can be locked by a modified lock.

3. exclusive lock

An exclusive lock is retained to modify data. The resources it locks. Other transactions cannot be read or modified. The exclusive lock cannot be compatible with other locks.

4. Structure lock

Structural locks are divided into schema modification locks (Sch-m) and schema stability locks (Sch-S ). When the table definition language operation is executed, SQL server uses the sch-M lock. When compiling and querying, SQL server uses the sch-s lock.

5. Intention lock

Intention lock indicates that SQL Server has the intention to obtain a shared lock or an exclusive lock at the lower layer of the resource. For example, table-level shared intention locks indicate the transaction intention to release the exclusive lock to the pages or rows in the table. Intention locks can be divided into shared intention locks, exclusive intention locks, and shared exclusive intention locks. The shared intention lock indicates that the transaction intention is to place a shared lock on the lower-level resources locked by the shared intention lock to read data. An exclusive intention lock indicates that the transaction intends to place an exclusive lock on the lower-level resources locked by the shared intention lock to modify data. A shared exclusive lock indicates that a transaction allows other transactions to use a shared lock to read top-level resources and intends to place an exclusive lock on the lower layer of the resource.

6. Modify locks in batches

Use batch modification locks when copying data in batches. You can use the tablock prompt of the table or use the "Table lock on bulk load" option of sp_tableoption in the system stored procedure to set batch modification locks.

In addition, the SQL server command statement operation will affect the locking method, and the combination of statements can also generate different locks, as shown in the following table:

Lock conflicts and Prevention Measures

In the database system, a deadlock occurs when multiple users (processes) Lock a resource respectively and attempt to request to lock the resources that the other user has locked. This produces a lock request ring, as a result, multiple users (processes) are waiting for the other party to release the locked resources.

In SQL Server, the system can automatically search for and handle deadlocks on a regular basis. The system identifies all Process sessions that are waiting for the lock request in each search. If the identified process is still in the waiting state in the next search, SQL Server starts the recursive deadlock search.

(Connected to D21) when a search detects a lock request ring, the system will end a transaction with the lowest priority based on the deadlock priority of each process session. After that, the system rolls back the transaction and sends error message No. 1205 to the process. In this way, other transactions may continue to run. The statement for setting the deadlock priority is:

Set deadlock_priority {LOW | normal}

Low indicates that the session priority of the process is low. When a deadlock occurs, the transaction of the process can be interrupted first. In addition, the lock_timeout option can be set for each process to set the maximum waiting time for the process to be locked. The setting statement:

Set lock_timeout {timeout_period}

Timeout_period is in milliseconds.

Understand the concept of deadlock, in the ApplicationProgramYou can use the following methods to avoid deadlocks:

(1) Reasonably arrange the table access sequence.

(2) Avoid user intervention in transactions as much as possible, and minimize the number of tasks processed by a transaction.

(3) use dirty reading technology. Dirty reads avoid lock conflicts because they do not lock the accessed table. In the Client/Server application environment, some transactions are often not allowed to read dirty data, but under specific conditions, we can use dirty read.

(4) Time-Domain discretization for data access. The time-domain Discretization Method for Data Access refers to various control measures used in the Client/Server structure to control the access time periods for objects in databases or data libraries. The following methods are used to reasonably arrange the execution time of background transactions and manage background transactions using workflows. When a workflow manages a task, it restricts the number of threads of the same type of task (usually one) to prevent excessive resource occupation. On the other hand, it rationally schedules the time series and times of running different tasks, avoid executing multiple background tasks at the same time. In addition, avoid running background tasks at the front-end transaction peak hours.

(5) data storage space discretization method. The data storage space discretization method is used to distribute data in a logical table to several discrete spaces, so as to improve the table access performance. The following methods are used: first, a large table is divided into several small tables by row or column. Second, it is divided by different user groups.

(6) use the isolation level as low as possible. Isolation level refers to the process of isolating multi-user transactions to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency for higher isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance.

(7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.

(8) Consider using Optimistic Locking or giving the transaction an exclusive lock first. One of the most common deadlocks occurs in the serial number generator, which is usually written as follows:

Begin tran

Select new_id from keytab holdlock

Update keytab set new_id = new_id + L

Commit tran

If two users are running this transaction at the same time, they will get shared locks and keep it. When both users attempt to obtain the exclusive lock of the keytab table, a deadlock occurs. In order to avoid this situation, the above transaction should be rewritten into the following form:

Begin tran

Update keytab set new_id = new_id + L

Select new_id from keytab

Commit tran

After rewriting in this way, only one transaction can obtain the exclusive lock of the keytab, and other processes must wait until the completion of the first transaction. This increases the execution time, but avoids deadlocks.

If you want to have the read repeatability capability in a transaction, you must consider writing the transaction in this way to obtain the exclusive lock of the resource and then read the data. For example, if a transaction needs to retrieve the average price of all books in the titles table and ensure that the results do not change before update is applied, the optimizer will assign an exclusive table lock. Consider the following SQLCode:

Begin tran

Update titles set title_idid = title_id.

Where 1 = 2

If (selectavg (price) fromtitles)> $15

Begin

/* Perform some additional processing */

End

Update titles set price = price * 1.10

Where price <(select AVG (price) from titles)

Commit tran

In this transaction, it is important that no other process modifies the price of any row in the table, or the value retrieved at the end of the transaction is different from the value retrieved at the beginning of the transaction. The where clause here seems strange, but whether you believe it or not, this is the most perfect and effective where clause encountered by the optimizer so far, although the result is always false. When the optimizer processes this query, because it cannot find any valid Sarg, its query plan will force an exclusive lock to scan the table. When the transaction is executed, the where clause immediately obtains a false value, so it does not perform an actual scan, but the process still gets an exclusive table lock.

Because this process now has an exclusive table lock, it can ensure that no other transaction will modify any data rows and can perform repeated read, and avoid potential deadlocks caused by holdlock. However, to avoid deadlocks, it is impossible to pay no price. When table locking is used to minimize deadlocks, contention for table locking is also increased. Therefore, before implementing this method, you need to weigh: To avoid deadlocks is more important than allowing concurrent access to tables.

Manual locking

In the SQL server system, it is recommended that the system automatically manage the locks. The system will analyze the user's SQL statement requirements and automatically add the appropriate locks for the request. When the number of locks is too large, the system automatically performs lock upgrade. As mentioned above, the upgrade threshold is automatically configured by the system and does not require user configuration.

In practical applications, sometimes in order to correctly run the application and maintain data consistency, you must manually lock a table in the database. For example, in a transaction operation in an application procedure, you need to perform statistical operations on several data tables according to the ID. To ensure the consistency and correctness of the statistical data time, from the first table in statistics to the end of all tables, other applications or transactions cannot write data to these tables. At this time, the application wants to artificially (explicitly) Lock the tables starting from the first data table or starting the entire transaction, this requires the use of manual locking (also known as explicit locking) technology.

The SQL statements (select, insert, delete, and update) in SQL server support explicit locking. The four statements are similar to the explicit lock syntax. The following uses the SELECT statement as an example to show the Syntax:

Select
From
[
]

Where,
The lock type that needs to be added to the table when the statement is executed.
The specified lock types are as follows:

1. holdlock: Keep the shared lock on the table until the entire transaction ends, instead of releasing the added lock immediately after the statement is executed.

2. nolock: no shared or exclusive locks are added. When this option takes effect, uncommitted data or "dirty data" may be read. This option is only applicable to select statements.

3. paglock: Specify to add a page lock (otherwise, a table lock may be added ).

4. readcommitted: sets the transaction to the Read committed isolation level.

5. readpast: Skip the data rows that have been locked. This option will allow the transaction to skip the data rows that have been locked by other transactions when reading data, rather than blocking until other transactions release the lock, readpast is only applicable to select statement operations in transaction operations at the Read committed isolation level.

6. readuncommitted: equivalent to nolock.

7. repeatableread: Set the transaction to a read-only isolation level.

8. rowlock: Specifies the row-Level Lock.

9. serializable: sets the transaction to a serializable isolation level.

10. tablock: Table-level locks are used instead of Row-level or page-level locks. After the statement is executed, SQL Server releases the lock. If both holdlock is specified, the lock remains until the transaction ends.

11. tablockx: Specifies the use of the exclusive lock on the table. This lock can prevent other transactions from reading or updating data in the table until the statement or the entire transaction ends.

12. updlock: Specify to set update lock when reading data in the table instead of the shared lock. The lock remains until the statement or the entire transaction ends, updlock is used to allow users to read data first (and does not block other users from reading data), and ensure that when the data is updated later, during this period, the data was not modified by other users.

As can be seen from the above, SQL Server can flexibly and flexibly apply explicit locks to SQL statements. If appropriate, we can fulfill special requirements of some programs to ensure data consistency and integrity. For general users, the unlock mechanism does not mean that they must be used. In fact, SQL Server recommends that the system automatically manage the locks in the database, and some lock setting options are not provided to users and database administrators. For special users, by explicitly locking resources in the database, the database can meet high data consistency and reliability requirements. You only need to pay special attention to avoiding deadlock.

(Apsara stack 48th D21 and D22)