Knowledge of SQL Server locks

Server

Why should I introduce a lock?

Concurrent operations on a database by multiple users can cause the following data inconsistencies:

Missing updates
A,b two users read the same data and make changes, one of the user's changes to destroy the result of another change, such as booking system

Dirty Read
A the user modified the data, then B users read out the data, but a user for some reason to cancel the modification of the data, data restore the original value, at this time the data obtained by B and the data in the database produced inconsistent

Do not read repeatedly
A the user reads the data, then the B user reads out the data and modifies it, at which time a user reads the data and finds that the value is inconsistent two times.

The main method of concurrency control is blocking, which prevents users from doing certain operations over a period of time to avoid data inconsistency

Classification of two locks

There are two types of classification of locks:
1. From the perspective of the database system: divided into exclusive locks (i.e. exclusive locks), shared locks and update locks

Ms-sql Server uses the following resource lock modes.

Lock mode description
Shares (S) are used for operations that do not change or do not update data (read-only operations), such as a SELECT statement.
Update (U) is used in updatable resources. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and subsequent resource updates that might occur.
Exclusive (X) for data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that you do not have multiple updates for the same resource at the same time.
Intent locks are used to establish a hierarchy of locks. The type of intent lock is: intent Share (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).
Schema locks are used when performing operations that depend on a table schema. Schema locks are of type: Schema modification (SCH-M) and schema Stability (sch-s).
Bulk Update (BU) is used when bulk copying data to a table and specifying TABLOCK prompts.

Shared locks
Shared (S) locks allow 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 released immediately, unless the transaction isolation level is set to repeatable read or higher, or a shared (s) lock is retained with a lock hint during the transaction lifetime.
Update lock
Update (U) locks can prevent deadlocks in the usual form. The general update mode consists of a transaction that reads records, gets a shared (S) lock on a resource (page or row), and then modifies the row, which requires the lock to be converted to an exclusive (X) lock. If two transactions obtain a shared schema 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 shared mode to exclusive lock must wait for some time because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; The second transaction attempted to obtain an exclusive (X) lock for an update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to free a 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 a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared lock.

Exclusive lock
Exclusive (X) locks prevent concurrent transactions from accessing resources. Other transactions cannot read or modify data in exclusive (X) lock locks.

Intent lock
Intent locks indicate that SQL Server needs to acquire shared (S) or exclusive (X) locks on some of the underlying resources in the hierarchy. For example, a shared intent lock placed at the 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 acquiring an exclusive (X) lock on the table containing that page. Intent locks can improve performance because SQL Server checks intent locks only at the table level to determine whether a transaction can safely acquire locks on that table. Instead of checking each row in the table or the locks on each page to determine whether the transaction can lock the entire table.

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

Lock mode description
Intent Sharing (IS) indicates that the intent of the transaction is to read the underlying resource in the hierarchy (not all) by placing an S lock on each resource.
Intent Exclusive (IX) by placing an X lock on each resource, the intent of the transaction is to modify the underlying resources in the hierarchy, not all of them. IX is a superset of IS.
With intent exclusive sharing (SIX) by placing an IX lock on each resource, the intent of the transaction is to read all the underlying resources in the hierarchy and modify the partial (not all) underlying resources. Allow concurrent is locks on top-level resources. For example, a table's SIX lock places a SIX lock on the table (which allows concurrent is locks) and an IX lock on the currently modified page (placing an X lock 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 obtaining the table-level is lock.

Exclusive locks: Only programs that allow locking operations are used, and any other action against him will not be accepted. When you perform a data update command, SQL Server automatically uses exclusive locks. You cannot add exclusive locks to an object when there are other locks on it.
Shared locks: A resource that is locked by a shared lock can be read by another user, but cannot be modified by another user, and SQL Server adds a shared lock to the object when the select is executed.
Update Lock: When SQL Server prepares to update data, it first updates lock locks on data objects so that the data cannot be modified but can be read. When SQL Server determines that the update data operation is to be performed, he automatically converts the update lock to an exclusive lock, and cannot update the lock when there are other locks on the object.
2. From the programmer's point of view: divided into optimistic lock and pessimistic lock.
Optimistic locking: The task of relying entirely on the database to manage locks.
Pessimistic Lock: The programmer manages the lock handling on data or objects themselves.

Ms-sqlserver uses locks to implement pessimistic concurrency control among multiple users who perform modifications simultaneously within a database

The granularity of three locks
Lock granularity is blocked target size, blocking granularity is high concurrency, but the cost is large, blocking the size of a large degree of concurrency is low but the cost of small

The lock granularity supported by SQL Server can be divided into rows, pages, keys, key ranges, indexes, tables, or databases to acquire locks

Resource Description
The RID line identifier. Used to lock a row in a table individually.
The row lock in the key index. Used to protect the range of keys in a serializable transaction.
Page 8,000 bytes (KB) of data pages or index pages.
A set 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-lock time

