SQL Server Lock Mechanism

SQL Server Lock Mechanism

All SQL Server activities produce locks. The smaller the lock unit, the more concurrent processing capability 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 a multi-threaded mechanism, which 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.