Utility to show what locks will be acquired by a given query.
Query is executed but not committed.
Warning
Don’t run this on a production DB.
The suggested strategy is to run this using a test DB to figure out the locks
And then use that information later if you need it.
Installation instructions
pip install pg_explain_locks
How this thing works
This tool runs a given query like this:
BEGIN-- Run given query-- Check which locks are takenROLLBACK
Example Usage
Examples shown against dvdrental sample database
pg_explain_locks --user DB_USER --password DB_PASSWORD --database DATABASE --host HOST --query "DROP table actor CASCADE" +-------------+----------------------------+---------------------+ | Relation ID | Relation Name | Lock Type | +-------------+----------------------------+---------------------+ | 16422 | actor | AccessExclusiveLock | | 16448 | film_actor | AccessExclusiveLock | | 16456 | actor_info | AccessExclusiveLock | | 16487 | film_list | AccessExclusiveLock | | 16506 | nicer_but_slower_film_list | AccessExclusiveLock | | 16557 | actor_pkey | AccessExclusiveLock | | 16588 | idx_actor_last_name | AccessExclusiveLock | +-------------+----------------------------+---------------------+
pg_explain_locks --user DB_USER --password DB_PASSWORD --database DATABASE --host HOST --query "SELECT * FROM film_actor fa JOIN actor a on a.actor_id=fa.actor_id FOR UPDATE" +-------------+---------------------+-----------------+ | Relation ID | Relation Name | Lock Type | +-------------+---------------------+-----------------+ | 16422 | actor | RowShareLock | | 16448 | film_actor | RowShareLock | | 16557 | actor_pkey | AccessShareLock | | 16569 | film_actor_pkey | AccessShareLock | | 16588 | idx_actor_last_name | AccessShareLock | | 16593 | idx_fk_film_id | AccessShareLock | +-------------+---------------------+-----------------+
pg_explain_locks --user DB_USER --password DB_PASSWORD --database DATABASE --host HOST --query "ALTER TABLE customer ADD COLUMN deleted BOOLEAN" +-------------+---------------+---------------------+ | Relation ID | Relation Name | Lock Type | +-------------+---------------+---------------------+ | 16411 | customer | AccessExclusiveLock | +-------------+---------------+---------------------+
