How to force a deadlock to test a stored procedure's try/catch?

Viewed 388

My procedure spUmowyXMLintoLOG looks like :

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

As you can see this procedure get insert into :

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

Now i try to get deadlock while execute this procedure to test .

I open 2 query windows and in first one i try to lock table like this :

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

The second window is just execute this procedure - to get deadlock while inserting data

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

After when i lock table - the procedure just executing 30 sec then output comes out without problem.

When i change delay time to 10 min it just execute 10 min... How can i get deadlock here? - it should appears because table is locked by another transaction.

1 Answers

Deadlocks aren't locks. They are conflicting locks. For example:

  • sp1 locks table a, then table b.
  • sp2 locks table b, then table a.

If you run the two sps concurrently, sp1 locks table a and then tries to lock table b. But sp2 has already locked b so sp1 waits for it to be unlocked. At the same time sp2 has locked table b so sp1 waits. They're both waiting, until SQL Server detects the situation and terminates one of the operations to break the deadlock.

Your database is functioning as designed, by delaying execution of one sp until another sp releases the locks it has.

To force a deadlock, you'll need two resources (tables) to lock, and two SQL sequences from separate database connections to lock them in the opposite order. You could do this with one stored procedure and a SSMS session. Start the SSMS session with SET DEADLOCK_PRIORITY HIGH; so SQL Server kills your SP rather than randomly picking your SSMS session to kill.

Consider Monty Python's Australian Philosophers sitting around a table with a big plate of spaghetti, and just one fork and one spoon they must share. To eat, each philosopher needs to use both the spoon and the fork and then put them down. If one grabs the fork first and another grabs the spoon first, they go hungry. But if all philosophers grab the fork first, then the spoon, they can eat one at a time.

Related