[pgpool-general: 4517] Re: pgpool and advisory locks

Glyn Astill glynastill at yahoo.co.uk
Fri Mar 4 21:41:21 JST 2016


> From: Štěpán Pilař <pilar at enerfis.cz>
>To: "pgpool-general at pgpool.net" <pgpool-general at pgpool.net> 
>Sent: Friday, 4 March 2016, 12:12
>Subject: [pgpool-general: 4516] pgpool and advisory locks
> 
>
>
>Hi,
>
>
>
>we're testing pgpool setup with two servers with streaming replication. Everything works alright, but we've encountered an issue with advisory locks.
>
>
>
>When our application uses advisory locks, in some cases two processes can acquire the same lock (i.e. process A begins a transaction, acquires the lock, process B begins a transaction, acquires the same lock, process A commits, process B runs a query which blows up). We reduced the situation to:
>
>
>
>BEGIN;
>
>SELECT * FROM pg_locks WHERE locktype = 'advisory';
>
>SELECT pg_try_advisory_xact_lock(123, 0);
>
>SELECT * FROM pg_locks WHERE locktype = 'advisory';
>
>ROLLBACK;
>
>
>
>where the second select does not return any record in some of the cases.
>
>
>
>The documentation describes the rules pgpool uses to determine which queries can be sent to standby and which must go to primary (http://www.pgpool.net/docs/latest/pgpool-en.html#load_balance_in_stream_mode). Among those that are always handled by the primary is LOCK command. Advisory locks are, however, acquired through a SELECT. Are we correct to think the problem lies in SELECT being routed to either of those two instances?
>
>
>
>If so, is there a simple solution to ensure consistent behavior in our setup? The documentation mentions a possibility to provide rules to route certain queries to specific nodes, but I haven't found an example of such configuration. Are there any resources available?
>
>


Have you tried adding the relevant advisory lock functions into the "black_function_list" parameter?

See:    http://www.pgpool.net/docs/latest/pgpool-en.html


More information about the pgpool-general mailing list