如何强制死锁来测试存储过程的 try/catch？

Question

我的程序spUmowyXMLintoLOG看起来像:

CREATE PROCEDURE dbo.spUmowyXMLintoLOG
(
@inXML XML,
@PROCID INT,
@idRekorduZrodlowego INT,
@idTabeliZrodlowej INT,
@TrybWywolania INT, /*  0 pierwszy wpis z inXML, 
1 drugi wpis z outXML, 
-1 czytanie z logu po nazwie obiektu idRekorduZrodloweho i idTabeliZrodlowej */
@outIdWpisuDoLogu INT OUTPUT,
@dataOd DATETIME,
@dataDo DATETIME,
@CzyBlad BIT,
@ErrorMessage VARCHAR(4000),
@uidOperacji VARCHAR(255) = NULL /* UM2-4818 */
)
AS
BEGIN
DECLARE @komunikatPrint VARCHAR(1000)
DECLARE @NazwaObiektu VARCHAR(255)

SELECT @NazwaObiektu = name
FROM dbo.sysobjects WITH(NOLOCK)
WHERE id = @PROCID

BEGIN TRY 

IF @TrybWywolania = 0
BEGIN

INSERT INTO dbo.UmowyXMLzInterfejsow_log2(ProcID,NazwaObiektu,inXml,CzyBlad,UIDOperacji)
SELECT @PROCID,@NazwaObiektu,@inXML,@CzyBlad,@uidOperacji
SELECT @outIdWpisuDoLogu = SCOPE_IDENTITY()

/* zamapowanie szczegółów */
INSERT INTO UmowyXMLzInterfejsow_log2_szczegoly WITH(XLOCK,ROWLOCK)
SELECT @outIdWpisuDoLogu,idRekorduZrodlowego,idTabeliZrodlowej
FROM #UmowyXMLzInterfejsow_log_szczegoly WITH(NOLOCK)

/* UM2-4842 BEGIN zapis uida biznesowego */
IF OBJECT_ID('tempdb..#umowyXMLzInterfejsow_log_UIDbiznesowy') IS NOT NULL
BEGIN
INSERT INTO dbo.UmowyXMLzInterfejsow_log2_UIDbiznesowy (idWpisuDoLogu,UIDBiznesowy)
SELECT @outIdWpisuDoLogu,UIDBiznesowy
FROM #umowyXMLzInterfejsow_log_UIDbiznesowy
END
/* UM2-4842 END zapis uida biznesowego */
END
ELSE
IF @TrybWywolania = 1
BEGIN

UPDATE dbo.UmowyXMLzInterfejsow_log2 WITH(XLOCK,ROWLOCK)
SET outXml = @inXML,
DataAktualizacji = GETDATE(),
CzyBlad = @CzyBlad,
ErrorMessage = @ErrorMessage
WHERE idWpisuDoLogu = @outIdWpisuDoLogu
END
ELSE
IF @TrybWywolania = -1
BEGIN

IF ISNULL(@PROCID,0) <> 0
AND ISNULL(@idRekorduZrodlowego,0) <> 0
AND ISNULL(@idTabeliZrodlowej,0) <> 0
BEGIN
SELECT 'Wyszukianie po idRekorduZrodlowego,idTabeliZrodlowej -> dbo.UmowyXMLzInterfejsow_log',l.*,'_szczegoly',ls.*,'SWUIM_SystemyTabeleZrodlowe',stz.*
FROM dbo.UmowyXMLzInterfejsow_log2 l WITH(NOLOCK)
JOIN UmowyXMLzInterfejsow_log2_szczegoly ls WITH(NOLOCK)
ON l.idWpisuDoLogu = ls.idWpisuDoLogu
LEFT JOIN SWUIM_SystemyTabeleZrodlowe stz WITH(NOLOCK)
ON ls.idTabeliZrodlowej = stz.id
WHERE ls.idRekorduZrodlowego = @idRekorduZrodlowego
AND ls.idTabeliZrodlowej = @idTabeliZrodlowej
END
END
IF @TrybWywolania = -2
BEGIN

