Masking Using READ_COMMITTED_SNAPSHOT (RCSI) (KBA6450)
KBA
KBA# 6450At a Glance
Description | This article provides a possible workaround to some specific Masking issues, such as Blocks and Deadlocks on MS SQL Server. This usually affects In-Place (IP) masking jobs, seen as a hung job. This solution can also be used to resolve Negative rows Remaining in the Job Monitor (caused by Phantom Reads). |
---|---|
Root cause | On MS SQL Server, the SELECT and UPDATE might need to access the same Database objects and this could cause data read errors or blocks. Examples of the root causes that this KBA provides:
|
Workaround | The workaround is to limit the affected tables to a separate job. Then use this ALTER DATABASE command which will set READ COMMITTED SNAPSHOT: Before masking: ALTER DATABASE <DB_NAME> SET READ_COMMITTED_SNAPSHOT ON;After masking: ALTER DATABASE <DB_NAME> SET READ_COMMITTED_SNAPSHOT OFF; |
Warning | There are two key warnings - please consult your DBA before running the command:
|
Important | Important notes:
|
More info | For more information see: |
Applicable Delphix Versions
- Click here to view the versions of the Delphix engine to which this article applies
-
Date Release Feb 13, 2023 8.0.0.0 Jan 12, 2023 7.0.0.0 Releases Prior to 2023 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.2.1, 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, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2
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 causes inconsistent reads or the read is blocking the job UPDATE statements.
Inconsistent reads
This happens because the update is changing data pages on the table that is being read, or if a filter is used on masked columns which changes the result set while the masking job is running.
The other cause is masking the key column (such as the Primary Key) - the masked values will affect the updated record set.
Blocks and Deadlocks
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 phantom reads (aka dirty reads)).
When the masking job starts, the engine reads every row in the table (as specified by the SELECT and WHERE) and this can cause the UPDATE operations (which for optimized performance runs in parallel with the SELECT) to get blocked or possibly deadlocked.
Resolution
A workaround is to separate the SELECT from the core table. To do this, use the 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
As this is a SQL Server command, troubleshooting will need to start on the SQL Server database.
Additional documentation can be found in the following section or the More Info section at the top of this article.