Using READ_COMMITTED_SNAPSHOT to Workaround Deadlocks (KBA6450)

At a Glance

ALTER DATABASE <DB_NAME> SET READ_COMMITTED_SNAPSHOT ON;

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:

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.

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.

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'.