Resolving Issues Where A Masking Job's SELECT Operations Block It's UPDATE Operations (KBA6450)

Issue

When Masking MS SQL Server you may encounter issues where a masking job's SELECT calls seem to be blocking the job's UPDATE calls.  This happens in the default isolation level because the DB uses locking mechanisms to ensure that the data in a SELECT is consistent for the duration of that SELECT (in order to avoid 'dirty reads'). 

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

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

Resolution

The best resolution that we have for this issue at this time is to change the isolation level of the database, at least for the duration of masking.  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. That means that '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 that 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.  That issue appears to be similar because of the deadlocks, but it springs from a different source.
• If you want this change to only exist for the duration of masking, you may prefer to apply / unapply 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, that 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'.