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?

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