8.2 Single-Object and Multi-Object Operations
Too slow with many emails → denormalize into a separate counter field, incremented on new mail and decremented on read.
Recap of what A and I promise when a client makes several writes:
- Atomicity — error halfway → abort, discard writes so far. An ALL-OR-NOTHING guarantee.
- Isolation — another transaction should see either ALL or NONE of a transaction's writes, but not a subset.
The motivating example — an unread-message counter:
SELECT COUNT(*) FROM emails WHERE recipient_id = 2 AND unread_flag = trueToo slow with many emails → denormalize into a separate counter field, incremented on new mail and decremented on read.
“If an incorrect counter in an email application seems too insignificant, think of a customer account balance instead of an unread counter and a payment transaction instead of an email.”
Txn 1 inserts the email, then the counter update fails with an error. Mailbox contents and counter are now permanently out of sync. With atomicity, the email insertion is rolled back.
How transactions are delimited: in relational databases, typically by the client's TCP connection — everything between BEGIN TRANSACTION and COMMIT on a connection. If the TCP connection is interrupted, the transaction must be aborted.
Many nonrelational databases don't have such a grouping mechanism. Even a multi-object API (a multi-put updating several keys) DOESN'T NECESSARILY MEAN TRANSACTION SEMANTICS: the command may succeed for some keys and fail for others, leaving the database PARTIALLY UPDATED.
2.1 Single-object writes
Three questions that make the need obvious, for writing a 20 kB JSON document:
- Network interrupted after 10 kB — does the database store that unparseable fragment?
- Power fails mid-overwrite — do you get old and new values SPLICED TOGETHER?
- Another client reads during the write — does it see a partially updated value?
Each outcome would be incredibly confusing, so storage engines ALMOST UNIVERSALLY provide atomicity and isolation at the level of a SINGLE OBJECT on ONE NODE. Atomicity via a log for crash recovery; isolation via a lock on each object.
Two richer single-object primitives:
- Atomic increment — removes the read-modify-write cycle
- Conditional write — a write happens only if the value has not been concurrently changed; the database equivalent of compare-and-set (CAS)
(Pedantic but useful: "atomic increment" uses "atomic" in the multithreading sense. In ACID terms it should be called an ISOLATED or SERIALIZABLE increment.)
These are NOT transactions in the usual sense. Aerospike's "strong consistency" mode and Cassandra/ScyllaDB's "lightweight transactions" offer linearizable reads and conditional writes on a SINGLE object, but NO guarantees across multiple objects.
2.2 Why you need multi-object transactions
| Data model | Why |
|---|---|
| Relational | A row has a foreign-key reference to a row in another table; in graphs, a vertex has edges. Multi-object transactions ensure these references remain valid — when inserting several records that refer to one another, the foreign keys have to be correct and up to date, or the data becomes nonsensical |
| Document | Fields updated together are often within the same document — no multi-object transaction needed. BUT document databases lacking joins ENCOURAGE DENORMALIZATION, and when denormalized information needs updating you must update several documents in one go |
| Anything with secondary indexes (almost everything but pure key-value) | The indexes must be updated on every value change. Indexes are DIFFERENT DATABASE OBJECTS from a transaction point of view — without isolation, a record can appear in one index but not another because the second index update hasn't happened yet |
2.3 Handling errors and aborts
ACID databases are based on the philosophy that if the database is in danger of violating atomicity, isolation, or durability, IT WOULD RATHER ABANDON THE TRANSACTION ENTIRELY than allow it to remain half-finished.
Datastores with leaderless replication work on more of a "BEST EFFORT" basis: "the database will do as much as it can, and if it runs into an error, IT WON'T UNDO SOMETHING IT HAS ALREADY DONE" — so it's the application's responsibility to recover.
The retry indictment:
Popular ORM frameworks such as Rails ActiveRecord and Django DON'T RETRY aborted transactions — the error usually results in an exception bubbling up the stack, so any user input is thrown away and the user gets an error message. THIS IS A SHAME, BECAUSE THE WHOLE POINT OF ROLLING BACK TRANSACTIONS IS TO ENABLE SAFE RETRIES.
But retrying isn't perfect — five caveats:
- The transaction actually succeeded but the acknowledgment was lost → retrying performs it twice unless you have application-level deduplication
- If the error is due to overload or high contention, retrying MAKES IT WORSE. Limit retries, use exponential backoff, and handle overload errors differently from other errors (Ch 2)
- Retry only after TRANSIENT errors (deadlock, isolation violation, temporary network interruption, failover). After a PERMANENT error (constraint violation) a retry is pointless
- Side effects outside the database may happen even if the transaction is aborted — e.g. you wouldn't want to send the email again on every retry. (2PC can help, §6)
- If the client process crashes while retrying, any data it was writing is lost