Day 12: Transactions & Concurrency – PostgreSQL in 15 Days

Day 12: Transactions & Concurrency – PostgreSQL in 15 Days

📋 Outline

ACID and transaction basics
Isolation levels in PostgreSQL
MVCC internals and snapshots
Locks: row, table, advisory
Deadlocks and how to avoid them
Long-run…


This content originally appeared on DEV Community and was authored by Hardik Kanajariya

Day 12: Transactions & Concurrency - PostgreSQL in 15 Days

📋 Outline

  1. ACID and transaction basics
  2. Isolation levels in PostgreSQL
  3. MVCC internals and snapshots
  4. Locks: row, table, advisory
  5. Deadlocks and how to avoid them
  6. Long-running transactions, bloat, and autovacuum
  7. Practical patterns (retry, idempotency, queues)
  8. Challenge
  9. Summary

🧱 ACID and Transaction Basics

  • Atomicity, Consistency, Isolation, Durability
  • BEGIN/COMMIT/ROLLBACK; savepoints for partial rollback 
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;

🔒 Isolation Levels

PostgreSQL supports: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • READ COMMITTED: each statement sees committed data at start of statement
  • REPEATABLE READ: snapshot for entire transaction; prevents non-repeatable reads, phantom reads by predicate locks
  • SERIALIZABLE: guarantees serializability; may raise serialization failures that must be retried

📸 MVCC Internals

  • Each row has xmin/xmax transaction IDs; readers see a snapshot
  • Vacuum removes dead tuples; prevents readers from blocking writers
  • Visibility functions: xmin, xmax, pg_visible_in_snapshot(), txid_current()

🔐 Locks Overview

  • Row-level locks: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE 
SELECT * FROM orders WHERE id = 42 FOR UPDATE; -- prevents concurrent updates
  • Table locks: ACCESS SHARE/EXCLUSIVE, etc. Mostly implicit
  • Advisory locks: application-controlled, via bigints or key tuples 
SELECT pg_advisory_lock(12345);
-- critical section
SELECT pg_advisory_unlock(12345);

Inspect locks:

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks WHERE NOT granted OR mode LIKE '%Exclusive%';

⚠️ Deadlocks and Avoidance

  • Acquire locks in consistent order
  • Keep transactions short; avoid user waits inside
  • Add indexes to reduce lock duration
  • Use NOWAIT/SKIP LOCKED for queues 
-- Avoid blocking consumers
SELECT * FROM jobs WHERE status = 'ready'
FOR UPDATE SKIP LOCKED LIMIT 10;

🐘 Long Transactions, Bloat, Autovacuum

  • Long transactions prevent vacuum cleanup -> table/index bloat
  • Monitor with pg_stat_activity, age(datfrozenxid), n_dead_tup
  • Use idle_in_transaction_session_timeout; cancel bad actors 
SET idle_in_transaction_session_timeout = '15s';

🧰 Practical Patterns

  • Retry on serialization failures (SQLSTATE 40001)
  • Idempotent writes with ON CONFLICT
  • Transactional outbox for reliable events 
-- Idempotent upsert
INSERT INTO payments(id, amount, status)
VALUES ($1, $2, 'processed')
ON CONFLICT (id) DO UPDATE
SET status = EXCLUDED.status, amount = EXCLUDED.amount;

💪 Challenge

  • Implement a job queue using SKIP LOCKED and advisory locks
  • Compare latency under READ COMMITTED vs REPEATABLE READ
  • Create a dashboard query to spot blockers using pg_locks and pg_stat_activity

📚 Summary

  • Choose isolation level per workload; default is often fine
  • Keep transactions short, index predicates, consider SKIP LOCKED
  • Monitor long transactions and bloat; tune autovacuum

Tomorrow (Day 13): Security Best Practices.

PostgreSQL #SQL #Transactions #Concurrency #Day12


This content originally appeared on DEV Community and was authored by Hardik Kanajariya


Print Share Comment Cite Upload Translate Updates
APA

Hardik Kanajariya | Sciencx (2025-10-30T13:43:00+00:00) Day 12: Transactions & Concurrency – PostgreSQL in 15 Days. Retrieved from https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/

MLA
" » Day 12: Transactions & Concurrency – PostgreSQL in 15 Days." Hardik Kanajariya | Sciencx - Thursday October 30, 2025, https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/
HARVARD
Hardik Kanajariya | Sciencx Thursday October 30, 2025 » Day 12: Transactions & Concurrency – PostgreSQL in 15 Days., viewed ,<https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/>
VANCOUVER
Hardik Kanajariya | Sciencx - » Day 12: Transactions & Concurrency – PostgreSQL in 15 Days. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/
CHICAGO
" » Day 12: Transactions & Concurrency – PostgreSQL in 15 Days." Hardik Kanajariya | Sciencx - Accessed . https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/
IEEE
" » Day 12: Transactions & Concurrency – PostgreSQL in 15 Days." Hardik Kanajariya | Sciencx [Online]. Available: https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/. [Accessed: ]
rf:citation
» Day 12: Transactions & Concurrency – PostgreSQL in 15 Days | Hardik Kanajariya | Sciencx | https://www.scien.cx/2025/10/30/day-12-transactions-concurrency-postgresql-in-15-days/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts