SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – READUNCOMMITTED, NOLOCK and dirty reads

10 December,2012 by Jack Vamvas

Question: A workflow application requires an unobtrusive lock on data in a transactional database. It’s a critical transactional system, so I don’t want to create blocking to INSERT or UPDATE statements by acquiring Shared Locks. What are the advantages\ disadvantages of using the READUNCOMMITED table hint?

Answer: Using the READUNCOMMITED or NOLOCK table hint for applications should be used with caution.  Analysis should be based on comparing the Concurrency levels required versus data access. Read more on SQL Server – Concurrency Control – Optimistic and Pessimistic

Advantages

1)  Allows dirty reads

2)  A Shared Lock is not issued. This allows UPDATE and DELETE statements to acquire Exclusive Locks.

3)  Exclusive Locks don’t block the READUNCOMMITTED requests

4)  Higher concurrency

 

 Disadvantages

1)  Risk of other transactions rolling back a transaction while READUNCOMMITTED  in process. Data may not be valid by the end of the Dirty Read.

2)  The application may return invalid data

3)  READUNCOMMITTED captures a Sch-S (Schema Stability Lock) , which conflicts with the Sch-M (Schema Modified Lock)  - such as a schema change.  This causes blocking. READUNCOMMITTED does not guarantee a lock is obtained

4)  The hint substitutes the query optimizer . Review the impact of the READUNCOMMITED before applying in Production

Read More

SQL Server – Concurrency Control – Optimistic and Pessimistic

Quick Insert - SQL Server DBA

SQL Server ISOLATION LEVELs ,UDF and table hints


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer