Detailed description of WITH (NOLOCK) in SQLSERVER

In SQL Server, detailed description of WITH (NOLOCK) [Switch] NOLOCK and READPAST are the methods to deal WITH locked data records when processing query, insert, delete, and other operations. To put it simply, NOLOCK may display the data that has not committed the transaction. READPAST will not display the locked rows without using NOLOCK and READPAST.

In SQL Server, detailed description of WITH (NOLOCK) [Switch] NOLOCK and READPAST are the methods to deal WITH locked data records when processing query, insert, delete, and other operations. To put it simply, NOLOCK may display the data that has not committed the transaction. READPAST will not display the locked rows without using NOLOCK and READPAST.

Detailed description of WITH (NOLOCK) in SQLSERVER [transfer]

Both NOLOCK and READPAST are used to process the locked data records during query, insert, and delete operations.

To put it simply:

NOLOCK may display data that has not been committed.

READPAST does not display the locked rows.

If NOLOCK, READPAST, and website space are not used, an error may be reported during the Select Operation: the transaction (process ID **) and another process are deadlocked on the locked resource, virtual host, and has been selected as a deadlock victim.

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: Strategy for handling locked records, server space, NOLOCK, and READPAST

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, 'Raw ')
Insert t2 (UserID, NickName) values (2, 'fuckcpp ')
Go

BEGIN TRANSACTION
Update t2 set NickName = 'fuckcpp. 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. In this case, dirty reads may occur.

Posted on