Oracle Transactions and Locking Explained – COMMIT, ROLLBACK & SAVEPOINT Guide

Introduction to Oracle Transactions and Locking

In Oracle, a transaction is a unit of work made up of one or more SQL statements. Transactions ensure that either all operations succeed or none at all — preserving data integrity. Unlike some other databases, Oracle does not use a specific BEGIN TRANSACTION command. Instead, a transaction begins implicitly when a DML (Data Manipulation Language) operation like INSERT, UPDATE, or DELETE occurs.

Understanding Locking in Oracle Transactions

When a DML operation starts, Oracle locks the affected rows to prevent other sessions from modifying the same data simultaneously. This ensures data consistency and concurrency control.

Example – Session 1:

UPDATE authors
SET first_name = 'Ronald'
WHERE id = 44;

This triggers a transaction, and Oracle holds two locks:

A Transaction Lock – held exclusively for the session
A DML Lock (Row-X) – held on the affected rows in the authors table

You can view these locks using:

SELECT d.session_id, d.lock_type, d.mode_held, d.blocking_others
FROM dba_locks d JOIN v$session v ON d.session_id = v.sid
WHERE v.username = 'PLSQL';

Session 2 – Lock Conflict

If another session tries to update the same row:

UPDATE authors
SET first_name = 'Ronald'
WHERE id = 44;

It hangs, waiting for the lock to be released. No error is thrown — the session simply waits until the first session issues a COMMIT or ROLLBACK.

COMMIT in Oracle

The COMMIT command ends a transaction and makes all changes permanent.

COMMIT;

Behind the Scenes:

Triggers a System Change Number (SCN)
Flushes redo log buffers to redo log files (not data files)
Releases all locks held by the transaction

ROLLBACK in Oracle

The ROLLBACK command undoes changes made by the current transaction. It also releases all locks.

ROLLBACK;

Used when an error occurs or the logic needs to abort changes before they're committed.

Using SAVEPOINT in Oracle Transactions and Locking

You can partially undo transactions using SAVEPOINT.

Example:

BEGIN
  INSERT INTO books (...) VALUES (...);
  SAVEPOINT A;

  INSERT INTO inventory (...) VALUES (...);
  SAVEPOINT B;

  UPDATE inventory SET status = 'IN STOCK' WHERE isbn = '12345678';
  
  ROLLBACK TO SAVEPOINT B;
  COMMIT;
END;

Result:

INSERT into books is saved
INSERT into inventory is saved
UPDATE to inventory.status is rolled back

Lock Views for Monitoring

View	Purpose
`DBA_LOCKS`	Shows all locks held by sessions
`V$SESSION`	Maps session IDs to usernames
`DBA_OBJECTS`	Helps identify the locked object name

📚 Related Articles

- 👉 Transaction Processing in Oracle
- 👉 PL/SQL Anonymous Blocks

🌐 Oracle Docs – Transaction Management

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