Re: pg_advisory_locks in a multithreaded application context

On Wed, Jun 29, 2011 at 10:11 AM, Vincent Ficet
<jean-vincent(dot)ficet(at)bull(dot)net> wrote:
> Hello,
>
> I'm having some trouble trying to use postgresql locks. After trying
> several options and reading the postgresql online documentation, I still
> can't figure out what I'm doing wrong. Here's the use case:
>
> A multithreaded application collecting adapter firmwares on a network
> loads data into the following table:
>
> CREATE TABLE firmware (
>    id SERIAL NOT NULL,
>    type CHARACTER VARYING(32),
>    version CHARACTER VARYING(30),
>    build_id INTEGER,
>    date CHARACTER VARYING(25),
>    ps_id CHARACTER VARYING(25)
> );
>
> Typically, there are a few hundred adapters, but only 5 firmwares (many
> adapters should have the same firmware if the sysadmins did their jobs
> properly ;-) ).
>
> Only a single entry is required per firmware (many separate adapters can
> share the same firmware by pointing to the appropriate firmware id field).
> To make sure that only one entry is created per firmware, I use the
> following trigger:
>
> CREATE TRIGGER firmware_pre_insert_trigger
>    BEFORE INSERT ON firmware
>    FOR EACH ROW
>    EXECUTE PROCEDURE firmware_pre_insert_trigger_cb();
>
> CREATE FUNCTION firmware_pre_insert_trigger_cb() RETURNS TRIGGER
>    AS $_$
> DECLARE
>    fw_id INT;
> BEGIN
>
> SELECT fw.id FROM firmware fw INTO fw_id
> WHERE (fw.type = new.type AND
>       fw.version = new.version AND
>       fw.build_id = new.build_id AND
>       fw.date = new.date AND
>       fw.ps_id = new.ps_id);
>
> IF fw_id IS NULL THEN
>   -- create the non-existing firmware
>   RETURN new;
> ELSE
>   -- skip firmware which already exists
>   RETURN NULL;
> END IF;
>
> END;
> $_$
>    LANGUAGE PLPGSQL;
>
> When a thread wishes to add a firmware after discovering one adapter, it
> executes the following code:
>
> PERFORM pg_advisory_lock(1);
>
> INSERT INTO firmware (type, version, build_id, date, ps_id)
> VALUES (chip_type, firm_version, firm_build_id, firm_date, firm_ps_id);
>
> PERFORM pg_advisory_unlock(1);

Advisory lock is not going to work here. You are releasing the lock
before the transaction resolves and that leaves a window for second
transaction to do the 'select' and not see the data because it hasn't
committed yet.

> Unfortunately, I still get duplicated entries using advisory locks, and
> they don't seem to lock anything at all...
>
> On the other hand, If I use builtin locks as follows without the
> trigger, I get deadlocks in the server logs:
>
> LOCK TABLE firmware IN SHARE MODE;

well a sharelock certainly isn't going to work. share blocks row
exclusive (see http://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-TABLES),
so two transactions can simultaneously get a share lock and wait for
each other to to resolve to get the exclusive lock on a row.
'EXCLUSIVE' would be better (although that would effectively serialize
the transactions).

merlin