Posts Tagged ‘SHARED Locks’

DIRTY reads and PHANTOM reads – SQL Server

July 20, 2011 14 comments

–> DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.

This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.

To prevent Dirty Reads, READ COMMITTED or SNAPSHOT isolation level should be used.

–> PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.

PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.

>> Check & Subscribe my [YouTube videos] on SQL Server.


Is WITH (READPAST) affected by readlocks – MSDN TSQL forum

March 19, 2011 Leave a comment

–> Question:

If a transaction calls a SELECT using the table hint WITH(READPAST)… will it skip rows that are locked by other SELECT statements that are in READ COMMITTED mode and are issuing shared locks?

ie. Does READPAST skip rows that are locked by SELECT statements, not just UPDATE and INSERT statements?

–> Answer:

I don’t think READPAST will skip rows locked by SELECT statement, but only in UPDATE & DELETEs.

Check here for an example and demo: Difference between NOLOCK and READPAST table hints

You may also check a similar old post in MSDN forum for more into.

Ref link.