Transactions, ACID, and What Consistency Actually Costs You

ACID is the kind of term that gets used in interviews and architecture discussions with more confidence than understanding. Engineers know it stands for Atomicity, Consistency, Isolation, and Durability. They know it is a good thing to have. They know that some databases are "ACID compliant" and others are not, or at least not by default. What fewer engineers can explain is what each property actually means at the storage level, what it costs to provide it, and where the guarantees break down even in systems that claim full ACID compliance.

That gap in understanding creates real problems in production. An application that assumes READ COMMITTED prevents phantom reads will have a subtle correctness bug that surfaces under load. A service that wraps every operation in a transaction "for safety" without understanding the locking implications will hit contention issues it cannot explain. A team that migrates from PostgreSQL to MongoDB and assumes transaction behaviour is equivalent will discover the differences at the worst possible time.

This article is about building the mental model that prevents those problems. We will go through each ACID property not as a definition but as a mechanism — what PostgreSQL and MongoDB actually do in their storage engines and concurrency control layers to provide (or approximate) each guarantee. Then we will go deep on isolation levels, which is where most of the practical complexity lives, with concrete examples of what each level permits and what it prevents.

Atomicity: All or Nothing at the Storage Level

Atomicity means that a transaction either completes entirely or has no effect at all. If you update three rows in a transaction and the system crashes after the second update, the first two updates must be rolled back. The database must look as though none of the updates happened.

This sounds simple. The mechanism that implements it is not.

PostgreSQL: Atomicity Through MVCC and WAL

In PostgreSQL, atomicity is implemented through the combination of MVCC tuple visibility and WAL.

When a transaction begins, PostgreSQL assigns it a transaction ID (xid). Every row written by the transaction has its xmin set to this xid. Every row deleted or superseded by the transaction has its xmax set to this xid. As long as the transaction has not committed, its xid is recorded in the pg_clog (commit log, now called pg_xact) as IN_PROGRESS.

From the perspective of any other transaction, rows written by an in-progress transaction are invisible — because the xmin xid is not yet committed. This is MVCC providing atomicity: partial writes are invisible to concurrent readers.

When the transaction commits, PostgreSQL writes a commit record to the WAL and marks the xid as COMMITTED in pg_xact. At that moment, all rows written by the transaction become visible simultaneously to subsequent transactions. There is no window where some rows are visible and others are not.

When the transaction aborts — either explicitly via ROLLBACK, or due to an error, or due to a crash — PostgreSQL marks the xid as ABORTED in pg_xact. The rows written by the transaction remain physically in the heap (PostgreSQL never deletes them immediately) but are permanently invisible because their xmin xid is marked aborted. VACUUM eventually reclaims them.

The crash case is handled by WAL replay. If the system crashes mid-transaction, the WAL does not contain a commit record for that transaction's xid. On recovery, PostgreSQL replays the WAL but without a commit record, the transaction's xid is effectively treated as aborted. No explicit rollback is needed — the absence of a commit record is sufficient.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- xmin = xid_42
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- xmin = xid_42
-- Crash here. On recovery: xid_42 has no commit record.
-- Both rows written by xid_42 are invisible. Atomicity preserved.
COMMIT;  -- never reached

PostgreSQL: Savepoints and Subtransactions

PostgreSQL supports savepoints within a transaction, allowing partial rollbacks:

BEGIN;
INSERT INTO orders (user_id, total) VALUES (42, 150.00);
SAVEPOINT before_payment;
INSERT INTO payments (order_id, amount) VALUES (currval('orders_id_seq'), 150.00);
-- Payment processor fails
ROLLBACK TO SAVEPOINT before_payment;
-- Order row still exists; payment row rolled back
INSERT INTO payments (order_id, amount, method) VALUES (..., 150.00, 'fallback');
COMMIT;

