SQL Server database transaction isolation level

All SQL Server activities generate 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. SQLServer has the following types of cons: 1. Shared locks are used for read-only operations (SELECT) to lock shared resources. No shared lock

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 complexities: 1. Shared locks are used for read-only operations (SELECT) to lock shared resources. No shared lock

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 attempts to request an exclusive lock after a transaction has requested a shared lock. For example, when two transactions use a shared lock on several rows of data and attempt 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. The purpose of update lock is to obtain an update lock for only one transaction 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 system performance and does not require page or row check.

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. A row is identified by a combination of fileid: page: rid. The rid is the row identifier in the page.

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. Prevents other transactions from accessing the locked objects.
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. SQLServer adopts a multi-threaded mechanism. Of 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, SQLServer 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, SQLServer 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.

Glossary:

In a program, there will be three situations based on the transaction isolation level.
　　
◆ Dirty read: A transaction reads data that has not been committed by another transaction, so you will see some data that is finally rolled back by another transaction.

◆ The read value cannot be reproduced: one transaction reads one record, and the other transaction changes this record and commits it. At this time, when the first transaction reads this record again, it has changed.

◆ Phantom read: A transaction uses the Where clause to retrieve data from a table. Another transaction inserts a new record and meets the Where condition. In this way, after the first transaction uses the same where condition to retrieve data, an additional record is generated.

(The above article is taken from the Internet)

Based on the above theoretical knowledge, the values of IsolationLevel enumeration are explained as follows:

ReadCommitted:
Assume that transaction A places A shared lock on the Data being read, so Data cannot be rewritten by other transactions. Therefore, when transaction B reads Data, the total Data read by transaction A is consistent, therefore, dirty reading is avoided. Since Data can be rewritten before A is submitted, A value read by B may be changed by A after it is read, resulting in that the value cannot be obtained repeatedly; or when B uses the same where clause again, it obtains a result set that is different from the previous data, that is, phantom data.

ReadUncommitted:
Assume that transaction A does not publish A shared lock or accept an exclusive lock, then concurrent transactions B or other transactions can rewrite the data read by transaction, the status of the data read by the concurrent C transaction may be different from that of A or B. Dirty reads, non-repeated reads, and phantom data may exist.

RepeatableRead:
(Note the first sentence in the original MSDN text:QueryLock on all data used in, so there is no dirty read ).
Assume that transaction A places A lock on all the Data read to prevent other transactions from making changes to the Data. Before transaction A does not commit, if the Data read by A new concurrent transaction exists in Data, the Data state is consistent with that in transaction A, thus avoiding repeated reads. However, before the end of transaction A, transaction B can insert A new record to the table where the Data is located. When other transactions query with the same where clause again, the number of results may be inconsistent last time, that is, phantom data.

Serializable:
An exclusive lock is placed on the data table to prevent other users from updating rows or inserting rows into the dataset before the transaction is completed. This is the strictest lock. It prevents dirty reads, non-repeated reads, and phantom data.

The following is a comparison table:

Isolation level	Dirty Read)	NonRepeatable Read)	Phantom Read)
Read uncommitted)	Possible	Possible	Possible
Read committed)	Impossible	Possible	Possible
Repeatable read)	Impossible	Impossible	Possible
Serializable)	Impossible	Impossible	Impossible