Skip to main content
Databases · lesson 2 of 6

Intermediate

2

Advanced

4

Also part of Databases

12
Open the Databases page →
Databases intermediate Lesson 2 of 6

Transactions Across Engines: Who Can Actually Roll Back

PostgreSQL and MongoDB both undo a partially-applied multi-step change when one step fails. Redis's MULTI/EXEC does not — a real queued transaction that keeps its successful command and drops only the failed one.

By SkillByExample · Updated

databases transactions postgresql mongodb redis rollback

Two of these three engines undo a partially-applied transaction when one step fails. The third one — despite calling the feature “transactions” too — does not, and the difference only shows up when something inside the transaction actually goes wrong.

PostgreSQL: a duplicate key aborts the whole transaction

psql -U postgres -c "SELECT * FROM accounts;"
 id | balance
----+---------
  1 |     100
  2 |      50
(2 rows)
psql -U postgres -c "BEGIN; UPDATE accounts SET balance = balance - 30 WHERE id = 1; INSERT INTO accounts VALUES (2, 999); ROLLBACK;"
BEGIN
UPDATE 1
ERROR:  duplicate key value violates unique constraint "accounts_pkey"
DETAIL:  Key (id)=(2) already exists.

The UPDATE succeeded — UPDATE 1 confirms one row changed. The INSERT then failed on a real primary-key collision (id=2 already exists). Checking the table afterward:

psql -U postgres -c "SELECT * FROM accounts;"
 id | balance
----+---------
  1 |     100
  2 |      50
(2 rows)

Still 100. The successful UPDATE was undone along with the failed INSERT, because Postgres marks the entire transaction as aborted the moment any statement inside it fails — “all or nothing” means the whole block, not each statement judged on its own.

MongoDB: a multi-document transaction, aborted the same way

const session = db.getMongo().startSession();
const coll = session.getDatabase("test").accounts;
session.startTransaction();
try {
    coll.updateOne({_id: 1}, {$inc: {balance: -30}});
    coll.insertOne({_id: 2, balance: 999}); // duplicate key, will fail
    session.commitTransaction();
} catch (e) {
    session.abortTransaction();
    print("aborted: " + e.message);
}
session.endSession();
printjson(db.getSiblingDB("test").accounts.find().toArray());
aborted: E11000 duplicate key error collection: test.accounts index: _id_ dup key: { _id: 2 }
[
  { _id: 1, balance: 100 },
  { _id: 2, balance: 50 }
]

Same result as Postgres: _id: 1’s balance is still 100, not 70 — the updateOne that ran first inside the transaction was undone along with the failed insertOne. MongoDB’s multi-document transactions (available since 4.0 on replica sets) give the same all-or-nothing guarantee as a relational database, just applied to documents instead of rows.

The bug: Redis’s MULTI/EXEC does not roll back

redis-cli SET counter 100
redis-cli SET name "Dune"
redis-cli
> MULTI
> DECRBY counter 30
> INCR name
> EXEC
OK
QUEUED
QUEUED
70
ERR value is not an integer or out of range

DECRBY counter 30 returned 70 — it actually ran and succeeded. INCR name failed, because "Dune" isn’t numeric. Checking both keys afterward:

redis-cli GET counter
redis-cli GET name
70
Dune

counter is permanently 70. The failed command didn’t roll anything back — MULTI/EXEC guarantees the queued commands run back-to-back with nothing else interleaved between them, but it does not guarantee that a failure in one command undoes another. This is documented Redis behavior, not a bug: catching a type mismatch before queuing is the client’s job, because EXEC treats each queued command as independent once it starts running them.

Practice

1. Reset the Redis keys and confirm queuing a genuinely malformed command (not just a type mismatch) behaves differently. 
redis-cli SET counter 100
redis-cli
> MULTI
> DECRBY counter 30
> NOTACOMMAND
> EXEC
OK
QUEUED
ERR unknown command 'NOTACOMMAND'

EXECABORT Transaction discarded because of previous errors.

This is the one case Redis does abort entirely — a command that doesn’t exist at all fails at queue time, not at execution time, and EXEC refuses to run any of the queued commands once that happens. The distinction is queue-time errors (unknown command, wrong number of arguments) vs. run-time errors (right command, wrong data) — only the first kind aborts the whole transaction.

2. Confirm Postgres does NOT roll back if you never call ROLLBACK or hit an error — a normal successful transaction commits everything. 
psql -U postgres -c "BEGIN; UPDATE accounts SET balance = balance + 10 WHERE id = 1; COMMIT;"
psql -U postgres -c "SELECT * FROM accounts WHERE id = 1;"
BEGIN
UPDATE 1
COMMIT
 id | balance
----+---------
  1 |     110
(1 row)

No error, no rollback — a transaction that completes without failing commits normally. Rollback only happens on an actual error or an explicit ROLLBACK, in both Postgres and MongoDB alike.

3. Check whether MongoDB's transaction still aborts if the *first* operation is the one that fails, instead of the second. 
session.startTransaction();
try {
    coll.insertOne({_id: 2, balance: 999}); // fails first this time
    coll.updateOne({_id: 1}, {$inc: {balance: -30}});
    session.commitTransaction();
} catch (e) {
    session.abortTransaction();
    print("aborted: " + e.message);
}
aborted: E11000 duplicate key error collection: test.accounts index: _id_ dup key: { _id: 2 }

Same abort, regardless of which operation inside the transaction fails first — the guarantee is about the whole transaction block, not about ordering.

Next: indexing across engines — the same B-tree idea, five very different real costs.

Frequently Asked Questions

Why did the successful UPDATE in Postgres get undone even though it never itself failed?
A transaction's atomicity guarantee covers the whole block, not each statement individually — once any statement inside BEGIN...COMMIT fails, Postgres marks the entire transaction as aborted, and every change since BEGIN is discarded when the connection ends or ROLLBACK is issued, including changes that succeeded on their own. That's what "all or nothing" actually means.
Why didn't Redis roll back the same way?
MULTI/EXEC guarantees the queued commands run back-to-back with nothing else interleaved between them — it does not guarantee that if one command errors, the others are undone. Redis executes each queued command independently during EXEC and reports each result separately; a runtime error in one command (like INCR on a non-numeric string) doesn't stop or reverse the ones around it.
Does this mean Redis transactions are broken?
No — they're doing exactly what they're documented to do; the mismatch is between what "transaction" implies from a relational-database background and what MULTI/EXEC actually promises. Redis's official documentation is explicit about this: errors during EXEC are not a reason to abort the remaining commands, by design, because catching type errors before queuing is the client's job, not EXEC's.
How does MongoDB achieve real rollback if it's not a traditional relational database?
Multi-document transactions (available since MongoDB 4.0 on replica sets, and on sharded clusters since 4.2) work through the same underlying mechanism as a relational database's transaction log — a session-scoped transaction buffers writes and only makes them visible atomically on commitTransaction(), or discards all of them on abortTransaction() or an uncaught error. It's a real ACID transaction, just added to a document model rather than a relational one.
How would you actually catch this Redis behavior before it causes a real bug?
Validate each command's inputs before queuing them in MULTI, since EXEC won't catch a type mismatch for you — checking that a value is numeric before calling INCR on it, for instance. Some client libraries also expose EXEC's per-command results as a list, which lets calling code check each result and manually compensate for a partial failure after the fact, rather than assuming an all-or-nothing outcome.