December 28, 2016 at 6:44 am
We are experience deadlocks in a stored procedure. This procedure is doing a merge-operation within a transaction with a HOLDLOCK-hint. It looks something like this:
_________________
BEGIN TRY
-- Create or save transaction as appropriate
IF @TranCount = 0
BEGIN TRANSACTION
...
...
...
MERGE tTable WITH (HOLDLOCK) l
USING (SELECT...
...
...
IF (@@TRANCOUNT > 0) AND (@TranCount = 0)
COMMIT TRANSACTION;
END TRY
_________________
For me this looks strange to have a HOLDLOCK-hint within a transaction. Kinda like duplicate layers of transaction and only one would be needed, right?
We have earlier removed some HOLDLOCKS within transactions in other procedures but this one was (un)intentionally left behind...
December 28, 2016 at 6:59 am
Quick thought, it is a good practice to use HOLDLOCK with the MERGE statement to avoid race conditions, whether that's in an explicit transaction or not is not really relevant.
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply