ACID Transactions in Practice: What Your Database Actually Guarantees

The same isolation-level name gives different guarantees on different engines. REPEATABLE READ on PostgreSQL rejected a lost update that the same level on MySQL let through. SERIALIZABLE on PostgreSQL stopped a write-skew anomaly that its REPEATABLE READ allowed. A failed statement aborts the whole transaction on PostgreSQL, but on MySQL and SQL Server it rolls back only that statement unless the application acts. Correctness comes from four things working together: isolation level, query shape, declared constraints and retry logic. Teams should write down which anomalies each critical transaction must be protected from, and then choose the mechanism engine by engine.

ACID in production terms

ACID transactions (production sense): A transaction is a unit of work the database applies entirely or not at all (atomicity), that must leave every declared constraint satisfied (consistency), whose interaction with concurrent transactions is governed by an isolation level (isolation), and whose committed result survives a crash under the engine's configured flush policy (durability). Haerder and Reuter introduced the acronym in 1983. Each letter has engine-specific defaults and settings that can weaken it.

The PostgreSQL and MySQL examples below were executed on PostgreSQL 17.11 and MySQL 8.4.9 LTS (InnoDB), both with default configuration. The SQL Server, Oracle and Databricks examples are based on vendor documentation and were not executed. Every code block is labelled accordingly. The examples share two small tables:

-- PostgreSQL 17 — executed (17.11). MySQL used equivalent types (DECIMAL, VARCHAR, BOOLEAN; ENGINE=InnoDB).
CREATE TABLE accounts (acctnum int PRIMARY KEY, balance numeric(12,2) NOT NULL,
    CONSTRAINT balance_nonnegative CHECK (balance >= 0));