IF ISNULL(@PROCID,0) <> 0
AND ISNULL(@dataOd,'9999-12-31') <= CONVERT(VARCHAR(10),GETDATE(),120)
AND ISNULL(@dataDo,'9999-12-31') >= CONVERT(VARCHAR(10),GETDATE(),120)
BEGIN
SELECT 'Wyszukianie po dacieWpisu -> dbo.UmowyXMLzInterfejsow_log',l.*,'_szczegoly',ls.*,'SWUIM_SystemyTabeleZrodlowe',stz.*
FROM dbo.UmowyXMLzInterfejsow_log2 l WITH(NOLOCK)
JOIN UmowyXMLzInterfejsow_log2_szczegoly ls WITH(NOLOCK)
ON l.idWpisuDoLogu = ls.idWpisuDoLogu
LEFT JOIN SWUIM_SystemyTabeleZrodlowe stz WITH(NOLOCK)
ON ls.idTabeliZrodlowej = stz.id
WHERE l.NazwaObiektu = @NazwaObiektu
AND l.DataWpisu >= @dataOd 
AND l.DataWpisu <= @dataDo
END             
END
END TRY
BEGIN CATCH

SELECT @komunikatPrint = 'Wystapił problem z zapisem do XML do logu: ' + ERROR_MESSAGE()

END CATCH

END

如你所见，这个过程插入到:

INSERT INTO dbo.UmowyXMLzInterfejsow_log2(ProcID,NazwaObiektu,inXml,CzyBlad,UIDOperacji)
SELECT @PROCID,@NazwaObiektu,@inXML,@CzyBlad,@uidOperacji

现在我尝试在执行这个过程来测试时得到死锁。

我打开2个查询窗口，在第一个我试图锁表这样:

BEGIN TRY
begin tran az

select top 10 * from UmowyXMLzInterfejsow_log2 with(tablockx)

WAITFOR DELAY '00:0:30'

commit tran az
END TRY
BEGIN CATCH
rollback tran az
END CATCH

第二个窗口只是执行这个过程-在插入数据时获得死锁

begin tran az
DECLARE @outIdWpisuDoLogu INT 
IF OBJECT_ID('tempdb..#umowyXMLzInterfejsow_log_szczegoly') IS NOT NULL
DROP TABLE #umowyXMLzInterfejsow_log_szczegoly
CREATE TABLE #umowyXMLzInterfejsow_log_szczegoly
(
idWpisuDoLogu [int] NULL,
idRekorduZrodlowego [int] NOT NULL,
idTabeliZrodlowej [int] NOT NULL
)

EXEC dbo.spUmowyXMLintoLOG 'xx',@@procid,null,null,0,@outIdWpisuDoLogu OUTPUT,NULL,NULL,0,NULL

select @outIdWpisuDoLogu
rollback tran az

当我锁表后-过程只执行30秒，然后输出没有问题。

当我将延迟时间更改为10分钟时，它只执行10分钟…我怎么能在这里得到死锁?-它应该出现，因为表被另一个事务锁定。

Answer 1

死锁不是锁。它们是冲突的锁。例如:

• sp1锁表a，然后锁表b。
sp2先锁表b，然后锁表a。

如果你同时运行两个sps, sp1锁了表a，然后试图锁表b，但是sp2已经锁了表b，所以sp1等待它被解锁。同时，sp2锁住了表b，所以sp1等待。它们都在等待，直到SQL Server检测到这种情况并终止其中一个操作来打破死锁。

数据库按照设计运行，延迟一个sp的执行，直到另一个sp释放它拥有的锁。

要强制死锁，您需要锁定两个资源(表)，并且需要两个来自不同数据库连接的SQL序列以相反的顺序锁定它们。您可以使用一个存储过程和一个SSMS会话来实现这一点。启动SSMS会话与SET DEADLOCK_PRIORITY HIGH;，这样SQL Server杀死你的SP，而不是随机选择你的SSMS会话杀死。

想象一下巨蟒剧团(Monty Python)的澳大利亚哲学家们围坐在一张桌子旁，面前摆着一大盘意大利面，他们必须分享一把叉子和一把勺子。吃东西时，每个哲学家都需要同时使用勺子和叉子，然后把它们放下。如果一个人先拿叉子，另一个人先拿勺子，他们就会挨饿。但是，如果所有的哲学家都先拿叉子，然后拿勺子，他们就可以一次吃一个。