Zeroing in on blocking on seemingly unrelated tables

In one of our recent lab tests we were surprised to see blocking occur on a table that did not participate in the transaction being reported as the cause of the blocking. From the sp_lock output we noticed an ‘X’ lock being held on the table, but we could guarantee that there was no insert, delete or update activity on the table. Needless to say, this was odd and baffled us for while and it was only when we analyzed the definitions of the tables that we could determine the source of the problem. Let’s take a look at this scenario via a simplified example and explain the cause of the blocking.

Consider the case where we have two tables, ORDERS and ORDER_LINE and a foreign-key relationship as shown below.

These tables are populated with the following 4 rows of data:

INSERT INTO ORDERS (ORDER_ID, CREATED_BY, DUE_DATE) VALUES

       (100, 'Burzin', '11/24/2003'),

       (101, 'Burzin', '11/28/2003');

      

INSERT INTO ORDER_LINE (ORDER_ID, ORDER_LINE_ID, ITEM, QUANTITY) VALUES

       (100, 1, 1028, 12),

       (101, 2, 1029, 24);

Furthermore, we had Read Committed Snapshot Isolation (RCSI) enabled on the database.

In our scenario we had two transactions executing the following two T-SQL statements via separate database connections (different SPIDs).

SPID-56

BEGIN TRAN

UPDATE ORDER_LINE SET ORDER_ID = 101 WHERE ORDER_ID = 100;

...

SPID-57

BEGIN TRAN

DELETE ORDERS WHERE ORDER_ID = 100;

...

(NOTE: both these transactions operate on different tables.)

When these transactions were executed, we observed that blocking occured on the ORDERS table. This was a bit non-intuitive and baffling at first. To get to the root of the problem we started by investigating the common causes but couldn’t find any reason for the two transactions operating on different tables to block each other. However, on further analysis we noticed that the ORDER_LINE table had a foreign-key relationship to the ORDERS table and because of this when the ORDER_LINE table was updated, the ORDERS table was referenced to ensure that the foreign-key relationship was being preserved. This was why the DELETE statement held a shared (‘S’) lock on the rows of child table, ORDER_LINE, even though it wass only deleting from parent table, ORDERS.

This solved a part of the mystery. Upon looking at the output of sp_lock we observed that there were two exclusive (X) Keylocks (see rows 11 and 12 in the screenshot below) acquired on the ORDERS table which were causing other transactions operating on the ORDERS table to block.

This once again was a bit confusing. If the ORDERS table was being accessed solely to verify the referential integrity of the data and preserve the foreign-key relationship, why were exclusive locks being acquired? Furthermore, since RCSI was enabled on the database we expected the read and write operations to not cause any blocking.

On digging deeper we determined that the database engine had to acquire an ‘X’ lock on the ORDERS table as soon as the second transaction tried to modify a row to prevent the possibility of the referential integrity being broken. One could imagine a pathological case where the first transaction updated the ORDER_LINE table with a value that qualified the foreign-key relationship, and then the second transaction DELETE the value from the ORDERS table leaving the referential integrity broken and the foreign-key pointing to a phantom value. In fact this is exactly what occurs in the example presented above and is depicted by the screenshot where ‘S’ lock on ORDER_LINE table requested by the DELETE statement is blocked by the UDATE statement. Therefore the blocking chain can be viewed as: Queries accessing the ORDERS table à blocked by DELETE statement à blocked by UPDATE statement. To prevent this situation from occurring, the database engine acquires an ‘X’ lock on a referenced table (ORDERS) as soon as it determines that there is a data modification transaction operating on it. This is by design and expected behavior as without this behavior there is a possibility of the referential integrity between the tables breaking.

If your application is encountering a similar issue you may want to try work around it by:

1. Modifying your application so that two transactions do not operate on the same or referenced objects concurrently, e.g. in the above example the update and the delete could have been performed via a single transaction

2. Removing the referential integrity. This assumes that your application does not need this, or can enforce it via some other means, e.g. within the business logic layer of the application.

 

Cross Posted from https://blogs.microsoft.com/mssqlisv

Comments

• Anonymous
  February 23, 2009
  You've been kicked (a good thing) - Trackback from DotNetKicks.com