UPDATE...RETURNING in MongoDB: ACID and idempotency with findOneAndUpdate()

Source: Dev.to

Atomic Read‑Write Operations in a Single Call

You can implement atomic read‑write operations in a single call instead of an explicit transaction, reducing both round trips and the window for concurrent conflicts.

• PostgreSQL – use UPDATE … RETURNING instead of SELECT FOR UPDATE followed by UPDATE.
• MongoDB – use findOneAndUpdate() instead of updateOne() followed by find().

This enables a single ACID read‑write operation that is failure‑resilient and safely retryable in MongoDB because it is idempotent.

Demo: Two Withdrawals and a Receipt

Scenario – Bob withdraws using a debit card (no negative balance allowed). The application first does updateOne to subtract the amount only if the balance is enough, then does a separate find() to print the receipt with the balance. Between those two calls, Alice uses a credit card (allowing a negative balance) and withdraws money from the same account, so Bob’s printed balance becomes inconsistent with the balance checked for his withdrawal.

Fix – Use findOneAndUpdate() to return the post‑update balance atomically with the withdrawal.

Connection and Collection

from pymongo import MongoClient, ReturnDocument
import threading
import time

# Connect to MongoDB
client = MongoClient("mongodb://127.0.0.1:27017/?directConnection=true")
db = client.bank
accounts = db.accounts

# Prepare test account: Bob & Alice share this account
def reset_account():
    accounts.drop()
    accounts.insert_one({
        "_id": "b0b4l3ce",
        "owner": ["Bob", "Alice"],
        "balance": 100
    })

reset_account()

Document view

bank> db.accounts.find()
[ { _id: 'b0b4l3ce', owner: [ 'Bob', 'Alice' ], balance: 100 } ]

The balance is set at 100.

Scenario with updateOne() Followed by find()

Bob’s withdrawal (debit)

def bob_withdraw_debit(amount):
    print("[Bob] Attempting debit withdrawal", amount)

    # Application logic checks balance then updates
    result = accounts.update_one(
        {"_id": "b0b4l3ce", "balance": {"$gte": amount}},   # must have enough money
        {"$inc": {"balance": -amount}}
    )

    # If no document was updated, the filter didn't find enough funds
    if result.modified_count == 0:
        print("[Bob] Withdrawal denied - insufficient funds")
        return

    # Simulate processing delay before printing the ticket
    time.sleep(1)

    # Query the balance for the receipt
    balance = accounts.find_one({"_id": "b0b4l3ce"})["balance"]
    print(f"[Bob] Debit card ticket: withdrew {amount}, balance after withdrawal: {balance}")

Alice’s withdrawal (credit)

def alice_withdraw_credit(amount, delay=0):
    time.sleep(delay)          # let Bob start first
    print("[Alice] Attempting credit withdrawal", amount)

    # No balance check for credit cards
    accounts.update_one(
        {"_id": "b0b4l3ce"},
        {"$inc": {"balance": -amount}}
    )
    print("[Alice] Credit withdrawal complete")

Demo script (race condition)

def demo():
    reset_account()
    t_bob = threading.Thread(target=bob_withdraw_debit, args=(80,))
    t_alice = threading.Thread(target=alice_withdraw_credit, args=(30, 0.5))  # starts just after Bob update
    t_bob.start()
    t_alice.start()
    t_bob.join()
    t_alice.join()

Output

>>> demo()
[Bob] Attempting debit withdrawal 80
[Alice] Attempting credit withdrawal 30
[Alice] Credit withdrawal complete
[Bob] Debit card ticket: withdrew 80, balance after withdrawal: -10

Bob receives a ticket showing a negative balance on his debit card – a bug ❌.

Scenario with findOneAndUpdate() (returnDocument = AFTER)

Bob’s withdrawal (debit)

def bob_withdraw_debit(amount):
    print("[Bob] Attempting debit withdrawal", amount)

    doc = accounts.find_one_and_update(
        {"_id": "b0b4l3ce", "balance": {"$gte": amount}},
        {"$inc": {"balance": -amount}},
        return_document=ReturnDocument.AFTER   # get post‑update document atomically
    )

    # No need to check the update count; we have the document if it was updated
    if not doc:
        print("[Bob] Withdrawal denied - insufficient funds")
        return

    # Ticket immediately shows consistent balance
    print(f"[Bob] Ticket: withdrew {amount}, balance after withdrawal: {doc['balance']}")

