Locking and deadlocks

Blocked, not slow

A query that is doing nothing for thirty seconds is usually waiting on a lock, not working. The lock views tell you which session holds what and who is waiting, which turns a mystery into a specific culprit.

Deadlocks happen when two transactions acquire the same locks in opposite orders. The database detects the cycle and aborts one, and the durable fix is to always acquire locks in a consistent order.

Schema changes take heavy locks. An ALTER that rewrites a table blocks reads and writes for the duration, which on a large table means an outage.