Savepoints are implemented as subtransactions. Each savepoint allocates a new subtransaction ID. Rows written after the savepoint have the subtransaction ID as their xmin. Rolling back to a savepoint marks the subtransaction ID as aborted, making those rows invisible, while rows written before the savepoint (under the parent transaction's xid) remain visible.

Savepoints have a performance cost that is not obvious: each savepoint allocation touches pg_subtrans, a shared memory structure that tracks subtransaction relationships. Heavy use of savepoints in a tight loop — a pattern that some ORMs use internally to wrap each operation in a savepoint for automatic retry — can create contention on pg_subtrans under concurrent load. This is one of the less-known sources of performance degradation in Django and Rails applications under PostgreSQL at scale.

MongoDB: Atomicity at the Document Level and Beyond

MongoDB's atomicity story has two distinct layers that are often conflated.

Document-level atomicity has always existed in MongoDB. A single write operation — insert, update, delete, or replace — on a single document is always atomic. Either the entire document modification is applied or none of it is. This is true even for complex update operators like $push, $inc, and $set applied simultaneously on the same document:

// This is always atomic — all three field updates or none
db.accounts.updateOne(
  { _id: ObjectId("...") },
  {
    $inc: { balance: -500, transactionCount: 1 },
    $push: { history: { type: "debit", amount: 500, ts: new Date() } },
    $set: { lastModified: new Date() }
  }
)

WiredTiger ensures this through its own internal transaction mechanism — the update to the document is applied as a single WiredTiger transaction, invisible to other operations until it commits.

Multi-document transactions were added in MongoDB 4.0 (for replica sets) and 4.2 (for sharded clusters). They provide atomicity across multiple documents and collections:

const session = client.startSession();
session.startTransaction();
try {
  await db.accounts.updateOne(
    { _id: senderId },
    { $inc: { balance: -500 } },
    { session }
  );
  await db.accounts.updateOne(
    { _id: receiverId },
    { $inc: { balance: 500 } },
    { session }
  );
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
}

Multi-document transactions in MongoDB use WiredTiger's MVCC mechanism at the storage level — uncommitted changes are invisible to other operations, and aborted transactions leave no trace. The implementation is conceptually equivalent to PostgreSQL's approach, but with important practical differences in how the session and transaction state are managed above the storage layer.

The critical operational difference: MongoDB multi-document transactions have a hard 60-second time limit (configurable via transactionLifetimeLimitSeconds). A transaction that runs longer than this limit is automatically aborted. PostgreSQL has no equivalent hard limit on transaction duration — a transaction can run for hours (though this creates other problems, as we discussed with VACUUM in Article 1). For long-running operations that need atomicity, PostgreSQL's model is more flexible; MongoDB requires the operation to complete within the time limit.

Consistency: The Property That Isn't Really a Database Property

Consistency in ACID is the most misunderstood letter in the acronym. It is also, arguably, the one that the database does the least to provide.

The ACID definition of consistency is: a transaction takes the database from one valid state to another valid state, where "valid" means all defined constraints are satisfied. Foreign keys must be intact. NOT NULL constraints must hold. Unique constraints must not be violated. Check constraints must pass.

This sounds like the database is doing a lot of work. But notice what the definition does not say: it does not define what "valid" means for your business logic. The database enforces the constraints you define. It cannot enforce constraints you did not define, and it cannot enforce business rules that exist only in application code.

-- The database will enforce these
ALTER TABLE orders ADD CONSTRAINT chk_positive_total CHECK (total > 0);
ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders(id);

-- The database cannot enforce these — application's responsibility
-- "An order cannot be placed if the user's account is suspended"
-- "An order total must equal the sum of its line items"
-- "A user cannot have more than 5 pending orders"

The practical implication: Consistency in ACID gives you schema-level integrity guarantees. Application-level consistency — the invariants your business logic depends on — requires careful transaction design and often explicit locking.

Constraint Enforcement in PostgreSQL

PostgreSQL enforces constraints at the tuple level during DML, with options for deferring enforcement to commit time:

-- Immediate constraint: checked on every row write
ALTER TABLE order_items
ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id)
DEFERRABLE INITIALLY IMMEDIATE;

-- Deferred constraint: checked only at COMMIT
ALTER TABLE order_items
ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id)
DEFERRABLE INITIALLY DEFERRED;

-- Or defer per-transaction:
BEGIN;
SET CONSTRAINTS fk_order DEFERRED;
-- Now you can insert order_items before the parent order exists
-- as long as the parent exists by COMMIT
INSERT INTO order_items ...;
INSERT INTO orders ...;
COMMIT;

Deferrable constraints are essential for certain data loading patterns — circular foreign keys, bulk imports where parent and child rows arrive interleaved, or any case where the valid final state cannot be reached through a sequence of individually valid intermediate states.

Consistency in MongoDB

MongoDB's consistency model is schema-flexible by design. There are no foreign key constraints, no NOT NULL enforcement by default, and no check constraints at the storage level. JSON Schema validation (added in MongoDB 3.6) provides some constraint enforcement:

db.createCollection("orders", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["userId", "total", "status"],
      properties: {
        total: { bsonType: "decimal", minimum: 0 },
        status: { enum: ["pending", "confirmed", "shipped", "completed"] }
      }
    }
  },
  validationAction: "error"  // reject invalid documents
})

But there are no cross-collection constraints. Referential integrity between collections is the application's responsibility entirely. This is a deliberate trade-off — schema flexibility enables the document model's power, but it places more responsibility on the application layer to maintain consistency.

Isolation: Where the Real Complexity Lives

Isolation is the ACID property with the most nuance and the most practical impact on application behaviour. It defines how concurrent transactions see each other's in-progress and committed changes.

The SQL standard defines four isolation levels and three anomalies that the levels are designed to prevent:

This table is the standard definition. The reality in PostgreSQL is more nuanced — and more protective — than the standard requires.

PostgreSQL Isolation: Stronger Than the Standard

PostgreSQL does not implement READ UNCOMMITTED. Even if you set the isolation level to READ UNCOMMITTED, PostgreSQL silently upgrades it to READ COMMITTED. There are no dirty reads in PostgreSQL, ever.

READ COMMITTED (PostgreSQL default): Each statement within the transaction sees a fresh snapshot of committed data as of the moment that statement begins. This means within a single transaction, two identical SELECT statements can return different results if another transaction commits between them.

-- Transaction A (READ COMMITTED):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- Transaction B commits: UPDATE accounts SET balance = 500 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 500 (different!)
COMMIT;

This is the non-repeatable read anomaly. In READ COMMITTED, it is permitted and expected. For most OLTP workloads this is fine — each statement is short and the fresh snapshot is usually what you want. But for transactions that make decisions based on values read earlier in the same transaction, it is a correctness concern.

-- Dangerous pattern in READ COMMITTED:
BEGIN;
SELECT balance INTO v_balance FROM accounts WHERE id = 1;
-- Another transaction could change balance here
IF v_balance > 500 THEN
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- May go negative!
END IF;
COMMIT;

The fix for this pattern is SELECT ... FOR UPDATE, which acquires a row lock and prevents other transactions from modifying the row between your read and write:

BEGIN;
SELECT balance INTO v_balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now locked. Other transactions block on this row until we commit.
IF v_balance > 500 THEN
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
END IF;
COMMIT;

REPEATABLE READ (PostgreSQL): PostgreSQL's REPEATABLE READ is stronger than the SQL standard requires. The standard says REPEATABLE READ must prevent non-repeatable reads but may permit phantom reads. PostgreSQL's REPEATABLE READ prevents both — it uses a consistent snapshot taken at the start of the first statement and never refreshes it. No new committed data becomes visible to the transaction once it has begun.

-- Transaction A (REPEATABLE READ):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 42
-- Transaction B commits: INSERT INTO orders (status) VALUES ('pending');
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Still returns 42
COMMIT;
-- Phantom read prevented by PostgreSQL's snapshot isolation

The snapshot is taken at the first data-access statement, not at BEGIN. This matters: if you BEGIN and then wait 10 minutes before running your first SELECT, the snapshot reflects committed state as of the moment of that SELECT, not as of BEGIN.

SERIALIZABLE (PostgreSQL): PostgreSQL implements true serializable isolation through Serializable Snapshot Isolation (SSI), introduced in PostgreSQL 9.1. SSI detects serialization anomalies — cases where concurrent transactions produce a result that could not have occurred if the transactions had run one at a time in any serial order — and aborts one of the conflicting transactions.

SSI does not use predicate locks in the traditional sense. It tracks read-write dependencies between transactions and detects dangerous cycles in the dependency graph. When a cycle is detected, one transaction is aborted with ERROR: could not serialize access due to read/write dependencies among transactions.

-- Classic write skew anomaly — prevented by SERIALIZABLE:
-- Two doctors both see that one doctor is on call
-- Both decide to take the day off (each thinking the other remains)
-- Result: no doctor on call — serialization anomaly

-- Transaction A (SERIALIZABLE):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM on_call WHERE date = TODAY;  -- Sees 2 doctors
UPDATE on_call SET status = 'off' WHERE doctor_id = 1 AND date = TODAY;
COMMIT;  -- May fail with serialization error if Transaction B committed first

-- Transaction B (SERIALIZABLE):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM on_call WHERE date = TODAY;  -- Also sees 2 doctors
UPDATE on_call SET status = 'off' WHERE doctor_id = 2 AND date = TODAY;
COMMIT;  -- One of these will be aborted

SSI has a performance cost — tracking dependencies consumes memory and CPU. For workloads where serialization anomalies are theoretical rather than practical, REPEATABLE READ is usually sufficient and cheaper. For financial systems, booking systems, or any domain where write skew would be a correctness violation, SERIALIZABLE is the right choice.

Serialization failure handling in application code is non-negotiable when using SERIALIZABLE. Any transaction can fail with a serialization error at commit time, even if it had no errors during execution. Application code must detect this error and retry the entire transaction:

for retries := 0; retries < maxRetries; retries++ {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return err
    }
    err = doWork(tx)
    if err != nil {
        tx.Rollback()
        if isSerializationError(err) {
            continue  // retry
        }
        return err
    }
    err = tx.Commit()
    if err != nil {
        if isSerializationError(err) {
            continue  // retry
        }
        return err
    }
    return nil  // success
}

MongoDB Isolation: Session-Level Snapshots and Causal Consistency

MongoDB's isolation model is layered differently from PostgreSQL's, and understanding the layers is essential.

Single-document operations are always isolated — they are atomic and isolated at the WiredTiger transaction level. No other operation sees a partial state of a single-document write.

Multi-document transactions support configurable read concern and write concern:

session.startTransaction({
  readConcern: { level: "snapshot" },
  writeConcern: { w: "majority" }
});

Read concerns in MongoDB control what data is visible to a read operation:

• local (default): reads the most recent data on the node, regardless of whether it has been replicated to a majority of replica set members. May return data that is rolled back if the current primary fails before replication completes.
• majority: reads only data that has been acknowledged by a majority of replica set members. This data will not be rolled back. More expensive — requires waiting for majority acknowledgment before reads can proceed.
• snapshot: within a multi-document transaction, reads from a consistent snapshot of the data as of the transaction start time. This is the isolation level equivalent to PostgreSQL's REPEATABLE READ — once a transaction has taken a snapshot, no new committed data from other transactions is visible.
• linearizable: the strongest guarantee — reads reflect all writes that have been majority-committed before the read began, across all nodes. Only applies to single-document operations, not transactions. Has significant latency cost.

// Snapshot read within a transaction — consistent view across all reads
session.startTransaction({ readConcern: { level: "snapshot" } });

const account = await db.accounts.findOne({ _id: senderId }, { session });
// Another transaction commits a balance change here
const sameAccount = await db.accounts.findOne({ _id: senderId }, { session });
// Both reads return the same balance — snapshot isolation

Causal consistency is a MongoDB concept with no direct PostgreSQL equivalent. In a distributed replica set, reads may go to different nodes that are at different replication points. Causal consistency guarantees that if operation B happened after operation A in your application code, and you read with causal consistency, you will always see the effects of A before seeing (or starting) B — even if reads go to different replica set members.

// Causal consistency: guaranteed to see the effect of the write before the read
const session = client.startSession({ causalConsistency: true });
await db.users.updateOne({ _id: userId }, { $set: { verified: true } }, { session });
const user = await db.users.findOne({ _id: userId }, { session });
// user.verified is guaranteed to be true — causal ordering preserved

This matters for distributed reads that would otherwise suffer from replication lag anomalies — where you write to the primary and immediately read from a secondary that has not yet received the write.

The Isolation Level Most Engineers Get Wrong: READ COMMITTED Under Concurrency

READ COMMITTED is the PostgreSQL default and the most commonly used isolation level. It is also the source of the most subtle correctness bugs I have seen in production systems.

The bug pattern is always a variation of the same thing: the application reads a value, makes a decision based on that value, and then acts on the decision — but between the read and the action, another transaction has changed the value the decision was based on.

-- Inventory deduction: dangerous in READ COMMITTED
BEGIN;
SELECT quantity INTO v_qty FROM inventory WHERE product_id = 42;
-- v_qty = 1 (last unit in stock)

-- Another transaction: sells the last unit, quantity becomes 0

IF v_qty > 0 THEN
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
  -- quantity is now -1. Oversold.
END IF;
COMMIT;

The correct pattern uses SELECT ... FOR UPDATE to lock the row, or uses an atomic conditional update:

-- Atomic conditional update — no separate read needed
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0;

-- Check affected rows — if 0, the update did not happen (out of stock)
GET DIAGNOSTICS v_row_count = ROW_COUNT;
IF v_row_count = 0 THEN
  RAISE EXCEPTION 'Product out of stock';
END IF;

The atomic conditional update is the pattern I reach for by default. It avoids the read-then-write race condition entirely by expressing the condition and the action as a single atomic operation.

In MongoDB, the equivalent is findOneAndUpdate with a filter that includes the condition:

const result = await db.inventory.findOneAndUpdate(
  { productId: 42, quantity: { $gt: 0 } },  // condition included in filter
  { $inc: { quantity: -1 } },
  { returnDocument: "after", session }
);

if (!result) {
  throw new Error("Product out of stock");
}

Durability: What "Committed" Actually Means

Durability means that once a transaction commits, its changes survive any subsequent failure — crash, power loss, hardware failure. We covered the WAL and journal mechanisms in Article 1. Here we focus on the durability configurations that affect application behaviour.

PostgreSQL Durability Configurations

The key durability lever is synchronous_commit:

-- Check current setting
SHOW synchronous_commit;

-- Session-level override for a batch insert
SET synchronous_commit = off;
INSERT INTO events SELECT * FROM staging_events;
RESET synchronous_commit;

With synchronous_commit = on (default), the commit does not return to the application until the WAL record has been flushed to disk (fsync). This guarantees zero data loss on crash at the cost of the fsync latency (typically 1–5ms on good hardware).

With synchronous_commit = off, the commit returns to the application after the WAL record is written to the WAL buffer but before it is flushed to disk. PostgreSQL's WAL writer flushes the buffer every 200ms (wal_writer_delay). A crash in that window can lose up to 200ms of committed transactions. The upside is that commit latency drops dramatically — from milliseconds to microseconds — and write throughput can increase by 5–10× for commit-bound workloads.

The key point: synchronous_commit = off does not risk database corruption or partial writes. The database remains consistent — you may lose some recent commits, but you will never have a half-applied transaction. The WAL ensures atomicity and consistency regardless of durability settings.

For write-heavy workloads where losing the last 200ms of data is acceptable — event logging, metrics ingestion, analytics pipelines — synchronous_commit = off is a legitimate and safe optimisation. Apply it at the session level for specific write paths rather than globally.

PostgreSQL and Replication Durability

With streaming replication, synchronous_commit controls whether the commit waits for the standby to receive and flush the WAL:

-- Options for synchronous_commit with replication:
-- 'on'           → local WAL flush only
-- 'remote_write' → primary flushed + standby received but not flushed
-- 'remote_apply' → standby has applied (replayed) the WAL
-- 'local'        → same as 'on'

-- For zero data loss even on primary failure:
synchronous_commit = remote_apply

remote_apply is the highest durability setting — a committed transaction is guaranteed to survive even if the primary is lost immediately after commit, because the standby has already applied the change and can be promoted. The cost is higher commit latency — you are waiting for the network round-trip to the standby plus its WAL replay time.

MongoDB Durability and Write Concern

MongoDB's equivalent of synchronous_commit is write concern:

// w:1 (default) — acknowledged by primary in-memory (not journal)
// Risk: up to 100ms loss if primary crashes (journal not yet synced)
db.events.insertOne(doc, { writeConcern: { w: 1 } })

// w:1, j:true — acknowledged after journal sync on primary
// Zero data loss on primary crash. Slightly higher latency.
db.events.insertOne(doc, { writeConcern: { w: 1, j: true } })

// w:"majority" — acknowledged after majority of replica set members
// have written to their journals. Survives primary failure.
db.events.insertOne(doc, { writeConcern: { w: "majority", j: true } })

