bigint)UPDATE 'dbo'.'Heap_2_Col' SET 'Mask' = WHERE ( ( ID = ) ) SELECT 'Mask', 'ID' FROM 'dbo'.'Heap_2_Col' The Masking Logs will just stop (the job will here show 14,9999 rows masked) (only Table input and Update shown). The masked row count will though stop at ' (COMMIT_SIZE x n) - 1'. The Masking Logs will not show any error here. The lock is usually on a PAGE (from MS SQL). LCK_M_IX: this wait type occurs when a thread is waiting for a table or page IX lock so that a row insert or update can occur. This can be seen in the example further down on this page where the index is a Nonclustered Index.The number of pages locked increases when WHERE is used.A larger number of locks are taken - examples of locks types are RID, KEY, PAGE, and TAB.The updated pages are then also scattered.due to the order in the index) resulting in scattered page locks on SELECT. The record order of the fetch is scattered (i.e.Few (as few as possible) numbers of locks are taken - locks seen are (KEY, PAG, TAB).The UPDATE does lock pages for the duration to update 10,000 (Commit Size) records.The SELECT locks are very brief and on two pages at the same time (due to overlap read).The locks are not taken on the same page at the same time.The two page-locks below (X) and (S) are not compatible. This is acceptable due to the following: The ID column is a Primary Key and is Indexed using a Clustered Index. The job is masking a column called ' Mask' using a Unique Row Identifier ( URI) called ' ID'. The animation below shows a normal masking job and how the locks are successfully taken and released. If a 'WHERE' clause is needed, then try removing any index on the columns in the WHERE.Īnimation of Masking Job and MS SQL Locks Limit the number of UPDATE page locks by reducing the COMMIT SIZE (try 1,000 and then 100).Try to avoid using ' WHERE' and ' JOIN' clauses on Heap Tables and when the PK is a NonClustered Index.Primary Keys are normally Clustered Indexes. Use where possible a Clustered Index as the URI.Go to " Management" > " Extended Events" > " Sessions" > "system_health" > " package0.event_file" and click " View Target Data…".īelow are three possible solutions listed: MS SQL Server has a deadlock reporting tool. See below for more details and additional queries. If a masking job hangs, use the following MS SQL command (and look for UPDATE and SELECT from the masked table): This includes Triggers (which should be disabled in this case). Ensure that there is no external transaction running which will change the data on the masked table.Limit the use of Custom SQL which could change the order the data is read.If existing the Masking Engine will automatically use this as the key. If possible use a Clustered Index as the Unique Row Identifier (Key).The best option is when all rows in a page are fetched and then moved on to the next page (accessing a page only once). A Clustered Index Scan will do this and generally also a Table Scans (though we have seen scattered reads on Table Scans). The SELECT statement fetching the data will define the order in which data is read. These points will reduce the chance of encountering a hang (or deadlock). There are some best practices for an MS SQL In-Place masking job. When a lock happens, the data from the SELECT is fetched in a random order causing a scattered read of the data pages for the table. This is a database operation and a hang (or deadlock) happens when a lock is taken on the UPDATE that is not compatible with the lock taken on the SELECT. On MS SQL Server, each action on the database is protected against 'dirty' data by setting locks on objects. This article details possible root causes and possible solutions to Blocks and Deadlocks on MS SQL Server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |