How to deal with locked data records: Nolock and Readpast

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) Go BEGIN TRANSACTION 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:

ROLLBACK TRANSACTION 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)) Go Insert T2 (Userid,nickname) VALUES (1, ' Guo Hongjun ') Insert T2 (Userid,nickname) VALUES (2, ' Grasshopper-June ') Go

BEGIN TRANSACTION 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.