BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News NOLOCK is Broken in SQL Server 2014 SP1

NOLOCK is Broken in SQL Server 2014 SP1

Bookmarks

The NOLOCK directive was broken in Cumulative Update #6 for SQL Server 2014 SP1. As a result, databases that relied on that directive may experience unexpected blocking and/or deadlocks. According to an updated article on the SQL Server Release Services blog, the specific scenarios are:

Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries that try to access SourceTable will be blocked.

While one transaction is holding an exclusive lock on an object (for example, an ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable by using the NOLOCK hint. In this scenario, the SELECT query that is trying to access SourceTable will be blocked.

According to Pedro Lopes, a member of the release team at Microsoft, this bug does not affect code that uses SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Prior to the introduction of row level versioning, also known as snapshot isolation, the NOLOCK directive was often necessary to avoid blocking and deadlocks. However, there are serious risks associated with the its use. By design, queries running under NOLOCK can read data that is in the process of being changed inside a transaction. This means decisions can be based on a transaction that will eventually be rolled back. Likewise, decisions can be based on partial updates, such as using the new version of one record and the old version of another, related record.

For these reasons, NOLOCK (and READ UNCOMMITTED) are generally frowned upon in new databases. However, one can’t simply just switch an existing database to use row level versioning. While it can solve the blocking issue without dirty reads, it does so at the cost of increased tempdb usage.

Brent Ozar Unlimited has offered a notification service to inform DBAs when the issue has been resolved. You can sign up at sqlserverupdates.com.

Rate this Article

Adoption
Style

BT