SQL Server transaction isolation level and lock request and release

Dirty reads: When a transaction begins to update the data, but the transaction is not fully committed, this time the second transaction begins to read the data and reads the data changed by the first firm.

The second transaction reads the data temporarily, because it is possible that the first transaction may end up doing a rollback operation

Non-REPEATABLE READ: Reading a row of data multiple times in a transaction may result in different results

Phantom read: In a transaction, we read the data, found that there is no specific row, the first transaction is not finished, this time the second transaction inserted the row of data,

Then, when the first transaction is read again, the row's data suddenly appears

SQL Server database supports isolation levels: uncommitted read, Read committed, repeatable read, snapshot, serializable.

• Uncommitted Read (can read rows that have been modified by another transaction but not committed)
  • does not issue a share to prevent other transactions from modifying the data read by the current transaction
  • will not be blocked.
• submitted Read (The specified statement cannot read data that has been modified but not committed by another transaction, so you can avoid dirty reads)
  • Other transactions can update data between statements in the current transaction, resulting in non-repeatable read and Phantom reads the
  • database uses shared locks to prevent other transactions from being read at the current When a transaction modifies rows during a read operation, a shared lock also prevents the statement from reading rows modified by those transactions before the other transaction finishes, and the
  • statement frees the shared lock when it finishes running, rather than waiting for the transaction to commit
• Repeatable Read (a row of data is read more than once in a transaction and may get the same result)
  • sets a shared lock on all data read by each statement in the transaction, and the shared lock remains until the transaction completes
• Snapshot
  • before any modification, make a copy of the pre-modified version, all subsequent reads will read the copied version, the modification will create a new version, this process can ensure that the read and write does not block each other
• Serializable
The
• serializable requirement is that, in addition to repeatable reads, other transactions cannot use the Read key value of any statement in the current transaction for insert and delete operations until the current transaction completes. SQL Server implements serializable
by adding range locks

Isolation level	Whether to request a shared lock	When to release	With or without range lock
Non-committed read	Not apply	--	No
Read Committed	Application	The current statement finishes executing	No
REPEATABLE READ	Application	When a transaction commits	No
Serializable	Application	When a transaction commits	Yes

Concurrency side effects at different isolation levels:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
Non-committed read	Is	Is	Is
Read Committed	Whether	Is	Is
REPEATABLE READ	Whether	Whether	Is
Snapshot	Whether	Whether	Whether
Serializable	Whether	Whether	Whether

SQL Server transaction isolation level and lock request and release