Types of locks and their usage in SQL Server

I. Why to introduce a lock

Concurrent operations on a database by multiple users can lead to inconsistencies in the following data:

Missing updates
A, a, two users read the same data and modify it, and one of the user's modifications destroys the result of another modification, such as a booking system

Dirty Read
A user modifies the data, then the B user reads the data again, but a user cancels the modification to the data for some reason, the data restores the original value, at this time the data obtained by B is inconsistent with the data in the database.

Non-REPEATABLE READ
A user reads the data and then the B user reads the data and modifies it, at which point a user reads the data and finds that the values are inconsistent two times before

The main method of concurrency control is blocking, a lock is a period of time to prohibit users from doing certain operations to avoid inconsistent data

Classification of two locks

There are two kinds of categories of locks:

1. From the database system point of view: divided into exclusive lock (that is, lock it), share locks and update locks

Ms-sql Server uses the following resource lock mode.

Lock mode description
Share (S) is used for operations that do not change or update data (read-only operations), such as a SELECT statement.
The update (U) is used in updatable resources. Prevents common forms of deadlocks that occur when multiple sessions are read, locked, and subsequent resource updates are possible.
The row (X) is used for data modification operations such as INSERT, UPDATE, or DELETE. Ensure that no multiple updates are made to the same resource at the same time.
Intent locks are used to establish the hierarchy of locks. The type of intent lock is: intent sharing (IS), intent Exclusive (IX), and sharing with intent Exclusive (SIX).
Schema locks are used when performing operations that depend on the table schema. Schema locks are of the type: Schema modification (SCH-M) and schema Stability (sch-s).
Bulk Update (BU) is used when bulk copying data to a table and specifying the TABLOCK hint.

Shared locks
A shared (S) lock allows concurrent transactions to read (SELECT) a resource. When a shared (S) lock exists on a resource, no other transaction can modify the data. Once the data has been read, the shared (s) lock on the resource is freed immediately, unless the transaction isolation level is set to repeatable read or higher, or a shared (s) lock is reserved with a lock hint during the lifetime of the transaction.

Update lock
The update (U) lock can prevent the usual form of deadlocks. The general update pattern consists of a transaction that reads a record, gets the shared (S) lock of the resource (page or row), and then modifies the row, which requires the lock to be converted to an exclusive (X) lock. If two transactions acquire a shared-mode lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of a shared mode to an exclusive lock must wait for a period of time, because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; a lock wait occurs. The second transaction attempted to get an exclusive (X) lock to update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to release the shared-mode lock.

To avoid this potential deadlock problem, use the update (U) lock. Only one transaction at a time can obtain an update (U) lock on the resource. If the transaction modifies the resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared lock.

Exclusive lock
An exclusive (X) lock prevents concurrent transactions from accessing resources. Other transactions cannot read or modify data that is locked by an exclusive (X) lock.

Intent lock
An intent lock indicates that SQL Server needs to acquire a shared (S) or exclusive (X) lock on some underlying resource in the hierarchy. For example, a shared intent lock placed at a table level indicates that a transaction intends to place a shared (S) lock on a page or row in a table. Setting an intent lock at the table level prevents another transaction from subsequently obtaining an exclusive (X) lock on the table that contains that page. Intent locks can improve performance because SQL Server examines intent locks at the table level only to determine whether a transaction can safely get locks on the table. Instead of checking each row in the table or the lock on each page to determine whether the transaction can lock the entire table.

Intent locks include intent sharing (IS), intent Exclusive (IX), and sharing with intent Exclusive (SIX).

Lock mode description
Intent Sharing (IS) indicates that the intention of a transaction is to read some, but not all, of the underlying resource in the hierarchy by placing S locks on each resource.
Intent Exclusive (IX) by placing an X lock on each resource, the intention of the transaction is to modify some, but not all, of the underlying resource in the hierarchy. IX is a superset of IS.
Share with intent Exclusive (SIX) by placing an IX lock on each resource, the intention of the transaction is to read all the underlying resources in the hierarchy and modify some, but not all, of the underlying resources. Allows concurrent is locks on top-level resources. For example, the six lock on the table places a six lock on the table (allowing concurrent is locks), and an IX lock on the currently modified page (X lock placed on the modified row). Although each resource can have only one SIX lock for a period of time to prevent other transactions from updating the resource, other transactions can read the underlying resource in the hierarchy by getting the IS lock at the table level.

