SQLserver Lock Mechanism

Source: Internet
Author: User
All SQL Server activities generate locks. The smaller the number of locked units, the more concurrent processing capability will be improved, but the higher the management lock overhead. It is difficult for SQL Server to find a balance between concurrency and performance. SQLServer has the following types of cons: 1. Shared locks are used for read-only operations (SELECT) to lock shared resources. Shared locks are not blocked.

All SQL server activities produce locks. The smaller the number of locked units, the more concurrent processing capability will be improved, but the higher the management lock overhead. It is difficult for SQL Server to find a balance between concurrency and performance. SQL Server has the following types of complexities: 1. Shared locks are used for read-only operations (SELECT) to lock shared resources. Shared locks are not blocked.

All SQL server activities produce locks. The smaller the number of locked units, the more concurrent processing capability will be improved, but the higher the management lock overhead. It is difficult for SQL Server to find a balance between concurrency and performance.
SQL Server has the following types of conveniences:
1. Shared lock
Used for read-only operations (SELECT) to lock shared resources. The shared lock does not stop other users from reading, but prevents other users from writing and modifying.
2. Update the lock
An update lock is an intent lock. An update lock occurs when a transaction has requested a shared lock and tries to request an exclusive lock. For example, when two transactions use a shared lock on several rows of data and try to obtain an exclusive lock to perform the update operation, a deadlock occurs: they are waiting for the other party to release the shared lock and implement the exclusive lock. Update locks are designed to obtain update locks only for one thing to prevent this situation.
3. exclusive lock
Only one exclusive lock can be used on one resource at a time, and all other locks including shared contraction are blocked. Write is an exclusive lock, which effectively prevents 'dirty read'
4. Intention reduction
Use intent locks before using shared and exclusive locks. View intent locks at the table level to determine whether a transaction can obtain a shared lock or an exclusive lock. This improves the system performance and does not need to be checked by the parent or row.
5. Plan lock
Sch-M, Sch-S. Sch-M is used for database structure change and Sch-S is used for query compilation. These two locks do not block any transaction locks, including exclusive locks.

Read is a shared lock, write is an exclusive lock, and then update is an update lock. When the update lock is successful and the data is changed, the update lock is upgraded to the exclusive lock. Lock types include:
DB ----- database, because the dbid column already contains the database ID, no information is provided
FIL ---- File
IDX ---- Index
PG ----- page, data or index page. Page number. A page is identified by a combination of fileid: page, where fileid is the fileid in the sysfiles table, and page is the logical page number in the file.
KEY ---- KEY, used to protect the KEY range in a serializable transaction
TAB-table, including all data and indexes. The ObjId column contains the table Object ID, so no information is provided.
EXT ---- A Group of Eight adjacent data pages or index pages. The first page number in the extended disk area being locked. The page is identified by a combination of fileid: page
RID ---- row, the row identifier of the locked row in the table. The row is identified by a combination of fileid: page: rid. The rid is the row identifier in the page.

Lock status:
Grant --- can use authorized resources
Wait ---- resources blocked by other tasks can be used
Cnvrt --- Convert, the lock is being converted

Subdivision lock mode:
0 Null does not get the resource access permission
1 Sch-S (Schema stability) Compile the query. This prevents lock objects from being deleted until they are unlocked.
2 Sch-M (Schema Modification) occurs when the database structure is changed. Prevent other things from accessing the locked object
3 IS (Intent Shares) intention to share the lock.
4 SIU (Share Intent Update) intends to put the Update lock on the lower-level resources of the lock hierarchy when maintaining the shared lock of resources.
5 IS-S (Intent Share-shared) composite key range lock
6 IX (Intent Exclusive) Intention Exclusive lock
7 SIX (Share Intent Exclusive)
8 S (Share) shared lock
9 U (Update) Update lock. Deadlock Prevention
10 Iin-Nul (Intent Insert-Null) Row-level locking and composite key range locking
11 IS-X (Intent Share-Exclusive)
12 IU (Intent Update) intends to Update the lock
13 IS-U (Intent Share Update) Serial Update Scan
14 X (Exclusive) Exclusive lock
Locks used for 15 BU block operations

Therefore, we have the following conclusions.

