SQL Server Lock Mechanism

From: http://blog.csdn.net/missmecn/archive/2008/10/06/3019798.aspx related articles:

• MySQL database lock
• Several lock modes in Oracle

Recommendation circle:Pipboy
More related recommendations

The Study of the lock mechanism requires two conditions:
1. Large data volume
2. Concurrent Operation by multiple users
Without these two conditions, the database is not prone to deadlock. It may take half the effort for research. If both conditions are met, but you still process the data according to the default settings of the database, there will be many problems, such:
1) lost updates
A and B read and modify the same data. The Modification result of one user destroys the Modification result of the other user.
2) Dirty read
When user a modifies the data, user B also reads the data, but user a cancels the modification to the data for some reason and restores the original data value, the data obtained by B is inconsistent with the data in the database.
3) Non-repeated read
User B reads the data and modifies it. At the same time, user A is also reading the data. At this time, user a reads the data again and finds that the values of the two operations are inconsistent.
As a multi-user database system, SQL server uses locks to implement Concurrency Control in units of transactions. Sqlserver uses a "Lock" to ensure transaction integrity and data consistency.

I. Lock Concept
Locking is the most common concurrency control mechanism. It is a major means to prevent other transactions from accessing specified resource control and achieve concurrency control. A lock is before a transaction accesses a database resource (such as tables and records), it first requests the system to block the resource. After the transaction acquires the lock, it obtains control of the data, before a transaction releases its lock, other transactions cannot update this data. When the transaction is canceled, the locked resources are released.
When a user locks an object in the database, other users cannot access the object.

Ii. Lock Granularity
SQL Server 2000 has multi-granularity locking, allowing a transaction to lock different types of resources. To minimize the cost of locking, SQL Server automatically locks resources at the appropriate task level. Locking at a small granularity (such as rows) can increase concurrency but requires a large overhead, because if many rows are locked, more locks need to be controlled. Locking at a large granularity (such as a table) is expensive in terms of concurrency, because locking the entire table limits access to any part of the table by other transactions, but requires a low overhead, because the number of locks to be maintained is small. SQL Server can lock rows, pages, extended disk areas, tables, databases, and other resources.

• Resource level description
• Single Row in the RID row lock table
• Rows in the key row-Level Lock Index
• Page locks a data page or index page
• Extent page-Level Lock a set of data pages or index pages
• Table-level lock for the entire table
• Database database locks the entire database

The granularity depends on the data operation. If all rows in the table are updated, table-level locks are used. If a row in the table is updated, row-level locks are used.
Row-level locks are the optimal locks, because row-level locks cannot be wasted because data is occupied and not used. However, if your transactions frequently operate on multiple records in a table, row-level locks will be applied to many record rows in the table, and the number of locks in the database system will increase dramatically, this increases the system load and affects system performance. Therefore, SQL Server also supports lock escalation ).
The so-called lock upgrade refers to adjusting the lock granularity and replacing multiple low-granularity locks with a few higher-granularity locks to reduce the system load. In SQL Server, when there are many locks in a transaction and the lock upgrade threshold is reached, the system automatically upgrades row-level locks and page locks to table-level locks.
In SQL Server, the threshold for Lock upgrade and lock upgrade are automatically determined by the system and do not need to be set by the user.

Iii. Lock mode
Lock mode and description table

Lock mode description
• Share (s) is used without changing or updating data (read-only operation), such as select statement
• 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. Ensure that multiple updates to the same resource are not performed at the same time
• Intention when the Microsoft SQL Server database engine acquires a low-level lock, it will also place the intention lock on the object containing lower-level objects. for example, when the row or index key range is locked, the database engine places the intention lock on the page that contains the row or key. When a page is locked, the database engine places an intent lock on a higher-level object that contains the page.
  Intention lock type: Intention sharing (is), intention ranking (IX), and intention ranking sharing (six)
• The architecture is used to perform operations dependent on the table architecture. The architecture 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.

