Início / dba / Perguntas / 128535
Accepted
Vladimir Baranov
Asked: 2016-02-08 16:51:31 +0800 CST

Em que casos uma transação pode ser confirmada de dentro do bloco CATCH quando XACT_ABORT é definido como ON?

  • 772

Eu tenho lido MSDN sobre TRY...CATCHe XACT_STATE.

Tem o seguinte exemplo que usa XACT_STATEno CATCHbloco de uma TRY…CATCHconstrução para determinar se deve confirmar ou reverter uma transação:

USE AdventureWorks2012;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Test XACT_STATE for 0, 1, or -1.
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should 
    --     be rolled back.
    -- XACT_STATE = 0 means there is no transaction and
    --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' + 
              ' Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO

O que não entendo é: por que devo me importar e verificar o que XACT_STATEretorna?

Observe que o sinalizador XACT_ABORTestá definido como ONno exemplo.

Se houver um erro grave o suficiente dentro do TRYbloco, o controle passará para CATCH. Então, se estou dentro do CATCH, sei que aquela transação teve um problema e realmente a única coisa sensata a fazer nesse caso é revertê-la, não é?

Mas, este exemplo do MSDN implica que pode haver casos em que o controle é passado CATCHe ainda faz sentido confirmar a transação. Alguém poderia dar algum exemplo prático de quando isso pode acontecer, quando faz sentido?

Não vejo em que casos o controle pode ser passado para dentro CATCHcom uma transação que pode ser confirmada quando XACT_ABORTestá definida comoON .

O artigo do MSDN sobre SET XACT_ABORTtem um exemplo quando algumas instruções dentro de uma transação são executadas com êxito e algumas falham quando XACT_ABORTé definido como OFF, eu entendo isso. Mas, SET XACT_ABORT ONcomo pode acontecer que XACT_STATE()retorne 1 dentro do CATCHbloco?

Inicialmente, eu teria escrito este código assim:

USE AdventureWorks2012;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Some severe problem with the transaction
    PRINT 'Rolling back transaction.';
    ROLLBACK TRANSACTION;
END CATCH;
GO

Levando em consideração uma resposta de Max Vernon, eu escreveria o código assim. Ele mostrou que faz sentido verificar se há uma transação ativa antes de tentar ROLLBACK. Ainda assim, com SET XACT_ABORT ONo CATCHbloco pode haver uma transação condenada ou nenhuma transação. Portanto, em qualquer caso, não há nada a fazer COMMIT. Estou errado?

USE AdventureWorks2012;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Some severe problem with the transaction
    IF (XACT_STATE()) <> 0
    BEGIN
        -- There is still an active transaction that should be rolled back
        PRINT 'Rolling back transaction.';
        ROLLBACK TRANSACTION;
    END;

END CATCH;
GO
sql-server sql-server-2008

4 respostas

  1. Best Answer

    It turns out that transaction can not be committed from inside the CATCH block if XACT_ABORT is set to ON.

    The example from MSDN is somewhat misleading, because the check implies that XACT_STATE can return 1 in some cases and it may be possible to COMMIT the transaction.

    IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;

    It is not true, XACT_STATE will never return 1 inside CATCH block if XACT_ABORT is set to ON.

    It seems that the MSDN sample code was meant to primarily illustrate the use of XACT_STATE() function regardless of the XACT_ABORT setting. The sample code looks generic enough to work with both XACT_ABORT set to ON and OFF. It is just that with XACT_ABORT = ON the check IF (XACT_STATE()) = 1 becomes unnecessary.

    There is a very good detailed set of articles about Error and Transaction Handling in SQL Server by Erland Sommarskog. In Part 2 - Classification of Errors he presents a comprehensive table that puts together all classes of errors and how they are handled by SQL Server and how TRY ... CATCH and XACT_ABORT changes the behaviour.

    +-----------------------------+---------------------------++------------------------------+
