Eight levels, and what conflicts with what

A lock is a promise about what else may happen

Every statement takes a lock on the tables it touches, and holds it until its transaction ends. A SELECT takes ACCESS SHARE. An INSERT, UPDATE or DELETE takes ROW EXCLUSIVE. Neither of those conflicts with the other, which is why a database can serve thousands of reads and writes at once without anybody waiting.

The eight levels are not a scale of severity so much as a set of promises. What matters is not how strong a lock sounds but which other locks it refuses to sit beside — and that is a table, not an ordering.

There is one level you have to know by name. ACCESS EXCLUSIVE conflicts with everything, including ACCESS SHARE. A statement holding it stops not only every write but every read, on that table, for as long as it holds.

Almost every DDL statement takes ACCESS EXCLUSIVE. The question is never whether it takes one — it is how long it holds it.

Before you run anything

Here is the shop's orders table: forty-one million rows, a hundred and twenty queries a second going through it. You are about to add a nullable column with no default.

ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

The lock level is not the danger. The duration is

Two statements can take the identical lock and differ by four orders of magnitude in how long they hold it. Adding a nullable column takes ACCESS EXCLUSIVE for about two milliseconds. Changing a column's type takes the same lock and holds it for as long as it takes to rewrite every row — minutes, on a table this size.

This is why 'what lock does it take' is only half the question. The other half is 'and for how long', and the answer to that depends on the size of the table, which is the one thing your test database does not have.

ALTER TABLE orders ALTER COLUMN customer_id TYPE integer;

Both of those statements pass a test database with forty rows in it in under a millisecond. The difference only exists at production scale.

The matrix is not an ordering

It is tempting to read the eight levels as a ladder and assume anything above a certain rung blocks anything below it. That is wrong in a way that matters.

SHARE UPDATE EXCLUSIVE — the lock a concurrent index build takes — does not conflict with ROW EXCLUSIVE, so writes carry on right through it. SHARE, one rung up, does conflict with ROW EXCLUSIVE, so a plain index build stops every write. Two adjacent levels, opposite consequences.

SHARE UPDATE EXCLUSIVE does conflict with itself, which is why you cannot run two concurrent index builds on the same table at once.

MySQLMySQL 8's InnoDB has its own online-DDL algorithm table rather than a lock matrix, and the equivalent question is which operations are INPLACE and whether they permit concurrent DML. The shape of the reasoning is the same; none of the level names carry over.