What is Transaction Management in Oracle?

Transaction Management in Oracle

A transaction in a database system is a logical unit of work. It is a series of one or more SQL statements that are treated as a single, indivisible action by the Oracle Database, such as INSERT, UPDATE, DELETE, or MERGE. These transactions must preserve data integrity even when there are system failures or several users accessing the application at the same time for it to be dependable. The ACID qualities (Atomicity, Consistency, Isolation, and Durability) ensure this dependability.

ACID Properties

1. The atomicity of Atomicity guarantees all-or-nothing changes to the database state from a transaction. This indicates that either all or none of the operations in a transaction are successfully completed and permanently recorded. In the event that a transaction component fails, the entire transaction is rolled back, returning the database to its initial state as though the transaction had never taken place. For instance, there are two phases involved in moving money between bank accounts: debiting and crediting the destination. Atomicity guarantees either both steps occur or neither does.
2. Stability In order to maintain consistency, a transaction must change the database’s valid state. This indicates that none of the integrity restrictions for that state such as primary keys, foreign keys, or check constraints are broken by the collective operations. To maintain the consistency of the database, a transaction is rolled back whenever it tries to break any business rules or restrictions.
3. Distancing Isolation makes it seem as though several concurrently running transactions are serially executing. Stated differently, two transactions cannot see each other’s intermediate state. By treating every transaction as though it were the only one executing on the system, unclean reads, non-repeatable reads, and ghost reads are avoided. Oracle provides a range of isolation levels.
   • Read Committed (Default): This is Oracle’s standard isolation level. Only changes that were committed prior to the transaction starting and the transaction’s own uncommitted changes will be visible to a transaction operating in this mode. A DML statement will wait for the release of any row locks that are held by another transaction if it needs them.
   • Serializable: If a DML statement inside a transaction is set to SERIALIZABLE, it will fail if it tries to change rows that have already been altered by another uncommitted transaction at the beginning of the serialisable transaction. As a result, transactions that modify separate rows inside the same “snapshot” timeframe cannot impact one another, offering a better degree of isolation.
4. Sturdiness. Durability ensures that changes made to the database by a transaction are permanent and will withstand any further system failures, including crashes or power outages, once the transaction has successfully finished (been committed). Before a transaction is tagged as committed, Oracle writes all modifications to redo log files to ensure persistence. Because they enable Oracle to reapply committed changes in the event of a failure, these redo logs are essential for recovery operations.

Controlling Transactions

Oracle offers a number of instructions for transaction control:

1. Be dedicated To make all changes made within the current transaction permanent, use the COMMIT command. All locks obtained by a transaction are released once it is committed, and its modifications are available to other users and sessions. Additionally deleted are any savepoints made during that transaction. The syntax is straightforward: COMMIT; or COMMIT WORK;. Oracle offers options like COMMIT WRITE IMMEDIATE WAIT (the default) or COMMIT WRITE BATCH to control how redo information is written to the log.
2. RECOVER By reverting all uncommitted changes performed during the current transaction, the ROLLBACK command essentially restores the database to its initial state. In order to fix mistakes or stop unfinished tasks, this is essential. The uncommitted modifications are deleted and all locks are released upon issuing a ROLLBACK. During the subsequent database setup, Oracle will automatically roll back any uncommitted work if a serious issue, such as a machine malfunction, arises prior to a commit. The developer must specifically handle this, though, as DML modifications made in a PL/SQL block are not automatically rolled back on exception. The basic syntax is ROLLBACK; or ROLLBACK WORK;.
3. A SAVEPOINT A SAVEPOINT enables you to break up a lengthy transaction into more manageable chunks. A defined savepoint can be made, and if an issue arises during the transaction, you can ROLLBACK TO that particular savepoint instead of rolling back the entire transaction, reversing only the modifications made since then. In this way, you can fix contemporary problems while maintaining prior, useful efforts. The syntax is SAVEPOINT savepoint_name; to savepoint. The savepoint itself is kept when you ROLLBACK TO it, but all subsequent savepoints are deleted.

