Use nolock and readpast (zt) in the query statement)

In order to learn and look up later, this article is reproduced in: http://blog.joycode.com/ghj/archive/2007/05/31/103636.aspx

When I was dealing with a database deadlock exception this morning, one of the suggestions that Yi Hongyi gave me was to use nolock or readpast. After I used it, I sorted out some technical knowledge about nolock and readpast to this blog: for industries that strictly require transactions such as non-banks, the search record appears or does not contain a certain record, all of them are within the tolerable range. When encountering a deadlock, we should first consider whether our business logic can tolerate or avoid some records, instead of looking for how to unlock the lock on both sides.

Both nolock and readpast are used to process the locked data records during query, insert, and delete operations. But pay attention to the limitations of nolock and readpast at this time. Make sure that your business logic can tolerate the appearance or absence of these records:

To put it simply:

Nolock may display data that has not been committed.

Readpast does not display the locked rows.

If nolock and readpast are not used, an error may be reported during the Select Operation: the transaction (process ID **) and another process are deadlocked on the lock resource, and has been selected as a deadlock victim.

The following is a demonstration of this situation.

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

 

Demonstrate the policies for processing uncommitted transactions, nolock, and readpast:

In the query window, execute the following script:

Create Table T1 (C1 int identity (1, 1), C2 INT)
Go

Begin transaction
Insert T1 (C2) values (1)

 

After executing in query window 1, query window 2 executes the following script:

Select count (*) from T1 with (nolock)
Select count (*) from T1 with (readpast)

 

Results and Analysis:

The query window 2 displays the following statistical results: 1 and 0.

The command for querying window 1 has not committed the transaction, so readpast does not calculate this record that has not committed the transaction. This record is locked and readpast cannot be seen; while nolock can see the locked record.

 

If we execute the following in the query window:

Select count (*) from T1 will see that the execution cannot be completed for a long time, because the query encountered a deadlock.

 

To clear the test environment, run the following statement in the query window:

Rollback transaction
Drop table T1

 

Demonstration 2: Policies for lock records, nolock and readpast Processing

 

This demo also requires two query windows.

Run the following statement in the query window:

Create Table T2 (userid int, nickname nvarchar (50 ))
Go
Insert T2 (userid, nickname) values (1, 'Guo hongjun ')
Insert T2 (userid, nickname) values (2, 'jun Jun ')
Go

Begin transaction
Update T2 set nickname = 'jun Jun. net' where userid = 2

 

Run the following script in the query window:

Select * From T2 with (nolock) Where userid = 2
Select * From T2 with (readpast) Where userid = 2

 

Results and Analysis:

In the second row of the query window, we can see the modified record in the query result corresponding to nolock. We cannot see any record in the query result corresponding to readpast.

For more information about how to clear the test environment, see demo 1.

 

References:

Using nolock and readpast table hints in SQL Server

Http://topic.csdn.net/t/20060905/14/4999881.html