Transactions Interview Questions

What is Transaction ?

A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID. All Oracle transactions obey the ACID properties.

What is autonomous transaction ?

An autonomous transaction is an independent transaction that can be called from another transaction, which is the main transaction. You can suspend the calling transaction, perform SQL operations and commit or undo them in the autonomous transaction, and then resume the calling transaction.

Autonomous transactions have the following characteristics:

• Does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
• Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit.
• Autonomous transactions can start other autonomous transactions. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.

What is Commit ?

The COMMIT statement makes permanent any changes made to the database during the current transaction. A commit also makes the changes visible to other users. The COMMIT statement releases all row and table locks, and erases any savepoints you marked since the last commit or rollback.

What is Rollback ?

The ROLLBACK statement undoes some or all database changes made during the current transaction. All savepoints marked after the savepoint to which you roll back are erased. The savepoint to which you roll back is not erased.

What is Set Transaction ?

SET TRANSACTION statement is used to establish the current transaction as read-only or read/write, establish its isolation level, or assign it to a specified rollback segment. The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions.

Syntax for the SET TRANSACTION statement:

SET TRANSACTION [ READ ONLY | READ WRITE ]

[ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ]

[ USE ROLLBACK SEGMENT 'segment_name' ]

[ NAME 'transaction_name' ];

What are transaction isolation levels in oracle ?

Oracle provides following transaction isolation levels:

Read Committed : In the read committed isolation level, which is the default, every query executed by a transaction sees only data committed before the query—not the transaction—began.

Serializable : In the serializable isolation level, a transaction sees only changes committed at the time the transaction—not the query—began and changes made by the transaction itself.

Read-Only : The read-only isolation level is similar to the serializable isolation level, but read-only transactions do not permit data to be modified in the transaction unless the user is SYS.

What are savepoints ?

The SAVEPOINT statement names and marks the current point in the processing of a transaction.

What will happen after commit statement ?

Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.

Before a transaction that modifies data is committed, the following has occurred:

• Oracle has generated undo information. The undo information contains the old data values changed by the SQL statements of the transaction.

• Oracle has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. These changes may go to disk before a transaction is committed.

• The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction is committed.

When a transaction is committed, the following occurs:

• The internal transaction table for the associated undo tablespace records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

• The log writer process (LGWR) writes redo log entries in the SGA's redo log buffers to the redo log file. It also writes the transaction's SCN to the redo log file. This atomic event constitutes the commit of the transaction.

• Oracle releases locks held on rows and tables.

• Oracle marks the transaction complete.

What is Locking ? What are different modes of locking ?

Locking is the mechanisms that prevent destructive interaction between transactions accessing the same resource — either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows.

Modes of Locking are Exclusive lock mode and Share lock mode.

What is Deadlock ?

Deadlock is a situation that can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks.

How can you Rollback a particular part of a procedure or any PL/SQL Program ?

It can be done using Savepoints during definition of a PL/SQL program.

What is the difference between Autonomous Transaction and Nested Transaction ?

As in case of Nested Transaction, Autonomous Transaction :

1. Do not share trasaction resources like variables with main transaction.

2. Have to be commited or rolled back separately.

3. It does not depend on main transaction. When main transaction rolls back, nested transaction also rolls back but autonomous transaction does not.

4. Exceptions raised in autonomous transaction are transaction-level and not statement-level.