t-sql - SQL Azure : Deadlock on concurrent delete operations

Question

我的 SQL azure 数据库遇到一些由并发删除操作引起的死锁，我不确定如何解决它。我把情况简化到了最基本的程度。我有下表:

CREATE TABLE [dbo].[Test2013](
    [ClientID] [int] NOT NULL,
    [ID] [uniqueidentifier] NOT NULL,
    [Value] [int] NOT NULL,
 CONSTRAINT [dbo-Test2013] PRIMARY KEY CLUSTERED 
(
    [ClientID] ASC,
    [ID] ASC
)
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
       ALLOW_PAGE_LOCKS  = ON)
)

GO

ALTER TABLE [dbo].[Test2013] ADD CONSTRAINT [Test2013-ID-Default-Value]
     DEFAULT (newid()) FOR [ID]
GO

导致该问题的查询如下:

INSERT INTO [Test2013]
    ([ClientID],[ID],[Value])
    SELECT
        CAST(-2147483648 AS INT) [ClientID], 
        '82ecb924-d2f0-44ee-9a8e-5240d12de088' [ID], 
        CAST(1 AS INT) [Value]

INSERT INTO [Test2013]
    ([ClientID],[ID],[Value])
    SELECT
        CAST(-2147483648 AS INT) [ClientID], 
        '82ecb924-d2f0-44ee-9a8e-5240d12de077' [ID], 
        CAST(2 AS INT) [Value]

 DECLARE @MyDateTime DATETIME
SET @MyDateTime = DATEADD(s,5,GETDATE())

DECLARE @MyDateTime2 DATETIME
SET @MyDateTime2 = DATEADD(ms,1,@MyDateTime)

BEGIN tran
    WAITFOR TIME @MyDateTime;
    DELETE FROM [Test2013]
    WHERE [ID] = '82ecb924-d2f0-44ee-9a8e-5240d12de088';
Commit tran

BEGIN tran
    WAITFOR TIME @MyDateTime2;
    DELETE FROM [Test2013]
    WHERE [ID] = '82ecb924-d2f0-44ee-9a8e-5240d12de077';
Commit tran

我认为这相对微不足道，但我无法找出实际锁定查询的内容。我已检查 sys.events_log 表，它不包含任何新的死锁事件。我以前见过其他死锁，但它们都抛出了我可以处理的异常，这个只是无限期地挂起。

顺便说一句，如果我将第二个操作延迟 50 毫秒，它就可以正常工作。

Answer 1

这里的挂起实际上并不是由死锁引起的，而是因为你正在等待一个已经过去的时间。下面是您的查询的修改版本，它会发送当前时间以及我运行中的消息。正如您所看到的，当您等待@MyDateTime2时，它已经过去了。 50 毫秒之所以有效，是因为不需要 50 毫秒就能完成所有这些工作。我将 WAITFOR TIME 更改为 WAITFOR DELAY，并且有效。不过，它实际上不会等待一毫秒。

INSERT INTO [Test2013]
    ([ClientID],[ID],[Value])
    SELECT
        CAST(-2147483648 AS INT) [ClientID], 
        '82ecb924-d2f0-44ee-9a8e-5240d12de088' [ID], 
        CAST(1 AS INT) [Value]

INSERT INTO [Test2013]
    ([ClientID],[ID],[Value])
    SELECT
        CAST(-2147483648 AS INT) [ClientID], 
        '82ecb924-d2f0-44ee-9a8e-5240d12de077' [ID], 
        CAST(2 AS INT) [Value]

 DECLARE @MyDateTime DATETIME
SET @MyDateTime = DATEADD(s,5,GETDATE())

PRINT(CONVERT(varchar, @MyDateTime, 121))
DECLARE @MyDateTime2 DATETIME
SET @MyDateTime2 = DATEADD(ms,1,@MyDateTime)

PRINT(CONVERT(varchar, @MyDateTime2, 121))
BEGIN tran
PRINT(CONVERT(varchar, getdate(), 121))
    WAITFOR TIME @MyDateTime;
PRINT(CONVERT(varchar, getdate(), 121))
    DELETE FROM [Test2013] 
    WHERE [ID] = '82ecb924-d2f0-44ee-9a8e-5240d12de088';
PRINT(CONVERT(varchar, getdate(), 121))
Commit tran

BEGIN tran
PRINT(CONVERT(varchar, getdate(), 121))
    WAITFOR TIME @MyDateTime2;
PRINT(CONVERT(varchar, getdate(), 121))
    DELETE FROM [Test2013]
    WHERE [ID] = '82ecb924-d2f0-44ee-9a8e-5240d12de077';
PRINT(CONVERT(varchar, getdate(), 121))
Commit tran

取消查询时生成的消息:

(1 row(s) affected)

(1 row(s) affected)
2013-06-21 15:13:09.980
2013-06-21 15:13:09.980
2013-06-21 15:13:04.980
2013-06-21 15:13:09.997

(1 row(s) affected)
2013-06-21 15:13:09.997
2013-06-21 15:13:10.017
Query was cancelled by user.

使用 WAITFOR DELAY 代替:INSERT INTO [Test2013] ([客户端ID]、[ID]、[值]) 选择 CAST(-2147483648 AS INT) [客户端ID], '82ecb924-d2f0-44ee-9a8e-5240d12de088'[ID], CAST(1 AS INT) [值]

INSERT INTO [Test2013]
    ([ClientID],[ID],[Value])
    SELECT
        CAST(-2147483648 AS INT) [ClientID], 
        '82ecb924-d2f0-44ee-9a8e-5240d12de077' [ID], 
        CAST(2 AS INT) [Value]


BEGIN tran
PRINT(CONVERT(varchar, getdate(), 121))
    WAITFOR delay '00:00:05'
PRINT(CONVERT(varchar, getdate(), 121))
    DELETE FROM [Test2013] 
    WHERE [ID] = '82ecb924-d2f0-44ee-9a8e-5240d12de088';
PRINT(CONVERT(varchar, getdate(), 121))
Commit tran

BEGIN tran
PRINT(CONVERT(varchar, getdate(), 121))
    WAITFOR delay '00:00:00.001'
PRINT(CONVERT(varchar, getdate(), 121))
    DELETE FROM [Test2013]
    WHERE [ID] = '82ecb924-d2f0-44ee-9a8e-5240d12de077';
PRINT(CONVERT(varchar, getdate(), 121))
Commit tran