close

Menu

Contact Info

Folow us on social

What’s blocking your way?

  • 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
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

2 Comments

Leave a comment

Your email address will not be published. Required fields are marked *