December 8, 2011 at 7:06 am
How are these two statements different with regard to locking the table:
-- Variation 1
BEGIN TRAN
-- Lock the table explicitly via a SELECT
DECLARE @TempId BIGINT=(SELECT TOP(1) Id FROM TargetTable WITH (TABLOCKX, HOLDLOCK));
-- Then perform DML operations
MERGE TargetTable as target
USING (...) AS source
ON (target.Id=source.Id)
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED THEN
INSERT ... ;
DELETE FROM Target
WHERE Id=...
COMMIT TRAN
As compared to:
-- Variation 2
BEGIN TRAN
-- Lock the table as part of the first DML operation
MERGE TargetTable WITH (TABLOCKX, HOLDLOCK) as target
USING (...) AS source
ON (target.Id=source.Id)
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED THEN
INSERT ... ;
DELETE FROM Target
WHERE Id=...
COMMIT TRAN
The same table is getting a TABLOCKX put on it in both cases, but with Variation 2, I get a deadlock when multiple sessions concurrently execute this code. With Variation 1, I do not. I thought the two would work identically, but apparently there is some difference in how the lock TABLOCKX is acquired or how long it is held.
Could some knowledgeable person please shed some light on this.
December 8, 2011 at 7:44 am
Do you have a transaction in your code? From what you wrote you didn’t start a transaction at both versions, so in the first version after the select statement finished running, it released the locks and the merge statement was using the default locking mechanism. In the second version the merge statement tried to get an exclusive table lock.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 8, 2011 at 7:56 am
I should have mentioned that the code in question is executing in an explicit transaction block. Had it not, neither version would have prevented the deadlock situation, since the TABLOCKX would not last up to the DELETE statement.
I should have put the BEGIN TRAN / COMMIT around the code I presented to make this explicit and I have edited my post to include this. Thank you for pointing this out.
December 8, 2011 at 4:06 pm
From MSDN for MERGE (Transact-SQL) http://msdn.microsoft.com/en-us/library/bb510625.aspx: (emphasis mine)
<table_hint_limited>
Specifies one or more table hints that are applied on the target table for each of the insert, update, or delete actions that are performed by the MERGE statement. The WITH keyword and the parentheses are required.
NOLOCK and READUNCOMMITTED are not allowed. For more information about table hints, see Table Hints (Transact-SQL).
Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. An exclusive lock is taken on the table. When FORCESEEK is specified, it is applied to the implicit instance of the target table joined with the source table.
The locks aren't taken until the inserts, updates or delete actions. Therefore, they don't apply to the SELECT portion of the merge, which is why you can get a deadlock.
December 9, 2011 at 7:03 am
Wow, thanks, that's really good to know and quite different from what I expected. I guess thinking of MERGE as a single atomic DML operation is a red herring.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply