Várias instruções UPDATE e INSERT usando BEGIN TRAN com COMMIT TRAN e ROLLBACK TRAN personalizados

Em primeiro lugar, como estou usando SET XACT_ABORT ON. Se ocorrer uma exceção em qualquer uma das instruções " INSERTou UPDATE" dentro do bloco de transação, ele deve encerrar e reverter toda a transação . Ou seja, se houver um erro em qualquer uma das instruções " INSERTe UPDATE" dentro do BEGIN TRAN. Então ele deve fazer o ROLLBACK da transação automaticamente .

Além disso, estou usando o SQL Server 2017. No meu script, inicializei uma variável de tabela DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50));e ela está temporariamente implementada no meu script para depurar meu procedimento armazenado devido à falta de recursos de depuração integrados no meu IDE do SQL Server Management Studio.

Aqui está meu script T-SQL completo:

SET XACT_ABORT ON; -- To automatically rollback the entire transaction if an exception is encountered
                    
BEGIN TRAN;

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

SELECT TOP 1
    @d_pay_method_id = pay_method_id
FROM
    [testDB].[dbo].[Payment]
WHERE
    method_name LIKE '%Bank Transfer%';
            
SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'PENDING';

SET @d_batch_code = 'BGUID' + '-' + CONVERT(VARCHAR,GETDATE(),23)
    + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13);--DATETIME2(4)-&-12-RANDOM-CODE

INSERT INTO -- first batch
    [testDB].[dbo].[Orders]
(
    order_id,
    batch_code,
    quantity,
    cart_id,
    user_id,
    pay_method_id,
    stat_ref_id
)
OUTPUT
    INSERTED.order_id,
    INSERTED.cart_id
INTO
    @order_id_tbl
SELECT
    'ORDER' + '-' + CONVERT(VARCHAR,GETDATE(),23)
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')), 1, 13) AS order_id,--DATE-&-12-RANDOM-CODE
    @d_batch_code AS batch_code,
    cart.quantity,
    cart.cart_id,
    @user_id AS user_id,
    @d_pay_method_id AS pay_method_id,
    @d_stat_ref_id AS stat_ref_id
FROM
    [testDB].[dbo].[PinkBasket] AS cart
LEFT OUTER JOIN
    [testDB].[dbo].[StatusRef] AS stat_ref
ON
    cart.stat_ref_id = stat_ref.stat_ref_id
WHERE
    cart.user_id = @user_id
    AND cart.disable = 0
    AND cart.is_selected = 1
    AND cart.is_purchased = 0
    AND cart.is_wishlisted = 0
    AND stat_ref.com_usd_wrds = 'PENDING';

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('first set');

END

INSERT INTO -- first batch
    [testDB].[dbo].[Payment]
(
    cnb_pay_id,
    pay_ref_no,
    amount_to_pay,
    order_id
)
SELECT
    'PGUID' + '-' + REPLACE(REPLACE(REPLACE(CAST(sysdatetime() AS DATETIME2(4)), ' ', '-'), ':', '-'), '.', '-')
        + '-' + SUBSTRING(CONVERT(VARCHAR(12),REPLACE(CONVERT(VARCHAR(64), NEWID()), '-', '')),1,13), --DATETIME2(4)-&-12-RANDOM-CODE
    @pay_ref_no,
    @amount_to_pay,
    tempData.order_id
FROM
    @order_id_tbl AS tempData;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('second set');

END

SELECT TOP 1
    @d_stat_ref_id = stat_ref_id
FROM
    [testDB].[dbo].[StatusRef]
WHERE
    com_usd_wrds = 'ORDERED';

UPDATE -- first batch
    [testDB].[dbo].[PinkBasket]
SET
    stat_ref_id = @d_stat_ref_id,
    is_purchased = 1
WHERE
    cart_id IN
        (
            SELECT
                tempData.cart_id
            FROM
                @order_id_tbl AS tempData
        );

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_fbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('third set');

END

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    SET @place_order_sbatch_cntr += 1;

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

IF (@place_order_fbatch_cntr + @place_order_sbatch_cntr) >= 4 --@place_order_fbatch_cntr should be 3, and @place_order_sbatch_cntr should be greater than or equal to 1

BEGIN

    COMMIT TRAN;

    SELECT @successful AS json_data;

END

ELSE

BEGIN

    ROLLBACK TRAN;

    SELECT(SELECT exec_checker FROM @cust_debugger FOR JSON PATH, INCLUDE_NULL_VALUES) AS json_data; -- debugging purposes only

END

Cada vez que executei meu procedimento armazenado, recebi esta saída (no meu serviço de API Express.js):

spOutput = "[{\"exec_checker\":\"first set\"},{\"exec_checker\":\"second set\"},{\"exec_checker\":\"third set\"}]"

Você notará que a variável de tabela @cust_debugger obteve com sucesso as três primeiras seções de IF @@ROWCOUNT > 0validação (ou seja, com uma mistura de duas INSERTe uma UPDATEinstrução).

O problema é: por que as duas últimas seções não são executadas corretamente? Mas, como declarei SET XACT_ABORT ONantes BEGIN TRAN, não há exceção durante o processo de transação.

Entretanto, se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON(para fins de teste e investigação), aqui está o seguinte script:

DECLARE @cust_debugger TABLE (exec_checker VARCHAR(50)); -- custom debugger

UPDATE
    prod
