NOLOCK vs READPAST

Comparison of different kind of dirty-read.

Lets create a table with 5 rows of data to start the testing.

CREATE TABLE Test_Locking (Col1 int) INSERT INTO Test_Locking VALUES (1),(2),(3),(4),(5)

Check the row count of the table before we start.

SELECT COUNT(*) FROM Test_Locking

Why sometime NOLOCK/READPAST is needed? You might had to read this.

A 5 row record table took more than 2 minute without returning any result.

We will be testing all of the DML (Data Manipulation Language) with OPEN transaction; without any commit and rollback activities.

For insert scenario:
Query

BEGIN TRANSACTION        INSERT INTO Test_Locking VALUES (6)

Output

Execute "ROLLBACK TRANSACTION" after the result were shown.

For update scenario:
Query

BEGIN TRANSACTION        UPDATE Test_Locking SET Col1 = 6 WHERE Col1 = 1

Output

Execute "ROLLBACK TRANSACTION" after the result were shown.

For delete scenario

BEGIN TRANSACTION        DELETE Test_Locking WHERE Col1 = 1

Output

Execute "ROLLBACK TRANSACTION" after the result were shown.