SQL Server transaction isolation level and lock request and release

Source: Internet
Author: User

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

Related Article

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.