SQL lock type and lock mechanism

SQL Server lock type (SQL) Favorites
1.　　HOLDLOCK: Holds a shared lock on the table until the entire transaction ends, rather than releasing the added lock immediately after the statement is executed.  
2.　　NOLOCK: No shared and exclusive locks are added, and when this option is in effect, it is possible to read UNCOMMITTED read data or "dirty data", which is only applied to select statements.  
3.　Paglock: Specifies that page locks are added (otherwise, table locks may be added).  
4. The readcommitted performs the scan with the same lock semantics as the transaction running at the Read isolation level of the commit.　By default, SQL Server 2000 operates at this isolation level:  
5. READPAST: Skipping data rows that have been locked, this option will cause the transaction to read the data while skipping those rows that have been locked by other transactions, rather than blocking until other transactions release the lock, READPAST only applies to read　　Committed the SELECT statement operation in a transactional operation under the isolation level.  
6.　　ReadUncommitted: Equivalent to Nolock.  
7.　RepeatableRead: Sets the level of the transaction as repeatable read isolation.  
8.　　Rowlock: Use row-level locks instead of coarser-grained page-level and table-level locks.  
9. SERIALIZABLE: Performs a scan with the same lock semantics as transactions running at the serializable isolation level.　Equivalent to HOLDLOCK.  
10.　　TABLOCK: Specifies that a table-level lock is used instead of a row-level or page-level lock, and SQL Server releases the lock after the statement is executed, and if Holdlock is specified at the same time, the lock remains until the end of the transaction.  
11.　Tablockx: Specifies that an exclusive lock is used on the table, which prevents other transactions from reading or updating the table's data until the end of the statement or the entire transaction.  
12. UPDLOCK: Specifies that the update lock is set when reading data in the table (update lock) instead of the shared lock, which remains until the end of the statement or the entire transaction, and uses UPDLOCK to allow the user to read the data first (without blocking other users from reading the data). And it is guaranteed that the data will not be modified by other users for a period of time when the data is later updated.
-----------------

I. Why to introduce a lock

When multiple users concurrently operate on the database, the following data inconsistencies are present:

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

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

Sets the transaction isolation level.

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

Configure the lock granularity of an 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

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

Shared locks

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

Shared locks

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

Dead lock

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

Cross-read transactions use row versioning.
Use snapshot Isolation.
Use a bound connection.
-----------------------------
Analyze and resolve SQL Server deadlock issues (cont.)
Above, we solved the deadlock problem for that scene. This time, let's analyze why we have a deadlock. Look again at the two SP notation:
CREATE PROC p1 @p1 int as
SELECT C2, C3 from T1 WHERE C2 between @p1 and @p1 +1
GO
CREATE PROC p2 @p1 int as
UPDATE T1 SET c2 = c2+1 WHERE C1 = @p1
UPDATE T1 SET c2 = c2-1 WHERE C1 = @p1
GO

It's strange! P1 no insert, no delete, no update, just a SELECT,P2 is update. This and we said before, trans1 inside Updata a,update b;trans2 inside Upate b,update A, no welt ah!
So, what caused the deadlock?

    need to see the SQL deadlock information from the event log:
   spid X is running this query (line 2 of proc [P1], Inputbuf Fer "... EXEC p1 4 ... "):  
   select C2, c3 from T1 WHERE C2 between @p1 and @p1 +1
   spid Y is running this query (line 2 of proc [P2], InputBuffer "EXEC P2 4"):  
   update t1 SET c2 = c2+1 WHERE C1 = @p1
                 
   the SELECT is waiting-a Shared KEY lock on index t1.cidx.  the UPDATE holds a CO Nflicting X lock. 
   the UPDATE is waiting for a eXclusive KEY lock on index t1.idx1.   The SELECT holds a conflicting S lock.

