Transaction isolation level, lock mechanism in SQL Server

Source: Internet
Author: User

Transaction

Function: Used to perform a series of actions, and to ensure that all actions are either executed or not executed.

Properties: Atomic row, consistency, isolation, persistence

Lock

Role: SQL Server uses locks to enforce transaction isolation properties.

Blocking

Definition: If one transaction holds a lock on a data resource and another transaction requests an incompatible lock of the same resource, the request for the new lock is blocked.

By default, blocked requests wait until the original transaction releases the associated lock.

Isolation level of a transaction

There are 6 possible settings (higher and lower isolation levels):

1. READ UNCOMMITTED

2. Read Committed

3. Repeatable READ

4, Serializable

5, Snapshot

6. Read Committed Snapshot

of which the first four levels are most commonly used

1, read UNCOMMITTED dirty read

For example, a is executing the following statement:

begin TranInsertTbunreadSelect 3,'Zhang San'UnionSelect 4,'John Doe'---delay seconds to simulate real-world trading scenarios for processing business logicwaitforDelay'00:00:05'rollback Tran

At this point, within 5 seconds of the wait, B has made a change to the resource:

Set Transaction Isolation  Level Read Uncommitted -- Querying Data Select *  from where  like ' Zhang% '

Originally a to insert two data, but the last transaction rollback, at this time the database should not exist in this two data, but B is read, this is dirty read.

To solve this problem, you need to elevate the transaction isolation level to read Committed

2, Read committed non-repeatable reading (avoid dirty read the lowest level)

If a is performing the following actions:

Set Transaction Isolation  Level Read committedbegin TranSelect *  fromTbunreadwhereId=2 ---delay seconds to simulate real-world trading scenarios for processing business logicwaitforDelay'00:00:05'Select *  fromTbunreadwhereId=2

At this point, within 5 seconds of the wait, B has made a change to the resource:

Update Tbunread Set name='jack_upd'where ID=2

The result is that within the same transaction, a query with the same two-time condition has different results.

To solve this problem, you need to elevate the transaction isolation level to repeatable READ

3, REPEATABLE read Phantom reading

If a is performing the following actions:

Set Transaction Isolation  Level Repeatable READbegin TranSelect *  fromTbunreadwhereId=3 ---delay seconds to simulate real-world trading scenarios for processing business logicwaitforDelay'00:00:05'Select *  fromTbunreadwhereId=3 Commit Tran

At this point, within 5 seconds of the wait, B has made a change to the resource:

Tbunread
Select 3,' Phantom reading '

At this point, a has set the isolation level to "repeatable read", although the data that has been read, whether it is a shared or exclusive lock to the end of the transaction, but can not prevent others to run the new operation, resulting in the first query without data, the second query has data, this phenomenon is called Phantom read.

To solve this problem, you need to elevate the transaction isolation level to Serializable

Transaction isolation level, lock mechanism in SQL Server

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.