In the below trace, a lock is considered dangerous if it conflicts with application code queries, such as SELECT or INSERT.
We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.
For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an AccessExclusiveLock while backfilling can do with a RowExclusiveLock which is much less likely to block concurrent transactions.
Overall Summary
Started at
Total duration (ms)
Number of dangerous locks
2024-05-05T14:10:03.189525880+00:00
1005
2 ❌
All locks acquired
Schema
Object
Mode
Relkind
OID
Safe
Duration held (ms)
public
books
AccessExclusiveLock
Table
16415
❌
1002
public
books
ShareRowExclusiveLock
Table
16415
❌
1002
public
books
AccessShareLock
Table
16415
✅
1002
public
books_pkey
AccessShareLock
Index
16421
✅
1002
Dangerous locks found
AccessExclusiveLock would block the following operations on public.books:
SELECT
FOR UPDATE
FOR NO KEY UPDATE
FOR SHARE
FOR KEY SHARE
UPDATE
DELETE
INSERT
MERGE
ShareRowExclusiveLock would block the following operations on public.books:
UPDATE
DELETE
INSERT
MERGE
Statement number 1 for 3 ms
SQL
create table authors(id serial primary key, name text not null);
Locks at start
No locks held at the start of this statement.
New locks taken
No new locks taken by this statement.
Statement number 2 for 0 ms
SQL
alter table books alter column title set not null;
Locks at start
No locks held at the start of this statement.
New locks taken
Schema
Object
Mode
Relkind
OID
Safe
public
books
AccessExclusiveLock
Table
16415
❌
Hints
Validating table with a new NOT NULL column
ID: make_column_not_nullable_with_lock
The column title in the table public.books was changed to NOT NULL. The statement blocks until all rows in the table are validated to be NOT NULL, unless a CHECK (title IS NOT NULL) constraint exists, in which case it is safe. Split this type of change into steps:
Add a CHECK (title IS NOT NULL) NOT VALID; constraint.
Validate the constraint in a later transaction, with ALTER TABLE ... VALIDATE CONSTRAINT.
Make the column NOT NULL
Statement number 3 for 0 ms
SQL
alter table books add column author_id integer not null;
Locks at start
Schema
Object
Mode
Relkind
OID
Safe
public
books
AccessExclusiveLock
Table
16415
❌
New locks taken
No new locks taken by this statement.
Hints
Running more statements after taking AccessExclusiveLock
ID: holding_access_exclusive
The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.
Statement number 4 for 1 ms
SQL
alter table books add foreign key (author_id) references authors(id);
Locks at start
Schema
Object
Mode
Relkind
OID
Safe
public
books
AccessExclusiveLock
Table
16415
❌
New locks taken
Schema
Object
Mode
Relkind
OID
Safe
public
books
ShareRowExclusiveLock
Table
16415
❌
public
books
AccessShareLock
Table
16415
✅
public
books_pkey
AccessShareLock
Index
16421
✅
Hints
Validating table with a new constraint
ID: validate_constraint_with_lock
A new constraint books_author_id_fkey was added to the table books. The constraint is of type FOREIGN KEY and is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints as NOT VALID and validate them later, to avoid holding dangerous locks for a long time. Constraints that are NOT VALID affect all new inserts and updates, but not existing data. Adding the constraint initially as NOT VALID, then validating with ALTER TABLE ... VALIDATE CONSTRAINT ... in a later transaction minimizes time spent holding dangerous locks.
Running more statements after taking AccessExclusiveLock
ID: holding_access_exclusive
The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.
Statement number 5 for 1001 ms
SQL
select pg_sleep(1);
Locks at start
Schema
Object
Mode
Relkind
OID
Safe
public
books
AccessExclusiveLock
Table
16415
❌
public
books
ShareRowExclusiveLock
Table
16415
❌
public
books
AccessShareLock
Table
16415
✅
public
books_pkey
AccessShareLock
Index
16421
✅
New locks taken
No new locks taken by this statement.
Hints
Running more statements after taking AccessExclusiveLock
ID: holding_access_exclusive
The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.
Statement number 6 for 0 ms
SQL
select * from books;
Locks at start
Schema
Object
Mode
Relkind
OID
Safe
public
books
AccessExclusiveLock
Table
16415
❌
public
books
ShareRowExclusiveLock
Table
16415
❌
public
books
AccessShareLock
Table
16415
✅
public
books_pkey
AccessShareLock
Index
16421
✅
New locks taken
No new locks taken by this statement.
Hints
Running more statements after taking AccessExclusiveLock
ID: holding_access_exclusive
The statement is running while holding an AccessExclusiveLock on the Table publicbooks, blocking all other transactions from accessing it. Once holding AccessExclusiveLock we should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.
Eugene 🔒 trace of
add_authors.sqlIn the below trace, a lock is considered dangerous if it conflicts with application code queries, such as
SELECTorINSERT.We should aim to hold dangerous locks for as little time as possible. If a dangerous lock is held while doing an operation that does not require it, we should split the migration into two steps.
For example, it often will make sense to add a new column in one migration, then backfill it in a separate one, since adding a column requires an
AccessExclusiveLockwhile backfilling can do with aRowExclusiveLockwhich is much less likely to block concurrent transactions.Overall Summary
All locks acquired
publicbooksAccessExclusiveLockpublicbooksShareRowExclusiveLockpublicbooksAccessShareLockpublicbooks_pkeyAccessShareLockDangerous locks found
AccessExclusiveLockwould block the following operations onpublic.books:SELECTFOR UPDATEFOR NO KEY UPDATEFOR SHAREFOR KEY SHAREUPDATEDELETEINSERTMERGEShareRowExclusiveLockwould block the following operations onpublic.books:UPDATEDELETEINSERTMERGEStatement number 1 for 3 ms
SQL
Locks at start
No locks held at the start of this statement.
New locks taken
No new locks taken by this statement.
Statement number 2 for 0 ms
SQL
Locks at start
No locks held at the start of this statement.
New locks taken
publicbooksAccessExclusiveLockHints
Validating table with a new
NOT NULLcolumnID:
make_column_not_nullable_with_lockThe column
titlein the tablepublic.bookswas changed toNOT NULL. The statement blocks until all rows in the table are validated to beNOT NULL, unless aCHECK (title IS NOT NULL)constraint exists, in which case it is safe. Split this type of change into steps:CHECK (title IS NOT NULL) NOT VALID;constraint.ALTER TABLE ... VALIDATE CONSTRAINT.NOT NULLStatement number 3 for 0 ms
SQL
Locks at start
publicbooksAccessExclusiveLockNew locks taken
No new locks taken by this statement.
Hints
Running more statements after taking
AccessExclusiveLockID:
holding_access_exclusiveThe statement is running while holding an
AccessExclusiveLockon the Tablepublicbooks, blocking all other transactions from accessing it. Once holdingAccessExclusiveLockwe should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.Statement number 4 for 1 ms
SQL
Locks at start
publicbooksAccessExclusiveLockNew locks taken
publicbooksShareRowExclusiveLockpublicbooksAccessShareLockpublicbooks_pkeyAccessShareLockHints
Validating table with a new constraint
ID:
validate_constraint_with_lockA new constraint
books_author_id_fkeywas added to the tablebooks. The constraint is of typeFOREIGN KEYand is valid. The statement blocks until all rows in the table are validated for the constraint. It is safer to add constraints asNOT VALIDand validate them later, to avoid holding dangerous locks for a long time. Constraints that areNOT VALIDaffect all new inserts and updates, but not existing data. Adding the constraint initially asNOT VALID, then validating withALTER TABLE ... VALIDATE CONSTRAINT ...in a later transaction minimizes time spent holding dangerous locks.Running more statements after taking
AccessExclusiveLockID:
holding_access_exclusiveThe statement is running while holding an
AccessExclusiveLockon the Tablepublicbooks, blocking all other transactions from accessing it. Once holdingAccessExclusiveLockwe should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.Statement number 5 for 1001 ms
SQL
Locks at start
publicbooksAccessExclusiveLockpublicbooksShareRowExclusiveLockpublicbooksAccessShareLockpublicbooks_pkeyAccessShareLockNew locks taken
No new locks taken by this statement.
Hints
Running more statements after taking
AccessExclusiveLockID:
holding_access_exclusiveThe statement is running while holding an
AccessExclusiveLockon the Tablepublicbooks, blocking all other transactions from accessing it. Once holdingAccessExclusiveLockwe should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.Statement number 6 for 0 ms
SQL
Locks at start
publicbooksAccessExclusiveLockpublicbooksShareRowExclusiveLockpublicbooksAccessShareLockpublicbooks_pkeyAccessShareLockNew locks taken
No new locks taken by this statement.
Hints
Running more statements after taking
AccessExclusiveLockID:
holding_access_exclusiveThe statement is running while holding an
AccessExclusiveLockon the Tablepublicbooks, blocking all other transactions from accessing it. Once holdingAccessExclusiveLockwe should immediately commit the transaction. Any extra steps necessary are better done in a separate transaction.