TRANSACTIONS

A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. Oracle supports transactions as defined by the SQL standard. As soon as the database is connected with sqlplus, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement issued subsequently becomes a part of this transaction. A transaction ends when database is disconnected or when a COMMIT or ROLLBACK command is issued.

COMMIT makes permanent any database changes made during the transaction. Until commit is issued, other users cannot see the changes. ROLLBACK clears the current transaction and undoes any changes made since the transaction began. After the current transaction has ended with a COMMIT or ROLLBACK, the first executable SQL statement issued subsequently will automatically begin another transaction.

For example, the following SQL commands have the final effect of inserting into table R the tuple (3, 4), but not (1, 2):

insert into R values (1, 2);

rollback;

insert into R values (3, 4);

commit;

AUTOCOMMIT

With this option set to ON each individual SQL statement is treated as a transaction, which will be automatically committed right after it is executed. Command to enable, disable, display AUTOCOMMIT option is given below.

SET AUTOCOMMIT ON

SET AUTOCOMMIT OFF

SHOW ALL   --Display current setting for all options

The same rules applies for programs interacting with Oracle like Pro*C or JDBC. Pro*C doesn't support the AUTOCOMMIT option whereas JDBC does and it has a default AUTOCOMMMIT option set to ON.

Note: Oracle automatically issues an implicit COMMIT before and after any DDL (Data Definition Language) statement (even if this DDL statement fails) .

SAVEPOINT

SAVEPOINT is the concept of storing current state in the processing of a transaction.

Commands

--Creates a savepoint named <sp_name>.

SAVEPOINT  <sp_name>

--To undo parts of a transaction back till a savepoint

ROLLBACK TO <sp_name>

Example,

insert into emp values (5, 6);

savepoint my_sp_1;

insert into emp values (7, 8);

savepoint my_sp_2;

insert into emp values (9, 10);

rollback to my_sp_1;

insert into emp values (11, 12);

commit;

The above set of commands will result in inserting into table emp records (5, 6) and (11, 12), but not (7, 8) or (9, 10)