INSERT INTO accounts VALUES (12345, 50.00), (7534, 500.00);
CREATE TABLE doctors (name text PRIMARY KEY, shift_id int NOT NULL, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice',1234,true),('bob',1234,true);

Atomicity: all-or-nothing, but the error path is yours

Atomicity promises that a committed transaction's changes apply together and an aborted one leaves no trace. What the engine does between a failed statement and the end of the transaction differs.

Take a transfer of 100.00 from account 12345, which holds 50.00, to account 7534. The credit succeeds; the debit violates the CHECK constraint.

-- PostgreSQL 17 — executed (17.11)
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 7534;   -- succeeds
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 12345;  -- ERROR, SQLSTATE 23514
SELECT 1;  -- ERROR 25P02: current transaction is aborted, commands ignored until end of transaction block
COMMIT;    -- server reports ROLLBACK
-- balances: 7534 = 500.00, 12345 = 50.00 (unchanged)

PostgreSQL marks the whole transaction as failed. Further statements are refused, and even an explicit COMMIT ends in a rollback.

-- MySQL 8.4 InnoDB — executed (8.4.9)
START TRANSACTION;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 7534;   -- succeeds
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 12345;  -- ERROR 3819: Check constraint 'balance_nonnegative' is violated.
COMMIT;
-- balances: 7534 = 600.00, 12345 = 50.00 (the credit persisted)

InnoDB documents that most errors roll back only the failing statement, while a deadlock rolls back the whole transaction. The transaction stays open, and a COMMIT keeps the successful credit, so 100.00 appears from nowhere. The same sequence ending in an explicit ROLLBACK restored 500.00 and 50.00. Atomicity held at the transaction level in both engines; whether the business operation was atomic depended on the application's error handling.

SQL Server's default also rolls back only the statement that raised a run-time error. With SET XACT_ABORT ON, a run-time error rolls back the whole transaction:

-- SQL Server / Azure SQL (T-SQL) — based on vendor documentation; not executed
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 7534;
    UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 12345;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Partial rollback is sometimes what you want. Savepoints make it explicit rather than accidental:

-- PostgreSQL 17 — executed (17.11)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE acctnum = 7534;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 10 WHERE acctnum = 99999;  -- wrong account: 0 rows
ROLLBACK TO SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 10 WHERE acctnum = 12345;
COMMIT;
-- balances: 7534 = 490.00, 12345 = 60.00

The practical rule: on any error inside a multi-statement transaction, roll back explicitly unless you have deliberately designed a partial outcome with a savepoint.

Consistency: the engine enforces what you declare

The "C" is the property teams most often over-read. The engine guarantees that each committed transaction satisfies the constraints that are declared: keys, foreign keys, NOT NULL, CHECK and triggers. That is why the CHECK above stopped a negative balance.

It does not know the invariants you have not declared. "At least one doctor on call per shift", "total exposure below the limit", "no overlapping bookings" — rules that span several rows usually cannot be written as a single-row constraint. Whether they hold under concurrency depends on isolation and locking, which is where the remaining surprises sit.

Isolation: the standard's names hide the anomalies that matter

The SQL standard defines its isolation levels by which phenomena they forbid: dirty reads, non-repeatable reads and phantoms. PostgreSQL's documentation adds a fourth row, the serialisation anomaly.

Standard isolation levels as summarised in the PostgreSQL documentation.

In 1995 Berenson and colleagues showed this ladder is incomplete. Their critique adds the anomalies that cause most production incidents:

• P0 dirty write — overwriting another transaction's uncommitted write; they argue every level should exclude it.
• P4 lost update — a transaction reads a value, another transaction updates it, and the first writes back a value computed from its stale read.
• A5A read skew and A5B write skew — anomalies that break a constraint spanning two items.

They also defined snapshot isolation (SI): each transaction reads from a snapshot taken at its start, and the first committer wins when two transactions update the same row. SI prevents lost updates on the same row, but it allows write skew and is not serialisable. SI and REPEATABLE READ are not the same level either; each permits anomalies the other forbids.

Same name, different guarantees

Engines map the standard names onto their own concurrency control, so a level name tells you little until you know the engine.

Two cells deserve a closer look. MySQL's REPEATABLE READ snapshot applies to SELECT statements, "not necessarily to DML statements":

-- MySQL 8.4 InnoDB — executed (8.4.9), session A at REPEATABLE READ
START TRANSACTION;
SELECT COUNT(*) FROM doctors WHERE shift_id=1234;                -- 2
-- session B (autocommit): INSERT INTO doctors VALUES ('carol',1234,TRUE)
SELECT COUNT(*) FROM doctors WHERE shift_id=1234;                -- still 2 (snapshot)
UPDATE doctors SET on_call=FALSE WHERE shift_id=1234;            -- 3 rows affected
SELECT COUNT(*) FROM doctors WHERE shift_id=1234;                -- now 3
ROLLBACK;

The UPDATE touched a row the transaction's own SELECT could not see. On Oracle, the snapshot comes from the transaction only at SERIALIZABLE. At the default READ COMMITTED, every query "sees only data committed before the query—not the transaction—began".

Lost update: the read-modify-write trap

The most common concurrency bug in application code reads a value, computes a new one, and writes it back. Two sessions each add 100.00 to a balance of 500.00:

-- PostgreSQL 17 and MySQL 8.4 InnoDB — executed (17.11, 8.4.9); both sessions at the same level
-- A and B each read (MySQL: after START TRANSACTION):
SELECT balance FROM accounts WHERE acctnum=7534;   -- A: 500.00, B: 500.00
-- A writes its computed value (500.00 + 100, sent as a bound parameter) and commits:
UPDATE accounts SET balance=600.00 WHERE acctnum=7534;
COMMIT;
-- B then writes its own computed value and commits:
UPDATE accounts SET balance=600.00 WHERE acctnum=7534;
COMMIT;

Engines that implement first-committer-wins, such as PostgreSQL at REPEATABLE READ, turn the lost update into an error the application can retry. In our test on MySQL 8.4.9 defaults, REPEATABLE READ did not, because InnoDB's UPDATE acts on the latest committed row rather than the snapshot.

The simplest fix is to avoid the stale read altogether. An atomic increment lets the row lock serialise the writers:

-- PostgreSQL 17 (READ COMMITTED) and MySQL 8.4 InnoDB (REPEATABLE READ) — executed, both defaults
UPDATE accounts SET balance = balance + 100 WHERE acctnum=7534;   -- in both sessions; B waits for A's row lock
-- final balance: 700.00 on both engines

Where the new value genuinely needs application logic, read with SELECT … FOR UPDATE first, or use a version column and a conditional UPDATE.

Write skew: what snapshot isolation allows

Write skew is the anomaly that separates snapshot isolation from serialisability. A hospital needs at least one doctor on call for shift 1234. Alice and Bob are both on call, and each asks to go off call at the same moment. Each transaction checks the rule, sees two doctors, and updates only its own row:

-- PostgreSQL 17 — executed (17.11). Session A and session B, each:
BEGIN ISOLATION LEVEL REPEATABLE READ;            -- second run: SERIALIZABLE
SELECT count(*) FROM doctors WHERE on_call AND shift_id = 1234;   -- A: 2, B: 2
-- A:
UPDATE doctors SET on_call=false WHERE name='alice' AND shift_id=1234;
-- B:
UPDATE doctors SET on_call=false WHERE name='bob' AND shift_id=1234;
-- A: COMMIT;  then B: COMMIT;

At REPEATABLE READ both transactions committed and nobody was on call. The rows were disjoint, so first-committer-wins never fired. At SERIALIZABLE, B's COMMIT failed with SQLSTATE 40001, "could not serialize access due to read/write dependencies among transactions", and one doctor stayed on call. PostgreSQL's SERIALIZABLE is serialisable snapshot isolation, described by Ports and Grittner: it tracks read/write dependencies without blocking and aborts a transaction when a dangerous pattern appears.

MySQL behaved differently for the same business rule:

-- MySQL 8.4 InnoDB — executed (8.4.9). Session A and session B, each:
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- first run: REPEATABLE READ
START TRANSACTION;
SELECT COUNT(*) FROM doctors WHERE on_call AND shift_id = 1234;   -- A: 2, B: 2
-- A, then B shortly after (B uses name='bob'):
UPDATE doctors SET on_call=FALSE WHERE name='alice' AND shift_id=1234;
COMMIT;

At REPEATABLE READ both committed, leaving nobody on call. At SERIALIZABLE the plain SELECTs took shared locks. The two UPDATEs then blocked each other, InnoDB rolled B back with error 1213 ("Deadlock found when trying to get lock; try restarting transaction"), and A committed. One doctor stayed on call. The invariant held, but through deadlock detection rather than a serialisation check, so the application needs the same retry path either way.

On SQL Server, write skew on disjoint rows under SNAPSHOT is expected per the documented snapshot semantics; we did not test it. On Oracle, SERIALIZABLE is snapshot-based, and per Fekete et al. 2005 it can permit write skew. Both engines document locking alternatives:

-- SQL Server / Azure SQL (T-SQL) — based on vendor documentation; not executed
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Protect the invariant: run at SERIALIZABLE, or lock the rows read for the check
SELECT COUNT(*) FROM doctors WITH (UPDLOCK, HOLDLOCK) WHERE on_call = 1 AND shift_id = 1234;

-- Oracle — based on vendor documentation; not executed
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- must be the first statement in the transaction
SELECT name FROM doctors WHERE on_call = 1 AND shift_id = 1234 FOR UPDATE;

The locking-read fix was executed on both open-source engines:

-- PostgreSQL 17 — executed (17.11), in both sessions before the UPDATE
SELECT name FROM doctors WHERE on_call AND shift_id = 1234 FOR UPDATE;
-- MySQL 8.4 InnoDB — executed (8.4.9), REPEATABLE READ
SELECT name FROM doctors WHERE on_call AND shift_id=1234 FOR UPDATE;

B blocked until A committed. On PostgreSQL READ COMMITTED and on MySQL REPEATABLE READ it then saw one doctor on call and made no change. On PostgreSQL REPEATABLE READ it failed with 40001 ("concurrent update"). One doctor stayed on call in every case. PostgreSQL's documentation notes that SELECT FOR UPDATE locks just the returned rows, so a rule that depends on rows not yet inserted needs a parent row to lock, a declared constraint, or SERIALIZABLE.

Choosing a protection

Whatever the level, serialisation and deadlock errors are part of normal operation, not exceptional failures. PostgreSQL's documentation says applications at REPEATABLE READ and SERIALIZABLE must be prepared to retry. One caveat for read replicas: PostgreSQL states that SERIALIZABLE protection does not yet extend to hot standby or logical replicas.

Durability: what "committed" means when the power fails

Durability means a committed transaction survives a crash, and every engine's default honours that by flushing the log before acknowledging COMMIT. Every engine also offers a setting that relaxes it.

PostgreSQL lets you scope the relaxation to a single transaction, which is the safest way to use it:

-- PostgreSQL 17 — executed (17.11)
BEGIN;
SET LOCAL synchronous_commit = off;
SHOW synchronous_commit;                                   -- off
UPDATE accounts SET balance=balance WHERE acctnum=7534;
COMMIT;
SHOW synchronous_commit;                                   -- on (the setting ended with the transaction)

-- SQL Server / Azure SQL (T-SQL) — based on vendor documentation; not executed
ALTER DATABASE CURRENT SET DELAYED_DURABILITY = ALLOWED;
BEGIN TRANSACTION;
-- ... event-log writes ...
COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);
EXEC sys.sp_flush_log;   -- force pending delayed-durable commits to disk

