Skip site navigation (1) Skip section navigation (2)

Re: pg_advisory_locks in a multithreaded application context

From: Vincent Ficet <jean-vincent(dot)ficet(at)bull(dot)net>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: pg_advisory_locks in a multithreaded application context
Date: 2011-06-30 08:30:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Merlin Moncure wrote:
> 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 (
>>    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
>>    EXECUTE PROCEDURE firmware_pre_insert_trigger_cb();
>> CREATE FUNCTION firmware_pre_insert_trigger_cb() RETURNS TRIGGER
>>    AS $_$
>>    fw_id INT;
>> SELECT FROM firmware fw INTO fw_id
>> WHERE (fw.type = new.type AND
>>       fw.version = new.version AND
>>       fw.build_id = new.build_id AND
>> = AND
>>       fw.ps_id = new.ps_id);
>> IF fw_id IS NULL THEN
>>   -- create the non-existing firmware
>>   RETURN new;
>>   -- skip firmware which already exists
>> END IF;
>> END;
>> $_$
>> 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:
> well a sharelock certainly isn't going to work.  share blocks row
> exclusive (see,
> 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).
Thanks for the tip.

It now works fine using a SHARE UPDATE EXCLUSIVE lock in the PRE INSERT
trigger. This does not conflict with the  ROW EXCLUSIVE lock which is
implicitely taken by the INSERT statement in add_firmware() function.

BTW I think there might be a thread safety issue in postgres, as I often
get a segfault when deadlocks occur (prior to applying the fix I just
described). For example, the following deadlock situation:

    DETAIL:  Process 7643 waits for RowExclusiveLock on relation 21060
of database 20535; blocked by process 7593.
            Process 7593 waits for RowExclusiveLock on relation 21060 of
database 20535; blocked by process 7643.
            Process 7643: SELECT
            Process 7593: SELECT
    HINT:  See server log for query details.
    QUERY:  INSERT INTO firmware (type, version, build_id, date, ps_id)
               VALUES (chip_type, firm_version, firm_build_id,
firm_date, firm_ps_id)
    CONTEXT:  PL/pgSQL function "add_firmware" line 31 at SQL statement


#0  0x0000003c46725742 in __strncpy_ssse3 () from /lib64/
#1  0x00007ffff5f1617f in pqParseInput3 (conn=0x7ffff0000da0) at
#2  0x00007ffff5f0cae7 in parseInput (conn=0x7ffff0000da0) at fe-exec.c:1493
#3  0x00007ffff5f0cc01 in PQgetResult (conn=0x7ffff0000da0) at
#4  0x00007ffff5f0d26b in PQexecFinish (conn=0x7ffff0000da0) at
#5  0x00007ffff5f0cee4 in PQexec (conn=0x7ffff0000da0,
query=0x7ffff612d340 "SELECT 1") at fe-exec.c:1648
#6  0x00007ffff612c344 in dbd_ping () from

Stack traces often occur in different flavours, but can always be
correlated with messages such as:

DBI error -9: unexpected field count in "D" message
DBI error -9: message contents do not agree with length in message type
"T" server sent data ("D" message) without prior row description ("T"

I'll try to set up a minimalist reproducer and see what can be done for
Note that this happens with both postgresql 8.4.7 and 9.0.4.



> merlin

In response to


pgsql-novice by date

Next:From: Odd HogstadDate: 2011-06-30 10:01:13
Subject: Re: Order-by and indexes
Previous:From: John MeredithDate: 2011-06-30 07:32:04
Subject: Re: Connecting to PostgreSQL server

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group