PostgreSQL Query Lock Explainer
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_locksHow 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 |
+-------------+---------------+---------------------+Example usage with settings file
Create a settings file at ~/.pg_explain_locks_settings in order to use the same DB settings every time.
Settings file contents :
USER=your_user
PASSWORD=your_password
DATABASE=your_database
HOST=your_host
PORT=your_postUsage:
pg_explain_locks "ALTER TABLE customer ADD COLUMN deleted BOOLEAN"
+-------------+---------------+---------------------+
| Relation ID | Relation Name | Lock Type |
+-------------+---------------+---------------------+
| 16411 | customer | AccessExclusiveLock |
+-------------+---------------+---------------------+