Detailed description of with (nolock) in sqlserver

Reprinted: When NOLOCK and READPAST are used in the query statement to handle a database deadlock exception, it is recommended to use NOLOCK or READPAST. Some technical knowledge points about NOLOCK and READPAST: for industries that strictly require transactions such as non-banks, the appearance or absence of a certain record in the search record is in the tolerable model.

Reprinted: When NOLOCK and READPAST are used in the query statement to handle a database deadlock exception, it is recommended to use NOLOCK or READPAST. Some technical knowledge points about NOLOCK and READPAST: for industries that strictly require transactions such as non-banks, the appearance or absence of a certain record in the search record is in the tolerable model.

Reprinted:

Use NOLOCK and READPAST in the query statement

When handling a database deadlock exception, it is recommended to use NOLOCK or READPAST. Some technical knowledge about NOLOCK and READPAST:

For industries that strictly require transactions such as non-banks, there are or no records in the search records. Hong Kong virtual hosts are all within the tolerable range. Therefore, when encountering deadlocks, consider first, whether our business logic can tolerate or avoid some records, rather than seeking to unlock 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 locked resource. The Hong Kong server, 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 on the Hong Kong virtual host. 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, '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.