SQL Server – Why Doesn’t IMPLICIT_TRANSACTIONS Increment @@TRANCOUNT for My IF Statement?

sql servert-sqltransaction

I was playing around with SET IMPLICIT_TRANSACTIONS ON. I found a situation where I am convinced the database is automatically creating and committing a transaction without leaving it open. If I do IF EXISTS (SELECT * FROM dbo.t1) or any variation, I am certain that a transaction is happening in the background which I’d expect to stay open if I’m using implicit transactions.

However, after reading the docs, it says something like:

This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction.

and the list it shows does not include IF. However, then I’d expect all statements not found in the documentation’s list to not leave @@TRANCOUNT incremented. So, I looked for another statement not included in that list. I found DECLARE. However, I found that if I perform a subquery with DECLARE, I get the behavior I’d expect from SET IMPLICIT_TRANSATIONS ON. That is, if my DECLARE has a subquery, it increments @@TRANCOUNT if it is zero. So it appears that the documentation is inconsistent with itself

SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
BEGIN
    DROP TABLE dbo.t1
    COMMIT
END
GO
CREATE TABLE dbo.t1 (x INT);
COMMIT;
GO

IF CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END = 1 PRINT 'unreachable';
PRINT 'after1 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after1 0

DECLARE @x INT = 2;
PRINT 'after2 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after2 0

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;
PRINT 'after3 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after3 1
GO

WHILE @@TRANCOUNT > 0 ROLLBACK
GO

Why does DECLARE increment @@TRANCOUNT for an embedded SELECT but not IF? Is the SELECT embedded in the IF somehow not actually using a transaction?

I’m using SQL Server 13.0.4411.

Best Answer

I suspect that what you're seeing is because some statements get transformed to SELECT statements behind the scenes and others do not. Consider your final DECLARE statement:

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

That is just shorthand for this:

DECLARE @y INT;
SET @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

Which can also be written like this:

DECLARE @y INT;
SELECT @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

In which case you'd expect to see @@TRANCOUNT incremented. However, can you make the same statement about the IF statement? If I run your original code with actual execution plans turned on I see the following:

I don't see a SELECT in the plan for the first query. I only see COND WITH QUERY. So it seems consistent with the documentation.

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Sql-server – Oracle GoldenGate add trandata errors

Related Question