Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
How many rows does the update affect? If this is a common problem (and I assume you wouldn't be messing with the lock timeout if it wasn't), have you considered a more asynchronous approach to this - a queue table with a background job, service broker, etc. instead of having your Java application wait on the results and ultimately time out? –  Aaron Bertrand Sep 24 '13 at 15:24

1 Answer 1

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.

share|improve this answer
    
Aaron, thank you, but I'm familiar with that. I don't use nested transactions, but that's the only explanation that I can give for this case. –  newbie123_ Sep 23 '13 at 19:12
    
@newbie123_ well your question is kind of confusing. You say the transactions were successfully committed but then later disappeared? Can you add details to the question that demonstrate exactly how you came to that conclusion? –  Aaron Bertrand Sep 23 '13 at 19:14
    
I've got logs on my client application. That's how I see that 1st call failed and other passed successfully. They had to be in same spid (session) because of connection pooling library, I've confirmed this with sql profiler. –  newbie123_ Sep 23 '13 at 19:20
    
Here's the stacktrack from 1st exception. pastebin.com/tJbz8Gee. I've debugged JDBC and I see that TDSParser.parse() failed in sqljdbc. Somehow stored procedure was executed, but not entirely? –  newbie123_ Sep 23 '13 at 19:22
    
@newbie123_ still impossible to explain to you exactly what happened. Not sure what the stack trace reveals except that you got an exception. If this transaction is inside of one stored procedure, then why are there "1st call" and "other" calls? If everything is isolated inside a stored procedure, then multiple calls to the stored procedure should not be related in any way at all (whether they have the same spid or not is really irrelevant). –  Aaron Bertrand Sep 23 '13 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.