8

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

Manngo
  • 3,065
  • 10
  • 38
  • 61

3 Answers3

2

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.

Shanky
  • 19,148
  • 4
  • 37
  • 58
1

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.

You are absolutely right. In an excplicit transaction (with excplicit Begin ... Commit statements) the transaction wrapped inside Begin..Commit will whether be fully completed or fully rolled back if even one statements inside fails. It refers to Atomicity property of transactions.

Does this suggest that ROLLBACK is more something that would occur, say, in a procedure?

As @Shanky stated, ROLLBACK usually occurs automatically inside a transaction, whether it is explicit or not. I've seen and used ROLLBACK only for testing.

George K
  • 2,306
  • 1
  • 17
  • 32
0

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements and Rollback is one of them.

TCL (Transaction Control Language)

BEGIN Transaction – opens a transaction COMMIT Transaction – commits a transaction ROLLBACK Transaction – ROLLBACK a transaction in case of any error

The ROLLBACK Command:

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

ROLLBACK TRANSACTION Information:

  • ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction.

  • When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0.

  • ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
  • ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.
  • A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction will also be rolled back, even if you have issued a COMMIT TRANSACTION for it.
  • Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK TRANSACTION using the duplicate savepoint name rolls back only to the most recent SAVE TRANSACTION using that savepoint name.

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?

In stored procedures, ROLLBACK TRANSACTION statements without a savepoint_name or transaction_name roll back all statements to the outermost BEGIN TRANSACTION. A ROLLBACK TRANSACTION statement in a stored procedure that causes @@TRANCOUNT to have a different value when the stored procedure completes than the @@TRANCOUNT value when the stored procedure was called produces an informational message. This message does not affect subsequent processing.

If a ROLLBACK TRANSACTION is issued in a trigger:

  • All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.
  • The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.
  • The statements in the batch after the statement that fired the trigger are not executed.

@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)

ROLLBACK TRANSACTION statements in stored procedures do not affect subsequent statements in the batch that called the procedure; subsequent statements in the batch are executed. ROLLBACK TRANSACTION statements in triggers terminate the batch containing the statement that fired the trigger; subsequent statements in the batch are not executed.

The effect of a ROLLBACK on cursors is defined by these three rules:

  1. With CURSOR_CLOSE_ON_COMMIT set ON, ROLLBACK closes, but does not deallocate all open cursors.
  2. With CURSOR_CLOSE_ON_COMMIT set OFF, ROLLBACK does not affect any open synchronous STATIC or INSENSITIVE cursors or asynchronous STATIC cursors that have been fully populated. Open cursors of any other type are closed but not deallocated.
  3. An error that terminates a batch and generates an internal rollback deallocates all cursors that were declared in the batch containing the error statement. All cursors are deallocated regardless of their type or the setting of CURSOR_CLOSE_ON_COMMIT. This includes cursors declared in stored procedures called by the error batch. Cursors declared in a batch before the error batch are subject to rules 1 and 2. A deadlock error is an example of this type of error. A ROLLBACK statement issued in a trigger also automatically generates this type of error.

Most of the content is copied from the following link:

https://msdn.microsoft.com/en-us/library/ms181299.aspx