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