Systems Engineering and RDBMS

Cancelled Queries in a Transaction

Posted by decipherinfosys on July 14, 2007

This is one of those things that many application developers do not pay attention to. If you start a transaction and your code has executed queries that take locks and then one of your subsequent query fails due to say a lock time out or a query time out, only the statement is terminated. Many developers remain under the impression that when this happens, the transaction will be automatically rolled back but that is not the case.

As a result of this, all the subsequent transactions that are started are then going to be treated as nested transactions which means that the previous locks that were acquired before the query/lock time out occurred are still retained by the application. This can of course cause a lot of blocking locks issues in your system and thus one needs to write application code with proper error and transaction nesting level checks to ensure that proper commit or rollbacks are being issued.

Speaking of nested transactions, in SQL Server, the outer commit controls the entire commit scope i.e. inner commits are meaningless where as any rollback done anywhere between the nested transaction will rollback all the nested transactions.

Sorry, the comment form is closed at this time.

%d bloggers like this: