The transaction isolation level of SQL Server

Source: Internet
Author: User

Set TRANSACTION Isolation Level XXX--each setting is only for the current transaction block

XXX Value:

READ UNCOMMITTED

READ COMMITTED

Repeatable READ

SNAPSHOT

SERIALIZABLE

Test1 table data is as follows:

Update the Name for AAAAA now and set the 2-minute delay

The above update uses Rowlock by default, so for the table is the IX lock (line 12th), at this point, if the query other rows, is not blocked, because IX is compatible with IS.

However, if update uses TABLOCK, the table will use an X lock, and queries for that table are blocked because X is incompatible.

Setting the transaction isolation level to READ UNCOMMITTEDallows dirty reads ( Note that this transaction isolation level setting is only currently active )

Equivalent to the following settings, but nolock only for the current SELECT statement:

This will be blocked if you use the following:

Finally, at the end of the delay, the update data is rolled back, causing the query with the result to be dirty read, and the blocked query will get the following data:

The transaction isolation level of 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.