4

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?

Improve this question
2
  • 3
    Because you are checking for the vendorID to be equal, I am guessing that you are in fact updating all 8721 records when you run this. If you were checking for the other values (Name, AddLine1, AddLine2, etc...) to be equal as well, then you would likely be updating much fewer rows but since you are just checking for Vendor equality you will update the records even if all the values are the same as long as the Vendor exists in both target and destination. In terms of the deadlock, I would look here sqlskills.com/blogs/paul/…
    – mskinner
    Sep 18, 2015 at 18:28
  • Thanks for this insight. It actually occurred to me (duh) as I was writing the original question. So, after writing it I found how to only update the changed rows. That was a good change but didn't solve the base issue of the deadlock.
    – S. Schleicher
    Sep 21, 2015 at 19:54

2 Answers 2

Reset to default
1

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.

Improve this answer
1
  • Didn't know about the deadlock graph feature. Very helpful. Found that two merges, the one above and another which was impacting a schema bound view tied to the VendorMaster table were running on the same schedule and butting heads once per hour. Consolidating the two jobs into sequential steps in the same job solved the problem. Thanks for all the help!
    – S. Schleicher
    Sep 21, 2015 at 19:53
0

I have just had a similar problem on SQL 2017, but changing the QUOTED IDENTIFER to "ON" fixed it. I have to admit, my issue involved a table with an index on a computed column so this may have been the reason in my case. I noticed it was OFF on the MERGE script but ON for the table scripts. I also notice it was turned ON at the end of the MERGE script! Why? I am just adding it here because even though it is an old post it still came up high in the Google search for "DEADLOCK" AND "QUOTED IDENTITIFER"

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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