SQL Transactions – When to Roll Back

rollbacktransaction

This is probably a simplistic question, but I would like to check on the mechanics of rolling back a transaction.

As far as I understand, the steps are as follows:

  1. BEGIN TRANSACTION (or equivalent)
  2. One or more SQL statements
  3. COMMIT or ROLLBACK

As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.

This would suggest that the above process, up to committing could be in a manually entered collection of statements.

However, at what stage do I make the decision to roll back? Does this suggest that ROLLBACK is more something that would occur, say, in a procedure? In other words, does one ever roll back manually?

By procedure, I’m not necessarily referring to a stored procedure. I suppose some application might be doing the same thing.

I think this question applies to any SQL database in general (provided it supports transactions, of course).

Best Answer

In other words, does one ever roll back manually?

No not as such, unless someone is doing testing I have hardly seen people manually doing a rollback. The rollback is specified in stored procedures or transactions in which you either want complete change to be entered or nothing at all.

As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.

Depending on how transaction is initiated, if it is under explicit begin transaction and commit yes it will rollback completely to its initial state after the query fails, while if the transaction is not under begin transaction and commit it will store the changes done till the query failed and from after the query failed the it would be stopped from making further changes.

A classic case where system initiates a rollback is when a deadlock happens and SQL Server by internal mechanism chooses deadlock victim, kills the transaction and it may or may not rollback depending on whether it was explicit or implicit transaction. Like when normal select statement is killed their is no rollback as such.

Related Question