-- Oracle — based on vendor documentation; not executed
COMMIT WRITE BATCH NOWAIT;

The distinction that matters is between losing recent commits and corrupting the database. Asynchronous commit in PostgreSQL loses at most a short window of recent commits. The documentation is explicit that "a bank would certainly not use asynchronous commit for a transaction recording an ATM's dispensing of cash", while event logging may not need that guarantee. Turning fsync off is a different category: the documentation warns of "unrecoverable data corruption". MySQL's documentation calls innodb_flush_log_at_trx_commit = 1 "required for full ACID compliance". Oracle warns that with NOWAIT a crash can "falsely indicate to a transaction that its changes are persistent".

Relaxed durability is a per-workload business decision about acceptable loss. It should never be a global default for financial or regulated data, and it should be visible in configuration review rather than discovered after an incident.

ACID on the lakehouse: table-level, optimistic, storage-dependent

Open table formats bring ACID semantics to files on object storage, with different mechanics. They build on the storage practices covered in Modern Data Platform Architecture.

• Delta Lake uses optimistic concurrency control. Writers proceed without locks and check for conflicts at commit, raising exceptions such as ConcurrentAppendException when they collide. Its ACID guarantees are "predicated on the atomicity and durability guarantees of the storage system". On Amazon S3, the default single-cluster mode supports concurrent writes only from a single Spark driver; the documentation warns that concurrent writes from multiple drivers can lead to data loss, and the multi-cluster mode is experimental and needs extra configuration.
• Databricks sets Delta tables to WriteSerializable by default: weaker than Serializable, but "still stronger than Snapshot isolation". The level is a table property:

