Transactions & Concurrency

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

A transaction is a unit of work that Oracle treats as all-or-nothing. Either every statement in the transaction succeeds and the changes are made permanent, or none of them are. This guarantee is what makes databases reliable for financial, medical, and operational data. Concurrency is what happens when multiple users or sessions work on the same data at the same time — Oracle must ensure they do not interfere with each other in ways that corrupt data or produce incorrect results.

COMMIT and ROLLBACK

A transaction in Oracle begins automatically with the first DML statement — INSERT, UPDATE, or DELETE. It ends when you issue COMMIT to make the changes permanent or ROLLBACK to undo them entirely. Until you commit, your changes are visible only to your own session. Other sessions see the data as it was before your transaction started. If your session disconnects without committing, Oracle automatically rolls back any uncommitted changes.

-- A transaction begins automatically with the first DML statement
UPDATE dataplexa_store.orders
SET    status = 'shipped'
WHERE  order_id = 1001;
UPDATE dataplexa_store.orders
SET    status = 'shipped'
WHERE  order_id = 1002;
-- Both updates succeed -- make them permanent
COMMIT;
-- Another transaction: something goes wrong mid-way
UPDATE dataplexa_store.products
SET    price = price * 1.10
WHERE  category = 'Electronics';
-- Discovered the wrong category was updated -- undo everything since last COMMIT
ROLLBACK;
-- Verify: the electronics prices are unchanged
SELECT product_id, product_name, price
FROM   dataplexa_store.products
WHERE  category = 'Electronics';

• DDL statements — CREATE, ALTER, DROP — issue an implicit COMMIT before and after they execute in Oracle, ending any open transaction
• A ROLLBACK only undoes changes back to the last COMMIT — it cannot undo a committed transaction
• In SQL Developer, auto-commit is off by default — always COMMIT explicitly when you intend changes to be permanent
• If a session crashes or disconnects without committing, Oracle automatically rolls back the open transaction during instance recovery

SAVEPOINT

A savepoint marks a point within a transaction that you can roll back to without undoing the entire transaction. This is useful in long transactions where you want to undo part of the work — from the savepoint forward — while keeping earlier changes intact. After rolling back to a savepoint the transaction is still open and you can continue working or commit the remaining changes.

-- Insert a new customer
INSERT INTO dataplexa_store.customers (customer_id, first_name, last_name, email)
VALUES (101, 'Alex', 'Morgan', 'alex.morgan@email.com');
-- Mark a savepoint after the customer insert
SAVEPOINT after_customer;
-- Insert an order for the new customer
INSERT INTO dataplexa_store.orders (order_id, customer_id, order_date, total_amount, status)
VALUES (5001, 101, SYSDATE, 0, 'pending');
-- Something is wrong with the order data -- roll back only to the savepoint
-- The customer insert is preserved; the order insert is undone
ROLLBACK TO SAVEPOINT after_customer;
-- The transaction is still open -- the customer row is still pending commit
-- Fix the order and insert correctly
INSERT INTO dataplexa_store.orders (order_id, customer_id, order_date, total_amount, status)
VALUES (5001, 101, SYSDATE, 249.99, 'pending');
-- Commit both the customer and the corrected order
COMMIT;

• ROLLBACK TO SAVEPOINT name undoes all changes made after the savepoint — the savepoint itself and everything before it remain intact
• After a ROLLBACK TO SAVEPOINT the transaction is still open — a full COMMIT or ROLLBACK is still required to close it
• Savepoints are session-local — they are not visible to other sessions and disappear when the transaction ends
• You can define multiple savepoints in one transaction — each must have a unique name within that transaction

Oracle's Multi-Version Read Consistency

Oracle solves one of the hardest problems in database concurrency — keeping reads accurate without blocking writers — through multi-version read consistency (MVCC). When a query starts, Oracle notes the current System Change Number (SCN). For the duration of the query, Oracle shows only data that was committed before that SCN, using the undo tablespace to reconstruct older versions of rows that have since been changed. Your query always sees a consistent snapshot of the data as it existed at the moment it started — regardless of what other sessions commit while your query is running.

• Readers do not block writers and writers do not block readers — this is Oracle's fundamental concurrency model
• A SELECT never waits for another session's uncommitted changes to be resolved — it simply reads the pre-change version from undo
• If a long-running query exhausts the undo data it needs, Oracle raises ORA-01555: snapshot too old — the undo for the SCN it needs has been overwritten
• Increasing the undo retention period (UNDO_RETENTION parameter) reduces ORA-01555 occurrences for long-running reports
• Statement-level read consistency is the default — a single query sees a consistent snapshot; a transaction can optionally use serializable isolation for transaction-level consistency

Locking and Blocking

When a session modifies a row, Oracle places a row-level lock on it. No other session can modify that same row until the first session commits or rolls back. This prevents two sessions from overwriting each other's changes. Reads are never blocked — only writes contend with other writes on the same row. If Session B tries to update a row that Session A has locked, Session B waits. If Session A never commits, Session B waits indefinitely — this is a blocking lock. The SELECT ... FOR UPDATE statement lets a session explicitly lock rows it intends to modify, preventing another session from changing them before the update arrives.

-- SELECT FOR UPDATE: lock the rows you are about to modify
-- Other sessions cannot UPDATE or DELETE these rows until this transaction ends
SELECT order_id, status, total_amount
FROM   dataplexa_store.orders
WHERE  customer_id = 14
FOR UPDATE;
-- Now update the locked rows safely
UPDATE dataplexa_store.orders
SET    status = 'processing'
WHERE  customer_id = 14;
COMMIT;  -- releases the lock
-- NOWAIT: fail immediately instead of waiting if the rows are already locked
SELECT order_id, status
FROM   dataplexa_store.orders
WHERE  order_id = 1001
FOR UPDATE NOWAIT;   -- raises ORA-00054 if another session holds a lock on this row
-- Find sessions that are currently blocking others (requires DBA access)
SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL;

• FOR UPDATE locks only the rows returned by the query — not the entire table
• FOR UPDATE NOWAIT raises ORA-00054 immediately if any row is locked — useful in applications where waiting is not acceptable
• FOR UPDATE WAIT n waits up to n seconds before raising an error — a middle ground between waiting indefinitely and failing immediately
• Deadlocks occur when Session A holds a lock Session B needs and Session B holds a lock Session A needs — Oracle detects these automatically, rolls back one of the statements, and raises ORA-00060
• V$SESSION is a dynamic performance view — querying blocking_session shows which session is blocking others

Lesson Summary

Practice Questions

Quiz

Next up — Backup & Recovery (RMAN) — How Oracle's Recovery Manager protects your data, how to take full and incremental backups, and how to restore and recover a database.