SQL with (unlock) and with (Readpast) (GO)

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:

NOLOCK may also display data that does not commit the transaction.
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 (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 two, NOLOCK corresponding query results we see the revised record, readpast the corresponding query results we do not see any record. it A dirty read may occur in the case of

Clear test Environment method see demo one.

Development examples:

Code
public static string Getusernamebyuserids (String userid,string split)
{
UserID = "'" + userid.replace (Split, "'" + Split + "'") + "'";
String sql = "SELECT * from _user with (NOLOCK) where UserID in (" + userid + ")";
DataSet ds = Framework.IDE.DB.ExecuteDataSet (CommandType.Text, SQL);
String userNames = String. Empty;
for (int i = 0; I < ds. Tables[0]. Rows.Count; i++)
{
if (i = = 0)
{
UserNames = ds. Tables[0]. rows[i]["username"]. ToString ();
}
Else
{
UserNames + = Split + ds. Tables[0]. rows[i]["username"]. ToString ();
}
}
return userNames;
}

SQL with (unlock) and with (Readpast) (GO)