Transaction Processing in Oracle – 5 Key ACID Properties Explained

What is Transaction Processing?

In Oracle PL/SQL, transaction processing refers to the handling of a group of operations as a single unit of work. A transaction may include multiple SQL operations like INSERT, UPDATE, and DELETE—but it’s considered complete only when a COMMIT is issued. If anything goes wrong, the entire transaction can be rolled back, undoing all changes.

Example: Imagine placing an online order. You select items, enter payment info, and hit “Submit.” If the site crashes at that moment, were you charged? Did the order go through? Without reliable transaction handling, this uncertainty could lead to data inconsistency and customer dissatisfaction.

Oracle solves this with transaction control and ACID properties.

How Does Oracle Handle Transactions?

Oracle automatically begins a transaction when the first DML statement (like INSERT, UPDATE, or DELETE) is executed. This transaction continues until a user issues either a COMMIT or a ROLLBACK.

Until you commit, the changes are only visible to your session.

What are ACID Properties?

Oracle ensures data reliability using the ACID model, a standard for transaction processing:

ACID Property	Description
Atomicity	All operations within a transaction succeed or all fail.
Consistency	Ensures data remains valid before and after the transaction.
Isolation	Transactions do not affect each other until committed.
Durability	Once committed, the transaction is permanent—even after a crash.

Example: Transaction with COMMIT and ROLLBACK

BEGIN
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
  COMMIT; -- Both updates become permanent
END;

Now, if something fails before the COMMIT, Oracle allows you to do this:

ROLLBACK; -- Reverts all changes made so far in the transaction

SAVEPOINT – Partial Rollbacks

Oracle allows setting savepoints in a transaction to mark specific rollback points.

SAVEPOINT before_bonus;

UPDATE employees SET bonus = 5000 WHERE department_id = 10;

ROLLBACK TO before_bonus; -- Undoes the bonus update only

Why Transaction Processing Matters

Without transaction control, your application could end up:

Deducting money from one account but failing to credit another
Saving incomplete form data
Leaving a customer order in limbo

These situations lead to data corruption, user distrust, and business loss.

Tools to View Transaction Status

You can use the following to monitor or debug transactions:

Tool	Purpose
`DBMS_TRANSACTION`	View transaction ID, state
`V$TRANSACTION`	Shows active transactions
`SHOW ERRORS`	Debug compilation issues in PL/SQL

Best Practices for Transaction Handling

Always COMMIT or ROLLBACK explicitly in PL/SQL.
Use exception handling to manage transaction failures.
Use SAVEPOINT in long or critical operations.
Avoid leaving transactions open for too long—it can block others.

More PL/SQL Type Families

Aside from scalar types, PL/SQL supports:

Composite Types (records, collections)
Reference Types (pointers, REF CURSORs)
LOBs (CLOB, BLOB, BFILE)

➡️ Learn more about PL/SQL Data Types in Oracle Docs

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.