Exclusive Lock: Only the program that allows the lock operation is used, and nothing else will be accepted for his operation. When you perform a data update command, SQL Server automatically uses exclusive locks. An exclusive lock cannot be added to an object when there are other locks on it.
Shared Lock: A resource with a shared lock lock can be read by another user, but other users cannot modify it, and when a select is executed, SQL Server adds a shared lock to the object.
Update Lock: When SQL Server prepares to update data, it first locks the data object as an update lock, so that the data cannot be modified but can be read. When SQL Server determines that an update data operation is to be made, he automatically changes the update lock to an exclusive lock, which cannot be updated when there are other locks on the object.

2. From the programmer's point of view: It is divided into optimistic and pessimistic lock.
Optimistic lock: The job of managing locks depends entirely on the database.
Pessimistic Lock: The programmer manages the lock handling on the data or object itself.

Ms-sqlserver uses locks to implement pessimistic concurrency control across multiple users who are simultaneously performing modifications within the database

The grain size of the three locks
The lock granularity is the size of the blocked target, the blocking granularity is high concurrency, but the overhead is large, the blockade granularity is large, the concurrency is low but the cost is small.

The lock granularity supported by SQL Server can be classified as a row, page, key, key range, index, table, or database acquisition lock

Resource Description
The RID row identifier. Used to lock a row in a table separately.
The row lock in the key index. Used to protect the range of keys in a serializable transaction.
Page 8,000 kilobytes (KB) of the Data page or index page.
A group of eight data pages or index pages that are adjacent to the extents.
The table includes the entire table, including all data and indexes.
DB database.

The length of the four locking time

The length of time the lock is held is the length of time required to protect resources at the requested level.

The hold time of a shared lock used to protect read operations depends on the transaction isolation level. When using the default transaction isolation level of Read COMMITTED, the shared lock is only controlled during the time the page is read. In the scan, the lock is released until the lock is acquired on the next page within the scan. If you specify HOLDLOCK hint or set the transaction isolation level to repeatable READ or SERIALIZABLE, the lock is not released until the end of the transaction.

Depending on the concurrency option set for the cursor, the cursor can obtain a scroll lock in shared mode to protect the extraction. When a scroll lock is required, the scroll lock is released until the next time the cursor is fetched or closed (whichever occurs first). However, if you specify HOLDLOCK, the scroll LOCK is not released until the end of the transaction.

The exclusive lock that is used to protect the update is not released until the end of the transaction.
If a connection attempts to acquire a lock that conflicts with a lock that is controlled by another connection, the connection attempting to acquire the lock is blocked until:

The conflict lock is freed and the connection acquires the requested lock.

The time-out interval for the connection has expired. There is no time-out interval by default, but some applications set a time-out interval to prevent waiting indefinitely

Five customizations of locks in SQL Server

1 handling deadlocks and setting deadlock priority

Deadlocks are multiple users requesting different blockades, as the applicant has a part of the blockade and waits for a partial blockade that other users have.

You can use set Deadlock_priority to control how the session reacts when a deadlock condition occurs. If two processes lock the data, and until other processes release their locks, each process can release its own lock, that is, a deadlock condition occurs.

2 processing time-out and setting the lock time-out duration.

@ @LOCK_TIMEOUT Returns the current lock time-out setting for the current session in milliseconds

The set LOCK_TIMEOUT setting allows the application to set the maximum amount of time that the statement waits for blocking resources. When the statement waits longer than the LOCK_TIMEOUT setting, the system automatically cancels the blocked statement and returns the 1222th error message for the application that has exceeded the lock request time-out period

Example
The following example sets the lock time-out period to 1,800 milliseconds.
SET lock_timeout 1800

3) Set the transaction isolation level.

4) Use table-level locking hints for SELECT, INSERT, UPDATE, and DELETE statements.