The length of time that a lock is maintained is the length of time required to protect resources at the requested level.

The retention time of the shared lock used to protect the read operation depends on the transaction isolation level. When the default transaction isolation level of the Read committed is used, only shared locks are controlled during the page reading period. In the scan, the lock is not released until the lock is acquired on the next page in the scan. If you specify a 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 options set for the cursor, the cursor can obtain a scroll lock in the shared mode to protect the extraction. When a scroll lock is required, the scroll lock is released until the next fetch or close cursor (whichever occurs first). However, if you specify HOLDLOCK, the scroll LOCK is not released until the end of the transaction.

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

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

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

Customization of locks in five SQL Server

1 Handling deadlocks and setting the deadlock priority

Deadlocks are the endless waits for multiple users to apply for a different blockade because the applicant has a part of the blockade and waits for a partial blockade owned by another user.

You can use set Deadlock_priority to control how the session responds when a deadlock situation occurs. If two processes lock the data, and until the other process releases its own lock, each process can release its own lock, a deadlock situation.

2 processing timeout and setting the lock timeout duration.

@ @LOCK_TIMEOUT Returns the current lock timeout setting for the current session in milliseconds

The set LOCK_TIMEOUT setting allows the application to set the maximum time that a statement waits for a blocked resource. When the statement waits longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled and the 1222th error message is returned to the application that has exceeded the lock request timeout 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.
See http://expert.csdn.net/Expert/topic/1785/1785314.xml?temp=.3050501

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

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

Six View the lock information

1 Executive EXEC sp_lock report information about the lock
2 Click Ctrl+2 to see the lock information in Query Analyzer

Seven uses to notice the matter

How to avoid deadlocks
1 use transactions, to minimize the logic of the transaction process, early to commit or roll back the transaction;
2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes-10 minutes, over time, automatically give up the operation, to avoid process suspension;
3 optimization program, check and avoid deadlock phenomenon;
4. All scripts and SPS should be carefully tested 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 locking is not recommended

Resolving problems how to lock a row table database

Eight questions about the lock

1 How to lock a row of a table

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * FROM table rowlock WHERE id = 1

2 lock a table in 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 in EXCLUSIVE MODE;
Lock after the other person can not operate until the lock user unlocked, with a commit or rollback unlock

A few examples to help you deepen your impression
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 the second connection
BEGIN Tran
SELECT * FROM table1
where b= ' B2 '
Commit Tran

If both of these statements are executed at the same time, the select query must wait 30 seconds for the update to complete before it can be executed.

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

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

If both of these statements are executed concurrently, the select query in the second connection can perform
The update must wait 30 seconds for the first transaction to release the shared lock before it can be executed.

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

Execution at the same time, the system detects a deadlock and aborts the process
Customization of locks in five SQL Server

1 Handling deadlocks and setting the deadlock priority

Deadlocks are the endless waits for multiple users to apply for a different blockade because the applicant has a part of the blockade and waits for a partial blockade owned by another user.

You can use set Deadlock_priority to control how the session responds when a deadlock situation occurs. If two processes lock the data, and until the other process releases its own lock, each process can release its own lock, a deadlock situation.

2 processing timeout and setting the lock timeout duration.

@ @LOCK_TIMEOUT Returns the current lock timeout setting for the current session in milliseconds

The set LOCK_TIMEOUT setting allows the application to set the maximum time that a statement waits for a blocked resource. When the statement waits longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled and the 1222th error message is returned to the application that has exceeded the lock request timeout 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.
See http://expert.csdn.net/Expert/topic/1785/1785314.xml?temp=.3050501

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

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

Six View the lock information

1 Executive EXEC sp_lock report information about the lock
2 Click Ctrl+2 to see the lock information in Query Analyzer

Seven uses to notice the matter

How to avoid deadlocks
1 use transactions, to minimize the logic of the transaction process, early to commit or roll back the transaction;
2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes-10 minutes, over time, automatically give up the operation, to avoid process suspension;
3 optimization program, check and avoid deadlock phenomenon;
4. All scripts and SPS should be carefully tested 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 locking is not recommended

Resolving problems how to lock a row table database

Eight questions about the lock

1 How to lock a row of a table

SET TRANSACTION Isolation Level READ UNCOMMITTED

SELECT * FROM table rowlock WHERE id = 1

2 lock a table in 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 in EXCLUSIVE MODE;
Lock after the other person can not operate until the lock user unlocked, with a commit or rollback unlock

A few examples to help you deepen your impression
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 the second connection
BEGIN Tran
SELECT * FROM table1
where b= ' B2 '
Commit Tran

If both of these statements are executed at the same time, the select query must wait 30 seconds for the update to complete before it can be executed.

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

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

If both of these statements are executed concurrently, the select query in the second connection can perform
The update must wait 30 seconds for the first transaction to release the shared lock before it can be executed.

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

Execution at the same time, the system detects a deadlock and aborts the process