SQL lock type and lock mechanism

SQL Server lock type (SQL) favorites
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 performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this 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, rather than blocking until other transactions release the lock, readpast is only applicable to select statement operations 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: Use row-level locks instead of coarse-grained page-level locks and table-level locks.
9. serializable: scan with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.
10. tablock: Table-level locks are used instead of Row-level or page-level locks. After the statement is executed, SQL Server releases the lock. If both 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: Specify the update lock when reading data in the table, instead of the shared lock. The lock remains until this statement or the entire transaction ends, updlock is used to allow users to read data first (and does not block other users from reading data), and ensure that when the data is updated later, during this period, the data was not modified by other users.
-----------------

I. Why should I introduce locks?

When multiple users concurrently perform database operations, the following data inconsistency occurs:

◆ Update loss

A and B read and modify the same data. The Modification result of one user destroys the Modification result of another user, such as the ticket booking system.

◆ Dirty read

User a modified the data, and user B then read the data again. However, user a canceled the data modification and restored the original value for some reason, the data obtained by B is inconsistent with the data in the database.

◆ Non-repeated read

User A reads the data, and user B then reads the data and modifies the data. At this time, user a reads the data again and finds that the values of the first and second times are inconsistent.

The main method of concurrency control is blocking. The lock is to prohibit users from performing some operations within a period of time to avoid data inconsistency.

Binary lock Classification

◆ There are two lock types:

1. From the perspective of the database system: exclusive locks (exclusive locks), shared locks, and update locks

MS-SQL server uses the following resource lock modes.

Lock mode description

Share (s) is used for operations without changing or updating data (read-only operations), such as select statements.

Update (u) is used in updatable resources. It prevents common deadlocks when multiple sessions are reading, locking, and subsequent resource updates.

Arrange it (x) for data modification operations, such as insert, update, or delete. Make sure that multiple updates are not performed for the same resource at the same time.

Intention locks are used to establish a lock hierarchy. The intention lock type is: Intention sharing (is), intention ranking (IX), and intention ranking sharing (six ).

The schema lock is used to perform operations dependent on the table schema. The schema lock types are: schema modification (Sch-m) and schema stability (Sch-S ).

Large-capacity Update (BU) is used to copy data to a table in large capacity and specify the tablock prompt.

◆ Shared lock

The share (s) Lock allows concurrent transactions to read (select) a resource. When a shared (s) lock exists on the resource, no other transactions 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 repeated read or higher, or use the lock prompt to keep the share (s) Lock within the transaction lifecycle.

◆ Update lock

Update (u) locks can prevent normal deadlocks. Generally, the update mode is composed of a transaction. The transaction reads the record, obtains the share (s) lock of the resource (page or row), and then modifies the row, this operation requires that the lock be converted to an exclusive (x) Lock. If two transactions obtain the Shared Mode Lock on the resource and attempt to update the data at the same time, a transaction attempts to convert the lock to the lock (X. The conversion from the sharing mode to the exclusive lock must wait for a while, because the exclusive lock of a transaction is incompatible with the Sharing Mode Lock of other transactions; a lock wait occurs. The second transaction attempts to obtain the row lock (x) for update. Because both transactions need to be converted to the exclusive (x) lock, and each transaction waits for another transaction to release the share mode lock, a deadlock occurs.

To avoid this potential deadlock problem, use the update (u) Lock. Only one transaction can obtain the resource Update (u) Lock at a time. If the transaction modifies the resource, the update (u) Lock is converted to the row (x) Lock. Otherwise, the lock is converted to a shared lock.

◆ Exclusive lock

Locking (x) prevents concurrent transactions from accessing resources. Other transactions cannot read or modify the data locked by the lock (X ).

 

Intention lock

The intention lock indicates that SQL server needs to obtain the share (s) lock or arrange it (x) Lock on some underlying resources in the hierarchy. For example, a table-level share intention lock indicates that the transaction intends to place the share (s) lock on the page or row of the table. Setting the intention lock at the table level can prevent another transaction from getting the row lock (X) on the table containing that page. Intention locks can improve performance, because SQL Server only checks intention locks at the table level to determine whether transactions can safely obtain the locks on the table. Instead of checking the locks on each row or page in the table to determine whether the transaction can lock the entire table.

Intention locks include intention sharing (is), intention arranging it (IX), and intention sharing (six ).

Lock mode description

By placing the S lock on each resource, intention sharing (is) indicates that the transaction intends to read some (not all) of the underlying resources in the hierarchy.

By placing the X lock on each resource, the intention of the transaction is to modify some (rather than all) underlying resources in the hierarchy. IX is the superset of is.

By placing an iX lock on each resource, Six shares with the intention to indicate that the transaction intends to read all the underlying resources in the hierarchy and modify some (rather than all) of the underlying resources. Allow concurrent is locks on top-level resources. For example, the table's six lock places a six lock on the table (the concurrency is allowed), and the IX lock on the current modification page (the 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 resources, however, other transactions can read the underlying resources in the hierarchy by obtaining the table-level is lock.

◆ Exclusive lock:

Only the lock operation is allowed by the program. Other operations on the program will not be accepted. When the data update command is executed, SQL Server automatically uses the exclusive lock. An exclusive lock cannot be applied to an object when other locks exist.

Shared lock: the shared lock can be read by other users, but other users cannot modify it. When executing select, SQL Server will apply a shared lock to the object.

◆ Update lock:

When SQL Server is preparing to update data, it first locks the data object so that the data cannot be modified but can be read. When SQL Server determines that it wants to update data, it will automatically replace the update lock with an exclusive lock. When other locks exist on the object, it cannot be updated.

2. From the programmer's perspective: Optimistic locks and pessimistic locks.

Optimistic lock: it relies entirely on the database to manage the lock.

Pessimistic lock: the programmer manages the lock processing on data or objects by himself.

The MS-SQLSERVER uses locks to implement pessimistic concurrency control among users who execute modifications simultaneously in the database

Granularity of three locks

 

The lock granularity is the size of the target to be blocked. If the lock granularity is small, the concurrency is high, but the overhead is large. If the lock granularity is large, the concurrency is low, but the overhead is small.

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

Resource Description

Rid row identifier. Used to lock a row in a table.

The row lock in the key index. Used to protect the key range in a serializable transaction.

Page 8 KB data page or index page.

A group of eight adjacent data pages or index pages in the extended Disk Area.

The entire table includes all data and indexes.

DB database.

4. Length of lock time

The lock hold duration is the time required to protect resources at the requested level.

The duration of the shared lock used to protect read operations depends on the transaction isolation level. When the default transaction isolation level of Read committed is adopted, the shared lock is only controlled during page reading. During the scan, the lock is released only when the lock is obtained on the next page within the scan. If you specify the holdlock prompt or set the transaction isolation level to Repeatable read or serializable, the lock will not be released until the transaction ends.

Based on the concurrency options set for the cursor, the cursor can obtain the scroll lock in the sharing mode to protect the extraction. The scroll lock is released only when the cursor is extracted or closed for the next time (whichever comes first. However, if holdlock is specified, the rolling lock is released until the transaction ends.

The exclusive lock used to protect updates will not be released until the transaction ends.

If a connection attempts to obtain a lock and the lock conflicts with the lock controlled by another connection, the connection attempting to obtain the lock will be blocked:

Release the conflicting lock and the connection obtains the requested lock.

The connection timeout interval has expired. By default, there is no timeout interval, but some applications set a timeout interval to prevent indefinite waiting.

 

5. Custom locks in SQL Server

◆ Handling deadlocks and setting deadlocks priority

A deadlock occurs when multiple users apply for different blockages. The applicant has part of the blockages and waits for the blockages of other users.

Set deadlock_priority can be used to control the session response mode when a deadlock occurs. If both processes lock data and wait until other processes release their own locks, each process can release its own locks, that is, deadlock occurs.

◆ 2. Process timeout and set lock timeout duration.

@ Lock_timeout returns the current lock timeout settings for the current session, in milliseconds

Set lock_timeout allows the application to set the maximum time for the statement to wait for resource blocking. When the waiting time of a statement is greater than the lock_timeout setting, the system automatically cancels the blocking statement and Returns Error 1222 to the application that "exceeds the lock request timeout period ".

Example

In the following example, the lock timeout period is set to 1,800 milliseconds.

Set lock_timeout 1800

◆ Set the transaction isolation level.

◆ Table-level locking tips are used for select, insert, update, and delete statements.

◆ Configure the index lock Granularity

You can use the sp_indexoption system stored procedure to set the locking granularity for indexes.

6. view the lock Information

1. Execute exec sp_lock to report lock information.

2. Press Ctrl + 2 in the query analyzer to view the lock information.

Seven precautions

How to avoid deadlocks

1. When using a transaction, try to shorten the logical processing process of the transaction and commit or roll back the transaction as soon as possible;

2. Set the deadlock timeout parameter to a reasonable range, for example, 3 minutes to 10 minutes. If the timeout period is exceeded, the operation is automatically abandoned to avoid process suspension;

3. Optimize the program to check and avoid deadlock;

4. All scripts and SP should be carefully tested before the version is correct.

5. All SP should have error handling (via @ error)

6. do not modify the default transaction level of SQL Server. Force lock not recommended

How to lock a row-table database

Eight lock problems

1. How to lock a row in a table

SET transaction isolation level read uncommitted

Select * from Table rowlock where id = 1

 

2. Lock a table in the database

Select * from table with (holdlock)

 

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;
No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback

 

Several examples help you better understand

Set Table1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

 

◆ Exclusive lock

Create two connections

Execute the following statement in the first connection:

Begin tran
Update Table1
Set a = 'A'
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 the preceding two statements are executed at the same time, the SELECT query must wait 30 seconds until the update statement is executed.

◆ Shared lock

Execute the following statement in the first connection:

Begin tran
Select * From Table1 holdlock-holdlock artificial lock
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran

 

◆ Shared lock

Execute the following statement in the first connection:

Begin tran
Select * From Table1 holdlock-holdlock artificial lock
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 = 'A'
Where B = 'b2'
Commit tran

 

If the preceding two statements are executed simultaneously, the SELECT query in the second connection can be executed.

However, update can only be executed 30 seconds after the first transaction releases the shared lock and changes it to the exclusive lock.

◆ Deadlock

Add Table2 (D, E)
D e
D1 E1
D2 E2
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
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 = 'A'
Where B = 'b2'
Commit tran

 

At the same time, the system detects a deadlock and terminates the process.

Add:

Table-level locking prompt supported by SQL Server

Holdlock holds the shared lock until the entire transaction is completed. It should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level.

The nolock statement does not issue a shared lock when it is executed. Dirty reads are allowed, which is equal to the read uncommitted transaction isolation level.

Paglock uses multiple page locks when a table lock is used

Readpast allows the SQL Server to skip any locked rows and execute transactions. This applies to the read uncommitted transaction isolation level, which only skips the RID lock and does not skip pages, regions, and table locks.

Rowlock force row lock

Tablockx forces the use of an exclusive table lock, which prevents any other transactions from using this table during the transaction.

Uplock forces the use of updates during table reading without sharing locks

Application lock:

The application lock is the lock generated by the client code, not the lock generated by the SQL server itself.

Two processes for processing application locks

Sp_getapplock: Lock application resources

Sp_releaseapplock unlock application resources

Note: What is the difference between locking a database table?

Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.

Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

Transaction reads use row version control.
Use Snapshot isolation.
Use bind connection.
-----------------------------
Analyze and solve the SQL Server deadlock problem (continued)
In the previous article, we solved the deadlock problem in that scenario. This time, let's analyze why the deadlock occurs? Let's review the two SP statements:
Create proc P1 @ P1 int
Select C2, C3 from T1 where C2 between @ P1 and @ P1 + 1
Go
Create proc P2 @ P1 int
Update T1 set C2 = C2 + 1 where c1 = @ p1
Update T1 set C2 = c2-1 where c1 = @ p1
Go

Strange! P1 has no insert, no Delete, no update, but a SELECT statement. P2 is the update statement. As we mentioned earlier, updata A and update B in trans1; Upate B and update a in trans2 are not pasted!
So what causes a deadlock?

Check the SQL deadlock information from the event log:
Spid X is running this query (line 2 of Proc [P1], inputbuffer "… 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 for a shared key lock on index t1.cidx. The update holds a conflicting x lock.
The update is waiting for an exclusive key lock on index t1.idx1. The select holds a conflicting s lock.

First, let's look at the execution plan of P1. What do you think? You can run set statistics profile on. The following is the execution plan of P1.
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 can see a nested loops. The first row uses the index t1.c2 to perform seek. The rowid obtained by seek is used in the second row to query the data of the entire row through the clustered index. What is this? Bookmark lookup! Why? Because the C2 and C3 we need cannot be completely brought out by the t1.c1 index, we need to bookmark it for search.
Okay, let's look at the P2 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...
| -- Top (rowcount est 0)
| -- Clustered index seek (Object :( [T1]. [CIDX]), seek :( [T1]. [C1] = [@ P1]) ordered forward)

Locate a row through the seek of the clustered index and start updating. Note that during update, it will apply for an X lock for clustered index.

As a matter of fact, we can see why update has a deadlock on select. During update, an X lock for clustered index will be applied to block the lock (note, it is not a deadlock !) The last clustered index seek in the SELECT statement. Where is the other half of the deadlock? Note that in our SELECT statement, C2 exists in index idx1, and C1 is a clustered index CIDX. The problem is here! We updated the value C2 in P2, so sqlserver will automatically update the non-clustered Index containing the C2 column: idx1. Where is idx1? In our SELECT statement just now. The change to this index column means that a row or some rows in the index set need to be re-arranged, and re-arranged requires an X lock.
So ........., The problem was discovered.

To sum up, a query uses a non-clustered index to select data, so it will hold an S lock on the non-clustered index. When some select columns are not on this index, it needs to find the corresponding clustered index row based on rowid, and then find other data. At this time, in the second query, update is busy on Clustered indexes: positioning, locking, modification, etc. However, because a column being modified is another non-clustered index column, it needs to change the information of the non-clustered index at the same time, this requires the second X lock on the non-clustered index. Select starts to wait for the X lock of the update, update starts to wait for the s lock of the select, deadlock, and so on.

So why does the deadlock disappear when we add a non-clustered index? Let's take a look at the execution plan following the Index automatically added 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)

Oh, there is no need for clustered index, because the added overwrite index is enough to select all the information. That's simple.

In fact, in sqlserver 2005, if you use profiler to capture eventid: 1222, a deadlock chart will appear, which is very intuitive.

The following method helps minimize the number of deadlocks (For details, refer to sqlserver online help and search: to minimize the number of deadlocks.

Access objects in the same order.
Avoid user interaction in transactions.
Keep the transaction brief and in a batch.
Use a lower isolation level.
Use the row version-based isolation level.
Set the read_committed_snapshot database option to on to enable row Version Control for committed read transactions.
Use Snapshot isolation.
Use bind connection.

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