Sql-server – connection pooling, transactions, nested transaction and rollback

connection-poolingrollbacksql servertransaction

I've got strange behavior on production.

First user request thru application failed, I've got SQLServerException with "Lock request time out period exceeded." After that following requests are successfully executed. After some time I've closed client application and all requests disappeared. I know that requests were successfully executed because I have JDBC logging on my client application.

Is there any other possible scenario other than nested transactions and rollback?

Connection pooling uses same spid (session) when it's possible so that must have been the case here.

Every request is just one stored procedure which has explicit transaction defined. I'm not sure how this could happened because transaction would either be committed or rollback because of this block.

begin try

  begin tran

  set lock_timeout x

  ...

  commit tran

end try

begin catch

  if @@trancount > 0

    rollback tran

  ...

end catch

Also, would it be wise to add on start just after "begin try" rollback tran if @@trancount > 0? That way I would prevent future issues like this.

Note, yes, I know that SQL Server doesn't have nested transactions and I don't use them. I'm mentioning them because I don't see other way that this could happened. Thanks!

EDIT:

I believe I have more conclusions about this.

BEGIN TRY
  BEGIN TRAN
      SET LOCK_TIMEOUT 10000
      UPDATE tbl ...
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
  INSERT INTO dbo.ErrorLog ...
END CATCH

I think that my update tbl went into lock timeout. After that it went to catch block and I believe that rollback tran went into timeout also. Is this possible? Imagine database maintenance job (index rebuilds) and synchronous mirroring running in parallel with this query.

That would match my application logs because I see 20 seconds (10 on update and 10 on rollback) difference between call to stored procedure and sql exception timeout.

I also don't have record in dbo.ErrorLog for that entry.

It must have been lock timeout on rollback because I wouldn't get such exception in application logs if it happened in try block because it's contained by catch so it must have been raised in catch.

Best Answer

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Related Question