Analysis of transaction lock mechanism in ms SQL Server database

Symptom:
Create Three query statements in the query Analyzer
Begin tran
Select * From Table1 with (tablock, updlock)

After each execution,

Block the original query statement in the first query analyzer Interface
Then write
Commit tran

At this time, the second query interface or the third query interface will query the data of Yao Xiang, but the other one will see the deadlock information.

The following lock mechanism: (originated from www.aspxuexi.com)

Locks are a very important concept in network databases. They are mainly used to ensure database integrity in multi-user environments.
And consistency. The basic theories of locks used by various large databases are consistent, but their implementations vary.
. Currently, most database management systems have more or less self-regulation and self-management functions. Therefore, many
The user does not know the theory of the lock and the specific implementation of the lock in the database used.

Microsoft SQL Server (hereinafter referred to as SQL Server), as a small and medium database management system, has
It has been widely used, and the system emphasizes the management of locks by the system. System Analysis requests when users have SQL requests
Automatically adds an appropriate lock to the database between the lock conditions and system performance, and the system is often
It is often automatically optimized to implement dynamic locking. For general users, the system automatically locks the management.
The mechanism can basically meet the usage requirements, but 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 database.
Lock method.

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
To prevent other users from modifying data that affects the user or resulting in incomplete database data.
And non-consistency. Resources mainly refer to data rows, indexes, and data tables that users can operate on. Root
According to different resources, 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, and key value
Cited row data), 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
User requests. After analysis, the database is automatically locked. Assume that a user only operates part of a table
Row data, the system may only add several row locks (RID) or page locks, so as to support as many users as possible
. However, if your transactions frequently operate on multiple records in a table
Row-level locks are applied to many record rows, and the number of locks in the database system increases sharply, increasing the system's negative
Load, affecting system performance. Therefore, lock escalation is generally supported in database systems ). Institute
Lock upgrade refers to adjusting the lock granularity and replacing multiple low-granularity locks with a few higher-granularity locks to reduce
Low system load. In SQL Server, when there are many locks in a transaction and the lock upgrade threshold is reached, the system will automatically
Row-level locks and page locks are upgraded to table-level locks. It is particularly worth noting that in SQL Server, the lock upgrade threshold is
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, 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 tasks.
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, and the first page is released.
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, both the transaction isolation level connection option setting and the lock setting in the SELECT statement can change the SQL server's
This is the default setting. For example, "select * From my_table holdlock" requires that the entire Query Process
, Keep the table locked 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, so as to avoid sharing.
The deadlock caused by the lock. When using the shared lock, you can modify the data in two steps. First, you can obtain a shared
Lock, read data, upgrade the shared lock to an exclusive lock, and then perform the modification operation. In this way, if there are two
One or more transactions apply for a shared lock for one transaction at the same time. When modifying data, these transactions must be shared.
The lock is upgraded to an exclusive lock. At this time, these transactions will not release the shared lock, but will wait for the other party to release.
It becomes a deadlock. If a data lock is applied for modification before modification, it is upgraded to an exclusive lock when the data is modified.
To avoid deadlocks. The modification lock is compatible with the shared lock, that is, after a resource is locked with the shared lock
You may use the modification lock to lock the instance.

3. exclusive lock

An exclusive lock is retained to modify data. The resources it locks. Other transactions cannot be read or modified. Exclusive
The 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 ). SQL
The server uses the sch-M lock. during compilation and query, the 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 sharing
The intention lock indicates that the transaction intends to release the exclusive lock to a page or row 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 intent is at the lower layer locked by the shared intention lock.
Put a shared lock on the resource to read data. Exclusive intention lock indicates that the transaction intention is at the lower level locked by the shared intention lock.
Place an exclusive lock on the source to modify the data. Shared exclusive locks indicate that transactions allow other transactions to use shared locks to read the top
Layer resource, 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 system stored procedure sp_t.
The "Table lock on bulk load" option of ableoption sets batch modification locks.

In addition, SQL Server command statement operations will affect the locking method, and statement combinations can also generate different locks.
As shown in the following table:

Lock conflicts and Prevention Measures

In the database system, a deadlock means that multiple users (processes) Lock one resource separately and attempt to request the lock.
The other party has locked the resource, which generates a lock request ring, resulting in multiple users (processes) waiting
The status of the target resource.

In SQL Server, the system can automatically search for and handle deadlocks on a regular basis. The system identifies all
If the identified process is still in the waiting status in the next search
The server starts recursive deadlock search.

(Connected to D21) when the search detects a lock request ring, the system will give priority to the deadlock priority of each process session.
End a transaction with the lowest priority. 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. Another
In each process, the lock_timeout option can be set to set the maximum waiting time of the process in the locked request State.
. 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/service
In the 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. In the Client/Server structure
Control means to control the access time period of objects in the database or database. This can be achieved through the following methods:
Schedules the execution time of background transactions and uses workflows to manage background transactions in a unified manner. When a workflow is managing a task
On the one hand, limit the number of threads of the same type of tasks (usually limited to 1) to prevent excessive resource occupation; on the other hand, limit
Schedule different tasks to run at the same time. Avoid multiple background tasks at the same time.
Run background tasks during peak hours.

(5) data storage space discretization method. The data storage space discretization method refers to the use of various means to logically put a table
Data in the table is distributed to several discrete spaces to improve the table access performance. Use the following methods to implement
Now: first, the Big 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 is used to ensure the integrity and consistency of database data.
To isolate multi-user transactions, sql92 defines four isolation levels: uncommitted read, committed read, and repeatable
Read and serializable. If the isolation level is too high, such as serializable, although the system can achieve better isolation
To a greater extent, data integrity and consistency are ensured. However, the opportunity for deadlocks due to conflicts between transactions is greatly increased.
The system performance is greatly affected.

(7) Use bound connections. Bound connections allows two or more transaction connections to share transactions
And lock, and any transaction connection needs to apply for a lock as another transaction needs to apply for a lock, so you can allow
These transactions 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
In series generators, they are 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
When you try to obtain the exclusive lock of the keytab table, the deadlock will occur. To avoid this situation
The transaction is rewritten as follows:

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. Other processes must wait until the first
The completion of the transaction increases the execution time, but avoids deadlocks.

If you want to have the read-repeatable ability in a transaction, you must consider writing the transaction in this way to obtain
Obtain the exclusive lock of the resource and then read the data. For example, if a transaction needs to retrieve all
The average price of the book, and ensure that the results will not change before the update is applied, the optimizer will assign an exclusive
The table is locked. 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 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 checks the price at the end of the transaction.
The value of the cable is different from the value retrieved at the beginning of the transaction. The where clause here looks strange, but whether you believe
No, this is the perfect and valid where clause encountered by the optimizer so far, although the calculated result is always Fals
E. When the optimizer processes this query, because it cannot find any valid Sarg, its query plan will be forcibly used.
An exclusive lock is used for table scanning. When this transaction is executed, the where clause immediately gets a false value, so no
Scan is performed, but the process still gets an exclusive table lock.

Because this process now has an exclusive table lock, it can ensure that no other transactions will modify any data rows,
It can be read repeatedly and avoids potential deadlocks caused by holdlock. However, to avoid deadlocks, do not
No cost. 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 deadlocks, whether to allow concurrent access to tables
Question is more important.

Manual locking

In SQL server, it is recommended that the system automatically manage the lock. The system will analyze the user's SQL statement requirements and automatically
The system automatically performs a lock upgrade when the number of locks is too large. As mentioned above, upgrade
The threshold is automatically configured by the system and does not need to be configured by the user.

In practical applications, sometimes in order to correctly run applications and maintain data consistency
To lock a table. For example, in a transaction operation of an application, you need
To ensure the consistency and correctness of the statistical data time, start from counting the first table to completing all the tables.
Other applications or transactions cannot write data to these tables. At this time, the application wants
Count the tables starting from the first data table or starting the entire transaction, which can be artificially (explicitly) locked by the program
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. These four languages
The syntax for explicit locking is similar. The following uses the SELECT statement as an example to explain the 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 immediately after the statement is executed.
The added lock.

2. nolock: no shared or exclusive locks are added. When this option takes effect, you may read uncommitted data or
Dirty data ", this option is only applied 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 rows that have been locked when reading data.
The data row that the transaction locks, instead of blocking until other transactions release the lock, readpast is only applied to read
Select statement operation in transaction operations at the 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. SQL Server executes
After the row ends, the lock is released. If both the holdlock is specified, the lock remains until the transaction ends.

11. tablockx: Specify to use the exclusive lock on the table. This lock can prevent other transactions from reading or updating the number of tables.
Until the statement or the entire transaction ends.

12. updlock: Specify the update lock when reading data in the table, instead of the shared lock,
The lock is maintained until this statement or the end of the transaction. The function of using updlock is to allow the user to read data first (
Does not block other users from reading data), and ensures that the data will be updated later.
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 fully
It can fulfill special requirements of some programs 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
Locks, and some lock settings are not provided to users and database administrators. For special users,
By explicitly locking resources in the database, high data consistency and reliability requirements can be met.
Pay special attention to avoid deadlock.