Skip to main content
Delphix

Masking Using READ_COMMITTED_SNAPSHOT (RCSI) (KBA6450)

 

KBA

KBA# 6450

At 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: 
  • Data read issues (Phantom Reads, masking key columns, filter clauses on masked columns).
  • Database blocks (the masking job becomes unresponsive).
  • Deadlocks.
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:
  • This is an ALTER DATABASE command (permissions are needed and it will affect all SELECT statements).
  • This will consume additional resources on the SQL Server. There needs to be enough space tempdb.
Important Important notes:
  • These commands need to be executed on SQL Server and will require a database-level lock. Therefore, it is essential to ensure that there are no ongoing transactions on the database at the time of execution.
  • Due to the above, it is not possible to execute this command using a Pre-SQL Script.
  • To determine the appropriate size and configuration for the tempdb, it is recommended to refer to Microsoft's documentation, which provides steps and best practices.
  • Consult SQL Server documentation before using these commands. 
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.

 

 

important

Important:

When using this feature, SQL Server will consume additional resources as a copy of all rows in the masked table will be loaded into a temporary location (the location is tempdb but how it works varies between SQL Server versions). This means ample resources are needed 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 could 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 reason.
  • It is recommended to have this for the duration of specific masking jobs, as such you need to apply / clear this before / after the job. If this is a VDB, another option is to apply this in a configure clone hook operation on the Virtualization Engine side.
  • Due to concurrent connection - Pre-SQL and Post-SQL Scripts can't be used (documented in DLPX-78024).

 

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.