sql-server - 如何强制死锁来测试存储过程 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

Deadlocks不是锁。它们是冲突锁。例如:

sp1 锁定表 a，然后锁定表 b。

sp2 锁定表 b，然后锁定表 a。

如果同时运行两个 sps，sp1 会锁定表 a，然后尝试锁定表 b。但是 sp2 已经锁定了 b，所以 sp1 等待它被解锁。同时 sp2 已锁定表 b，因此 sp1 等待。它们都在等待，直到 SQL Server 检测到这种情况并终止其中一项操作以打破僵局。
您的数据库按设计运行，通过延迟一个 sp 的执行，直到另一个 sp 释放它拥有的锁。
要强制死锁，您需要锁定两个资源(表)，以及来自不同数据库连接的两个 SQL 序列以相反的顺序锁定它们。您可以使用一个存储过程和一个 SSMS session 来完成此操作。使用 SET DEADLOCK_PRIORITY HIGH; 启动 SSMS session 所以 SQL Server 会杀死您的 SP，而不是随机选择您的 SSMS session 来杀死。
考虑 Monty Python's Australian Philosophers围坐在 table 旁，摆着一大盘意大利面，他们必须共用一把 fork 和一把勺子。为了吃饭，每个哲学家都需要同时使用勺子和 fork ，然后将它们放下。如果一个人先拿 fork ，另一个人先拿勺子，他们就会饿。但是如果所有的哲学家先拿起 fork ，然后是勺子，他们就可以一次吃一个。