SET
    prod.stock = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN prod.stock ELSE prod.stock - nested_ref.quantity END,
    prod.availability = CASE WHEN (prod.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[Product] AS prod
INNER JOIN
(
    SELECT
        cart.prod_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod.prod_id = nested_ref.prod_id
WHERE
    prod.disable = 0
    AND prod.availability = 1
    AND prod.is_draft = 0;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fourth set');

END

UPDATE
    prod_var
SET
    prod_var.stock = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN prod_var.stock ELSE prod_var.stock - nested_ref.quantity END,
    prod_var.availability = CASE WHEN (prod_var.stock - nested_ref.quantity) <= 0 THEN 0 ELSE 1 END
FROM
    [testDB].[dbo].[SubProduct] AS prod_var
INNER JOIN
(
    SELECT
        cart.prod_var_id,
        cart.quantity
    FROM
        [testDB].[dbo].[PinkBasket] AS cart
    LEFT OUTER JOIN
        [testDB].[dbo].[StatusRef] AS stat_ref
    ON
        cart.stat_ref_id = stat_ref.stat_ref_id                     
    WHERE
        cart.user_id = @user_id
        AND cart.prod_var_id IS NOT NULL
        AND cart.disable = 0
        AND cart.is_selected = 1
        AND cart.is_purchased = 0
        AND cart.is_wishlisted = 0
        AND stat_ref.com_usd_wrds = 'PENDING'
) AS nested_ref
ON
    prod_var.prod_var_id = nested_ref.prod_var_id
WHERE
    prod_var.disable = 0
    AND prod_var.availability = 1
    AND prod_var.is_reserved = 1;

IF @@ROWCOUNT > 0
BEGIN

    INSERT INTO @cust_debugger VALUES ('fifth set');

END

SELECT * FROM @cust_debugger

A seguinte saída está sendo gerada:

Minha pergunta é: por que o quarto e o quinto conjuntos funcionam corretamente somente quando executados sem BEGIN TRAN, COMMIT TRAN, e ROLLBACK TRAN?

Se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRANe SET XACT_ABORT ON), então ele deverá executar a IF @@ROWCOUNT > 0instrução. (ou seja, a variável também deverá ser incrementada: SET @place_order_sbatch_cntr += 1;).

Por outro lado, descobri que as tabelas Producte SubProducttêm um TRIGGER definido, aqui estão as seguintes funções TRIGGER:

GATILHO do produto:

ALTER TRIGGER [dbo].[ProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[Product]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.ProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_id = d.prod_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.ProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.Product
        WHERE
            prod_id IN (SELECT prod_id FROM deleted);
    END
END

GATILHO do subproduto:

ALTER TRIGGER [dbo].[SubProductReferentialIntegrityInsteadOfDelete]
   ON  [dbo].[SubProduct]
   INSTEAD OF DELETE
AS 
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF EXISTS (
        SELECT
            1
        FROM
            testImgDB.dbo.SubProductImg prodImg
        JOIN
            deleted d
        ON
            prodImg.prod_var_id = d.prod_var_id
    )
    BEGIN
        RAISERROR ('Cannot delete, as related records exist in testImgDB.dbo.SubProductImg.', 16, 1);
        ROLLBACK;
    END
    ELSE
    BEGIN
        DELETE FROM
            testDB.dbo.SubProduct
        WHERE
            prod_var_id IN (SELECT prod_var_id FROM deleted);
    END
END

Ainda assim, é questionável por que,

se esses dois últimos conjuntos estiverem funcionando corretamente (se eu extrair a parte do quarto e quinto conjuntos e excluir as instâncias de BEGIN TRAN e SET XACT_ABORT ON)

Apesar de desabilitar os dois TRIGGERs.

e após novos testes, o problema permanece.

Aqui está o que tenho pensado para resolver meu caso:

• É recomendável separar o processo de leitura complexa (SELECT) para minhas duas últimas seções? Ou seja, preciso passar os dados do SELECT para uma tabela temporária ou variável de tabela. Então, esses dados armazenados serão eventualmente processados ​​com o UPDATE com menor complexidade devido ao conceito de separação de responsabilidades.

• I want to know how to review the processing of my script where the issue arises. Such as if I could review why the UPDATE process doesn't update, then I could also decide what measures of response I needed to take.

• How to check for Locks / Deadlocks / Blocking, hoping that it might cause the unexpected outcomes.

Here are my assumptions that might be causing the conflicts:

• For the Product and SubProduct tables, there are triggers in each of the two tables. But it is defined with INSTEAD OF DELETE. Could it affect the process? Ahhh, because UPDATE statements are also deleting a record, right? The first thing it does is DELETE, and the second is INSERT. Because that's how UPDATE works under the hood of SQL Server, right? I forgot to look at it; it seems it was because of the TRIGGER.

• According to what I've observed. When you create a TRIGGER. You can use the INSERTED table and the DELETED table (if it's handling an UPDATE statement). When you UPDATE a table, it will delete the record first, then it will be stored in the DELETED table, then the DELETED table will pass the data into the INSERTED table and use the argument that is passed to the UPDATE statement instead. Take note, I'm not sure how SQL Server processes the DELETED table and INSERTED table when the UPDATE triggers.

• Since I am using a test server. Here's the screenshot of the test server specifications:

• Is 8GB RAM and 178 GB of storage insufficient for testing purposes?

Does anyone know why this is happening?