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?