All select Plus with (NoLock) resolves blocking deadlocks using NoLock and READPAST in query statements
When dealing with a database deadlock exception, one of the recommendations is to use NOLOCK or READPAST. Some technical knowledge points about NOLOCK and Readpast:
For non-banking and other stringent requirements of the industry, the search records appear or do not appear in a record, are within the tolerable range, so encountered deadlock, should first consider whether our business logic can tolerate the presence or not to appear some records, rather than seek to the two sides lock the issue of how to unlock.
NOLOCK and READPAST are all handled when querying, inserting, deleting, and so on, how to deal with locked data records. However, it is important to note the limitations of NOLOCK and Readpast, and confirm that your business logic can tolerate the presence or failure of these records:
In simple terms:
NOLOCKData that does not commit the transaction may also be displayed.
READPAST will not show 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 * *) is deadlocked with another process on the lock resource and has been selected as the deadlock victim.
Here's a demonstration of this.
To demonstrate the situation of two transaction deadlocks, we need to open two query windows in SQL Server Management Studio in the following tests. Ensure that the transaction is not disturbed.
Demonstrates a policy that does not commit transactions, NOLOCK, and readpast processing:
Query window one please execute the following script:
CREATE TABLE T1 (C1 int IDENTITY (), C2 int) go
BEGIN TRANSACTION Insert T1 (C2) VALUES (1)
After the query window is executed, 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 shows the results in turn: 1, 0
The command to query window one does not commit a transaction, so READPAST does not calculate this record without committing the transaction, which is locked, READPAST not visible, and nolock can see the locked record.
If we do this in Query window two:
Select COUNT (*) from T1 will see that this execution has not been completed for a long time because the query encountered a deadlock.
To clear out this test environment, you need to execute the following statement in the query window one:
ROLLBACK TRANSACTION drop table T1
Demo Two: 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 ()) Go Insert T2 (userid,nickname) VALUES (1, ' Guo Hongjun ') Insert T2 (userid,nic Kname) 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 two, NOLOCK corresponding query results we see the revised record, readpast the corresponding query results we do not see any record.In this case, dirty reads can occur
SQL Server with (NOLOCK)
