Re: pg_advisory_locks in a multithreaded application context
От | Merlin Moncure |
---|---|
Тема | Re: pg_advisory_locks in a multithreaded application context |
Дата | |
Msg-id | BANLkTimVoZptY4eEKrveEe=YHoLxV+jidg@mail.gmail.com обсуждение исходный текст |
Ответ на | pg_advisory_locks in a multithreaded application context (Vincent Ficet <jean-vincent.ficet@bull.net>) |
Ответы |
Re: pg_advisory_locks in a multithreaded application context
|
Список | pgsql-novice |
On Wed, Jun 29, 2011 at 10:11 AM, Vincent Ficet <jean-vincent.ficet@bull.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
В списке pgsql-novice по дате отправления:
Предыдущее
От: Mike ThomsenДата:
Сообщение: Re: Locking out a user after several failed login attempts
Следующее
От: "Jean-Yves F. Barbier"Дата:
Сообщение: Re: Locking out a user after several failed login attempts