Locks & Concurrency

Introduction

Locks serialize concurrent access to rows/tables to keep data consistent. Used implicitly during transactions; explicitly via SELECT ... FOR UPDATE.

Understanding the topic

Core concepts to understand:

• Row-level vs table-level locks.
• FOR UPDATE locks selected rows for write.
• Deadlock: two txs each hold a lock the other wants.
• MVCC (Postgres, Oracle) lets readers not block writers.
• Detect deadlocks and retry from the app.

Syntax reference

Visual workflow / architecture:

tx1 holds row 1 lock, wants row 2
tx2 holds row 2 lock, wants row 1
              │
              ▼
        DEADLOCK → DB aborts one
              │
              ▼
        app retries

Real-world use

High-throughput payment systems and inventory apps see deadlocks daily — they handle them with idempotent retries.

Best practices

• Always lock rows in the same order across services.
• Use SELECT FOR UPDATE on critical rows you'll update.
• Retry on deadlock errors automatically.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is a deadlock?
• Q2. SELECT FOR UPDATE — when?
• Q3. MVCC — what is it?
• Q4. Lock ordering — why important?
• Q5. How does Postgres detect deadlocks?