5) Configure the lock granularity of the index
You can use the sp_indexoption system stored procedure to set the lock granularity for indexing

Six-View lock information

1 Execute EXEC sp_lock report information about the lock
2 Information about locks can be seen in Query Analyzer by pressing CTRL+2

Seven usage precautions

How to avoid deadlocks
1 when using transactions, try to shorten the logical processing of the transaction, commit or rollback the transaction as soon as possible;
2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes-10 minutes; Over time, automatically abandon this operation, to avoid the process of hanging;
3 Optimize the program, check and avoid the deadlock phenomenon;
4. Carefully test all scripts and SPS before it is the version.
5 All SPS must have error handling (via @error)
6 generally do not modify the default level of SQL Server transactions. Forced lock is not recommended

Fix the problem how to lock the row table database

Eight questions about the lock

1 How to lock a row in a table

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * FROM table rowlock WHERE id = 1

2 locking a table for a database

SELECT * from table with (HOLDLOCK)

Add lock Statement:
Sybase:
Update table set col1=col1 where 1=0;
MSSQL:
Select col1 from table (Tablockx) where 1=0;
Oracle
LOCK table table in EXCLUSIVE MODE;
Locking after the other person is not operational until the lock user unlocked, with a commit or rollback unlock


A few examples to help you deepen your impressions
Set table1 (a,b,c)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

1) Exclusive lock
Create a new two connection
Execute the following statement in the first connection
BEGIN Tran
Update table1
Set a= ' AA '
where b= ' B2 '
WAITFOR DELAY ' 00:00:30 '--wait 30 seconds
Commit Tran
Execute the following statement in a second connection
BEGIN Tran
SELECT * FROM table1
where b= ' B2 '
Commit Tran

If both statements are executed at the same time, the select query must wait for 30 seconds for the update to complete before executing

2) shared lock
Execute the following statement in the first connection
BEGIN Tran
SELECT * FROM table1 holdlock-holdlock manual lock
where b= ' B2 '
WAITFOR DELAY ' 00:00:30 '--wait 30 seconds
Commit Tran

Execute the following statement in a second connection
BEGIN Tran
Select A,c from table1
where b= ' B2 '
Update table1
Set a= ' AA '
where b= ' B2 '
Commit Tran

If both statements are executed at the same time, the select query in the second connection can perform a
and update must wait 30 seconds for the first transaction to release the shared lock to the lock before it can execute

3) Deadlock
Additional table2 (d,e)
D E
D1 E1
D2 E2
Execute the following statement in the first connection
BEGIN Tran
Update table1
Set a= ' AA '
where b= ' B2 '
WAITFOR DELAY ' 00:00:30 '
Update table2
Set d= ' D5 '
Where e= ' E1 '
Commit Tran

Execute the following statement in a second connection
BEGIN Tran
Update table2
Set d= ' D5 '
Where e= ' E1 '
WAITFOR DELAY ' 00:00:10 '
Update table1
Set a= ' AA '
where b= ' B2 '
Commit Tran

While executing, the system detects a deadlock and aborts the process

Add one point:
Table-level locking hints supported by SQL Server2000

HOLDLOCK holds a shared lock until the entire transaction is complete and should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level

NOLOCK statement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation LEVEL

Paglock with multiple page locks where a table lock is used

READPAST let SQL Server skip any locking lines, perform transactions, apply to READ UNCOMMITTED transaction isolation LEVEL only skip RID lock, skip page, zone and table lock

Rowlock forcing the use of row locks

Tablockx enforces exclusive table-level locks, which prevent any other transactions from using this table during a transaction

Uplock forcing updates to be used when reading tables without sharing locks

Application Lock:
The application lock is the lock generated by the client code, not the lock generated by SQL Server itself

Two procedures for handling application locks

Sp_getapplock Locking Application Resources

Sp_releaseapplock Unlocking an application resource

Note: The difference between a table that locks a database

SELECT * from table with (HOLDLOCK) Other transactions can read the table but cannot update the delete

SELECT * from table with (Tablockx) Other transactions cannot read table, update and delete

Types of locks and their usage in SQL Server