First, let's look at P1 's execution plan. What do you think? The SET STATISTICS profile can be executed on, which can be done. Here's the P1 execution plan.
SELECT C2, C3 from T1 WHERE C2 between @p1 and @p1 +1
|--nested Loops (Inner Join, OUTER REFERENCES: ([Uniq1002], [t1].[ C1]))
|--index Seek (OBJECT: ([t1].[ IDX1]), SEEK: ([T1]. [C2] >= [@p1] and [T1]. [C2] <= [@p1]+ (1)) ORDERED FORWARD)
|--clustered Index Seek (OBJECT: ([t1].[ CIDX]), SEEK: ([T1]. [C1]=[t1]. [C1] and [uniq1002]=[uniq1002]) LOOKUP ORDERED FORWARD)

We see a nested loops, the first line, the ROWID that seek,seek out using the index T1.C2, in the second row, to find the entire row of data through a clustered index. What is it? Is the bookmark lookup Ah! Why? Because we need C2, C3 can not be fully indexed t1.c1 out, so we need to bookmark the search.
OK, we'll go on to see P2 's execution plan.
UPDATE T1 SET c2 = c2+1 WHERE C1 = @p1
|--clustered Index Update (OBJECT: ([t1].[ CIDX]), OBJECT: ([T1]. [idx1]), SET: ([T1]. [C2] = [Expr1004])
|--compute Scalar (DEFINE: ([expr1013]=[expr1013]))
|--compute Scalar (DEFINE: ([expr1004]=[t1].[ c2]+ (1), [expr1013]=case when case ...
|--top (ROWCOUNT est 0)
|--clustered Index Seek (OBJECT: ([t1].[ CIDX]), SEEK: ([T1]. [c1]=[@p1]) ORDERED FORWARD)

Seek by the clustered index finds a row and then begins the update. Note here that when update is applied, it will apply for an X lock for clustered index.

In fact, here we understand why update creates a deadlock on select. Update, will apply for a clustered index x lock, so blocked (note, not a deadlock!) The last clustered in the Select Index Seek. Where is the other half of the deadlock? Note Our SELECT statement, C2 exists in index IDX1, C1 is a clustered index CIDX. Here's the problem! We have updated C2 this value in P2, so SQL Server automatically updates the nonclustered index that contains the C2 column: Idx1. And where is IDX1? Just in the SELECT statement we just made. The change to this index column means that a row or rows of the index collection need to be rearranged and rearranged, requiring an X lock.
So ..., the problem has been found out.

To summarize, that is, if a query uses a nonclustered index to select data, it will hold an S lock on the nonclustered index. When there are some columns of select that are not on the index, it needs to find the row of the corresponding clustered index according to rowID, and then find the other data. At this point, in the second query, update is busy on the clustered index: Locate, locking, modify, and so on. But because a column that is being modified is a column of another nonclustered index, at this point it needs to change the information for that nonclustered index at the same time, which requires a second x lock on that nonclustered index. Select starts waiting for the x lock of the update, and update begins to wait for the select's S lock, deadlock, and so on the bird.

So why do we add a nonclustered index, and the deadlock disappears the bird? Let's take a look at the execution plan following the automatically added index above:
SELECT C2, C3 from T1 WHERE C2 between @p1 and @p1 +1
|--index Seek (OBJECT: ([deadlocktest].[ DBO]. [T1]. [_dta_index_t1_7_2073058421__k2_k1_3]), SEEK: ([Deadlocktest]. [dbo]. [T1]. [C2] >= [@p1] and [deadlocktest]. [dbo]. [T1]. [C2] <= [@p1]+ (1)) ORDERED FORWARD)

Well, there's no need for clustered index, because the increased coverage index is enough to select all of the information. It's so simple.

In fact, in SQL Server 2005, if you use Profiler to catch eventid:1222, then there will be a deadlock diagram, it is very intuitive to say.

The following methods help minimize deadlocks (for details, see SQL Server online Help, search: Minimize deadlocks.)

Access the object in the same order.
Avoid user interaction in a transaction.
Keep the transaction short and in a batch.
Use a lower isolation level.
Use the isolation level based on row versioning.
Set the READ_COMMITTED_SNAPSHOT database option to on so that committed read transactions use row versioning.
Use snapshot Isolation.
Use a bound connection.

---------------------------

Transferred from: http://blog.csdn.net/huwei2003/article/details/4047191

SQL lock type and lock mechanism