|                             |     Without TRY-CATCH     ||        With TRY-CATCH        |
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+
|              SET XACT_ABORT |  OFF  |  ON   | OFF | ON  ||    ON or OFF     | OFF | ON  |
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+
| Class Name                  |    Aborts     |   Rolls   ||    Catchable     |   Dooms   |
|                             |               |   Back    ||                  |transaction|
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+
| Fatal errors                |  Connection   |    Yes    ||       No         |    n/a    |
| Batch-aborting              |     Batch     |    Yes    ||       Yes        |    Yes    |
| Batch-only aborting         |     Batch     | No  | Yes ||       Yes        | No  | Yes |
| Statement-terminating       | Stmnt | Batch | No  | Yes ||       Yes        | No  | Yes |
| Terminates nothing at all   |    Nothing    |    No     ||       Yes        | No  | Yes |
| Compilation: syntax errors  |  (Statement)  |    No     ||       Yes        | No  | Yes |
| Compilation: binding errors | Scope | Batch | No  | Yes || Outer scope only | No  | Yes |
| Compilation: optimisation   |     Batch     |    Yes    || Outer scope only |    Yes    |
| Attention signal            |     Batch     | No  | Yes ||       No         |    n/a    |
| Informational/warning msgs  |    Nothing    |    No     ||       No         |    n/a    |
| Uncatchable errors          |    Varying    |  Varying  ||       No         |    n/a    |
+-----------------------------+-------+-------+-----+-----++------------------+-----+-----+

    The last column in the table answers the question. With TRY-CATCH and with XACT_ABORT ON the transaction is doomed in all possible cases.

    One note outside the scope of the question. As Erland says, this consistency is one of the reasons to set XACT_ABORT to ON:

    I have already given the recommendation that your stored procedures should include the command SET XACT_ABORT, NOCOUNT ON. If you look at the table above, you see that with XACT_ABORT in effect, there is some higher level of consistency. For instance, the transaction is always doomed. In the following, I will show many examples where I set XACT_ABORT to OFF, so that you can get an understanding of why you should avoid this default setting.

    • 11

  2. TL;DR / Resumo executivo: Com relação a esta parte da pergunta:

    Não vejo em que casos o controle pode ser passado para dentro CATCHcom uma transação que pode ser confirmada quando XACT_ABORTestá definida comoON .

    Eu fiz alguns testes sobre isso agora e não consigo encontrar nenhum caso em que XACT_STATE()retorna 1dentro de um CATCHbloco quando @@TRANCOUNT > 0 e a propriedade de sessão de XACT_ABORTé ON. E, de fato, de acordo com a página atual do MSDN para SET XACT_ABORT :

    Quando SET XACT_ABORT for ON, se uma instrução Transact-SQL gerar um erro de tempo de execução, toda a transação será encerrada e revertida.

    Essa afirmação parece estar de acordo com sua especulação e minhas descobertas.

    O artigo do MSDN SET XACT_ABORTtem um exemplo de quando algumas instruções dentro de uma transação são executadas com sucesso e algumas falham quando XACT_ABORTé definido comoOFF

    Verdadeiro, mas as instruções nesse exemplo não estão dentro de um TRYbloco. Essas mesmas instruções dentro de um TRYbloco ainda impediriam a execução de quaisquer instruções após aquela que causou o erro, mas supondo que XACT_ABORTseja OFF, quando o controle é passado para o CATCHbloco, a transação ainda é fisicamente válida, pois todas as alterações anteriores ocorreram sem erro e podem ser cometidos, se esse for o desejo, ou podem ser revertidos. Por outro lado, se XACT_ABORTfor ON, todas as alterações anteriores serão revertidas automaticamente e você terá a opção de: a) emitir umROLLBACKque é principalmente apenas uma aceitação da situação, pois a transação já foi revertida menos a redefinição @@TRANCOUNTpara 0, ou b) obter um erro. Não há muita escolha, não é?

    Um detalhe possivelmente importante para esse quebra-cabeça que não é aparente nessa documentação SET XACT_ABORTé que essa propriedade de sessão, e até mesmo esse código de exemplo, existe desde o SQL Server 2000 (a documentação é quase idêntica entre as versões), anterior à TRY...CATCHconstrução que foi introduzido no SQL Server 2005. Olhando para aquela documentação novamente e olhando para o exemplo ( sem o TRY...CATCH), using XACT_ABORT ONcausa uma reversão imediata da Transação: não há nenhum estado de Transação de "incommittável" (observe que não há nenhuma menção em todos de um estado de Transação "não compromissável" nessa SET XACT_ABORTdocumentação).

    Acho razoável concluir que:

    1. a introdução da TRY...CATCHconstrução no SQL Server 2005 criou a necessidade de um novo estado de Transação (ou seja, "incommittable") e a XACT_STATE()função para obter essas informações.
    2. verificar XACT_STATE()em um CATCHbloco realmente só faz sentido se ambos os itens a seguir forem verdadeiros:
    3. XACT_ABORTis OFF(else XACT_STATE()deve sempre retornar -1e @@TRANCOUNTseria tudo que você precisa)
    4. Você tem lógica no CATCHbloco, ou em algum lugar na cadeia se as chamadas estiverem aninhadas, que faz uma alteração (uma COMMITou mesmo qualquer instrução DML, DDL, etc) em vez de fazer um ROLLBACK. (este é um caso de uso muito atípico) ** consulte a nota na parte inferior, na seção UPDATE 3, sobre uma recomendação não oficial da Microsoft para sempre verificar XACT_STATE()em vez de @@TRANCOUNT, e por que o teste mostra que seu raciocínio não dá certo.
    5. a introdução da TRY...CATCHconstrução no SQL Server 2005 tornou, em grande parte, obsoleta a XACT_ABORT ONpropriedade da sessão, pois fornece um maior grau de controle sobre a transação (você tem pelo menos a opção de COMMIT, desde que XACT_STATE()não retorne -1).
      Outra maneira de ver isso é, antes do SQL Server 2005 , XACT_ABORT ONfornecer uma maneira fácil e confiável de interromper o processamento quando ocorre um erro, em comparação com a verificação @@ERRORapós cada instrução.
    6. O código de exemplo da documentação para XACT_STATE()é errôneo ou, na melhor das hipóteses, enganoso, pois mostra a verificação de XACT_STATE() = 1quando XACT_ABORTé ON.

    A parte longa ;-)

    Sim, esse código de exemplo no MSDN é um pouco confuso (veja também: @@TRANCOUNT (Rollback) vs. XACT_STATE ) ;-). E, eu acho que é enganoso porque mostra algo que não faz sentido (pelo motivo que você está perguntando: você pode ter uma transação "committable" no CATCHbloco quando XACT_ABORTis ON), ou mesmo se for possível, é ainda se concentra em uma possibilidade técnica que poucos irão querer ou precisar, e ignora a razão pela qual é mais provável que alguém precise dela.

    Se houver um erro grave o suficiente dentro do bloco TRY, o controle passará para CATCH. Então, se estou dentro do CATCH, sei que aquela transação deu problema e realmente a única coisa sensata a se fazer nesse caso é revertê-la, não é?

    Acho que ajudaria se tivéssemos certeza de que estamos na mesma página em relação ao significado de certas palavras e conceitos:

    • "erro grave o suficiente": Só para ficar claro, TRY...CATCH interceptará a maioria dos erros. A lista do que não será capturado está listada na página vinculada do MSDN, na seção "Erros não afetados por uma construção TRY…CATCH".

    • "se estou dentro do CATCH, sei que a transação teve um problema" ( ênfase adicionada): Se por "transação" você quer dizer a unidade lógica de trabalho conforme determinado por você agrupando instruções em uma transação explícita, então Muito provavelmente sim. Acho que a maioria de nós, pessoal do banco de dados, tenderia a concordar que a reversão é "a única coisa sensata a fazer", pois provavelmente temos uma visão semelhante de como e por que usamos transações explícitas e concebemos quais etapas devem constituir uma unidade atômica de trabalho.

      Mas, se você quer dizer as unidades reais de trabalho que estão sendo agrupadas na transação explícita, então não, você não sabe que a própria transação teve um problema. Você sabe apenas que uma instrução em execução dentro da transação definida explicitamente gerou um erro. Mas pode não ser uma instrução DML ou DDL. E mesmo que fosse uma instrução DML, a própria transação ainda poderia ser confirmada.

    Considerando os dois pontos mencionados acima, provavelmente deveríamos fazer uma distinção entre transações que você "não pode" confirmar e aquelas que você "não deseja" confirmar.

    Quando XACT_STATE()retorna um 1, isso significa que a Transação é "confirmável", que você pode escolher entre COMMITou ROLLBACK. Você pode não querer confirmá-lo, mas se, por algum motivo difícil de encontrar-um-exemplo, você quisesse, pelo menos você poderia, porque algumas partes da transação foram concluídas com sucesso.

    Mas quando XACT_STATE()retorna um -1, você realmente precisa ROLLBACKporque alguma parte da transação entrou em um estado ruim. Agora, concordo que, se o controle foi passado para o bloco CATCH, faz sentido apenas verificar @@TRANCOUNT, porque mesmo que você pudesse confirmar a transação, por que faria isso?

    Mas se você observar no início do exemplo, a configuração XACT_ABORT ONmuda um pouco as coisas. Você pode ter um erro regular, depois de fazer BEGIN TRANisso passará o controle para o bloco CATCH quando XACT_ABORTfor OFFe XACT_STATE() retornará 1. MAS, se XACT_ABORT for ON, então a Transação é "abortada" (ou seja, invalidada) por qualquer erro 'ol e então XACT_STATE()retornará -1. Nesse caso, parece inútil verificar XACT_STATE()dentro do CATCHbloco, pois sempre parece retornar um -1when XACT_ABORTis ON.

    Então para que serve XACT_STATE()? Algumas pistas são:

    • A página do MSDN para TRY...CATCH, na seção "Uncommittable Transactions and XACT_STATE", diz:

      Um erro que normalmente encerra uma transação fora de um bloco TRY faz com que uma transação entre em um estado não confirmável quando o erro ocorre dentro de um bloco TRY.

    • A página do MSDN para SET XACT_ABORT , na seção "Comentários", diz:

      Quando SET XACT_ABORT é OFF, em alguns casos, apenas a instrução Transact-SQL que gerou o erro é revertida e a transação continua o processamento.

      e:

      XACT_ABORT deve ser definido como ON para instruções de modificação de dados em uma transação implícita ou explícita na maioria dos provedores OLE DB, incluindo SQL Server.

    • A página do MSDN para BEGIN TRANSACTION , na seção "Comentários", diz:

      A transação local iniciada pela instrução BEGIN TRANSACTION será escalada para uma transação distribuída se as seguintes ações forem executadas antes de a instrução ser confirmada ou revertida:

      • Uma instrução INSERT, DELETE ou UPDATE que faz referência a uma tabela remota em um servidor vinculado é executada. A instrução INSERT, UPDATE ou DELETE falhará se o provedor OLE DB usado para acessar o servidor vinculado não oferecer suporte à interface ITransactionJoin.

    O uso mais aplicável parece estar dentro do contexto das instruções DML do Linked Server. E acredito que me deparei com isso anos atrás. Não me lembro de todos os detalhes, mas tinha algo a ver com o servidor remoto não estar disponível e, por algum motivo, esse erro não foi detectado no bloco TRY e nunca foi enviado para o CATCH e assim foi um COMMIT quando não deveria. Claro, isso poderia ter sido um problema de não ter XACT_ABORTdefinido em ONvez de não ter verificado XACT_STATE(), ou possivelmente ambos. E eu me lembro de ter lido algo que dizia que se você usa Servidores Vinculados e/ou Transações Distribuídas, então você precisa usar XACT_ABORT ONe/ou XACT_STATE(), mas não consigo encontrar esse documento agora. Se eu encontrar, atualizarei com o link.

    Mesmo assim, já tentei várias coisas e não consigo encontrar um cenário que tenha XACT_ABORT ONe passe o controle para o CATCHbloco com XACT_STATE()reporting 1.

    Experimente estes exemplos para ver o efeito de XACT_ABORTno valor de XACT_STATE():

    SET XACT_ABORT OFF;

