00:00

OK, sometimes you can lock tables.

16 January 2023

Previously, I wrote that you should never lock tables. And you usually shouldn’t! But sometimes, there’s a good reason to. Here’s one.

When you are doing a schema-modifying operation, like adding a column to a table, PostgreSQL needs to take an ACCESS EXCLUSIVE lock on the table while it is modifying the system catalogs. Unless it needs to rewrite the table, this lock isn’t held for very long.

However, locks in PostgreSQL are first-come, first-served. If the system is busy, there may be conflicting locks on the table that you are attempting to modify. (Even just a SELECT statement takes lock on the tables it is operating on; it just doesn’t conflict with much.) If the ALTER TABLE statement can’t get the lock right away, it enters a queue, waiting to get to the front and get the lock.

However, now, every lock after that enters the queue, too, behind that ALTER TABLE. This can create the result of a long-running ACCESS EXCLUSIVE lock, even though it’s not granted. On a busy table on a busy system, this can shut things down.

So, what to do?

You can do this:

DO $$
   BEGIN
   FOR i IN 1 .. 1000 LOOP
      BEGIN
         LOCK TABLE t NOWAIT;
         ALTER TABLE t ADD COLUMN i BIGINT;
         RETURN;
      EXCEPTION WHEN lock_not_available THEN
         PERFORM pg_sleep(1);
         CONTINUE;
      END;
   END LOOP;
   RAISE lock_not_available;
   END;
$$;

This loops until it can acquire the lock, but doesn’t sit in the queue if it can’t. Once it acquires the lock, it does the modification and exits. If it can’t acquire the lock after a certain number of cycles, it exits with an error (you can set the number of cycles to anything, and you can adjust time it sleeps after failing to get the lock).

There are no comments yet.