-- Databricks SQL — based on vendor documentation; not executed
ALTER TABLE main.sales.orders SET TBLPROPERTIES ('delta.isolationLevel' = 'Serializable');

• Apache Iceberg commits by atomically swapping table metadata, giving "a linear history of atomic table updates", with optimistic concurrency and retries when commits conflict.

These guarantees are documented per table. A pipeline that publishes several tables together needs its own design for what readers see between those commits, and the storage and writer topology are part of the durability story, not a deployment detail.

Checklist: making transaction guarantees explicit

1. List critical transactions. Payments, inventory, entitlements, scheduling: anything where a concurrency bug costs money or trust.
2. Name the anomalies each must not suffer. Lost update, write skew, read skew, phantom; not just a level name.
3. Record the engine and the level actually in force. Check database defaults, session settings and driver or ORM overrides; SQL Server's READ_COMMITTED_SNAPSHOT differs between SQL Server and Azure SQL Database.
4. Choose the mechanism per engine. Atomic update, locking read, constraint or SERIALIZABLE, using the behaviour in Figure 1 rather than the level name.
5. Standardise error handling. Roll back explicitly on any error; use XACT_ABORT on SQL Server; reserve partial outcomes for savepoints.
6. Build retries in from the start. Treat 40001, 1213, Msg 3960 and ORA-08177 as retryable, with bounded attempts and idempotent side effects.
7. Test concurrency, not just logic. Two-session tests like the ones above belong in the suite for each critical path.
8. Audit durability settings. Alert on synchronous_commit, fsync, innodb_flush_log_at_trx_commit, DELAYED_DURABILITY and COMMIT WRITE overrides that differ from policy.
9. Check lakehouse writer topology. Confirm the storage guarantees and the number of concurrent writers each Delta or Iceberg table supports.

