You possibly can implement atomic read-write operations in a single name as an alternative of an express transaction, lowering each spherical journeys and the window for concurrent conflicts. In PostgreSQL, use UPDATE ... RETURNING as an alternative of SELECT FOR UPDATE adopted by UPDATE. In MongoDB, use findOneAndUpdate() as an alternative of updateOne() adopted by discover(). This permits a single ACID read-write operation that’s failure-resilient and safely retryable in MongoDB as a result of it’s idempotent. Right here is an instance.
Demo: two withdrawals and a receipt
Bob withdraws utilizing a debit card (no damaging stability allowed). The applying first does updateOne to subtract the quantity provided that the stability is sufficient, then does a separate discover() to print the receipt with the stability. Between these two calls, Alice makes use of a bank card (permitting a damaging stability) and withdraws cash from the identical account, so Bob’s printed stability turns into inconsistent with the stability checked for his withdrawal.
To repair this, we’ll use findOneAndUpdate() to return the post-update stability atomically with the withdrawal.
Connection and assortment
Right here’s a Python + PyMongo program simulating this race situation and exhibiting the repair:
from pymongo import MongoClient, ReturnDocument
import threading
import time
# Hook up with MongoDB
shopper = MongoClient("mongodb://127.0.0.1:27017/?directConnection=true")
db = shopper.financial institution
accounts = db.accounts
# Put together take a look at account: Bob & Alice share this account
def reset_account():
accounts.drop()
accounts.insert_one({
"_id": "b0b4l3ce",
"proprietor": ["Bob", "Alice"],
"stability": 100
})
reset_account()
I’ve just one doc for this instance:
financial institution> db.accounts.discover()
[ { _id: 'b0b4l3ce', owner: [ 'Bob', 'Alice' ], stability: 100 } ]
The stability is ready at 100.
Situation with updateOne() adopted by discover()
Bob’s withdrawal course of:
def bob_withdraw_debit(quantity):
print("[Bob] Trying debit withdrawal", quantity)
# Software logic checks stability then updates
outcome = accounts.update_one(
{"_id": "b0b4l3ce", "stability": {"$gte": quantity}}, # should find the money for
{"$inc": {"stability": -quantity}}
)
# If no doc was up to date, the filter did not discover sufficient within the quantity
if outcome.modified_count == 0:
print("[Bob] Withdrawal denied - inadequate funds")
return
# Simulate some processing delay earlier than printing the ticket - to make sure you reproduce the race situation
time.sleep(1)
# question the stability for the receipt
stability = accounts.find_one({"_id": "b0b4l3ce"})["balance"]
print(f"[Bob] Debit card ticket: withdrew {quantity}, stability after withdrawal: {stability}")
Alice’s withdrawal course of:
# Alice withdraws utilizing bank card (damaging stability allowed)
def alice_withdraw_credit(quantity, delay=0):
time.sleep(delay) # let Bob begin first
print("[Alice] Trying credit score withdrawal", quantity)
# There is not any stability examine for bank cards
accounts.update_one(
{"_id": "b0b4l3ce"},
{"$inc": {"stability": -quantity}}
)
print("[Alice] Credit score withdrawal full")
Demo script to run in two threads and get the race situation:
def demo():
reset_account()
t_bob = threading.Thread(goal=bob_withdraw_debit, args=(80,))
t_alice = threading.Thread(goal=alice_withdraw_credit, args=(30, 0.5)) # begins simply after Bob replace
t_bob.begin()
t_alice.begin()
t_bob.be part of()
t_alice.be part of()
The output is:
>>> demo()
[Bob] Trying debit withdrawal 80
[Alice] Trying credit score withdrawal 30
[Alice] Credit score withdrawal full
[Bob] Ticket: withdrew 80, stability after withdrawal: -10
Bob obtained a ticket exhibiting a withdrawal with a damaging stability from his debit card — it is a bug ❌
We should get the stability on the time of the replace, earlier than, and subtract the quantity, or after.
Situation with findOneAndUpdate() and returnDocument: “after”
Bob’s withdrawal course of:
def bob_withdraw_debit(quantity):
print("[Bob] Trying debit withdrawal", quantity)
doc = accounts.find_one_and_update(
{"_id": "b0b4l3ce", "stability": {"$gte": quantity}},
{"$inc": {"stability": -quantity}},
return_document=ReturnDocument.AFTER # get post-update doc atomically
)
# No must examine the replace rely, we have now the doc if it was up to date
if not doc:
print("[Bob] Withdrawal denied - inadequate funds")
return
# Ticket instantly exhibits constant stability
print(f"[Bob] Ticket: withdrew {quantity}, stability after withdrawal: {doc['balance']}")
Working the demo once more:
>>> demo()
[Bob] Trying debit withdrawal 80
[Bob] Ticket: withdrew 80, stability after withdrawal: 20
[Alice] Trying credit score withdrawal 30
[Alice] Credit score withdrawal full
Bob obtained a ticket exhibiting the stability on the precise time of withdrawal ✅
The replace write and post-update learn occurred as a single atomic operation on the doc, leaving no alternative for an additional write between the replace and the displayed learn outcome.
After-image is saved for consistency and resilience
In MongoDB, reads and writes don’t purchase transaction locks like they will in relational databases, however doc updates are nonetheless atomic on the doc degree, even with out staring an express transaction. MongoDB makes use of light-weight document-level locks internally to make sure ACID ensures for a single doc, since even one replace can contain a number of inside reads and writes, reminiscent of implementing distinctive constraints and updating indexes.
updateOne() returns solely metadata (such because the variety of paperwork up to date), whereas findOneAndUpdate() returns the up to date doc itself, with the learn and write occurring in the identical atomic operation on the single-document degree. This atomicity is preserved even within the occasion of failures.
If the community is misplaced or the first crashes and a secondary is promoted, the MongoDB driver retries the operation as a part of retryable writes. As a result of retries have to be idempotent, find_one_and_update() returns the identical doc picture on retry.
To assist this, MongoDB shops a doc picture—both the after picture (as on this instance, utilizing returnDocument: "after") or the earlier than picture—in an inside system assortment (config.image_collection) that’s replicated independently of the oplog, as a part of the identical transaction:
financial institution> use config
config> db.image_collection.discover()
[
{
_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' ], stability: 20 },
invalidated: false
}
]
config>
If you allow write retries, this picture assortment is used internally to make write operations resilient to failures, that are dealt with transparently by the database driver and server, not by the applying, and with the strongest consistency.
Comparability with PostgreSQL
In PostgreSQL, you’ll use the next queries to do the equal:
CREATE TABLE accounts (
id TEXT PRIMARY KEY,
stability NUMERIC,
homeowners TEXT[]
);
INSERT INTO accounts VALUES ('b0b4l3ce', ARRAY['Bob','Alice'], 100);
-- Alice transaction
UPDATE accounts
SET stability = stability - 30
WHERE id = 'shared_account';
-- Bob transaction
UPDATE accounts
SET stability = stability - 80
WHERE id = 'b0b4l3ce' AND stability >= 80
RETURNING stability AS new_balance;
PostgreSQL drivers don’t routinely retry failures and depend on MVCC and locks to make sure ACID properties.
With Repeatable Learn isolation—acceptable as a result of the write (SET stability = stability - 80) depends upon the learn (WHERE stability >= 80)—Bob’s transaction takes a snapshot firstly and, when run concurrently with Alice’s, nonetheless sees stability = 100. If Alice commits first and reduces the stability to 70, Bob’s transaction fails with: ERROR: couldn't serialize entry as a consequence of concurrent replace. The applying should deal with this by retrying the complete transaction. The driving force doesn’t do that routinely.
With the default Learn Dedicated isolation degree, Bob’s transaction waits if Alice’s replace has locked the row. After Alice commits, PostgreSQL reevaluates Bob’s WHERE clause. The stability is now 70, which not satisfies stability >= 80, so the UPDATE impacts 0 rows and the withdrawal is refused, stopping a damaging stability. That is nice when just one row is affected, however in a multi-row assertion it might be inconsistent, working on rows from completely different transactional states.
Conclusion
MongoDB helps each multi-document transactions and single-document atomic operations, however strongly encourages utilizing single-document operations when potential. In the event you design your schema in order that enterprise logic matches in a single doc, findOneAndUpdate() can carry out conditional checks, apply updates, and return the up to date doc atomically, avoiding race situation anomalies and enabling idempotent retries.
In some instances—such because the one described within the earlier weblog publish on FOR UPDATE SKIP LOCKED in MongoDB—an updateOne() adopted by a discover() with acceptable circumstances is sufficient and avoids storing before- or after-images.