1. When a connection modifies a data block, other connections cannot modify the data block until it is unlocked.
Parallel access is the most important issue for any database solution. In order to solve the problem of parallel access, a variety of solutions are proposed for various database systems. SQL Server adopts MultithreadingMechanismOf course, it can process multiple requests at a time. However, the problem of parallel access becomes complicated when users modify data. Obviously, a database usually allows only one user to modify specific data at a time. When a user starts to modify a piece of data, SQL Server can quickly lock the data and prevent other users from updating the data, until the first user who modified the data completes the operation and submits the transaction or rolls back the data. However, what happens when a user is modifying a piece of data and another user is trying to query the data information?
2. Generally, when a connection modifies a data block, other connections cannot query the data block until it is unlocked. And vice versa: it cannot be written or modified during read. This solution will reduce the system performance and efficiency, even though it is currently a row-Level Lock (7.0 was a lock page or even a lock table before), If you modify multiple rows of data at a time, SQL Server will upgrade the data lock range to the page level or even lock the entire data table, so that you do not have to track and maintain the data lock for each record, which can speed up the modification, it consumes a small amount of server resources, but the concurrency is poor ..
3. When one connection is written, another connection can be written but cannot be read.
4. Multiple connections can read the same row at the same time.

Therefore, locks compete for reading and writing.

5. SET the TRANSACTION LEVEL SET TRANSACTION ISOLATION LEVEL
A. read committed: Specifies to control the shared lock when reading data to avoid dirty reading. However, data can be changed before the transaction ends, resulting in non-repeated READ or phantom data.
This option is the default value of SQL Server.
B. READ UNCOMMITTED: Execute dirty READ or 0-level isolation lock, which means no shared lock is issued or the exclusive lock is not accepted. When this option is set, uncommitted or dirty reads can be performed on the data. Before the transaction ends, the values in the data can be changed, and the rows can also appear in the dataset or disappear from the dataset. This is the minimum limit among the four isolation levels.
C. repeatable read: Lock all data used in the query to prevent other users from updating data. However, other users can insert new Phantom rows into the dataset, the phantom row is included in the subsequent reading of the current transaction. Because concurrency is lower than the default isolation level, this option should be used only when necessary.
D. SERIALIZABLE: place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction is completed. This is the most restrictive level among the four isolation levels. Because the concurrency level is low, this option should be used only when necessary. This option is used to set HOLDLOCK for all tables in all SELECT statements in the transaction.
Note
Only one of these options can be set at a time, and the set options will remain valid for that connection until the option is explicitly changed. This is the default action, unless the optimization option is specified at the table level in the FROM clause of the statement.
Set transaction isolation level is SET during execution or running, rather than during analysis.

A transaction can request the system to Lock the operated data ). Other transactions can access the data only after the transaction is unlocked (Unlock. Therefore, when multiple users access the database concurrently, make sure that they do not interfere with each other. Lockable units are: rows, pages, tables, disk areas, and databases.
1. Lock type
SQL Server supports three basic blocking types: shared (S) locks, exclusive (X) locks and update (U) locks. The basic granularity of blocking is row.
1) share (S) Lock: used for read operations.
Multiple transactions can block the data of one shared unit.
No transaction can modify the data with the S lock.
Generally, the data with the S lock is read, and the S lock is immediately released.
2) Exclusive (X) Lock: used for write operations.
Only one transaction is allowed to block this shared data.
Any other transaction must wait until the X lock is released to access the data.
The X lock cannot be released until the transaction ends.
3) Update the (U) Lock.
Used to reserve the X lock applied to this page, which allows other transactions to read, but does not allow the U Lock or X lock to be applied again.
When the read data page is about to be updated, it is upgraded to the X lock.
The U Lock cannot be released until the transaction ends.
2. Compatibility of the three locks
The following table describes the compatibility of the three locks:
Generally, a read operation (SELECT) acquires a shared lock, while a write operation (INSERT or DELETE) acquires an exclusive lock. An update operation can be divided into a read and a write operation with an update intention, therefore, the update lock is obtained first, and then upgraded to the exclusive lock.
Can other processes query the obtained lock by executing the command? Can other processes be modified?
Select title_id from titles S Yes No
Delete titles where price> 25 X No
Insert titles values (...) X No
Update titles set type = "general" U Yes No
Where type = "business" and then x no No

Use indexes to reduce lock concurrency
Why should we discuss locks?Mechanism? If you lock as much data as possible during data operations, the processing process will not wait for the locked data to be unlocked, which can potentially improve the performance of SQL Server. If 200 users intend to modify data of different customers, it would be much better to lock a single row that stores individual customer information than to lock the entire table. So, how do users lock only rows rather than tables? Of course, the index is used. As mentioned above, using indexes for fields with data to be modified can improve the performance, because the index can directly find the page where the data is located, instead of searching all the data pages to find the required rows. If you directly locate the corresponding row in the table and update the table, you only need to lock the row, instead of locking multiple pages or the entire table. The performance improvement is not only because the number of pages read during modification is small, but also because the number of pages locked is small, which may prevent other users from waiting for unlocking before the data modification is complete.