What this is not

• Not a benchmark. It makes no claims about how isolation levels or engines compare under load.
• Not a ranking of databases. Each engine's behaviour is a documented design choice; the risk lies in assuming one engine's semantics on another.
• Not distributed systems theory. Consensus, CAP and multi-region replication are out of scope.
• Not a guide to every version. Executed results are from PostgreSQL 17.11 and MySQL 8.4.9 defaults; verify behaviour on the versions and settings you run.

Operational implications

Transaction semantics belong in the same design reviews as schemas and data contracts. They are part of Modern Data Engineering work rather than a database administrator's footnote. In practice that means an isolation and durability record per critical service, concurrency tests in CI, and retry handling owned by the application team.

Settings drift is the run-time risk. A durability override applied during a migration, or an ORM that silently changes the session level, can weaken a guarantee without any code change. Continuous checks on these settings fit naturally into Managed Data, AI & Cloud Operations. The engines discussed here are among the technologies we work with.

Frequently asked questions

Does MySQL REPEATABLE READ prevent lost updates?

Not for application read-modify-write patterns in our test on MySQL 8.4.9 defaults: both transactions committed and one increment was lost. InnoDB's UPDATE acts on the latest committed row, not the transaction's snapshot. Use an atomic UPDATE or SELECT … FOR UPDATE.

Is snapshot isolation the same as SERIALIZABLE?

No. Snapshot isolation prevents dirty reads, non-repeatable reads and lost updates on the same row, but it allows write skew. PostgreSQL's SERIALIZABLE adds dependency tracking to prevent it; Oracle's SERIALIZABLE is snapshot-based.

What does SERIALIZABLE ask of an application?

A retry path. PostgreSQL aborts conflicting transactions with SQLSTATE 40001; lock-based implementations block and can end in deadlocks, such as MySQL error 1213. Transactions must be safe to re-run, with side effects that are idempotent or deferred until commit.

Can relaxed durability settings corrupt a database?

It depends on the setting. PostgreSQL's synchronous_commit = off can lose recent commits but does not corrupt data. fsync = off can cause unrecoverable corruption. The relaxed InnoDB, SQL Server and Oracle options are documented in terms of losing recent commits.

References

Vendor documentation reflects the pages as of 8 October 2026.

1. Principles of Transaction-Oriented Database Recovery — Haerder & Reuter, ACM Computing Surveys, 1983
2. A Critique of ANSI SQL Isolation Levels — Berenson et al., SIGMOD 1995
3. Making Snapshot Isolation Serializable — Fekete et al., ACM TODS, 2005
4. Serializable Snapshot Isolation in PostgreSQL — Ports & Grittner, VLDB 2012
5. Transaction Isolation — PostgreSQL documentation
6. Data Consistency Checks at the Application Level — PostgreSQL documentation
7. Asynchronous Commit — PostgreSQL documentation
8. Write Ahead Log configuration — PostgreSQL documentation
9. Transaction Isolation Levels — MySQL 8.4 Reference Manual
10. Consistent Nonlocking Reads — MySQL 8.4 Reference Manual
11. InnoDB Error Handling — MySQL 8.4 Reference Manual
12. InnoDB Startup Options and System Variables — MySQL 8.4 Reference Manual
13. SET TRANSACTION ISOLATION LEVEL (Transact-SQL) — Microsoft Learn
14. Transaction locking and row versioning guide — Microsoft Learn
15. Control transaction durability — Microsoft Learn
16. Data Concurrency and Consistency — Oracle Database 23 Concepts
17. COMMIT — Oracle Database 23 SQL Language Reference
18. Concurrency control — Delta Lake documentation
19. Storage configuration — Delta Lake documentation
20. Isolation levels and write conflicts — Databricks documentation
21. Reliability — Apache Iceberg documentation