- Notifications
You must be signed in to change notification settings - Fork5
Command line utility to show what locks will be acquired by a given query.
License
AdmTal/PostgreSQL-Query-Lock-Explainer
Folders and files
Name | Name | Last commit message | Last commit date | |
---|---|---|---|---|
Repository files navigation
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 locksAnd then use that information later if you need it.
pip install pg_explain_locks
This tool runs a given query like this:
BEGIN
-- Run given query
-- Check which locks are taken
ROLLBACK
Examples shown againstdvdrental 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 |+-------------+---------------+---------------------+
Create a settings file at~/.pg_explain_locks_settings
in order to use the same DB settings every time.
Settings file contents :
USER=your_userPASSWORD=your_passwordDATABASE=your_databaseHOST=your_hostPORT=your_post
Usage:
pg_explain_locks "ALTER TABLE customer ADD COLUMN deleted BOOLEAN"+-------------+---------------+---------------------+| Relation ID | Relation Name | Lock Type |+-------------+---------------+---------------------+| 16411 | customer | AccessExclusiveLock |+-------------+---------------+---------------------+
About
Command line utility to show what locks will be acquired by a given query.
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Releases
Packages0
Uh oh!
There was an error while loading.Please reload this page.
Contributors2
Uh oh!
There was an error while loading.Please reload this page.