Resolving Issues Where A Masking Job's SELECT Operations Block It's UPDATE Operations (KBA6450)
KBA
KBA# 6450
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.
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'.
Related Articles
The following articles may provide more information or related information to this article:
- Executing_SQL_in_a_PowerShell_Hook_Script_(KBA1370)
- hook-scripts-for-automation-and-customization
- using-pre-and-post-scripts-with-sql-server-vdbs
- Executing_a_Stored_Procedure_as_a_Masking_Pre-Script_or_Post-Script_(KBA6373)
- using-sp_who2-to-help-with-sql-server-troubleshooting