Skip to main content
Delphix

Using READ_COMMITTED_SNAPSHOT to Workaround Deadlocks (KBA6450)

 

KBA

KBA# 6450

At a Glance

Description: This article provides a possible workaround to Blocks and Deadlocks on MS SQL Server. 
Root cause: On MS SQL Server, each action on the database is protected against 'dirty' data by setting object locks. This is a database operation and a hang or deadlock happens when a lock is taken on the SELECTUPDATE, or another statement that cannot be resolved.
Workaround: A possible workaround is to change SQL Server to use 'tempdb' to separate out the SELECT SQL statement: 
 
ALTER DATABASE <DB_NAME> SET READ_COMMITTED_SNAPSHOT ON;
Warning: This will require enough storage in 'tempdb'. The startup time of the masking job will also be longer as a snapshot needs to be taken.
More info: As this is a workaround, for more information see: 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Issue

When masking a table on MS SQL Server, you may encounter issues where a masking job SELECT statement is blocking the job UPDATE statements. This happens in the default isolation level because SQL Server is using locking mechanisms to ensure the data in a SELECT is consistent for the duration of that SELECT (in order to avoid dirty reads). 

When masking, the engine reads every selected row out of a table (using SELECT and WHERE) and this can cause the UPDATE operations (which for performance optimization run in parallel with the SELECT) to get blocked or possibly deadlocked.

Resolution

A workaround is to separate the SELECT to read from 'tempdb'. To do this, you have to perform an ALTER DATABASE statement which will look something like the following:

ALTER DATABASE <DB_NAME> SET READ_COMMITTED_SNAPSHOT ON;

This change will cause the database to perform a database level snapshot of the data at the time of the SELECT so that the SELECT operation can read from the snapshot rather than the raw table. When this happens, the SELECT operation no longer needs to use locks to keep the data consistent which allows the UPDATE operations to proceed unhindered.

 

important

Important:

When using this feature, a copy of all rows in the masked table will be loaded into the 'tempdb' database. This means 'tempdb' needs to be large enough to accommodate a copy of the largest masked table in your masking job (or more, if the job is configured with multiple Streams). It also means the masking performance will be negatively affected by using this.

 

Note

Notes:

  • This procedure will NOT solve the deadlock issue that happens with multiple writers to the same table. This issue appears to be similar because of the deadlocks, but it arises from a different source.
  • If you want this change to only exist for the duration of masking, you may prefer to apply / clear this change as masking job pre- and post-scripts. If this is a VDB and you want to make the change permanent for the VDB, you might consider applying this change as a configure clone hook operation on the Virtualization Engine side.

 

Troubleshooting

You can help to diagnose if this is happening on MS SQL Server by using the sp_who2 utility. It produces a column indicating if the SQL call is being blocked and by whom.

Using-sp_who2-to-identify-blocked-queries.jpg

It is important to note that if you change from the default isolation level to READ_COMMITTED_SNAPSHOT, the change involves holding a database level snapshot of the data in a table (or tables if you have multiple streams), and a database level snapshot involves copying rows (every row by default) into 'tempdb'.