Using READ_COMMITTED_SNAPSHOT to Workaround Deadlocks (KBA6450)
KBA
KBA# 6450At a Glance
Description: | This article provides a possible workaround to Blocks and Deadlocks on MS SQL Server. |
---|---|
Root cause: | On MS SQL Server, each action on the database is protected against 'dirty' data by setting object locks. This is a database operation and a hang or deadlock happens when a lock is taken on the SELECT, UPDATE, or another statement that cannot be resolved. |
Workaround: | A possible workaround is to change SQL Server to use 'tempdb' to separate out the SELECT SQL statement: ALTER DATABASE <DB_NAME> SET READ_COMMITTED_SNAPSHOT ON; |
Warning: | This will require enough storage in 'tempdb'. The startup time of the masking job will also be longer as a snapshot needs to be taken. |
More info: | As this is a workaround, for more information see: |
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.
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'.
Related Articles
The following articles may provide more information or related information to this article:
- KBA: MS SQL Server Masking and Blocks (Deadlocks) (KBA6319)
- KBA: Executing_SQL_in_a_PowerShell_Hook_Script_(KBA1370)
- KBA: Executing_a_Stored_Procedure_as_a_Masking_Pre-Script_or_Post-Script_(KBA6373)
- Docs: Hook-scripts-for-automation-and-customization
- Docs: Using-pre-and-post-scripts-with-sql-server-vdbs
- External: using-sp_who2-to-help-with-sql-server-troubleshooting