Transactions

What is the transaction?

The transaction is a operation of change the database, consisting of multiple operations updates rows in the table. If you stop the operations base changes during the course of the transaction, the database should return to the state before the transaction. Only after the completion of the transaction changes made during the transaction will be visible to other users.

The transaction begins automatically when performing the first operation DML - changes in the base and continues to:

• explicit approval of the transaction - COMMIT command. The changes become irreversible and visible to other users
• the removal of sensible transaction - ROLLBACK command. The database then returns to the state before the transaction
• implementation of DDL (CREATE, ALTER, DROP). Any such order is not approved the transaction and approve the transaction early
• completion of application session - depending on the application transaction is either approved or withdrawn
• interrupt the session - a network failure, power failure - transactions are rolled back

The transaction should form a coherent whole, to avoid too long a transaction - they should be approved after the parts constituting a logical whole. This saves system resources and allows other users to the changes.

COMMIT command

This command is used to explicitly commit the transaction.

COMMIT [WORK];

This command:

• completion of the transaction
• approval of changes - they become irreversible
• changes become visible to other users
• remove all locks and all points of conduct

Implementation of any DDL commands will have the same effect as the execution of a COMMIT.

ROLLBACK command

Used to explicitly rollback the transaction.

ROLLBACK [WORK];

ROLBACK command results in:

• completion of the transaction
• withdrawal of all changes made in the session from the beginning of the transaction
• remove all locks and all points of conduct

Savepoints

Savepoints are used to split the transaction into smaller parts.

SAVEPOINT savepoint_name;

The introduction point can withdraw transaction behavior to a certain point behavior, without losing the changes you made previously.

To revert to the last point of behavior is the following:

ROLLBACK [WORK] TO [SAVEPOINT]  savepoint_name;

The command is:

• withdraws part of the transaction to the specified savepoint
• retains the  savepoint, but has beaten all later
• relieve the blockade established by commands issued after this  savepoint.

Implicit withdrawal

Implicit transaction is complete withdrawal of the case such as interrupt user sessions, computer crashes, power failure etc.

Implicit partial withdrawal transaction in the case of an error command. Be withdrawn if the changes made by the wrong order.

Auto COMMIT

In SQL * Plus, we can force the auto-approve the transaction after each INSERT, UPDATE, and DELETE.

SET AUTO [COMMIT] ON

Directive refers command:

SET AUTO [COMMIT] OFF