pg_advisory_locks in a multithreaded application context
От | Vincent Ficet |
---|---|
Тема | pg_advisory_locks in a multithreaded application context |
Дата | |
Msg-id | 4E0B40B8.1010508@bull.net обсуждение исходный текст |
Ответы |
Re: pg_advisory_locks in a multithreaded application context
|
Список | pgsql-novice |
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); 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; SELECT f.id INTO firmware_id FROM firmware f WHERE (f.type = chip_type AND f.version = firm_version AND f.build_id = firm_build_id AND f.date = firm_date AND f.ps_id = firm_ps_id) LIMIT 1; IF firmware_id IS NULL THEN INSERT INTO firmware (type, version, build_id, date, ps_id) VALUES (chip_type, firm_version, firm_build_id, firm_date, firm_ps_id); END IF; Here's the log: 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 add_firmware('0x08003800013731aa','hca','512.1792.0',0,'0920-10-06','BL_0010030001000'); Process 7593: SELECT add_firmware('0x08003800013734b0','hca','512.1792.0',0,'0920-10-06','BL_0010030001000'); 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) Looking at #13.3.3. Deadlocks on http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html, I guess I'm probably in the same situation, but I can't figure out how that applies to threads and more importantly, how to deal with it. Note that each of the 16 threads uses its own connection to the database backend (postgresql 9.0.4) Serializing calls to add_firmware() on the client side by putting them in the same thread does not help much either, as there is not guarantee that the requests will be executed in a serialized manner on the server side. In this case, I also get deadlocks, although less frequently... Any idea is more than welcome ;-) Thanks Vincent
В списке pgsql-novice по дате отправления: