Saturday, September 3, 2016

Reading records that are not locked - READPAST hint

When a set of records are being updated, with the default Isolation Level which is Read Committed, SQL Server makes sure that no one can read them until the transaction is completed. This avoids Dirty Read concurrency issue but it decreases concurrency.

What if you need to read rest of the records that are no locked? Now the question is, how can we instruct to SQL Server to skip locked records and read other records. It makes possible with READPAST hint.

The READPAST hint allows us to read records by ignoring locked record without waiting till incompatible locks to be released. This is NOT similar to READUNCOMMITTED hint because it reads all records including records that are locked.

Here is an example:

Execute the following query in a new window.
USE AdventureWorks2014;

SELECT * FROM Production.Product;

-- This starts a transaction and locks set of records
 UPDATE Production.Product
  SET Color = 'Black'


Now try following codes with another window
-- This wait until locks are released.
SELECT * FROM Production.Product

-- This returns all records including 
-- records that are being changed.

-- This returns all records excluding
-- records that are being changed.
SELECT * FROM Production.Product WITH (READPAST);

See the difference in results. You may find READPAST hint useful with certain scenario.


Asela Abeysinghe said...

Nolock or READPAST has any thing to do with performance (anything other than not locking) ? If we use these hints, will the quarry optimizer behave differently ? or same ?

Dinesh Priyankara said...

If I am not mistaken, not directly but indirectly for sure. Assume that, something is being blocked because of a transaction, and if the second transaction is trying to read some records (like a report), he will be blocked and will receive once the lock is released. Since this is transparent to the end-user, it is considered as a performance issue. If the second transaction uses READPAST or READUNCOMMITTED, then it does not need to wait and, request is satisfied immediately. It can be considered as a performance improvement depend on the situation.

As per my knowledge, this will be handled at the "query execution" logical phase, hence I do not think that Query Optimizer behaves differently based on locks held.