Transaction isolation level
The ANSI standard defines four isolation levels for SQL transactions. The higher the isolation level, the lower the possibility of data inconsistency (the lower the concurrency ). A higher level includes the limits set in a lower level.
Isolation level 0: prevents "loss of modifications" and allows dirty reads.
Isolation Level 1: prevents dirty reads. Read committed data is allowed.
Isolation Level 2: prevents "repeated reads ".
Isolation Level 3: serializable ). It means that the result of a cross-scheduling of a group of parallel transactions is the same as that of a serial scheduling of these transactions (which can avoid data consistency destruction ). SQL Server supports four isolation levels. Level 1 is the default isolation level, and there is no isolation level 2 in the table. Please refer to the table:
SQL Server supports isolation-level blocking to ensure data consistency
The X lock is applied to the modified page. The S lock is applied to the read page to prevent the loss of the modification and prevent the dirty data from being read repeatedly.
Level 0 blocking to transaction end is
Level 1 (default) is immediately released after the transaction ends reading
Level 3 blocking to transaction End Blocking to transaction end is yes
Level 2 is also specified in SQL Server, but Level 3 already contains Level 2. Level 3 is required in ANSI-92 SQL as the default isolation level for all transactions.
SQL Server uses the holdlock option to enhance the S lock restriction and implement isolation level 3. The default isolation level of SQL Server is level 1, and the shared read lock (S lock) is released immediately after the page is read. If you add the holdlock option to the select statement, the S lock can be released until the transaction ends. She complies with ANSI isolation level 3-"serializable ".

In the following example, avg (advance) needs to be read twice in the same transaction and the value is required to remain unchanged-"Repeatable read". Therefore, the option holdlock is used.
BEGIN tran
DECLARE @ avg-adv money
SELECT @ avg-adv = avg (advance)
FROM titles holdlock
WHERE type = "business"
If @ avg-adv> 5000
SELECT title from titles
WHERE type = "business" and advance> @ avg_adv
COMMIT tran
There are three ways to set the transaction isolation level in SQL Server:
Session Layer settings
Syntax:
SET TRANSACTION ISOLATION LEVEL
{
READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
The System-provided system stored procedure is executed under Level 1, which is not affected by the session-layer setting.
Syntax layer settings
Add options to SELECT, DECLARE cursor, and read text statements. For example:
SELECT... at isolation {0 | read uncommitted}
Note: the setting of the syntax layer replaces the setting of the Session Layer.
Keyword setting
-In the SELECT statement, if the option holdlock is added, the level is set to 3.
-In the SELECT statement, if noholdlock is added, the level is set to 0.

The script instance listed in the following program list holds a shared lock on the authors table, which delays the user's check of the current activity of the server by two minutes.
Program list test transaction isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
SELECT *
FROM authors
WHERE au_lname = 'green'
Waitfor delay '00: 02: 00'
ROLLBACK TRAN
GO
Activity Legend indicates that the intention lock of the table on the page is removed when SQL Server retrieves data. The Current Activity window (see-3) shows that the shared lock is kept until the transaction is completed (that is, until the WAITFOR and rollback tran statements are completed ).
Prompt for using the locking optimization program
Let's further examine the examples of the program list. By changing the optimization program prompt, you can set an exclusive table lock for SQL Server on the authors table (as shown in the program ).
BEGIN TRAN
SELECT *
FROM authors (tablockx)
WHERE au_lname = 'green'
Waitfor delay '00: 02: 00'
ROLLBACK TRAN
GO

The SELECT statement uses the optimizer to prompt tablockx to keep the exclusive table lock until the transaction ends. The following table shows the available locking optimizer prompts.
Locking optimization program prompts and descriptions
Optimization Program prompt description of optimization program prompt
Holdlock remains locked until the transaction ends
No lock is used when nolock is used to retrieve data.
Paglock use page lock
Tablock
Tablockx uses an exclusive table lock
Updlock update lock
The holdlock optimizer prompts that shared locks can be maintained throughout the transaction. Readers are familiar with this in serializable and Repeatable read transaction isolation levels. If you occasionally want to use a shared lock, it is best to use the system's default read delivery transaction isolation level and require a notification using the holdlock optimizer. The holock optimizer prompts that the read-only transaction isolation level has the same function, it performs read operations on non-delivered data without locking during Data Reading (thus avoiding any blocking caused by exclusive locking ). Note the following when using the index and lock optimization program: You can combine these two types of prompts
But the index prompt must be listed at the end. This is important. The Code in the following program list provides the correct method for prompting valid optimizers. As prompted by a mixed optimization program
SELECT *
FROM authors (paglock holdlock index = aunmind)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.