Sql-Server

合併導致死鎖

  • September 20, 2015

我們正在執行 MS SQL Server 2008 R2。

我們有一個 VendorMaster 表（大約有 8700 行），我們試圖使用 Merge 語句在兩個數據庫之間保持同步。合併程式碼在 SQL 調度程序中每 5 分鐘執行一次，但由於死鎖情況，大約每小時執行一次。sql 程式碼在 management studio 中執行時，大約需要 28 秒才能執行。

源表定義如下：

/****** 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

另一個數據庫中的目標表定義為：

/****** 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

這是我們的 Merge 語句的程式碼：

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

這是此更新的合併語句的執行計劃：

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

大約每小時一次，由於與其他程序的死鎖，查詢被終止。除了這個之外，目標數據庫中沒有任何程序正在更新 VendorMaster 表。那麼，這似乎表明死鎖必鬚髮生在源數據庫中？正確的？合併期間源表中的行是否被鎖定？我們可以指定只對源表進行讀鎖定嗎？

我的建議是你做一個合併 upsert（只插入和更新）。如果要刪除記錄，我建議您在源和目標之間創建一個臨時表。登台表被清除、插入，然後與目標表合併。創建一個按計劃清理源表的作業（如果需要）。我建議您在您的情況下將刪除排除在合併之外。

至於你的僵局。執行計劃不會告訴你太多。您需要生成死鎖跟踪。您可以通過在 SQL-profiler 中打開跟踪（謹慎/臨時）來做到這一點。閱讀 MSDN 了解更多資訊。

死鎖跟踪可以生成一個死鎖圖，向您顯示正在發生的事情以及究竟發生了什麼死鎖。您可以分別在源表和目標表上生成 Shared-X 鎖和獨占鎖。

引用自：https://dba.stackexchange.com/questions/115512

相關問答

Sql-Server

合併語句本身死鎖

  • October 4, 2019
檢視問答
Sql-Server

如何插入/組合來自多個表的值？

  • December 25, 2021
檢視問答
Sql-Server

診斷執行多層儲存過程的程序中的塊

  • March 2, 2021
檢視問答
Sql-Server

為什麼死鎖圖上有無受害者條目？

  • October 5, 2020
檢視問答
Sql-Server

在合併中使用 Where 子句

  • June 9, 2020
檢視問答
Sql-Server

在合併更新語句中使用聚合

  • May 7, 2020
檢視問答