Iv. SQL Server lock settings
1. Handle deadlocks and set deadlock 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.
Syntax:
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.
2. Process timeout and set the 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
1) set the lock timeout period to 1,800 milliseconds.
Set lock_timeout 1800
2) configure the index lock Granularity
You can use the sp_indexoption system stored procedure to set the locking granularity for indexes.
3) set the transaction isolation level
SET transaction isolation level

5. View lock Information
1. Execute exec sp_lock to report lock information.
2. Press Ctrl + 2 in the query analyzer to view the lock information.

6. Strange SQL statementsJava code

1. Begin tran
2. Update titles set title_idid = title_id where 1 = 2
3. If(Selectavg (price) fromtitles)> $15
4. Begin
5. Update titles set price = price * 1.10
6. Where price <(select AVG (price) from titles)
7. End
8. Commit tran

begin tranupdate titles set title_idid＝title_id  where 1＝2if (selectavg(price)fromtitles)>$15beginupdate titles set price＝price*1.10where price<(select avg(price)from titles)endcommit tran

Update titles set title_idid = title_id where 1 = 2. This condition will never be true. What is the meaning of this write?
The where clause here looks strange, although the calculated result is always false. When the optimizer processes this query, because it cannot find any valid Sarg, its query plan will force an exclusive lock to scan the table. When the transaction is executed, the where clause immediately obtains a false value, so it does not perform an actual scan, but the process still gets an exclusive table lock.
Because this process now has an exclusive table lock, it can ensure that no other transaction will modify any data rows and can perform repeated read, and avoid potential deadlocks caused by holdlock.
However, when table locking is used to minimize deadlocks, contention for table locking is also increased. Therefore, before implementing this method, you need to consider whether to avoid deadlocks is more important than allowing concurrent access to tables.
Therefore, in this transaction, no other process modifies the price of any row in the table.

7. 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. All SP servers must handle errors (via @ error)
4. Do not modify the default transaction level of SQL Server. Force lock not recommended
5. Optimize the program and check and avoid deadlock;
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 read technology. Dirty reads avoid lock conflicts because they do not lock the accessed table. In the Client/Server 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. The time-domain Discretization Method for Data Access refers to various control measures used in the Client/Server structure to control the access time periods for objects in the database or database. The following methods are used to reasonably arrange the execution time of background transactions and manage background transactions using workflows. When a workflow manages a task, it restricts the number of threads of the same type of tasks (usually one) to prevent excessive resource occupation. On the other hand, it rationally schedules the time series and times of running different tasks, avoid executing multiple background tasks at the same time, and avoid running background tasks at the front-end transaction peak hours.
5) data storage space discretization method. The data storage space discretization method is used to distribute data in a logical table to several discrete spaces, so as to improve the table access performance. The following methods are used: first, a large table is divided into several small tables by row or column. Second, it is divided by different user groups.
6) Use a minimum isolation level. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance.
7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.
8) Consider using Optimistic Locking or giving the transaction an exclusive lock first.

8. How to lock rows, tables, and databases
1. How to lock a row of Java code in a table

1. SET transaction isolation level read uncommitted
2. Select * From Table1 rowlock where a = 'a1'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM table1 ROWLOCK WHERE A = 'a1'

2. Lock a table in the database
Select col1 from table (tablockx) where 1 = 1;
No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback
3. Instance
Table creation Java code

1. Create Table Table1 (A varchar (50) NotNull, B varchar (50), c varchar (50 ));
2. Create Table Table2 (d varchar (50), E varchar (50 ))
3. Insert Table1 (a, B, c) values ('a1', 'b1', 'c1 ');
4. Insert Table1 (a, B, c) values ('a2 ', 'b2', 'c2 ');
5. Insert Table1 (a, B, c) values ('a3 ', 'b3', 'c3 ');
6. Insert Table2 (d, e) values ('d1 ', 'e1 ');
7. Insert Table2 (d, e) values ('d2 ', 'E2 ');

create table table1(A varchar(50)  not  null, B varchar(50) ,C varchar(50));create table table2(D varchar(50),E varchar(50))insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);insert table2 (D,E) values(‘d1’,’e1’);insert table2 (D,E) values(‘d2’,’e2’);

1) exclusive lock Java code

