Transactions, Locking and Concurrency in Django + PostgreSQL¶
This document is for backend developers who want to understand how data integrity is maintained in concurrent systems. Inside: an MVCC deep dive, select_for_update(), race conditions, deadlocks and how to resolve them.
🔬 Live transaction examples:
docs/reference/legacy_source/orm_laboratory_reference.md
Concept Notebook section transaction.atomic()— BEGIN / COMMIT / ROLLBACK## 13. 🔒 ТранзакціїF()expressions — race-condition safe## 6. 📐 F() Expressionsselect_for_update()— row locking## 13. 🔒 ТранзакціїDuplicate query detection ## 11. 🔬 SQL Inspection—connection.queries🧠 Mental model: A server processes thousands of requests at the same time. Each request can read and modify data. Without proper concurrency control, data gets corrupted: balances are increased incorrectly, items are sold twice, important operations are performed only partially. Transactions, MVCC and locking are the mechanisms that prevent this chaos.
📚 Why this matters for an ordinary Django project: Even a simple product page can become a problem with 1000 concurrent shoppers.
note.views_count += 1in a loop with 1000 parallel requests = a race condition = 999 views lost. Understanding transactions = writing reliable code.🌐 Where it lives in Django:
transaction.atomic(),select_for_update(),F(),on_commit()are the Python API for managing PostgreSQL transactions and locks.❌ Common mistake: Thinking that
save()is already "atomic".Note.objects.create()+NoteRevision.objects.create()are TWO separate INSERTs, two separate transactions. If an exception happens between them → the Note is saved, the Revision is not → corrupted state. You needtransaction.atomic().
1. MVCC in detail¶
🧠 Mental model: MVCC = Multi-Version Concurrency Control. Imagine Google Docs: several people open a document at the same time. Each sees the version as of the moment they opened it. Someone saves changes — the others don't see them yet (or do, depending on refresh). PostgreSQL does the same: every transaction sees a snapshot of the database state.
📚 Readers don't block writers: This is a key difference between PostgreSQL and MySQL InnoDB (where readers can also block). In PostgreSQL,
SELECTnever blocksINSERT/UPDATE/DELETEand vice versa. They see different row versions (MVCC).🌐 Isolation levels:
READ COMMITTED— each query in a transaction sees fresh committed data.REPEATABLE READ— the snapshot is fixed at the start of the transaction.SERIALIZABLE— transactions execute as if sequentially (safest, slowest).❌ Common mistake: Thinking that
READ COMMITTEDgives an "exact" result for two queries within one transaction.READ COMMITTED= each query sees newly committed data. If someone committed between your two SELECTs, the second SELECT sees the new data. For a "frozen snapshot" →REPEATABLE READ.
# Demonstrating MVCC and isolation levels:
from django.db import transaction, connection
# READ COMMITTED (default):
with transaction.atomic():
count1 = Note.objects.filter(user=user).count() # → 100
# Another process: Note.objects.create(user=user, ...)
count2 = Note.objects.filter(user=user).count() # → 101 (sees the new note!)
# READ COMMITTED: every SELECT sees newly committed data
# REPEATABLE READ:
with transaction.atomic():
connection.cursor().execute('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ')
count1 = Note.objects.filter(user=user).count() # → 100
# Another process: Note.objects.create(user=user, ...)
count2 = Note.objects.filter(user=user).count() # → 100 (snapshot frozen!)
# REPEATABLE READ: the snapshot is fixed at the start of the transaction
PostgreSQL isolation levels:
Level | Dirty Read | Non-Repeatable | Phantom Read
──────────────────┼────────────┼────────────────┼─────────────
READ UNCOMMITTED | Possible¹ | Possible | Possible
READ COMMITTED | ✅ Protected| Possible | Possible
REPEATABLE READ | ✅ Protected| ✅ Protected | ✅ Protected²
SERIALIZABLE | ✅ Protected| ✅ Protected | ✅ Protected
¹ PostgreSQL always uses READ COMMITTED as the minimum
² PostgreSQL REPEATABLE READ protects against phantoms (a difference from the SQL standard)
2. transaction.atomic() — the full mechanics¶
🧠 Mental model:
with transaction.atomic():is a "safety bubble". Everything that happens inside is either executed COMPLETELY or canceled COMPLETELY. There is no "half an operation" or "partially saved data". A bank transfer: withdraw from A, add to B — either both operations, or neither.📚 SQL lifecycle:
transaction.atomic()→ SQLBEGIN(if not yet in a transaction) orSAVEPOINT(if already in a transaction). On success →COMMITorRELEASE SAVEPOINT. On an exception →ROLLBACKorROLLBACK TO SAVEPOINT.🌐 Nested atomic(): Django implements them via Savepoints. The outer
atomic()=BEGIN. The inneratomic()=SAVEPOINT sp_1. If the inner one raises an exception →ROLLBACK TO SAVEPOINT sp_1(only the inner one is canceled). The outer one continues.❌ CRITICAL mistake:
try/exceptinsideatomic(). If you catch a DB error (for example,IntegrityError) inside a transaction, the transaction is in an "aborted" state, while the code carries on as if all is well. (A plain Python exception such asValueErrordoesn't break the transaction.) The nextNote.objects.create()will raiseTransactionManagementError: You can't execute queries until the end of the 'atomic' block.Catch OUTSIDE atomic or in a nestedatomic()(savepoint), never just inside!
from django.db import transaction
import logging
logger = logging.getLogger(__name__)
# ✅ CORRECT: atomic() → all operations either all succeed or none do
def transfer_note(note_id, from_notebook, to_notebook, user):
"""
Move a note between notebooks.
Atomic operation: either both UPDATEs run, or neither.
"""
with transaction.atomic():
note = Note.objects.select_for_update().get(id=note_id, user=user)
note.notebook = to_notebook
note.save(update_fields=['notebook'])
# Log the move (if this fails → the note is rolled back too)
NoteActivity.objects.create(
note=note,
action='moved',
from_notebook=from_notebook,
to_notebook=to_notebook,
)
# COMMIT happens only here — after successfully leaving the block
# ❌ WRONG: catch inside atomic()
def wrong_create_note(title, user):
with transaction.atomic():
note = Note.objects.create(title=title, user=user)
try:
# If a DB query fails here, the transaction is "broken"!
send_notification(user) # suppose it writes to the DB and raises IntegrityError
except Exception:
pass # ← DISASTER (for DB errors)!
# PostgreSQL is now in an aborted state, but the code carries on as if all is well
# Next query in this atomic(): TransactionManagementError!
# (a plain Python exception, e.g. ValueError, does NOT break the transaction)
# On leaving atomic(), Django does a ROLLBACK — the note is silently not saved
# ✅ CORRECT: catch OUTSIDE atomic()
def correct_create_note(title, user):
try:
with transaction.atomic():
note = Note.objects.create(title=title, user=user)
send_notification(user) # if it fails → ROLLBACK of the Note
except Exception as e:
logger.error(f"create_note failed: {e}")
return None
return note # we get here only if everything is OK
# Nested atomic() — Savepoints:
def create_note_with_optional_revision(title, content, user):
with transaction.atomic(): # BEGIN
note = Note.objects.create(
title=title, content=content, user=user
) # INSERT note
try:
with transaction.atomic(): # SAVEPOINT sp_1
NoteRevision.objects.create(
note=note, content=content, version=1
) # INSERT revision
except Exception:
pass
# ROLLBACK TO SAVEPOINT sp_1 (only the revision was rolled back)
# The Note remains!
# COMMIT — the note is saved, the revision maybe not (depending on the exception)
3. Savepoints — nested transactions¶
🧠 Mental model: A savepoint is a "checkpoint" inside a transaction. Like in a game:
SAVEPOINT= save your progress on a level. If you die →ROLLBACK TO SAVEPOINT= return to the checkpoint without starting the game over.📚 Django and Savepoints: Every nested
with transaction.atomic():automatically becomes a savepoint if we are already in a transaction. You don't write SQL directly — Django decides by itself:BEGINorSAVEPOINT.🌐 Where this is useful: Operations with "optional" parts. The main operation is mandatory. The extra one is desirable but not critical. If the extra one raises an exception → only it is rolled back. The main one is saved.
Sequence Diagram — Savepoints:
Django PostgreSQL
│ │
│ with transaction.atomic():
│ ────BEGIN──────────────► │
│ │
│ Note.objects.create() │
│ ────INSERT note─────────► │
│ │
│ with transaction.atomic(): (nested!)
│ ────SAVEPOINT sp_1──────► │
│ │
│ NoteRevision.create() │
│ ────INSERT revision─────► │
│ │
│ (on exception): │
│ ────ROLLBACK TO sp_1────► │ (only the revision was rolled back)
│ (on success): │
│ ────RELEASE sp_1────────► │ (savepoint released)
│ │
│ ────COMMIT───────────────► │ (note saved)
4. select_for_update() — row-level locks¶
🧠 Mental model:
select_for_update()is an "occupied" sign on a resource. When T1 executesSELECT FOR UPDATE, PostgreSQL places a lock on the row. T2 tries to executeSELECT FOR UPDATEon the same row → it waits until T1 does COMMIT or ROLLBACK.📚 Why it's needed: Without a lock: T1 and T2 both read
stock=1, both compute1-1=0, both writestock=0. The item was sold twice. WithSELECT FOR UPDATE: T1 locked the row, T2 waits. T1 writesstock=0and COMMITs → T2 readsstock=0→ doesn't sell (stock is empty).🌐 Options:
nowait=True— raises an error instead of waiting.skip_locked=True— skips locked rows (for a task queue).of=('table',)— lock only a specific table in a JOIN.❌ Common mistake:
select_for_update()WITHOUTtransaction.atomic(). The lock is released only on COMMIT or ROLLBACK. Without atomic, Django is in autocommit mode — every query = its own COMMIT → the lock is released right after the SELECT. The point of the lock is lost.
from django.db import transaction
# ALWAYS inside transaction.atomic()!
# Basic select_for_update:
def book_ticket(event_id, user):
"""Buying a ticket — a classic race condition scenario."""
with transaction.atomic():
# SELECT ... FOR UPDATE → locks the event row
event = Event.objects.select_for_update().get(id=event_id)
if event.available_tickets <= 0:
raise ValueError("Tickets are sold out")
# Only ONE process runs this code at a time here (lock!)
event.available_tickets -= 1
event.save(update_fields=['available_tickets'])
Ticket.objects.create(event=event, user=user)
# COMMIT → the lock is released → the next process can read
# nowait=True — raise an error if the row is already locked
def try_process_note(note_id, user):
"""If the note is already being processed — refuse (don't wait)."""
try:
with transaction.atomic():
note = Note.objects.select_for_update(nowait=True).get(
id=note_id, user=user
)
# Fast operation without waiting
note.status = 'processing'
note.save(update_fields=['status'])
except Exception as e:
if 'could not obtain lock' in str(e):
return {'error': 'The note is already being processed, try again later'}
raise
# skip_locked=True — for a task queue (skip busy rows)
def process_pending_tasks():
"""
A Celery-like pattern for processing a queue.
skip_locked=True: if a row is locked by another worker → skip it.
Don't wait (that would slow down the queue).
"""
with transaction.atomic():
tasks = Task.objects.select_for_update(
skip_locked=True
).filter(status='pending')[:10] # take 10 at a time
for task in tasks:
task.status = 'processing'
task.save(update_fields=['status'])
return list(tasks)
5. Race Condition — Lost Update¶
🧠 Mental model: A race condition = a "race" between two processes. Both read the same value, both "win" the contest to write first → the second overwrites the first. A "Lost Update".
📚 Classic example: A view counter. 1000 parallel requests all read
views=100, all writeviews=101. Instead ofviews=1100we getviews=101. 999 views are "lost".🌐 The F() solution:
Note.objects.filter(pk=42).update(views=F('views')+1)→UPDATE notes SET views = views + 1 WHERE id=42→ an atomic operation at the PostgreSQL level, a race condition is impossible.❌ Common mistake:
note = Note.objects.get(pk=42); note.views += 1; note.save()— the classic lost update pattern. Always useF()for counters.
Race Condition — Lost Update (without F()):
Time | Process A | Process B
─────────────────────────────────────────────
T=1 | SELECT views=100 |
T=2 | | SELECT views=100
T=3 | views = 100 + 1 = 101 |
T=4 | | views = 100 + 1 = 101
T=5 | UPDATE views=101 |
T=6 | | UPDATE views=101 ← overwrote A!
Result: views=101 instead of 102. With 1000 processes → views=101 instead of 1100!
from django.db.models import F
# ❌ RACE CONDITION (with concurrent requests):
def increment_views_UNSAFE(note_id):
note = Note.objects.get(id=note_id) # SELECT: reads views=100
note.views_count += 1 # Python: 100+1=101
note.save() # UPDATE: sets views=101
# 2 operations, a race condition between them!
# ✅ SAFE — an atomic UPDATE at the PostgreSQL level:
def increment_views_SAFE(note_id):
Note.objects.filter(id=note_id).update(
views_count=F('views_count') + 1
)
# 1 SQL: UPDATE notes SET views_count = views_count + 1 WHERE id=?
# PostgreSQL performs read+write atomically — a race condition is impossible
# ✅ SAFE — for more complex logic (select_for_update):
def increment_with_cap(note_id, max_views=10000):
"""Increment, but not beyond max_views."""
with transaction.atomic():
note = Note.objects.select_for_update().get(id=note_id)
if note.views_count < max_views:
note.views_count += 1
note.save(update_fields=['views_count'])
6. Race Condition — Double Submit¶
🧠 Mental model: Double submission: the user clicked "Buy" twice (or a double click, or network latency). Two HTTP POST requests fly at the same time. Without protection — two identical orders. An idempotency key is a unique token that lets "repeated" requests avoid creating a duplicate.
📚 Idempotency Key: The client generates a UUID and sends it with every request. The server stores the idempotency_key in the database. On a second request with the same key → it returns the same result instead of creating a new one. A UniqueConstraint on idempotency_key guarantees this at the database level.
🌐 Django implementation: An
IntegrityErrorwhen trying to INSERT a duplicate idempotency_key → we catch it in the view → return the existing result.
import uuid
from django.db import IntegrityError
class Order(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
total_price = models.DecimalField(max_digits=10, decimal_places=2)
# Idempotency key — a unique token from the client
idempotency_key = models.UUIDField(unique=True, default=uuid.uuid4)
# UniqueConstraint guarantees: one key → one order (even under a race condition)
def create_order_idempotent(user, cart, idempotency_key):
"""
Idempotent order creation.
Two parallel requests with the same key → only one order.
"""
try:
with transaction.atomic():
order = Order.objects.create(
user=user,
total_price=cart.total,
idempotency_key=idempotency_key,
)
# Further logic...
return order, 'created'
except IntegrityError:
# A second request with the same key → UNIQUE violation → return the existing one
existing = Order.objects.get(idempotency_key=idempotency_key)
return existing, 'already_exists'
7. Deadlock — mutual blocking¶
🧠 Mental model: A deadlock = two transactions waiting for each other. T1 locked row A and waits for B. T2 locked row B and waits for A. Both wait forever. PostgreSQL detects the deadlock and automatically kills one of them (psycopg raises
DeadlockDetected, which reaches Django code asOperationalError).📚 When it happens: With a different locking order. T1:
lock(note_1) → lock(note_2). T2:lock(note_2) → lock(note_1). If T1 locked note_1 and T2 locked note_2 at the same time → deadlock.🌐 Prevention: Always lock in one stable order (for example, by id ASC). If T1 and T2 always take the lock on the smaller id first → T1 takes note_1, T2 waits for note_1 → T1 takes note_2 → COMMIT → T2 gets note_1 → takes note_2 → COMMIT. A deadlock is impossible!
❌ Common mistake: A different locking order in different views. View A:
lock(user) → lock(note). View B:lock(note) → lock(user). With simultaneous requests → deadlock.
# ❌ DEADLOCK POTENTIAL:
def transfer_A_to_B(note_a_id, note_b_id, user):
with transaction.atomic():
a = Note.objects.select_for_update().get(id=note_a_id) # lock A
b = Note.objects.select_for_update().get(id=note_b_id) # waits for B
...
def transfer_B_to_A(note_a_id, note_b_id, user):
with transaction.atomic():
b = Note.objects.select_for_update().get(id=note_b_id) # lock B
a = Note.objects.select_for_update().get(id=note_a_id) # waits for A ← DEADLOCK!
...
# ✅ SOLUTION: always lock in a stable order (by id ASC):
def transfer_notes_safe(note_a_id, note_b_id, user):
with transaction.atomic():
# ALWAYS lock in ascending id order
notes = Note.objects.select_for_update().filter(
id__in=[note_a_id, note_b_id]
).order_by('id') # ← STABLE ORDER!
note_map = {n.id: n for n in notes}
note_a = note_map[note_a_id]
note_b = note_map[note_b_id]
# Logic...
# ✅ RETRY on Deadlock (for complex systems):
import time
import random
from django.db import OperationalError
def with_deadlock_retry(func, max_retries=3, base_delay=0.1):
"""
Decorator: retries the operation on a deadlock.
Exponential backoff + jitter (random delay).
"""
for attempt in range(max_retries):
try:
return func()
except OperationalError as e:
if 'deadlock detected' in str(e).lower() and attempt < max_retries - 1:
delay = base_delay * (2 ** attempt) + random.uniform(0, 0.1)
time.sleep(delay)
continue
raise
# Usage:
with_deadlock_retry(lambda: transfer_notes_safe(a_id, b_id, user))
8. on_commit() — actions after a successful commit¶
🧠 Mental model:
on_commit()means "send the email only if the order was saved successfully". If the transaction was rolled back → the email is not sent. If the commit succeeded → the email is sent. Never send a Celery task or an email insideatomic()— the transaction is not finished yet and may be rolled back!📚 The common mistake without on_commit:
celery_task.delay(note.id)insidetransaction.atomic(). The Celery task starts immediately. The task tries to read the note → it is not in the database yet (the transaction hasn't finished). The task fails or sees the wrong state.🌐 How it works: Django accumulates
on_commitcallbacks. After a successfulCOMMIT, it runs all callbacks in registration order. OnROLLBACK, the callbacks are NOT run.
from django.db import transaction
def create_note_with_notification(user, title, content):
with transaction.atomic():
note = Note.objects.create(
title=title, content=content, user=user
)
# ❌ WRONG: the email may be sent even if the transaction was rolled back!
# send_notification_email.delay(note.id)
# ✅ CORRECT: send only after a successful COMMIT
transaction.on_commit(
lambda: send_notification_email.delay(note.id)
)
# ✅ You can register several callbacks:
transaction.on_commit(
lambda: update_search_index.delay(note.id)
)
transaction.on_commit(
lambda: send_webhook.delay('note_created', note.id)
)
# After this line: the COMMIT has been executed
# on_commit callbacks run in registration order
# If a problem occurs between on_commit(email) and on_commit(webhook) —
# the email has already been sent (each callback runs independently)
return note
9. Isolation Levels and a solution for each scenario¶
# Setting the isolation level for a specific transaction:
from django.db import transaction, connection
def critical_report_generation(user_id):
"""
A financial report: needs a consistent snapshot.
REPEATABLE READ: all SELECTs in the transaction see one snapshot.
"""
with transaction.atomic():
connection.cursor().execute(
'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ'
)
total_notes = Note.objects.filter(user_id=user_id).count()
published = Note.objects.filter(user_id=user_id, status='published').count()
# If someone adds a note between these two COUNTs — we won't see it
# (REPEATABLE READ: the snapshot is frozen)
return {'total': total_notes, 'published': published}
def book_last_seat(event_id, user_id):
"""
Booking the last seat: SERIALIZABLE for maximum safety.
"""
with transaction.atomic():
connection.cursor().execute(
'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
)
event = Event.objects.get(id=event_id)
if event.seats_available <= 0:
raise ValueError("No seats available")
# SERIALIZABLE guarantees: if T1 and T2 both see seats=1,
# one of them will get a SerializationFailure and will be retried
event.seats_available -= 1
event.save()
Booking.objects.create(event=event, user_id=user_id)
10. A practical decision table¶
| Problem | Solution | Django API |
|---|---|---|
| Counter (N+1 race) | Atomic UPDATE | F('count') + 1 |
| Partial operation | Atomic transaction | transaction.atomic() |
| Concurrent read-write | Row-level locking | select_for_update() |
| Double submit | Idempotency key | UNIQUE + catch IntegrityError |
| Deadlock | Stable lock order | order_by('id') before locking |
| Email after save | After commit | on_commit(lambda: task.delay(...)) |
| Consistent snapshot | REPEATABLE READ | SET TRANSACTION ISOLATION LEVEL |
Prediction Exercises¶
Exercise 1: F() vs save()¶
# Option A:
note = Note.objects.get(pk=42)
note.likes += 1
note.save()
# Option B:
Note.objects.filter(pk=42).update(likes=F('likes') + 1)
Question: With 1000 simultaneous requests for each option — what is the result?
Option A: All 1000 read
likes=0, all writelikes=1. Result:likes=1(999 updates lost!)Option B: Each UPDATE atomically increments by 1. Result:
likes=1000(no update lost!)
Exercise 2: atomic() Exception¶
with transaction.atomic():
note = Note.objects.create(title='Test')
try:
raise ValueError("oops")
except ValueError:
pass
note2 = Note.objects.create(title='Test2') # what happens here?
Question: How many notes will be created?
Answer: Both notes are saved.
ValueErroris a plain Python exception unrelated to the DB: PostgreSQL knows nothing about it, and the transaction stays usable. ATransactionManagementErrorwould appear only if a DB operation failed inside thetry(for example,IntegrityError) and was caught without a nestedatomic(): then the transaction enters the aborted state, the nextcreate()raisesTransactionManagementError, and on leaving the block a ROLLBACK happens (no note is saved). The rule: catch DB errors OUTSIDEatomic(), or wrap the risky operation in a nestedatomic()(savepoint).
Self-check questions¶
- Why shouldn't you use
try/exceptinsidetransaction.atomic()? - What is a Savepoint and when does Django use it?
- What is the difference between
select_for_update(nowait=True)andselect_for_update(skip_locked=True)? - What is a Lost Update and how does F() prevent it?
- What is a Deadlock and how do you avoid it?
- Why do we need
on_commit()and why shouldn't you send an email/task inside atomic()? - What is the difference between READ COMMITTED and REPEATABLE READ?
- What is an idempotency key and in which scenarios is it needed?
- Why must
select_for_update()ALWAYS be insidetransaction.atomic()? - PostgreSQL detected a deadlock and killed one transaction. Which one? What will the killed transaction receive?