postgresql concurrent queries debug

Question

There is a multithreaded application executing some PL/pgsql function. That function produces record inserts to a critically important resource( table ). Also it executes some select/update/etc operations while executing.

The issue is, sometimes we face duplicate( 2-3 ) records each one passed to the function in a parallel thread. And they all are inserted into table as a function execution result, while they should not.

It happens, because both transactions are executed in parallel, and have no idea that the same record is being prepared to insert in a parallel transaction.

The table is critically important and all kinds of LOCK TABLE are extremely not welcomed (LOCK FOR SHARE MODE meanwhile gave as some useful experience).

So, the question is, is there any best practice how to organize PL/pgsql function working with a critical resource (table) to be executed by multithreaded app and producing no harmful locks on this resource?

PS. I know, that some thread partinioning by record.ID in the app is a possible solution. But I.m interested in a PL/pgsql solution first of all.

Is it safe to assume that you either do not have a Primary/Unique key, or that by "duplicates", you mean attributes other than the Primary/Unique key? Locking or serialized access are the only ways to ensure the uniqueness of your entries, or at least that I can think of. A resource available in Postgresql 9.2+ is Serializable Snapshot isolation (wiki.postgresql.org/wiki/SSI) but that might be a bit heavyweight for your needs. — bma
– bma, Commented Aug 1, 2013 at 14:43
We have a PK constraint, but it is a logical duplicateness, not a straightforward field-by-field matching. And Serializable Isolation level is not a acceptable solution for us: it produces serialization access denies for parallel transaction that can unpredictably affect application behavior. Thanks for advice anyway! — xacinay
– xacinay, Commented Aug 1, 2013 at 15:02
If your primary key allows duplicates, it's not a primary key is it? What precisely do you mean by duplicateness. — Richard Huxton
– Richard Huxton, Commented Aug 1, 2013 at 15:27
Well @Richard Huxton you are right about that. Building the Unique index is a possible solution no doubt. But im looking for more flexible solution (pg_advisory_lock seems to be the best candidate at the moment) — xacinay
– xacinay, Commented Aug 1, 2013 at 15:59

Ihor Romanchenko · Accepted Answer · 2013-08-01 15:51:51Z

2

Sometimes you can use a advisory locks - http://www.postgresql.org/docs/current/static/explicit-locking.html .With these locks some subset of numbers. I used it for synchronization of parallel inserts with success.

edited Aug 1, 2013 at 15:51

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

answered Aug 1, 2013 at 14:48

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xacinay Over a year ago

Unfortunately advisory_lock cant do what I would like him to do. Actually, expected to find a database engine module, that would allow me to declare some lock rule like 'select do_lock(*) from resource_tbl where id between 1 and 100' that would mean database will not allow to insert new records with id from interval from 1 to 100. All existing techniques, as far as I realised, allow me to lock some already existing records only. Anyway, I came to the conclusions I needed, thanks for paying attention to the question!

Collectives™ on Stack Overflow

postgresql concurrent queries debug

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related