What is Database Transaction ?

Transaction Management

In database, transaction gins technically when 1^st executable SQL statements is encountered. An executable SQL statement is encountered. An executable SQL statement generates calls to an instance using DML & DDL statements.

When a transaction begins; it is assigned (the transaction) to an available undo tablespace to record rollback entries for new transaction.

When Transaction Ends

 User issues COMMIT, ROLLBACK statement without a SAVEPOINT.

• If the current transaction contains any DDL(Data Definition Language) statement clause.

              1^st the transaction is COMMIT & then

              Runs the DDL statement as a new, single transaction.

• When the user is disconnected from DB. The transaction committed.

• When user terminates abnormally. The current transaction is ROLLBACK

 

When Transaction is committed

The internal transaction table for the associated undo tablespace record that the transaction has committed, the corresponding unique System Change No.(SCN) of the transaction is assigned and recorded in the table.

The Log writer process writes redo log entries in SGA’s redo log buffers. SCN also writes redo log files. This atomic event constitutes the commit of transaction.

Releases Locks held on rows and table & other. Marks transaction complete.

 

ROLLBACK

Undo the changes to data that have been performed by SQL statements within an uncommitted transaction. It uses undo tablespace ( or rollback segments ) to store old values. The redo log contains a record of changes.

Also, you can ROLLBACK to a marker called SAVEPOINT.

 ROLLBACK Type

• Statement-Level ROLLBACK

• ROLLBACK to SAVEPOINT

• ROLLBACK of transaction due to user request.

• ROLLBACK due to abnormal process termination/incomplete transaction or when instances terminates abnormally.

All the changes made by transaction are undoes by using corresponding undo tablespace.

Then releases all LOCKs of data transaction. Transaction ends.

 

SAVEPOINT

Intermediate Markers: SAVEPOINT

SAVEPOINT divide a long transaction into smaller parts.

This makes an option later of Rolling Back work performed before the current point in the transaction but after a declared SAVEPOINT within current point in the transaction but after a declared SAVEPOINT within current point in transaction. ( Transaction does not ends, it remain active and can be continued )

Eg. SAVEPOINT before each function in a prog. improves transaction efficiency. If a function fails it is easy to recover the data as compare to whole transaction ROLLBACK. DB preserves the data upto the specified SAVEPOINT. ( After that SAVEPOINT all other SAVEPOINT are lost )

NESTED Transaction

A nested transaction occurs when a new transaction is started by an instruction that is already inside an existing transaction. The new transaction is said to be nested within the existing transaction.

• Nested transaction are implemented differently in diff. DB however, they have incomman that the changes are not visible to any unrelated transaction until the changes are not visible to any unrelated transaction until the outermost transaction have/has completed. 

          This means that COMMIT in an inner transaction does no necessary persist updates to the database.

• In some DB changes made by nested transaction are not visible even to host transaction until COMMIT. Thus, it follows isolates property of transaction.

RULES for NESTED Transaction

• When nested (child) transaction is active, the parent transaction may not perform any transaction operation other than to COMMIT / ABORT or to create more child transaction.

• Committing a nested transaction has no effect on the state of parent transaction. The parent transaction is still uncommitted.

• Aborting a nested transaction has no effect on parent transaction state. The result of abort is that neither parent transaction nor other any transaction will see any of DB modifications performed by nested transaction.

• If parent transaction COMMIT/ABORT, the child transaction is/are also resolved in same way as the parent transaction.

• The locks held by the nested transaction are not released after COMMIT. They are now held by parent transaction.

• The depth of the nesting that you can achieve with nested transaction is limited only by memory.