Running the demo again

>>> demo()
[Bob] Attempting debit withdrawal 80
[Bob] Ticket: withdrew 80, balance after withdrawal: 20
[Alice] Attempting credit withdrawal 30
[Alice] Credit withdrawal complete

Bob now receives a ticket showing the balance at the exact time of withdrawal ✅.
The update write and post‑update read occurred as a single atomic operation on the document, leaving no opportunity for another write between the update and the displayed read result.

Resilience

In MongoDB, reads and writes do not acquire transaction locks like they can in relational databases, but document updates are still atomic at the document level, even without starting an explicit transaction. MongoDB uses lightweight document‑level locks internally to ensure ACID guarantees for a single document, since even one update can involve multiple internal reads and writes (e.g., enforcing unique constraints and updating indexes).

• updateOne() returns only metadata (such as the number of documents updated).
• findOneAndUpdate() returns the updated document itself, with the read and write occurring in the same atomic operation at the single‑document level. This atomicity is preserved even in the event of failures.

If the network is lost or the primary crashes and a secondary is promoted, the MongoDB driver retries the operation as part of retryable writes. Because retries must be idempotent, findOneAndUpdate() returns the same document image on retry.

To support this, MongoDB stores a document image—either the after image (as in this example, using returnDocument: "after") or the before image—in an internal system collection (config.image_collection). This collection is replicated independently of the oplog, as part of the same transaction:

// Switch to the config database
use config

// View the image collection
db.image_collection.find()
[
  {
    _id: {
      id: UUID('d04e10d6-c61d-42ad-9a44-5bb226a898d8'),
      uid: Binary.createFromBase64('47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=', 0)
    },
    txnNum: Long('15'),
    ts: Timestamp({ t: 1767478784, i: 5 }),
    imageKind: 'postImage',
    image: { _id: 'b0b4l3ce', owner: [ 'Bob', 'Alice' ], balance: 20 },
    invalidated: false
  }
]

When you enable write retries, this image collection is used internally to make write operations resilient to failures. The handling is transparent to the application and provides the strongest consistency guarantees.

Comparison with PostgreSQL

In PostgreSQL, the equivalent operations would look like this:

CREATE TABLE accounts (
    id TEXT PRIMARY KEY,
    balance NUMERIC,
    owners TEXT[]
);

INSERT INTO accounts VALUES ('b0b4l3ce', ARRAY['Bob','Alice'], 100);

-- Alice's transaction
UPDATE accounts
SET balance = balance - 30
WHERE id = 'shared_account';

-- Bob's transaction
UPDATE accounts
SET balance = balance - 80
WHERE id = 'b0b4l3ce' AND balance >= 80
RETURNING balance AS new_balance;

PostgreSQL drivers do not automatically retry failures; they rely on MVCC and locks to ensure ACID properties.

• Repeatable Read isolation (appropriate because the write SET balance = balance - 80 depends on the read WHERE balance >= 80):

  • Bob’s transaction takes a snapshot at the start. When run concurrently with Alice’s, it still sees balance = 100.

  • If Alice commits first and reduces the balance to 70, Bob’s transaction fails with:

    ERROR: could not serialize access due to concurrent update

  • The application must handle this by retrying the entire transaction; the driver does not do this automatically.

• Read Committed (the default) isolation level:

  • Bob’s transaction waits if Alice’s update has locked the row.
  • After Alice commits, PostgreSQL re‑evaluates Bob’s WHERE clause. The balance is now 70, which no longer satisfies balance >= 80, so the UPDATE affects 0 rows and the withdrawal is refused, preventing a negative balance.
  • This works fine when only one row is affected, but in a multi‑row statement it could be inconsistent, operating on rows from different transactional states.

Conclusion

MongoDB supports both multi‑document transactions and single‑document atomic operations, but it strongly encourages using single‑document operations whenever possible. If you design your schema so that business logic fits in a single document, findOneAndUpdate() can:

• Perform conditional checks,
• Apply updates, and
• Return the updated document atomically,

thereby avoiding race‑condition anomalies and enabling idempotent retries.

In some cases—such as the one described in the earlier blog post on FOR UPDATE SKIP LOCKED in MongoDB—an updateOne() followed by a find() with appropriate conditions is sufficient and avoids storing before‑ or after‑images.