Implicit Commits and Rollbacks: Oracle executes an implicit COMMIT both before and after each DDL statement, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. For uncommitted transactions, an implicit commit may also be triggered by leaving SQL*Plus or ending a session. On the other hand, if a program ends unexpectedly, all uncommitted transactions are automatically reversed.

Read Consistency in Oracle

The foundation of Oracle’s transaction management system is its read consistency model. It makes sure that a user always sees the same view of the data as it was at the beginning of the query or, if SERIALISABLE isolation is enabled, at the beginning of the transaction. Your query won’t see any changes made by other users while they are simultaneously editing the data until your transaction commits and a fresh query is started.

Undo segments, formerly known as rollback segments, are a key component of this method. Before applying the modifications to the table, Oracle saves the “before image” of the data the original values in an undo segment whenever a user makes changes to it. Oracle fetches the original values from the undo segment to provide a consistent display if another user queries the same data while the changes are still being undone.

Since its introduction in Oracle9i, Automatic Undo Management (AUM) has been widely utilised to streamline administration by automating the management of undo segments, storage, and retention. Long-running enquiries and Flashback enquiries depend on the UNDO_RETENTION option, which controls how long committed undo data is retained. Users can use the undo information to obtain data as it was at a particular past period, or SCN (System Change Number), by utilising flashback queries.

Table Example: Creating a Table, Inserting Values, and Output

Let’s illustrate with a simple EMPLOYEES table:

1. Create Table Statement

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID    NUMBER(6)     PRIMARY KEY,
    FIRST_NAME     VARCHAR2(20),
    LAST_NAME      VARCHAR2(25)   NOT NULL,
    EMAIL          VARCHAR2(25)   NOT NULL UNIQUE,
    PHONE_NUMBER   VARCHAR2(20),
    HIRE_DATE      DATE           NOT NULL,
    JOB_ID         VARCHAR2(10)   NOT NULL,
    SALARY         NUMBER(8,2),
    COMMISSION_PCT NUMBER(2,2),
    MANAGER_ID     NUMBER(6),
    DEPARTMENT_ID  NUMBER(4)
);

2. Insert Values and Transaction Control

Here’s an example demonstrating INSERT, COMMIT, ROLLBACK, and SAVEPOINT:

-- Start of a new transaction (implicitly)

-- Insert first employee
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (101, 'John', 'Doe', 'john.doe@example.com', '555-1234', SYSDATE, 'IT_PROG', 60000);
1 row created.

-- Create a savepoint
SAVEPOINT initial_insert;
Savepoint created.

-- Insert second employee
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (102, 'Jane', 'Smith', 'jane.smith@example.com', '555-5678', SYSDATE, 'IT_PROG', 75000);
1 row created.

-- At this point, only the current session sees both John and Jane.
-- Other sessions still see no employees in the table.

-- Let's say we realize Jane's salary was wrong and want to undo only her insert.
ROLLBACK TO initial_insert;
Rollback complete.

-- Now, Jane's record is undone. Only John's record is pending.
-- Let's insert Jane again with the correct salary.
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (102, 'Jane', 'Smith', 'jane.smith@example.com', '555-5678', SYSDATE, 'IT_PROG', 70000);
1 row created.

-- Insert a third employee
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY)
VALUES (103, 'Peter', 'Jones', 'peter.jones@example.com', '555-9012', SYSDATE, 'HR_REP', 55000);
1 row created.

-- All three records are now pending in the current session.
-- Let's make them permanent.
COMMIT;
Commit complete.

3. Output After Commit

After the COMMIT, if you query the EMPLOYEES table from any session, you will see all three records:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES;

Output:

EMPLOYEE_ID FIRST_NAME LAST_NAME      SALARY
----------- ---------- -------------- ----------
        101 John       Doe             60000
        102 Jane       Smith           70000
        103 Peter      Jones           55000

This comprehensive approach to transaction management, underpinned by ACID properties and controlled by COMMIT, ROLLBACK, and SAVEPOINT, along with Oracle’s robust read consistency model, is what makes Oracle Database a reliable and powerful system for managing critical business data.