SQL Server – Resolving Deadlocks Caused by Merge

We are running MS SQL Server 2008 R2.

We've got a VendorMaster table (with about 8700 rows) that we are trying to keep in sync across two databases using a Merge statement. The merge code runs every 5 minutes in the SQL Scheduler but about once per hour gets killed due to a deadlock situation. The sql code, when run in management studio, takes about 28 seconds to run.

The source table is defined as follows:

/****** Object:  Table [dbo].[VendorMaster]    Script Date: 09/18/2015 10:59:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[VendorMaster](
    [VendorID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [char](50) NOT NULL,
    [AddLine1] [char](50) NULL,
    [AddLine2] [char](50) NULL,
    [City] [char](25) NULL,
    [State] [char](3) NULL,
    [Zip] [char](10) NULL,
    [Phone] [char](20) NULL,
    [Fax] [char](14) NULL,
    [Commission] [real] NULL,
    [Contact] [char](50) NULL,
    [MemberPercent] [real] NULL,
    [Link_Variable] [varchar](12) NULL,
    [Click_To_URL] [varchar](255) NULL,
    [Vendor_Label] [varchar](64) NULL,
    [image_path] [varchar](255) NULL,
    [vmt_code] [int] NOT NULL,
    [Description] [varchar](250) NULL,
    [vm_date_added] [datetime] NULL,
    [vm_last_modified] [datetime] NULL,
    [vm_who_modified] [char](10) NULL,
    [ContactEmail] [varchar](255) NULL,
    [CoBrandedPage] [bit] NULL,
    [CoBrandedPageDesc] [varchar](8000) NULL,
    [CoBrandedPageLastChecked] [datetime] NULL,
 CONSTRAINT [PK___2__11] PRIMARY KEY CLUSTERED 
(
    [VendorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [Unq_VendorID] UNIQUE NONCLUSTERED 
(
    [VendorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[VendorMaster] ADD  CONSTRAINT [DF_VendorMaster_vmt_code]  DEFAULT (1) FOR [vmt_code]
GO

ALTER TABLE [dbo].[VendorMaster] ADD  CONSTRAINT [DF_VendorMaster_vm_date_added]  DEFAULT (getdate()) FOR [vm_date_added]
GO

ALTER TABLE [dbo].[VendorMaster] ADD  DEFAULT ((0)) FOR [CoBrandedPage]
GO

The destination table in the other database is defined as:

/****** Object:  Table [dbo].[VendorMaster]    Script Date: 09/18/2015 11:00:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[VendorMaster](
    [VendorID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [AddLine1] [varchar](50) NULL,
    [AddLine2] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Zip] [varchar](10) NULL,
    [Phone] [varchar](20) NULL,
    [Fax] [varchar](14) NULL,
    [Commission] [real] NULL,
    [Contact] [varchar](50) NULL,
    [MemberPercent] [real] NULL,
    [Link_Variable] [varchar](12) NULL,
    [Click_To_URL] [varchar](255) NULL,
    [Vendor_Label] [varchar](64) NULL,
    [image_path] [varchar](255) NULL,
    [vmt_code] [int] NOT NULL,
    [Description] [varchar](250) NULL,
    [vm_date_added] [datetime] NULL,
    [vm_last_modified] [datetime] NULL,
    [vm_who_modified] [varchar](10) NULL,
    [ContactEmail] [varchar](255) NULL,
    [CoBrandedPage] [bit] NULL,
    [CoBrandedPageDesc] [varchar](8000) NULL,
    [CoBrandedPageLastChecked] [datetime] NULL,
 CONSTRAINT [PK___2__11] PRIMARY KEY CLUSTERED 
(
    [VendorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [Unq_VendorID] UNIQUE NONCLUSTERED 
(
    [VendorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[VendorMaster] ADD  CONSTRAINT [DF_VendorMaster_vmt_code]  DEFAULT ((1)) FOR [vmt_code]
GO

ALTER TABLE [dbo].[VendorMaster] ADD  CONSTRAINT [DF_VendorMaster_vm_date_added]  DEFAULT (getdate()) FOR [vm_date_added]
GO

ALTER TABLE [dbo].[VendorMaster] ADD  DEFAULT ((0)) FOR [CoBrandedPage]
GO

Here is the code for our Merge statement:

SET QUOTED_IDENTIFIER OFF

SET IDENTITY_INSERT targetdb.dbo.vendormaster ON

BEGIN TRAN;

    MERGE targetdb.dbo.vendormaster AS tvm
    USING vendormaster AS svm ON tvm.vendorid = svm.vendorid
    WHEN NOT MATCHED BY TARGET
        THEN INSERT
        (
            VendorID
            ,Name
            ,AddLine1
            ,AddLine2
            ,City
            ,State
            ,Zip
            ,Phone
            ,Fax
            ,Commission
            ,Contact
            ,MemberPercent
            ,Link_Variable
            ,Click_To_URL
            ,Vendor_Label
            ,image_path
            ,vmt_code
            ,Description
            ,vm_date_added
            ,vm_last_modified
            ,vm_who_modified
            ,ContactEmail
            ,CoBrandedPage
            ,CoBrandedPageDesc
            ,CoBrandedPageLastChecked
        ) 
        VALUES
        (
            svm.VendorID
            ,svm.Name
            ,svm.AddLine1
            ,svm.AddLine2
            ,svm.City
            ,svm.State
            ,svm.Zip
            ,svm.Phone
            ,svm.Fax
            ,svm.Commission
            ,svm.Contact
            ,svm.MemberPercent
            ,svm.Link_Variable
            ,svm.Click_To_URL
            ,svm.Vendor_Label
            ,svm.image_path
            ,svm.vmt_code
            ,svm.Description
            ,svm.vm_date_added
            ,svm.vm_last_modified
            ,svm.vm_who_modified
            ,svm.ContactEmail
            ,svm.CoBrandedPage
            ,svm.CoBrandedPageDesc
            ,svm.CoBrandedPageLastChecked
        )

    WHEN MATCHED AND EXISTS
            (SELECT svm.VendorID
                    ,svm.Name
                    ,svm.AddLine1
                    ,svm.AddLine2
                    ,svm.City
                    ,svm.State
                    ,svm.Zip
                    ,svm.Phone
                    ,svm.Fax
                    ,svm.Commission
                    ,svm.Contact
                    ,svm.MemberPercent
                    ,svm.Link_Variable
                    ,svm.Click_To_URL
                    ,svm.Vendor_Label
                    ,svm.image_path
                    ,svm.vmt_code
                    ,svm.Description
                    ,svm.vm_date_added
                    ,svm.vm_last_modified
                    ,svm.vm_who_modified
                    ,svm.ContactEmail
                    ,svm.CoBrandedPage
                    ,svm.CoBrandedPageDesc
                    ,svm.CoBrandedPageLastChecked 

             EXCEPT
             SELECT tvm.VendorID
                    ,tvm.Name
                    ,tvm.AddLine1
                    ,tvm.AddLine2
                    ,tvm.City
                    ,tvm.State
                    ,tvm.Zip
                    ,tvm.Phone
                    ,tvm.Fax
                    ,tvm.Commission
                    ,tvm.Contact
                    ,tvm.MemberPercent
                    ,tvm.Link_Variable
                    ,tvm.Click_To_URL
                    ,tvm.Vendor_Label
                    ,tvm.image_path
                    ,tvm.vmt_code
                    ,tvm.Description
                    ,tvm.vm_date_added
                    ,tvm.vm_last_modified
                    ,tvm.vm_who_modified
                    ,tvm.ContactEmail
                    ,tvm.CoBrandedPage
                    ,tvm.CoBrandedPageDesc
                    ,tvm.CoBrandedPageLastChecked )
        THEN UPDATE 
        SET tvm.Name = svm.Name
            ,tvm.AddLine1 = svm.AddLine1
            ,tvm.AddLine2 = svm.AddLine2
            ,tvm.City = svm.City
            ,tvm.State = svm.State
            ,tvm.Zip = svm.Zip
            ,tvm.Phone = svm.Phone
            ,tvm.Fax = svm.Fax
            ,tvm.Commission = svm.Commission
            ,tvm.Contact = svm.Contact
            ,tvm.MemberPercent = svm.MemberPercent
            ,tvm.Link_Variable = svm.Link_Variable
            ,tvm.Click_To_URL = svm.Click_To_URL
            ,tvm.Vendor_Label = svm.Vendor_Label
            ,tvm.image_path = svm.image_path
            ,tvm.vmt_code = svm.vmt_code
            ,tvm.Description = svm.Description
            ,tvm.vm_date_added = svm.vm_date_added
            ,tvm.vm_last_modified = svm.vm_last_modified
            ,tvm.vm_who_modified = svm.vm_who_modified
            ,tvm.ContactEmail = svm.ContactEmail
            ,tvm.CoBrandedPage = svm.CoBrandedPage
            ,tvm.CoBrandedPageDesc = svm.CoBrandedPageDesc
            ,tvm.CoBrandedPageLastChecked = svm.CoBrandedPageLastChecked

    WHEN NOT MATCHED BY SOURCE
        THEN DELETE;

    --OUTPUT $action, Inserted.*, Deleted.*;

commit TRAN;

SET IDENTITY_INSERT targetdb.dbo.vendormaster OFF

update targetdb.dbo.vendormaster
set vmt_code = 3
where vmt_code = 1
    and VendorID in
    (
        select VendorID
        from VendorException ve
        where ve.MainVendorId in (select VendorID from targetdb.dbo.VendorMergeExceptions)
    )       

SET QUOTED_IDENTIFIER ON
GO

Here is the execution plan for this updated merge statement:

http://beta1.igive.com/test/igive.zip

About once per hour the query gets killed due to a deadlock with some other process. There are no processes in the target database which are updating the VendorMaster table other than this one. So, that would seem to indicate that the deadlock must be occurring in the source database? Right? Are the rows in the source table locked during the merge? Could we specify just doing a read lock on the source table?

Best Answer

My recommendation is you do a merge upsert (Only insert and update). If you want delete the records, I recommend you create a staging table between the source and target. The staging table gets cleaned out, inserted into, and then merged with the target table. Create a job that cleans up the source table on a schedule (if that's required). I recommend you leave deletes out of the merge in your case.

As far as your deadlock. The execution plan will not tell you much. You need to generate a deadlock trace. You can do this by turning on tracing (cautiously/temporarily) in SQL-profiler. Read MSDN for more info.

Deadlock traces can generate a deadlock graph that shows you what is happening and what exactly is getting deadlocked. You could be generating a Shared-X lock and a Exclusive Lock on the source and target table, respectively.

Best Answer

Related Solutions

SQL Server – Merge Statement Deadlocking Itself

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Related Question