t-SQL复合语句导致死锁，为什么？ [英] t-SQL compound statement causes the deadlock, any idea why?
问题描述
我正在学习一种更复杂的SQL Server 2008技术，因此如果我问一个太明显的问题，我会提前道歉。
I'm in the process of learning a more complex SQL Server 2008 techniques so I apologize in advance if I ask a too obvious question.
我有以下内容这样创建的表：
I have the following table created as such:
CREATE TABLE [dbo].[t_Log_2]
(
[id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[oid] INT,
[idtm] DATETIME2,
[odtm] DATETIME2,
[type] TINYINT,
[state] TINYINT,
[huid] UNIQUEIDENTIFIER,
[cnm] NVARCHAR(256),
[cmdl] NVARCHAR(256),
[batt] TINYINT,
[dvtp0] SMALLINT,
[dvtp1] SMALLINT
);
CREATE INDEX idx_idt
ON [dbo].[t_Log_2]([idtm]);
CREATE INDEX idx_odt
ON [dbo].[t_Log_2]([odtm]);
CREATE INDEX idx_huid
ON [dbo].[t_Log_2]([huid]);
CREATE INDEX idx_cnm
ON [dbo].[t_Log_2]([cnm]);
然后可以从多个同时线程中运行以下查询ASP.NET Web应用程序。请注意，整个查询需要以原子方式运行：
And then the following query can be run from several simultaneous threads from an ASP.NET web application. Note that this whole query needs to run atomically:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DELETE FROM [dbo].[t_Log_2]
WHERE [idtm]<'2011-03-12 08:41:57';
WITH ctx AS(
SELECT MIN([idtm]) AS mdIn,
MAX([odtm]) AS mdOut
FROM [dbo].[t_Log_2]
WHERE [type] = 0
AND [state] = 0
AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4'
AND [odtm] >= '2013-03-11 06:33:32'
AND [idtm] <= '2013-03-11 06:43:12'
)
INSERT INTO [dbo].[t_Log_2]
([oid],[idtm],[odtm],[type],[state],[huid],
[cnm],[cmdl],[batt],[dvtp0],[dvtp1])
SELECT
2,
CASE WHEN mdIn IS NOT NULL
AND mdIn < '2013-03-11 06:33:32'
THEN mdIn
ELSE '2013-03-11 06:33:32'
END,
CASE WHEN mdOut IS NOT NULL
AND mdOut > '2013-03-11 06:43:12'
THEN mdOut
ELSE '2013-03-11 06:43:12'
END,
0,
0,
N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4',
null,
null,
0,
1,
null
FROM ctx
SELECT ROWCOUNT_BIG()
DELETE FROM [dbo].[t_Log_2]
WHERE [type] = 0
AND [state] = 0
AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4'
AND [odtm] >= '2013-03-11 06:33:32'
AND [idtm] <= '2013-03-11 06:43:12'
AND [id] <> SCOPE_IDENTITY()
DELETE FROM [dbo].[t_Log_2]
WHERE [type] = 0
AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4'
AND [idtm] >= (SELECT [idtm] FROM [dbo].[t_Log_2]
WHERE [id] = SCOPE_IDENTITY())
AND [odtm] <= (SELECT [odtm] FROM [dbo].[t_Log_2]
WHERE [id] = SCOPE_IDENTITY())
AND [id] <> SCOPE_IDENTITY()
;WITH ctx1 AS(
SELECT [idtm] AS dI
FROM [dbo].[t_Log_2]
WHERE [id] = SCOPE_IDENTITY()
)
UPDATE [dbo].[t_Log_2]
SET [odtm] = ctx1.dI
FROM ctx1
WHERE [id] <> SCOPE_IDENTITY()
AND [type] = 0
AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4'
AND [idtm] < ctx1.dI
AND [odtm] > ctx1.dI
;WITH ctx2 AS(
SELECT [odtm] AS dO
FROM [dbo].[t_Log_2]
WHERE [id] = SCOPE_IDENTITY()
)
UPDATE [dbo].[t_Log_2]
SET [idtm] = ctx2.dO
FROM ctx2
WHERE [id] <> SCOPE_IDENTITY()
AND [type] = 0
AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4'
AND [idtm] < ctx2.dO
AND [odtm] > ctx2.dO
COMMIT TRANSACTION;
SET XACT_ABORT OFF
请注意，上面的查询是从动态组成它的C＃代码。实际上，它的参数不是上面显示的那样硬编码。
Note that the query above was copied 1-to-1 from the C# code that dynamically composes it. In reality its parameters are not hard-coded as is shown above.
此查询在大多数情况下都有效，但是偶尔我会在日志中收到以下错误：
This query works in most times but once in a while I get the following error in the log:
事务（进程ID 80）在锁定资源上处于死锁状态，而另一个进程
被选为死锁受害者。重新运行
交易。
Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
任何人知道我该怎么做才能防止这种僵局？
Any idea what shall I do to prevent this deadlock?
推荐答案
您要么需要持有更多的锁，要么要持有更少的锁。
You either need to hold more locks or fewer.
最简单的答案是选择 NOLOCK （最佳性能）或 TABLOCKX （一致性而不必考虑）。
The easiest answer is to go either NOLOCK (best performance) or TABLOCKX (consistency without having to think).
如果由于一致性要求而不能将与（nolock）一起使用，则可以添加 （tablockx）。
这将实际上意味着一次只能有一个线程可以像语句一样执行-不会并发。
If you cannot use with (nolock) because of consistency requirements, you can add with (tablockx).
This will effectively mean that only one thread can execute like statements at a time - there will be no concurrency.
另一种方法是在一个还有更多细节，如果不了解为什么要更新表，数据用于什么等，这是无法完成的。
The alternative is to analyse your requirements in a lot more detail, which cannot be done without an understanding why you are updating the table, what the data is for etc.
例如，此语句是否确实需要在交易中？闻起来像是客房整理：
For example, does this statement really need to be in the transaction? It smells like housekeeping:
DELETE FROM [dbo].[t_Log_2]
WHERE [idtm]<'2011-03-12 08:41:57';
如果您将其从交易中取出，并放在单独的批次中，则可能会发现问题消失了。
If you take that out of the transaction, and put it in a separate batch, you may find the problems go away.
这篇关于t-SQL复合语句导致死锁，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