BEGIN TRY
    BEGIN TRAN;

    SELECT 1/0 AS [DivideByZero]; -- error, yo!

    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            XACT_STATE() AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage]

    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK;
    END;
END CATCH;

GO ------------------------------------------------

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;

    SELECT 1/0 AS [DivideByZero]; -- error, yo!

    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            XACT_STATE() AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage]

    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK;
    END;
END CATCH;

GO ------------------------------------------------

SET XACT_ABORT ON;

BEGIN TRY
    SELECT 1/0 AS [DivideByZero]; -- error, yo!
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            XACT_STATE() AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage]
END CATCH;

    ATUALIZAR

    Embora não faça parte da pergunta original, com base nestes comentários sobre esta resposta:

    Eu tenho lido os artigos de Erland sobre Error and Transaction Handling , onde ele diz que XACT_ABORTé OFFo padrão por motivos herdados e normalmente devemos defini-lo como ON.
    ...
    "... se você seguir a recomendação e executar com SET XACT_ABORT ON, a transação sempre estará condenada."

    Prior to using XACT_ABORT ON everywhere, I would question: what exactly is being gained here? I have not found it necessary to do and generally advocate that you should use it only when necessary. Whether or not you want to ROLLBACK can be handle easily enough by using the template shown in @Remus's answer, or the one that I have been using for years that is essentially the same thing but without the Save Point, as shown in this answer (which handles nested calls):

    Are we required to handle Transaction in C# Code as well as in stored procedure

    UPDATE 2

    I did a bit more testing, this time by creating a small .NET Console App, creating a Transaction in the app layer, prior to executing any SqlCommand objects (i.e. via using (SqlTransaction _Tran = _Connection.BeginTransaction()) { ... ), as well as using a batch-aborting error instead of just a statement-aborting error, and found that:

    1. An "uncommitable" Transaction is one that has been, for the most part, rolled back already (the changes have been undone), but @@TRANCOUNT is still > 0.
    2. When you have an "uncommitable" Transaction you cannot issue a COMMIT as that will generate and error saying that the Transaction is "uncommittable". You also cannot ignore it / do nothing as an error will be generated when the batch finishes stating that the batch completed with a lingering, uncommittable transaction and it will be rolled back (so, um, if it will auto-roll-back anyway, why bother throwing the error?). So you must issue an explicit ROLLBACK, maybe not in the immediate CATCH block, but before the batch ends.
    3. In a TRY...CATCH construct, when XACT_ABORT is OFF, errors that would terminate the Transaction automatically had they occurred outside of a TRY block, such as batch-aborting errors, will undo the work but not terminate the Tranasction, leaving it as "uncommitable". Issuing a ROLLBACK is more of a formality needed to close out the Transaction, but the work has already been rolled-back.
    4. When XACT_ABORT is ON, most errors act as batch-aborting, and hence behave as described in the bullet point directly above (#3).
    5. XACT_STATE(), at least in a CATCH block, will show a -1 for batch-aborting errors if there was an active Transaction at the time of the error.
    6. XACT_STATE() sometimes returns 1 even when there is no active Transaction. If @@SPID (among others) is in the SELECT list along with XACT_STATE(), then XACT_STATE() will return 1 when there is no active Transaction. This behavior started in SQL Server 2012, and exists on 2014, but I haven't tested on 2016.

    With the above points in mind:

    • Given points #4 and #5, since most (or all?) errors will render a Transaction "uncommitable", it seems entirely pointless to check XACT_STATE() in the CATCH block when XACT_ABORT is ON since the value returned will always be -1.
    • Checking XACT_STATE() in the CATCH block when XACT_ABORT is OFF makes more sense because the return value will at least have some variation since it will return 1 for statement-aborting errors. However, if you code like most of us, then this distinction is meaningless since you will be calling ROLLBACK anyway simply for the fact that an error occurred.
    • If you find a situation that does warrant issuing a COMMIT in the CATCH block, then check the value of XACT_STATE(), and be sure to SET XACT_ABORT OFF;.
    • XACT_ABORT ON seems to offer little to no benefit over the TRY...CATCH construct.
    • I can find no scenario where checking XACT_STATE() provides a meaningful benefit over simply checking @@TRANCOUNT.
    • I can also find no scenario where XACT_STATE() returns 1 in a CATCH block when XACT_ABORT is ON. I think it is a documentation error.
    • Yes, you can roll-back a Transaction that you did not explicitly begin. And in the context of using XACT_ABORT ON, it's a moot point since an error happening in a TRY block will automatically roll-back the changes.
    • The TRY...CATCH construct has the benefit over XACT_ABORT ON in not automatically cancelling the whole Transaction, and hence allowing the Transaction (as long as XACT_STATE() returns 1) to be committed (even if this is an edge-case).

    Example of XACT_STATE() returning -1 when XACT_ABORT is OFF:

    SET XACT_ABORT OFF;

BEGIN TRY
    BEGIN TRAN;

    SELECT CONVERT(INT, 'g') AS [ConversionError];

    COMMIT TRAN;
END TRY
BEGIN CATCH
    DECLARE @State INT;
    SET @State = XACT_STATE();
    SELECT @@TRANCOUNT AS [@@TRANCOUNT],
            @State AS [XactState],
            ERROR_MESSAGE() AS [ErrorMessage];

    IF (@@TRANCOUNT > 0)
    BEGIN
        SELECT 'Rollin back...' AS [Transaction];
        ROLLBACK;
    END;
END CATCH;

    UPDATE 3

    Related to item #6 in the UPDATE 2 section (i.e. possible incorrect value returned by XACT_STATE() when there is no active Transaction):

    • The odd / erroneous behavior started in SQL Server 2012 (so far tested against 2012 SP2 and 2014 SP1)

    • In SQL Server versions 2005, 2008, and 2008 R2, XACT_STATE() did not report expected values when used in Triggers or INSERT...EXEC scenarios: xact_state() cannot be used reliably to determine whether a transaction is doomed (archived page). However, in these 3 versions (I only tested on 2008 R2), XACT_STATE() does not incorrectly report 1 when used in a SELECT with @@SPID.

    • There is was a Connect bug filed against the behavior mentioned here but is closed as "By Design": XACT_STATE() can return an incorrect transaction state in SQL 2012 (link no longer valid due to incompetent and/or grossly negligent site migration ?). However, the test was done when selecting from a DMV and it was concluded that doing so would naturally have a system generated transaction, at least for some DMVs. It was also stated in the final response by MS that:

      Note that an IF statement, and also a SELECT without FROM, do not start a transaction.
      for example, running SELECT XACT_STATE() if you don't have a previously existing transaction will return 0.

      Those statements are incorrect given the following example:

        SELECT @@TRANCOUNT AS [TRANCOUNT], XACT_STATE() AS [XACT_STATE], @@SPID AS [SPID];
  GO
  DECLARE @SPID INT;
  SET @SPID = @@SPID;
  SELECT @@TRANCOUNT AS [TRANCOUNT], XACT_STATE() AS [XACT_STATE], @SPID AS [SPID];
  GO

    • Hence, I filed a new Feedback bug:
      XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause

    PLEASE NOTE that in the "XACT_STATE() can return an incorrect transaction state in SQL 2012" Connect item linked directly above, Microsoft (well, a representative of) states:

    @@trancount returns the number of BEGIN TRAN statements. It is thus not a reliable indicator of whether there is an active transaction. XACT_STATE() also returns 1 if there is an active autocommit transaction, and is thus a more reliable indicator of whether there is an active transaction.

    However, I can find no reason to not trust @@TRANCOUNT. The following test shows that @@TRANCOUNT does indeed return 1 in an auto-commit transaction:

    --- begin setup
GO
CREATE PROCEDURE #TransactionInfo AS
SET NOCOUNT ON;
SELECT @@TRANCOUNT AS [TranCount],
       XACT_STATE() AS [XactState];
GO
--- end setup

DECLARE @Test TABLE (TranCount INT, XactState INT);

SELECT * FROM @Test; -- no rows

EXEC #TransactionInfo; -- 0 for both fields

INSERT INTO @Test (TranCount, XactState)
    EXEC #TransactionInfo;

SELECT * FROM @Test; -- 1 row; 1 for both fields

    I also tested on a real table with a Trigger and @@TRANCOUNT within the Trigger did accurately report 1 even though no explicit Transaction had been started.

    • 8

  3. I would approach this differently. XACT_ABORT_ON is a sledge hammer, you can use a more refined approach, see Exception handling and nested transactions:

    create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

    This approach will rollback, when possible, only the work performed inside the TRY block, and restore state to the state before entering the TRY block. This way you can do complex processing, like iterating a cursor, w/o loosing all the work in case of an error. The only draw back is that, by using transaction savepoints, you are restricted from using anything that is incompatible with savepoints, like distributed transactions.

    • 7

  4. A programação defensiva exige que você escreva um código que lide com tantos estados conhecidos quanto possível, reduzindo assim a possibilidade de erros.

    Verificar XACT_STATE() para determinar se uma reversão pode ser executada é simplesmente uma boa prática. Tentar cegamente um rollback significa que você pode inadvertidamente causar um erro dentro do seu TRY...CATCH.

    Uma maneira de um rollback falhar dentro de um TRY...CATCH seria se você não iniciasse explicitamente uma transação. Copiar e colar blocos de código pode facilmente causar isso.

    • 5

relate perguntas