How to deal with locked data records: Nolock and Readpast

Source: Internet
Author: User
Tags count insert query window management studio sql server management sql server management studio

For non-banks and other stringent requirements of the business, if a record appears or does not appear in the search record, it is within tolerable bounds, so the deadlock should first be considered as to whether our business logic tolerates or does not appear, rather than seeking a question of how to unlock both sides of the lock.

NOLOCK and READPAST are processing queries, inserts, deletes, and so on, how to deal with locked data records. But it's time to pay attention to the limitations of NOLOCK and Readpast, and verify that your business logic can tolerate the presence or failure of these records:

In simple terms:

NOLOCK may also display data that is not committed to the transaction.

READPAST will not display the locked line. 

Without the use of NOLOCK and READPAST, it is possible to report an error during a Select operation: the transaction (Process ID * *) and another process are deadlocked on the lock resource and have been selected as the deadlock victim.

Here's a demonstration of this.

To demonstrate two transaction deadlocks, we need to open two query windows in SQL Server Management Studio in the following tests. Ensure that transactions are not disturbed.

Demonstrates a policy that has not been committed, NOLOCK and readpast processing:

Query window one please execute the following script:

CREATE TABLE T1 (C1 int IDENTITY (1,1), C2 int)

Insert T1 (C2) VALUES (1)

After the Query window executes, Query window two executes the following script:

Select COUNT (*) from T1 with (NOLOCK)
Select COUNT (*) from T1 with (READPAST)

Results and Analysis:

Query window two, in turn, shows statistics as: 1, 0

The command for Query window one does not commit the transaction, so READPAST does not compute this record of uncommitted transactions, which is locked, READPAST invisible, and nolock can see the locked record.

If we do this in Query window two:

The Select COUNT (*) from T1 will see that the execution is long overdue, because the query encountered a deadlock.

To clear out this test environment, you need to execute the following statement in the query window:

drop table T1

Demo II: Strategies for Locked records, NOLOCK and readpast processing

This demo also requires two query Windows.

In Query window one, execute the following statement:

CREATE TABLE T2 (UserID int, nickname nvarchar (50))
Insert T2 (Userid,nickname) VALUES (1, ' Guo Hongjun ')
Insert T2 (Userid,nickname) VALUES (2, ' Grasshopper-June ')

Update t2 Set nickname = ' Grasshopper June. Net ' where UserID = 2

Please execute the following script in Query window two:

SELECT * from T2 with (NOLOCK) where UserID = 2
SELECT * from T2 with (READPAST) where UserID = 2

Results and Analysis:

Query window II, NOLOCK corresponding query results we saw the modified records, readpast corresponding query results we do not see any record.

Clear test Environment method see demo one.

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: 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.