Oracle transaction control.

Oracle transaction control

A transaction is a series of SQL statements that either succeeds or fails as a unit. Transactions are a standard part of relational databases and prevent inconsistent data. The classic example of this is a bank transaction:

update accounts
   set balance = balance - trans_amount
 where account_no = from_account;
update accounts
   set balance = balance + trans_amount
 where account_no = to_account;

Suppose the first update statement succeeds, but the second statement fails due to an error. The data is now inconsistent. We prevent this combining two statements into a transaction, whereby either both statements will succeed or both statements will fail.

A transaction begins with the first SQL statement issued after the previous transaction, or the first SQL statement after connection to the database. The transaction ends with the commit or rollback statement.

`commit` versus `rollback`

When a commit statement is issued to the database, the transaction is ended, and:

All work done by the transaction is made permanent.
Other sessions can see the changes made by this transaction.
Any locks acquired by the transaction are released.

The syntax of the commit statement is:

commit [work];

The optional keyword work is available for increased readability. Until the transaction is committed, only the session executing that transaction can see the changes made by that session.

When a rollback statement is issued to the database, the transaction is ended, and:

All work done by the transaction is undone, as if hadn't been issued.
Any locks acquired by the transaction are released.

The general syntax of the rollback statement is:

rollback [work] [to savepoint save_point_name];

An explicit rollback statement is often used when an error that prevents further work is detected. If a session disconnects from the database without ending the current transaction with commit or rollback, the transaction is automatically rolled back by the database.

Savepoints

The rollback statement undoes the entire transaction. With the savepoint command, however, only a part of the transaction need be undone. The syntax for the savepoint command is:

savepoint save_point_name;

Note that savepoints are not declared in the declarative section, since they are global to a transaction, and the transaction can continue past the end of the block. Once a savepoint is defined, the program can roll back to the savepoint using the "to savepoint" rollback syntax. When a rollback to savepoint is issued, the following things occur:

Any work done since the savepoint is undone. The savepoint remains active, however. It can be rolled back to again, if needed.
Any locks and resources acquired by the SQL statements since the savepoint will be released.
The transaction is not finished, because SQL statements are still pending.

savepoint is often used before a complicated section of a transaction. If this part of the transaction fails, it can be rolled back, allowing the earlier part to continue.

Transactions versus Blocks

It's important to note the distinction between transactions and PL/SQL blocks. When a block starts, it doesn't mean that a transaction starts. For example:

insert into pl_test values ('transaction starts', 111);
begin
 update pl_test set name='block starts' where id=237;
 rollback;
end;

Note that we issued an insert statement and then an anonymous PL/SQL block. The block issues an update transaction and then rollback. This rollback undoes not only the update statement, but the prior insert as well. Both the insert statement and the block are the part of the same transaction.

Similarly, a single PL/SQL block can contain multiple transactions:

declare
  v_iteration binary_integer := 0;
begin
  for v_counter in 1..500 loop
    insert into pl_test values (null, v_counter);
    v_iteration := v_iteration + 1;
    if v_iteration = 50 then
      commit;
      v_iteration := 0;
    end if;
  end loop;
end;

Autonomous Transactions

An autonomous transaction is a transaction that is started within the context of another transaction, known as the parent transaction, but it's independent of it. The autonomous transaction can be committed and rolled back regardless of the state of the parent transaction.

The only way to execute an autonomous transaction is from a PL/SQL block. The block is marked as autonomous by using a pragma in the declarative section:

insert into pl_test values ('non autonomous', 21);
declare
  pragma autonomous_transaction;
begin
  insert into pl_test values ('autonomous', 19);
  commit;
end;
/
rollback;
select * 
  from pl_test;

pragma autonomous_transaction must appear in the declarative section of the block, and only one pragma is allowed in the block. It can go anywhere in the declarative section, but it is good style to put it at the beginning. Not all blocks can be marked as autonomous. Only the following are legal:

Top-level anonymous blocks
Local, stand-alone, and packaged subprograms
Methods of an object type
Database triggers

In particular, nested PL/SQL blocks cannot be autonomous. Also, only individual subprograms in a package can be marked as autonomous - the package itself cannot be.

Properties of Autonomous Transactions

An autonomous transaction begins with the first SQL statement in an autonomous block, and ends with a commit or rollback statement. Any transaction control statement can be used in an autonomous transaction. Savepoints are local to the current transaction; that is, we cannot rollback to a savepoint in the parent transaction.

An autonomous transaction ends with a commit or rollback statement. It does not end when the block containing it ends. If an autonomous block ends without ending the transaction, an ORA6519 error is raised and the autonomous transaction is rolled back:

declare 
  pragma autonomous_transaction;
begin
  insert into pl_test values ('You will not see this', 222);
end;
/

declare
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 4