Database Locking (Row vs Table)

Why thousands of concurrent writes can bring your database to a crawling halt.

The idea

When you update a database, you don't want someone else editing the same data at the exact same millisecond. To prevent corruption, databases use Locks. If you use a Table Lock, you lock the entire spreadsheet—nobody else can write anything until you are done. This is safe, but incredibly slow. If 1,000 users buy tickets at once, they must form a single-file line. Modern databases use Row Locks instead. They only lock the specific row (e.g., Ticket #42) being edited, allowing 999 other people to safely buy different tickets at the exact same time.

Step 1: Alice wants to update Row 2. Bob wants to update Row 4.

How it works (Row-Level Locking)

In databases like PostgreSQL or MySQL (InnoDB), an UPDATE statement automatically grabs an Exclusive Row Lock on the rows being modified. The lock is held until the transaction COMMITs or ROLLBACKs. Other transactions that want to update those same rows will pause and wait. Transactions updating different rows proceed at full speed.

// 1. Automatic Row Lock (Implicit)
// Postgres locks ticket #42 until the transaction finishes.
// Bob buying ticket #99 is NOT blocked.
BEGIN;
UPDATE tickets SET owner = 'Alice' WHERE id = 42;
COMMIT;

// 2. Manual Row Lock (Explicit)
// Locks the row so NO ONE can even read it or update it 
// until you are done with your complex calculations.
BEGIN;
SELECT * FROM bank_account WHERE id = 1 FOR UPDATE;
-- Do complex math in NodeJS...
UPDATE bank_account SET balance = new_calc WHERE id = 1;
COMMIT;

Cost

Row locks consume RAM. If you run a massive query like UPDATE users SET active = false that touches 10 million rows, the database doesn't want to create 10 million individual row locks (it would run out of memory). Instead, it performs Lock Escalation: it throws away the row locks and grabs one giant Table Lock. Your entire system will freeze until that massive update finishes.

Watch out for