Four letters, four different owners
Three are machinery the engine runs for you. Consistency is the only one with your code in it.
The C in ACID
Bad: “The database keeps data correct”
It will happily commit a transfer to the wrong account.
Good: Constraints plus your code
CHECK (balance ≥ 0) stops every writer. Every other invariant is yours.
How “committed” survives a crash
- Append the change to a sequential log
- Flush the log with fsync before replying OK
- Write the scattered data pages later
- After a crash, replay the log
50×
more commits when one flush carries fifty
Suppose one flush takes 1 ms. A session flushing alone commits at most 1,000 times a second. Group commit shares each flush.
The server flushes your COMMIT, then dies before the OK reaches you. Did Asha’s ₹500 move?
The catch
It did, but you can’t tell. Retry with the same idempotency key; a duplicate means “already done”.
Skip the flush?
Bad: Skip it for payments
A crash forgets up to 600 ms of commits already called “paid”.
Good: Skip it per transaction
SET LOCAL synchronous_commit TO OFF on view counters only.
ACID ends at the edge of one database
The debit commits locally, but no transaction spans the bank’s API. Use an outbox, an idempotent credit and a refund if it fails.
Remember
The engine gives you A, I and D. Consistency is your constraints and your code. And ACID ends at one database.