Concurrency Control

How a database lets many transactions run at once without corrupting data: locks and two-phase locking, optimistic version checks, MVCC, and how deadlocks are found and avoided.

Overview

A database serves many clients at the same time. Two of them can read and change the same row within a few milliseconds of each other. Without rules, one change can silently overwrite the other, or a reader can see a half-finished transfer. Concurrency control is the set of rules a database uses so that transactions running at the same time still leave the data correct.

There are two basic attitudes. A pessimistic scheme assumes conflicts will happen, so it locks data before using it and makes others wait. An optimistic scheme assumes conflicts are rare, so it lets everyone work and checks for a conflict only at write time, retrying the loser. Most modern databases add a third tool, MVCC (multi-version concurrency control): they keep several versions of a row so that readers see a stable snapshot while writers make new versions.

This chapter covers each mechanism, what it costs, and how the same ideas show up in PostgreSQL and MySQL's InnoDB engine. The next chapter, SQL Isolation Levels, builds on it: isolation levels are the promises, and the mechanisms here are how databases keep them.

What you will be able to do

  • Explain the lost update problem and fix it three ways: a row lock, a version check, or an atomic update.
  • Describe two-phase locking and why intention locks make table locks cheap to check.
  • Explain how PostgreSQL's MVCC stores row versions, and why long transactions cause bloat.
  • Walk through how a deadlock forms, how databases detect it, and how to prevent it.
  • Choose between optimistic and pessimistic control for a given workload, and say why.

Key concepts

  • Lost updates
  • Shared and exclusive locks
  • Two-phase locking
  • Intention locks
  • Optimistic concurrency
  • MVCC
  • VACUUM and old versions
  • Deadlock detection
  • SKIP LOCKED queues

Interview questions

  • What is a lost update, and how do you prevent it?
  • How does MVCC work in PostgreSQL, and what does it cost?
  • Explain two-phase locking. Does it guarantee serializability?
  • What are intention locks and why are they needed?
  • How do databases detect deadlocks, and how would you avoid them?
  • When would you choose optimistic over pessimistic concurrency?
  • Why can one long-running transaction slow down a PostgreSQL database?
  • How would you build a job queue on a relational database?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio