0

I am using Microsoft SQL Server version:

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 6.3 (Build 14393: ) (Hypervisor)

I have a stored procedure using a MERGE statement that is named [UpdateReports] enter image description here

ReportType is a user-defined type like below:

enter image description here

When I run the UpdateReports procedure, it works good in normal situations. But when it is executed while a full backup is running on the database, Update Statistic (I think many reads of DB), this UpdateReports procedure deadlocks and it takes about 10 minutes to complete.

Is it because I am using HOLDLOCK ?

I searched this issue and many people saying MERGE is not stable....

Could you please let me know if there is any good alternative instead of MERGE?

If I can use IF/ELSE, UPDATE/INSERT instead of MERGE.... I am worried about concurrency

  • >>>this UpdateReports procedure deadlock and it takes about 10 minutes to complete<<< Any deadlock is resolved by server in less than 5 seconds, so what you are talking about? Locking? – sepupic Sep 18 at 6:46
  • 3
    Can you post details on the deadlock, including the deadlock graph? Are you seeing a single deadlock or many deadlocks? Also, including the formatted code is preferred over a screenshot, as it makes it easier for us to help you. – AMtwo Sep 18 at 10:54
2

I think what you are talking about is not "deadlock issue", it's not even blocking issue.

Full backup locks nothing, but when it happens you have intesive I/O operations, it's just increasing server load. Full backup should be done with the less possible activity on the server, but your activity is not even readonly, it's data modification. If your db is in simple recovery model, making merge during full backup means fully logging, so you have more writelog on inserts.

Update statistics really can deadlock with merge when it reads indexes in opposite order, I saw the cases. But it's surely not your case:

The system task Deadlock Monitor wakes up every five seconds and checks if there are any deadlocks in the system. When a deadlock is detected, SQL Server rolls back one of the transactions with the error 1205. That releases all locks held in that transaction and allows the other sessions to continue.

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners By Dmitri Korotkevitch

So when you say that your task completes in 10 minutes it's definitely not a case of a deadlock: even if it could be UPDATE STATISTICS to be rollbacked, it happened in some seconds, then it was killed and your merge continued as usual.

To find out the exact cause of slowness you should monitor sys.dm_os_waiting_tasks during the execution to detect for what your merge is waiting for.

  • 1
    @George.Palacios I updated my answer – sepupic Sep 19 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.