The MongoDB default write concern (w: 1) is weaker than PostgreSQL's default (synchronous_commit = on) because it acknowledges before the journal is synced. For financial data or any operation where loss is unacceptable, w: "majority", j: true is the MongoDB equivalent of PostgreSQL's default durability.

This default asymmetry is one of the most important things to understand when migrating between the two systems. An application designed around PostgreSQL's default durability guarantees needs explicit write concern upgrades when moving to MongoDB to maintain equivalent safety.

Practical Transaction Design Patterns

Understanding ACID at the mechanism level changes how you design transactions. Here are the patterns I apply consistently.

Keep Transactions Short

Long transactions are expensive in both systems, for different reasons.

In PostgreSQL: a long transaction holds back VACUUM (as covered in Article 1), potentially causing table bloat. It also holds any locks it has acquired, blocking concurrent writers. And it consumes a slot in the active transaction list, which is a bounded resource.

In MongoDB: multi-document transactions have a hard 60-second limit. Beyond that, the performance cost of tracking a long transaction's snapshot window increases over time.

The discipline: do all application logic (API calls, calculations, validations) outside the transaction. Open the transaction only for the database writes, do them as fast as possible, and commit. A transaction that opens a database connection, makes an external HTTP call, then writes to the database is a pattern that causes production incidents.

// Anti-pattern: external call inside transaction
tx.Begin()
result := callExternalPaymentAPI()  // could take 5 seconds, or timeout
tx.Insert(result)
tx.Commit()

// Correct: external call outside transaction
result := callExternalPaymentAPI()
tx.Begin()
tx.Insert(result)
tx.Commit()

Use Advisory Locks for Application-Level Mutex

PostgreSQL provides advisory locks — application-defined locks that the database manages but that do not correspond to any table or row. They are useful for coordinating distributed processes:

-- Try to acquire an advisory lock (non-blocking)
SELECT pg_try_advisory_lock(42);  -- returns true if acquired, false if not

-- Acquire an advisory lock (blocking)
SELECT pg_advisory_lock(42);

-- Release
SELECT pg_advisory_unlock(42);

-- Transaction-scoped advisory lock (auto-released on commit/rollback)
SELECT pg_advisory_xact_lock(42);

Advisory locks are a cleaner alternative to "select a row and lock it" patterns for distributed mutex scenarios — cron job deduplication, leader election, rate limiting.

Detect and Handle Deadlocks

Deadlocks occur when two transactions each hold a lock that the other needs. Both databases detect deadlocks and resolve them by aborting one transaction.

-- PostgreSQL deadlock example:
-- Transaction A: locks row 1, then tries to lock row 2
-- Transaction B: locks row 2, then tries to lock row 1
-- PostgreSQL detects the cycle and aborts one with:
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890

Deadlock resolution is automatic but the aborted transaction must be retried. Application code that uses explicit row locking (SELECT FOR UPDATE) must handle deadlock errors and retry, just like serialization errors.

The most reliable way to avoid deadlocks is to always acquire locks in a consistent order. If your application always locks account rows in ascending ID order, two transactions that need to lock accounts 1 and 2 will always acquire the locks in the same order and will never deadlock.

-- Consistent lock ordering prevents deadlocks
SELECT * FROM accounts
WHERE id IN (sender_id, receiver_id)
ORDER BY id  -- always lock in ascending id order
FOR UPDATE;

In MongoDB, deadlocks between multi-document transactions are handled similarly — WiredTiger detects the cycle and aborts one transaction with a WriteConflict error, which should trigger a retry.

Conclusion

ACID is not a binary property — it is a set of guarantees, each with a cost, each configurable, and each with nuances in implementation that affect application correctness and performance. The engineers who understand those nuances make better decisions:

• They know when READ COMMITTED is sufficient and when SELECT FOR UPDATE is required
• They know when synchronous_commit = off is a safe optimisation and when it is not
• They know that PostgreSQL's REPEATABLE READ prevents phantoms even though the SQL standard does not require it
• They know that MongoDB's default write concern is weaker than PostgreSQL's and configure accordingly
• They know that SERIALIZABLE requires retry logic in application code, always

These are not academic distinctions. Each one corresponds to a class of production bugs or performance problems that engineers without this knowledge cannot diagnose or prevent.

The final article in this series covers concurrency control — how MVCC works at the detail level for both systems, what lock types exist, when they conflict, and how to diagnose and resolve contention in production.