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!
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.