4

I am having issues with postgres advisory locks not acquiring correctly on postgres 9.4.4. If I ssh into a postgres server on two screens and open psql to acquire a lock on one and attempt to aquire the lock on another it works perfectly. However, if I do it from another server that is pointed to that server I am able to freely 'acquire' locks, but it never actually gets a lock from the database.

Normally, we use python to obtain a lock and that is where we initially noticed the issue. To obtain a lock manually I am using select pg_advisory_lock(123456789); to check which locks are out currently I am using select objid from pg_locks where locktype = 'advisory';

I will play it out here so you can visually see it and tell me what I am doing.

Attempting to get a lock using an app_server (remote server using pgbouncer) but it is failing.

app_server> psql
app_server> \c mydb
app_server> select pg_advisory_lock(123456789);
 pg_advisory_lock 
------------------

(1 row)

app_server>select objid from pg_locks where locktype = 'advisory';
 objid 
-------
(0 rows)

Using db_server to get a lock, then trying to get the lock again on app_server (remote) but on the same db. 

db_server> psql
db_server> \c mydb
db_server> select pg_advisory_lock(123456789);
 pg_advisory_lock 
------------------

(1 row)

db_server> select objid from pg_locks where locktype = 'advisory';
   objid   
-----------
 123456789
(1 row)

Here you can see that db_server has the lock so I will now go back to app_server and attempt to get the same lock but this time it will work as expected where it will wait for the unlock from db_server. 

app_server> select pg_advisory_lock(123456789);
...

Meanwhile, I will go unlock it from db_server.

db_server> select pg_advisory_unlock(123456789);

Immediately app_server gets and releases the lock. 

servers> select objid from pg_locks where locktype = 'advisory';
 objid 
-------
(0 rows)
Jared Mackey
  • 3,998
  • 4
  • 31
  • 50

1 Answers1

3

The problem is that the broken servers were using transaction based connections to postgres while the working servers were using session based connections in the pgbouncer.ini. Not sure how this was missed in our initial look at the ini. 

; When server connection is released back to pool:
;   session      - after client disconnects
;   transaction  - after transaction finishes
;   statement    - after statement finishes
pool_mode = session
Jared Mackey
  • 3,998
  • 4
  • 31
  • 50