ACID Guarantees

C means a transaction moves the database from one valid state to another: every declared integrity rule (primary key, unique, not null, check, foreign key) holds at commit. The engine enforces the declared rules; the developer decides which rules to declare.

It promises the committed changes survive a crash or power loss of that node: the data reached stable storage, not just memory. It does not promise the data survives losing that machine or its disk, nor that any replica already has it.

Isolation means concurrent transactions must not see each other's unfinished work: the outcome should match some order where they ran one at a time. Enforcing that fully costs locking, blocking and aborts, so engines offer weaker levels that trade specific anomalies for throughput.

The engine durably records how to reverse each change before making it — before-images or inverse operations — and rollback replays those backwards. Multi-version engines instead leave the old row version in place and simply never make the new one visible.

Two sessions can both run the SELECT before either INSERT, so both see nothing and both insert. Only a UNIQUE constraint on the column makes the invariant unbreakable; the application then catches the unique-violation error instead of pre-checking.

Before acknowledging, the engine must get the transaction's record onto storage that survives power loss — an fsync that the device honours, not just a write into the OS page cache. That is a physical round trip to the device, and adds a network round trip too if a replica must confirm.

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. The standard defines each by which phenomena it may permit: dirty reads at the lowest level only, then non-repeatable reads, then phantoms, and SERIALIZABLE permits none. Levels are upper bounds, so engines may be stricter.

With asynchronous replication, the primary acknowledges a commit once it is durable locally; replicas receive it slightly later. If the primary dies before shipping those transactions and you promote a replica, they are lost. Preventing it means requiring at least one replica to acknowledge before the client is told committed.

The statement is atomic too: all 699 changes are reversed, so the statement leaves no partial effect. Whether the surrounding transaction survives differs by engine — some abort the whole transaction, others undo only the failed statement and let you continue.

By default most constraints are checked immediately, at the end of each statement, so intermediate rows inside one statement do not trip them. Constraints declared DEFERRABLE INITIALLY DEFERRED are instead checked once at COMMIT, which lets a transaction pass through temporarily invalid states.

Rollback is real work: the engine must reverse every change already made, including index maintenance, and the transaction's locks are only released when that finishes. Killing the session starts the rollback; it does not skip it. So keep write transactions bounded and chunk bulk changes.

Nothing — atomicity covers only changes inside that database. The HTTP call already happened and is not reversed. Teams keep the external effect out of the transaction: record the intent in a table in the same transaction, and let a separate worker perform the call after commit, idempotently.

Push every invariant the engine can express into the schema — it then holds for all writers with no race window. Invariants spanning many rows or tables need a serialized write path: a lock on a designated row, a serializable transaction, or a derived column kept correct by a trigger.

You gain much lower commit latency and far higher small-transaction throughput, because commits no longer wait on the storage flush. You risk losing the most recent commits — a bounded window, typically well under a second — if the machine loses power or the OS panics. The database still comes back structurally intact.

Two families: pessimistic two-phase locking with range/predicate locks, which serializes by blocking and produces deadlocks; and optimistic serializable snapshot isolation, which never blocks readers but aborts transactions whose read/write dependencies could not come from any serial order. Optimistic requires an application retry loop.

Snapshot isolation gives every transaction a consistent view of committed data as of its start, with writers checked for conflicting updates to the same rows. It avoids all three phenomena the standard lists, yet still allows non-serializable schedules, so passing the standard's checklist does not equal serializability.

Start from a documented default (usually READ COMMITTED), identify the few transactions whose correctness depends on stable reads, and raise only those. Prefer pushing invariants into constraints or atomic statements, keep transactions short, and monitor lock waits and serialization failures.

Choose a partition key that keeps related rows together so most invariants stay engine-enforced. For the ones that genuinely cross a boundary, name an owning service, enforce on write in that owner, and add continuous reconciliation plus a repair path — the invariant becomes eventual, so design for detecting and fixing violations.

Classify data by two questions: can the writer replay it, and did anyone outside act irreversibly on the acknowledgement? That yields an acceptable loss window per dataset, which maps to a commit level — relaxed local, node-durable, or replica/quorum-acknowledged — and the latency budget each costs. Then verify by testing real failover.