- Denish Patel
- 8th November 2010
- postgresql
If you’ve ever gotten a page about database connections are blocked, or phone call or email from an annoyed user whose transaction just won’t go through, or from a developer who can’t understand why application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked.
Omniti’s labs project Tasty Treats for PostgreSQL provides useful tools for proactive database monitoring . email_locked_queries is the new addition to this toolset . This tool can be handy for proactive lock monitoring in your database to find the blocked and blocking sessions and it sends out email notification if the there is a connection waiting for lock(s) for more than 10 minutes. It’s trivial to change the query if you want to be notified at different threshold.
Let’s test the query tool and see the results by creating locking scenario:
postgres=# create table testlock(foo text,bar text);
CREATE TABLE
postgres=# insert into testlock values (‘lock’,’me’);
INSERT 0 1 postgres=# insert into testlock values (‘find’,’me’);
INSERT 0 1
postgres=# select * from testlock;
foo | bar
——+—–
find | me
CREATE TABLE
postgres=# insert into testlock values (‘lock’,’me’);
INSERT 0 1 postgres=# insert into testlock values (‘find’,’me’);
INSERT 0 1
postgres=# select * from testlock;
foo | bar
——+—–
find | me
lock | me
(2 rows)
Session 1:
postgres=# begin;
BEGIN
postgres=# update testlock set foo='hang' where foo='lock';
UPDATE 1
And
Session 2, try to update same row:postgres=# begin;
BEGIN
postgres=# update testlock set foo='escapeme' where foo='lock';
This statement will hang, blocked by the lock that Session 1 is holding on the row.
I ran the query tool and I got this in my email:
-[ RECORD 1 ]
--------+-----------------------------------------------------
locked_relation |
locked_mode | ShareLock
blocked_pid | 9468
blocked_user | postgres
blocked_statement | update testlock set foo='escapeme' where foo='lock';
blocked_client_addr |
blocked_query_age | 00:10:28.98475
blocking_pid | 8056
blocking_user | postgres
blocking_statement | IDLE in transaction
blocking_client_addr |
blocking_query_age | 00:10:24.403767
blocking_xact_age | 00:10:58.529881
Let me know if you have suggestions to make it more better.
Join the conversation
Leave a comment
Leave a comment
Categories
Recent Blog
- Do you really need a DBA?
- Running VACUUM FULL in Non-blocking Mode
- Connection Scaling
- PSQL Helper: Managing Connections and Simplifying Queries
- Vacuum Those MVs!
- Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
- Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
- Working with Amazon Aurora PostgreSQL: what happened to the stats?
- How to set application_name for psql command line utility?
- Is there a limit on number of partitions handled by Postgres?
Just one suggestion, based on the very similar script I wrote just before reading this post: make the email address a variable, set at the beginning of the script, so it's easy to modify.
@Joshua Thanks for your suggestion.