Analysis of SQL Server database transaction Lock Mechanism

Locks are network databases
One
A very important concept. It is mainly used to ensure databases in multi-user environments.
Integrity and consistency. Various large databases
The basic theories of the locks used are consistent.
But there are differences in implementation. Currently, most databases
Management systems are more or less self-tuned
Therefore, many users do not know the Locking Theory and the database used.
The specific implementation of the lock.


Microsoft SQL Server (hereinafter referred to as SQL Server) as a small and medium-sized Database
Management System has been widely applied
The system emphasizes that the system manages locks. When a user has an SQL request, the system analysis request is automatically used as a database between the locking condition and system performance.
Add the appropriate lock while the system is running
During this period, automatic optimization is often performed and dynamic locking is implemented. For general users, the system's automatic lock management mechanism can basically meet the usage requirements.
Integrity and consistency have special requirements.
You must control the database yourself.
And
This requires understanding of the SQL Server lock mechanism, master the database
Lock method.


Multi-granularity and lock upgrade

Database
A lock in is a software mechanism used
Indicates that a user (that is, a process session, the same below) has occupied a certain resource, so as to prevent other users from making data modifications that affect the user or cause the database
Data Integrity and non-consistency. This
Resources mainly refer to data rows, indexes, and data tables that users can operate on. Based on different resources, locks have the concept of multigranular, that is, resources that can be locked.
. The granularity of resources that can be locked in SQL Server includes: Database
, Table, region, page, and key value
Indexed row data) and row identifier (RID, that is, single row data in the table ).


Multi-granularity locks are used to support concurrent operations and ensure data integrity. SQL Server automatically sends an analysis to the database based on the user's request.
Add the appropriate lock. Assume that a user only operates
As part of the row data in a table, the system may only add several row locks (RID) or page locks, so as to support concurrent operations by multiple users as much as possible. However
Multiple record operations will add row-level locks to many record rows in the table.
The number of locks in the system increases dramatically.
This increases the system load and affects system performance. Therefore
In the system, lock upgrade is generally supported
(Lock escalation ). 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. It is particularly worth noting that in SQL
In 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


In the database
In addition
You can also use different lock modes to lock different resources. The lock modes in SQL Server include:

1. Shared lock


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
Server immediately releases the shared lock. For example, execute the query "SELECT * FROM
"My_table", first lock the first page, read, release the lock on the first page, and then lock the second page. In this way, you can modify the first page that is not locked during the reading operation. However, transactions
Both the 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 using shared locks, you can modify data in two steps:
A shared lock reads data, upgrades the shared lock to an exclusive lock, and then performs modification. In this way, if two or more transactions apply for a shared lock for one transaction at the same time, during data modification
Some transactions must upgrade the shared lock to an exclusive lock. 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 directly before modification
You can avoid deadlocks by upgrading to an exclusive lock. 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. during compilation and query, SQL
Server uses the sch-s lock.

5. Intention lock

Intention lock description SQL
The server has the intention to obtain a shared or 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 also be divided into shared intention locks,
Exclusive intent lock and shared intent lock. 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. Exclusive intention lock indicates that the transaction intention is locked in 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 the "table lock on
Bulk load "option to set batch modification locks.

In addition, SQL
The 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
In general, a deadlock means that multiple users (processes) Lock a resource respectively and attempt to lock the resources that the other user has locked. This produces a lock request ring, multiple users (processes) are waiting
Released the status of the locked resource.

In SQL
Server, the system can automatically regularly search for and handle deadlocks. The system identifies all Process sessions 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 recursive deadlock search.

When the lock request ring is detected in the search, the system ends 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 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}

Here, LOW indicates that the session priority of the process is LOW. In the case of a deadlock, the transaction of the process can be interrupted first. In addition, the LOCK_TIMEOUT option can be set for each process
The maximum wait time for the request to be locked. The setting statement:

SET LOCK_TIMEOUT {timeout_period}

Timeout_period is in milliseconds.


After understanding the concept of deadlock, you can use the following methods in the application to avoid deadlock as much as possible:

(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
To use dirty read.

(4) Time-Domain discretization for data access. The time-domain Discretization Method for Data Access refers to the use of various control measures in the Client/Server structure to control
Or Database
Object Access period in. Mainly through
The following methods are implemented:
Reasonably arrange the execution time of background transactions and use workflows to manage background transactions in a unified manner. When a workflow manages a task, it limits the number of threads of the same type of tasks (usually one) to prevent excessive resources from occupying
On the other hand, reasonably arrange the time sequence and time for different tasks to avoid simultaneous execution of multiple background tasks. In addition, avoid running background tasks during foreground transaction peaks.

(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. Mainly through
The following methods are implemented: 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 guarantee of Database
Data integrity and consistency.
User transaction isolation level. SQL92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If you choose a high isolation level, such as serializable, although the system can achieve better
Isolation ensures data integrity and consistency to a greater extent, but the chance of deadlocks due to conflicts between transactions is greatly increased, greatly affecting system performance.


(7) Use Bound Connections. Bound connections
Allow two or more transactions to connect to shared transactions and locks, and apply for locks for any transaction connection is the same as applying for locks for another transaction, 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. To avoid this situation
The above transactions 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 will not change before the update is applied, the optimizer will assign an exclusive table lock. Consider the following SQL code:

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
Prices = 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 looks odd.
Strange, but whether you believe it or not, this is the perfect and valid where clause encountered by the optimizer so far, although the calculated result is always false. When the optimizer processes this query, because it cannot be found
For any valid SARG, its query plan will force an exclusive lock to scan the table. When this transaction is executed, the where clause immediately obtains a false value, so it does not actually execute
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 consider: To avoid deadlock is
No is more important than allowing concurrent table access.

Manual locking

SQL
In the server system, it is recommended that the system automatically manage the lock. The system will analyze the user's SQL statement requirements, automatically add the appropriate lock for the request, and 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 applications and maintain data consistency
To lock a table. For example, in an application
In a transaction operation of the program, you need to perform statistical operations on several data tables according to a number. To ensure the consistency and correctness of the statistical data time, from the first table of statistics to the end of all tables, other applications or things
You cannot write data to these tables. At this time, the application wants to be artificially (explicitly) to lock these tables, you need to use
To manual locking (also known as explicit locking) technology.

In SQL Server
SQL statements (select, insert, delete, and update) support explicit locking. The four statements are similar in the explicit lock syntax. The following statements are only
Example Syntax:

Select from [with]


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, instead of blocking until other transactions release the lock. readpast only applies
Select statement operation 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.
The server releases the lock after the statement is executed. If both the 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: Specifies the update lock for data in the read table.
Instead of setting a shared lock, the lock remains until this statement or the end of the entire transaction. The role of updlock is to allow users to read data first (and not block other users from reading data), and
Ensure that the data is not modified by other users during this period of time when the data is updated later.

As shown in the preceding figure
SQL statements can be explicitly locked in a flexible and diverse manner in the Server. If appropriate, we can complete some special requirements of the program to ensure data consistency and integrity. For general users,
The unlock mechanism does not mean you must use it. In fact, SQL Server recommends that the system automatically manage databases.
And some lock settings
The options are not provided to users and databases.
Management
For special users
Medium
Explicit resource locking can meet high data consistency and reliability requirements, but you must pay special attention to avoid deadlock.