Transaction

Question

In explicit transaction, if i start the transaction by giving BEGIN TRANS but if i dont give COMMIT,ROLLBACK or END TRANS then what will happen to the application?

Answer 1

nothing - the transaction is still on-going. If you terminate the connection, it is rolled-back.

Answer 2

Because your transaction may lock parts of the database, you may cause other transactions/queries to block until you do issue one of those commands.

At some point, one way or another, the transaction has to be completed. When something goes 'wrong' (eg application ended), it will be rolled back, unless you commit it first.

Answer 3

If the application quits then the connection is implicitly closed and gets rolled back.

If the application is running in an application server (e.g. ASPNET, Java app server etc) then the connection probably gets returned to a pool, where hopefully it is reset before it's used again, and the transaction eventually gets rolled back (but maybe not immediately).

If the unused connection sits in a pool it probably gets closed eventually by the app server, at which point the transaction is definitely rolled back.

Be wary of keeping open transactions for too long - you'll hold locks (if you've changed anything) and stop old rows from being cleaned up, which might have very detrimental effect on a database with a lot of "churn".

Answer 4

You will end up with unwanted behavior if you leave a transaction open. If it is not rolled back when your app returns the connection to the pool, you will have a blocking transaction. If it is rolled back, your work is lost. Either way, this is not the behavior you would like in a production environment.

/Håkan Winther