1. -- Transaction a first updates table 1 and excludes other transactions during the update.
2. Begin tran
3. Update Table1 set a = 'A' where B = 'b2 ';
4. Waitfor delay '00: 00: 30'; -- wait 30 seconds
5. Commit tran
6. -- Transaction a first updates table 2
7. Begin tran
8. Select * From Table1 where B = 'b2 ';
9. Commit tran

-- Transaction a first updates table 1. During the update, other transactions are exclusive begin tranupdate Table1 set a = 'A' where B = 'b2'; waitfor delay' 00: 00: 30'; -- wait 30 seconds for commit tran -- a transaction to update Table2 table begin transelect * From Table1 where B = 'b2'; Commit tran

If the preceding two transactions are executed at the same time, the SELECT query must wait until the update operation is complete before execution, that is, 30 seconds.
2) shared lock Java code

1. -- Transaction a first queries the table 1 Table and adds a shared lock during the query to prevent other transactions from modifying the table.
2. Begin tran
3. Select * From Table1 holdlock where B = 'b2 ';
4. -Holdlock: artificially locked
5. Waitfor delay '00: 00: 30'; -- wait 30 seconds
6. Commit tran
7. -- Transaction a first queries table 1 and then changes table 1
8. Begin tran
9. Select a, c from Table1 where B = 'b2 ';
10. Update Table1 set a = 'A' where B = 'b2 ';
11. Commit tran

-- Transaction a first queries the table 1 Table and adds a shared lock during the query to prevent other transactions from performing the modification operation begin transelect * From Table1 holdlock where B = 'b2 '; -holdlock artificially locks waitfor delay '00: 00: 30'; -- wait 30 seconds for commit tran -- A to query Table 1 first, and then change table 1 Table begin transelect, c from Table1 where B = 'b2'; update Table1 set a = 'A' where B = 'b2'; Commit tran

If the preceding two transactions are concurrently executed, the SELECT query in transaction B can be executed, and the update MUST wait 30 seconds until the first transaction releases the shared lock and converts it to the exclusive lock.
3) deadlock Java code

1. -- Transaction a updates table 1 with a delay of 30 seconds, and then updates table 2;
2. Begin tran
3. Update Table1 set a = 'A' where B = 'b2 ';
4. -- This will generate an exclusive row lock in Table1 until the transaction is completed.
5. Waitfor delay '00: 00: 30 ';
6. -- Entry latency
7. Update Table2 set D = 'd5 'where E = 'e1 ';
8. Commit tran
9. -- Transaction B First updates table 2, then latencies of 10 seconds, and then updates table 1;
10. Begin tran
11. Update Table2 set D = 'd5 'where E = 'e1 ';
12. -- This will generate an exclusive row lock in Table2 until the transaction is completed.
13. Waitfor delay '00: 00: 10'
14. -- Entry latency
15. Update Table1 set a = 'A' where B = 'b2 ';
16. Commit tran

-- Transaction a first updates table 1 with a delay of 30 seconds, and then updates table 2; begin tranupdate Table1 set a = 'A' where B = 'b2 '; -- this will generate an exclusive row lock in Table1 until the transaction is completed. Waitfor delay '00: 00: 30'; -- enter the delayed update Table2 set D = 'd5 'where E = 'e1'; Commit tran -- B transaction first updates table 2, then, the table 1 is updated after a delay of 10 seconds. Begin tranupdate Table2 set D = 'd5 'where E = 'e1 '; -- this will generate an exclusive row lock in Table2, the lock waitfor delay '00: 00: 10' will not be released until the transaction is complete. -- enter the delayed update Table1 set a = 'A' where B = 'b2'; Commit tran

If the preceding two transactions are concurrently executed, both transactions A and B will wait for the other party to release the exclusive lock, thus forming a deadlock.

9. Table-level locks provided by sqlserver
The table-level locking tips specified by sqlserver are as follows:
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 (without blocking other users from reading data), and ensure that the data is not modified by other users during this period of time when the data is updated later.
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.

10. Application lock

Application locks are the locks generated by client code, rather than the two system stored procedures generated by SQL Server to process application locks.
Sp_getapplock: Lock application resources
Sp_releaseapplock: Unlock application resources