Oracle Database - ROLLBACK (undo of a transaction)

Card Puncher Data Processing

Purpose

Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction.

Type

Implicit

An implicit ROLLBACK occurs when the session (or program) abnormally terminates.

Explicit

An explicit commit occurs when the ROLLBACK statement is executed.

Syntax

rollback [work] to savepoint;

If the savepoint is unknown, Oracle will display a warning but the transaction can continue.

  • Rollback. Rollback the whole transaction ignoring the savepoints.
rollback

Statement-Level Rollback

Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.

A successful statement is different from a committed transaction. A single SQL statement executes successfully if the database parses and runs it without error as an atomic unit, as when all rows are changed in a multirow update.

If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback. This operation has the following characteristics:

  • A SQL statement that does not succeed causes the loss only of work it would have performed itself.

The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction. For example, if the execution of the second UPDATE statement in Figure 10-1 causes an error and is rolled back, then the work performed by the first UPDATE statement is not rolled back. The first UPDATE statement can be committed or rolled back explicitly by the user.

  • The effect of the rollback is as if the statement had never been run.

Any side effects of an atomic statement, for example, triggers invoked upon execution of the statement, are considered part of the atomic statement. Either all work generated as part of the atomic statement succeeds or none does.

An example of an error causing a statement-level rollback is an attempt to insert a duplicate primary key. Single SQL statements involved in a deadlock, which is competition for the same data, can also cause a statement-level rollback. However, errors discovered during SQL statement parsing, such as a syntax error, have not yet been run and so do not cause a statement-level rollback.

Example

gerardnico@orcl>create table big_table as select * from all_objects where 1=0;

Table created.

gerardnico@orcl>select count(*) from big_table;

  COUNT(*)
----------
         0

gerardnico@orcl>insert INTO big_table SELECT * FROM all_objects

66651 rows created.
gerardnico@orcl>select count(*) from big_table;

  COUNT(*)
----------
     66651

gerardnico@orcl>rollback;

Rollback complete.

gerardnico@orcl>select count(*) from big_table;

  COUNT(*)
----------
         0

Reference





Discover More
Card Puncher Data Processing
Oracle Database - Deadlock

A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlock situations...
Card Puncher Data Processing
Oracle Database - Distributed Transactions

A distributed database is a set of databases in a distributed system that can appear to applications as a single data source. A distributed transaction is a transaction that includes one or more statements...
Toad Lock
Oracle Database - Locks

Lock Mechanism in Oracle Oracle Database provides: data concurrency, consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished. and...
Oracle Database Row Lock Tx
Oracle Database - Row Locks (TX)

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement. The...
Card Puncher Data Processing
Oracle Database - TableSpace

A tablespace is a logical data structure which is physically represented by one or more datafiles physically. From a analysis point of view, a tablespace is a group of one or more: physic datafile....
Card Puncher Data Processing
Oracle Database - Transactions

All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. A transaction begins when the first executable SQL statement is encountered. An executable...
Card Puncher Data Processing
PL/SQL - Autonomous Transactions (Pragma)

An autonomous transaction is anindependent transaction that can be called from another transaction, called the main transaction. Autonomous transactions are useful for actions that must be performed...



Share this page:
Follow us:
Task Runner