Database.Middle.Does write lock depends on isolation level ?

✅ First: Your Query Again

UPDATE users SET balance = balance - 100 WHERE id = 42;

This query always locks the row, regardless of isolation level — because:

🔒 All UPDATEs require a write lock to prevent conflicting changes.

So even under the lowest isolation level (e.g., READ COMMITTED), the row is exclusively locked until the transaction ends.

🔍 What Does Depend on Isolation Level?

While writes always lock, the read behavior and blocking behavior of other queries do depend on the isolation level.

Let’s explore each isolation level with examples:

🔹 1. READ UNCOMMITTED

Reads can see uncommitted changes (“dirty reads”)

  • 🚫 Not supported in PostgreSQL (because it’s unsafe).
  • ✅ Supported in MySQL InnoDB, but:
    • Your UPDATE still locks the row
    • Other transactions can read the uncommitted value if allowed

🔒 Write lock? → YES

👀 Other reads blocked? → NO (but may read dirty data)

🔹 2. READ COMMITTED (default in PostgreSQL and Oracle)

  • Your UPDATE locks the row
  • Other transactions:
    • Can read the old committed version (thanks to MVCC)
    • Can’t update the same row — they wait for your lock to release

🔒 Write lock? → YES

👀 Readers blocked? → NO

🧍 Writers blocked? → YES

🔹 3. REPEATABLE READ (default in MySQL)

  • Like READ COMMITTED, but repeatable reads see the same snapshot throughout the transaction
  • Still uses row-level locks for writes
  • Still blocks conflicting writes

🔒 Write lock? → YES

👀 Readers blocked? → NO

🧍 Writers blocked? → YES

🔹 4. SERIALIZABLE (strictest)

  • Acts like transactions are completely isolated
  • May escalate to predicate locks (PostgreSQL) or gap locks (MySQL)
  • May abort concurrent transactions to avoid conflicts

🔒 Write lock? → YES

👀 Readers blocked? → Not blocked directly, but risk of failure

🧍 Writers blocked or aborted → YES

🧠 Summary Table

Isolation LevelRow Write Lock?Reads Blocked?Other Writes Blocked?
Read Uncommitted✅ Yes❌ No (can see dirty)✅ Yes
Read Committed✅ Yes❌ No✅ Yes
Repeatable Read✅ Yes❌ No (same snapshot)✅ Yes
Serializable✅ Yes❌* (may fail)✅/❌ May rollback

✅ Final Answer for Your Case

UPDATE users SET balance = balance - 100 WHERE id = 42;

  • Always locks the row for writing
  • Isolation level affects what other transactions can see or do:
    • At low levels, they can see older values
    • At high levels, they might get blocked or rolled back