Sql-server – Microsoft SQL Server MERGE deadlock issue

deadlockmergesql server

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]

ReportType is a user-defined type like below:

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

Best Answer

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.

Related Solutions

Sql-server – How to create SOAP endpoint in SQL Server 2016

You can no longer use SOAP/HTTP endpoints. This isn't a SQL Server 2016 problem, since this would not have worked in 2014 either. From the documentation on CREATE ENDPOINT:

NOTE: Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

It has been deprecated for at least 8 years (this is the oldest version of the current document I could find):

Deprecated Database Engine Features in SQL Server 2008

If you look at that document, you will see that "The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option" was deprecated at that time. Their suggestion for a replacement is to "Use Windows Communications Foundation (WCF) or ASP.NET instead."

Arguably, there could be a better error message, like the following (emphasis mine):

Msg 7877, Level 16, State 1
SOAP/HTTP endpoints are no longer supported in this version of SQL Server.

But your workarounds remain the same:

Continue using an older version of SQL Server.
Find another source for this information that doesn't require this type of information.
As the docs suggest, don't try to make SQL Server perform this work (it's not a web browser) - e.g. create an application or service that stores the data in SQL Server.

Sql-server – Simple insert in stored procedure runs twice

I created a test DB and table, I adjusted the schema to make it a bit easier and also commented out the set in the proc.

USE [Test]
GO

/****** Object:  Table [dbo].[news]    Script Date: 2/4/2017 12:21:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[news](
    [id_news] [int] NULL,
    [news_owner_id] [int] NULL,
    [news_owner_type] [bit] NULL,
    [news_cat_id] [int] NULL,
    [news_producer] [nvarchar](1000) NULL,
    [news_title] [nvarchar](1000) NULL,
    [news_sutitr] [nvarchar](1000) NULL,
    [news_text] [nvarchar](1000) NULL,
    [news_date] [nvarchar](128) NULL,
    [news_time] [nvarchar](16) NULL,
    [news_link] [nvarchar](512) NULL,
    [img_uri_type] [tinyint] NULL,
    [img_uri] [xml] NULL,
    [newsContentID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I'm going to answer 2 first. I ran the proc once, it inserted one time. I looked through the execution plan and it shows a straight forward insert. You can see it here: Standard run. I then set your error handling to 0, just to see the plan with that being triggered. Also straight forward. Error handling triggered. I would run SQL Profiler when you see it insert two records or set up sp_whoisactive to catch the plan when it does multiple inserts. My guess is that it's being executed twice.

For question 1, it looks fine to me. You could put in more exact error handling, but overall I would not say it's bad. I really like this template by spaghetti dba.

Best Answer

Related Solutions

Sql-server – How to create SOAP endpoint in SQL Server 2016

Sql-server – Simple insert in stored procedure runs twice

Related Question