SQL Server 2014 SP1 NOLOCK command interrupted

SQL Server 2014 SP1 NOLOCK command interrupted

In the Cumulative Update #6 Update for SQL Server 2014 SP1, The NOLOCK command is interrupted. Therefore, databases dependent on the NOLOCK command may experience congestion or deadlocks in other places. According to a newly updated article on the SQL Server Release Services blog, the specific scenario is as follows:

Execute parallel SELECT (…) at the default lock-based isolation level or higher isolation level (...) INTO Table FROM
SourceTable statement, especially when NOLOCK is used. In this case, other queries that attempt to access SourceTable will be blocked.
When a transaction holds the exclusive lock of an object (such as updating a table in progress), another transaction is executing a parallel SELECT (...) FROM
SourceTable, and use the NOLOCK prompt. In this case, SELECT queries that attempt to access SourceTable will be blocked.

According to Pedro Lopes, a member of the Microsoft release team, this error does not affect the code that uses set transaction isolation level read uncommitted.

As early as row-level versioning was launched, also known as Snapshot isolation, NOLOCK prompts often need to avoid blocking and deadlock. However, using it may pose some risks. Through design, the query running NOLOCK can read the changed data in the transaction. This indicates that the decision can be based on the final rollback transaction. Similarly, the decision can also be based on local 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) is generally not supported in the new database ). However, we cannot simply transfer an existing database to a row-level version. Although it can solve the blocking problem without dirty reads, it still needs to increase the cost of tempdb usage.

Brent Ozar Unlimited provides a notification service that notifies all DBAs when the problem is resolved. You can register at sqlserverupdates.com.

NOLOCK is Broken in SQL Server 2014 SP1

This article permanently updates the link address: