Transactions, Sequences, and Concurrency

COMMIT, ROLLBACK, and SAVEPOINT#

A transaction groups statements into a single all-or-nothing unit. Oracle, unlike some databases, doesn't run in autocommit mode by default for DML from a client session such as SQL*Plus — a transaction implicitly begins with the first statement that modifies data, and stays open until an explicit COMMIT or ROLLBACK:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

ROLLBACK undoes every change made since the transaction began, as if none of it happened:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something goes wrong here
ROLLBACK;   -- the UPDATE above never happened, as far as the database is concerned

SAVEPOINT marks an intermediate point inside a longer transaction that a partial rollback can return to, without discarding everything done since the transaction started:

UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

SAVEPOINT after_raise;

DELETE FROM employees WHERE employee_id = 999;  -- turns out to be a mistake

ROLLBACK TO after_raise;   -- undoes only the DELETE; the salary raise above is kept

COMMIT;

This is genuinely useful in a multi-step procedure that wants to attempt something risky, and cleanly back out of just that risky part without losing everything already done in the same transaction.

Oracle's read-consistency model#

Oracle guarantees that every query sees a read-consistent view of the data — a snapshot as of the moment the query (or, under SERIALIZABLE, the transaction) began — without ever taking a read lock to get it. It does this using undo: before a row is changed, Oracle writes the row's prior value into an undo segment, a genuinely separate storage structure from the table itself. A query that started before a concurrent transaction committed simply reconstructs the older version of any row it needs on the fly from undo, rather than reading the in-place, currently-being-modified row.

The practical consequence, stated plainly: in Oracle, readers never block writers, and writers never block readers. A long-running report can run against a table while other transactions freely insert, update, and delete rows in it — the report just keeps seeing its own consistent snapshot throughout, regardless of what commits in the meantime. The only contention that exists is writer-versus-writer on the same row, exactly as in most other databases.

This is a meaningfully different default from databases whose default isolation relies on shared read locks to guarantee a reader only ever sees committed data — in that model, a reader can be made to wait behind an in-progress writer holding a lock on the same rows, something that simply doesn't happen in Oracle's undo-based model. It's conceptually close to PostgreSQL's MVCC (covered on this app's PostgreSQL transactions page) in spirit — both give a query a consistent snapshot without blocking concurrent writers — but the mechanism differs in an important way: PostgreSQL keeps old row versions physically in the table itself (requiring VACUUM to reclaim them later), while Oracle reconstructs older versions on demand from a separate undo tablespace, which is automatically reused once no active read-consistent query still needs that particular piece of undo — there's no equivalent of VACUUM to run by hand.

One edge case worth knowing: a very long-running query against a table with heavy concurrent writes can occasionally fail with ORA-01555: snapshot too old, if the undo data it needed to reconstruct an old row version has already been overwritten. It's addressed by sizing the undo tablespace and its retention period generously enough for the longest queries a system actually runs — a tuning knob, not a design flaw in the model itself.

Sequences — Oracle's answer to auto-increment#

Oracle has historically had no AUTO_INCREMENT column the way MySQL does, or a SERIAL type the way PostgreSQL does. Instead, a sequence is its own independent schema object that generates unique numbers on demand — not tied to any single table or column, and shareable across several tables if a design genuinely calls for it:

CREATE SEQUENCE employees_seq
    START WITH 1000
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

.NEXTVAL advances the sequence and returns the new value; .CURRVAL returns whatever .NEXTVAL most recently returned in the current session (it raises an error if .NEXTVAL hasn't been called yet this session):

INSERT INTO employees (employee_id, first_name, last_name, email, department_id)
VALUES (employees_seq.NEXTVAL, 'Noor', 'Haddad', 'noor.haddad@example.com', 20);

SELECT employees_seq.CURRVAL FROM DUAL;   -- the value just used above

Sequences are deliberately not transactional and not guaranteed gap-free — a rolled-back INSERT still permanently consumes the sequence value it used, and a sequence with caching enabled (the default CACHE 20) can lose a batch of pre-allocated values if the instance restarts. None of that matters for a surrogate key whose only job is uniqueness, which is the overwhelming majority of real use cases — but it's worth knowing that "gaps in employee_id" are normal and expected, not a bug to chase down.

Populating a key with a trigger (pre-12c pattern)#

Before Oracle 12c, the standard way to auto-populate a primary key from a sequence on every insert was a BEFORE INSERT row-level trigger (triggers are covered in full on the views/triggers page later in this track):

CREATE OR REPLACE TRIGGER trg_employees_bi
BEFORE INSERT ON employees
FOR EACH ROW
WHEN (NEW.employee_id IS NULL)
BEGIN
    :NEW.employee_id := employees_seq.NEXTVAL;
END;
/

With this trigger in place, an INSERT that simply omits employee_id gets one assigned automatically:

INSERT INTO employees (first_name, last_name, email, department_id)
VALUES ('Noor', 'Haddad', 'noor.haddad@example.com', 20);

GENERATED ... AS IDENTITY (modern Oracle, 12c+)#

Since Oracle 12c, an identity column does the same job natively, with no separate sequence object or trigger to maintain by hand — Oracle creates and manages a sequence internally, invisibly:

CREATE TABLE employees (
    employee_id NUMBER(6) GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 1),
    first_name  VARCHAR2(30) NOT NULL,
    last_name   VARCHAR2(30) NOT NULL,
    -- ...
    CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

INSERT INTO employees (first_name, last_name) VALUES ('Noor', 'Haddad');

GENERATED BY DEFAULT AS IDENTITY lets an explicit value still be supplied on insert if genuinely needed (e.g., migrating existing rows with known IDs); GENERATED ALWAYS AS IDENTITY refuses any explicitly-supplied value and always generates its own. For any new Oracle table, GENERATED ... AS IDENTITY is the recommended approach — it's less code, less to get wrong, and behaves identically from the application's point of view to the older sequence-plus-trigger pattern, which mostly survives today only in schemas that predate Oracle 12c.

Common mistakes#

• Forgetting a transaction is implicitly open after the first DML statement in a session and closing the client without an explicit COMMIT — depending on the client, that can silently roll back work the user assumed was already saved.
• Expecting sequence values to be gap-free — a rollback, an instance restart with cached values in flight, or simply skipping a value never guarantees which numbers actually end up in the table, and that's fine for a surrogate key.
• Calling .CURRVAL before ever calling .NEXTVAL in the current session — it raises ORA-08002 because there's nothing to return yet.
• Assuming Oracle's read consistency means a query is guaranteed to reflect the very latest committed data — it reflects the data as of the query's (or transaction's) start, which is a stronger guarantee for internal consistency but does mean a long-running report can knowingly be "looking at the past" relative to commits that happened after it started.