Monday, 25 August 2008

Lock, Stock and Two Fuming Developers

Oh Hai!

If you have the misfortune of having to deal with locks in your postgreSQL database, run the following to find out if there are any nasty ExclusiveLocks (whole table locks) on the loose:

select * from pg_locks where mode = 'ExclusiveLock';

There are 4 types of lock in postgreSQL, listed here in order of decreasing niceness:
  • AccessShareLock
  • RowShareLock
  • RowExclusiveLock
  • ExclusiveLock
If you're running some tests that create your lock condition it's sometimes useful to quickly look at what's happening on the console:

watch -n1 ps -U postgres u

Lastly, make sure your postgreSQL version doesn't have REPLICA printed on the side :-/

No comments: