Skip to main content

PG.L3.RISKY_OPS_SINGLE_TX — Locks are held until the whole transaction commits

  • Category: safety
  • Level: 3
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

A lock taken inside a transaction is released when the transaction ends — not when the statement that took it finishes. Laravel wraps a migration's up() in one transaction, so a migration that alters three tables holds three locks simultaneously, and every one of them until the last statement commits.

The blocked window is not the sum of the operations. It is the total duration of the migration, applied to every table it touched — including the one it finished with in the first ten milliseconds.

The arithmetic that surprises people​

Three ALTER TABLEs of 2 s, 1 s and 40 s. Intuition says each table is blocked for its own step. What actually happens: the first table is locked for all 43 seconds, the second for 41, the third for 40. The cheap operations inherit the expensive one's duration.

That is why the fix is not "make the slow one faster".

Flagged​

// Two table-locking operations in one transaction hold both locks until the last commits:
DB::statement('ALTER TABLE orders ADD COLUMN note_a text');
DB::statement('ALTER TABLE customers ADD COLUMN note_b text');

Preferred​

// Put each locking operation in its own migration so its lock is taken and released alone:
DB::statement('ALTER TABLE orders ADD COLUMN note_a text');

One locking operation per migration file. It reads like bureaucracy and it is the difference between one lock at a time and all of them at once.

Deadlock, the other reason​

Two migrations that touch the same two tables in different orders can deadlock — PostgreSQL detects it and aborts one of them, mid-deploy, with half the work applied if the transaction wrapper was already off. Splitting the operations makes the lock order trivially consistent, because there is only ever one.

What the rule counts​

It counts lock-taking statements sharing a transaction, not statements in general. An INSERT, a SELECT, a SET — none of them are counted. The list of what takes a strong lock is derived from the classified statement stream rather than pattern-matched on text, so an ALTER TABLE spelled across three lines counts once and a table named alter_table_log counts not at all.

  • PG.L3.MISSING_LOCK_TIMEOUT — bound the wait for each of those locks.
  • PG.L3.CONCURRENTLY_IN_TRANSACTION — the opposite mistake: an operation that must not be in the transaction at all.

Sources​

  • PostgreSQL 18 — explicit locking — a lock held by a transaction is kept until that transaction commits or rolls back, not until the statement that acquired it completes

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • transaction_split — Give each strong lock its own migration, so each is released before the next is taken.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.

Raising the threshold​

pgsql.max_locks_per_transaction is how many distinct existing tables one transaction may strong-lock before this rule fires. It ships at 1.

// config/sqlens.php
'pgsql' => ['max_locks_per_transaction' => 2],

One is the honest default: two strong locks in one transaction is already the shape that turns a short operation into a queue behind the slower of them. Raising it is a statement that this project has measured its own tolerance, not a way to quieten the rule — a value that admits the migration you happen to be writing today admits every one after it.

A value that is not a positive integer is rejected by the configuration schema before the rule sees it, so the rule always receives a usable number.