How to force PostgreSQL function run sequentially

Question

I have a PostgreSQL function A. Many clients will call A:

- client X1 send query 1 "SELECT A();" then
- client X2 send  query 2 "SELECT A();" then
- client X3 send query 3  "SELECT A();" then
...

How to force function A to run sequentially?

Mean that force: query 1 run --> finish or timeout --> query 2 run --> finish or timeout --> query run --> finish or timeout ... (not allow query 1 and query 2 run simultaneously)

klin · Accepted Answer · 2017-11-06 02:39:16Z

6

Use advisory locks.

The first command in the function body should be (1234 is an exemplary integer constant):

perform pg_advisory_xact_lock(1234);

When two concurrent sessions call the function, one of them will wait until the function in the second one completes. This is a transaction-level advisory lock, automatically released when a transaction terminates.

Alternatively, you can use a session-level advisory lock, which can (should) be manually released:

create function example()
returns void language plpgsql as $$
begin
    perform pg_advisory_lock(1234);
    --
    -- function's commands
    --
    perform pg_advisory_unlock(1234);
end $$;

Any advisory lock obtained in a session is automatically released at the end of the session (if it hasn't been released earlier).

edited Nov 6, 2017 at 2:39

answered Nov 5, 2017 at 18:00

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

TrungNT Over a year ago

Thank you, PostgreSQL document describe "pg_advisory_xact_lock works the same as pg_advisory_lock, except the lock is automatically released at the end of the current transaction and cannot be released explicitly". Can you explain it more detail: 1. When transaction error, is lock automatically released? 2. When transaction timeout, is lock automatically released?

klin Over a year ago

It's hard to say what transaction error means. When a statement terminates with an error (maybe because of a statement timeout) then the next statements are ignored (if such exist) and the transaction's rollback occurs (so transaction locks are released). If the transaction is cancelled then it is equivalent to rollback. See the edited answer if you're not sure whether to use transaction-level locks.

TrungNT · Accepted Answer · 2017-11-07 01:37:05Z

0

I think the full answer is use pg_advisory_xact_lock + idle_in_transaction_session_timeout in function A.

Query 2 will wait until query 1 is completed (lock auto released) or timeout (session auto killed and lock also auto released).

answered Nov 7, 2017 at 1:37

TrungNT

1431 silver badge7 bronze badges

2 Comments

klin Over a year ago

You don't have to worry about deadlocks as long as you act rationally. Advisory locks never causes deadlocks even if concurrent sessions use multiply nested locks, provided that the order of locks in time is the same in all sessions. To cause a deadlock you have to consciously run concurrent sessions with e.g. locks in reversed order. I don't think there are any reasons to try this.

klin Over a year ago

You may want to set idle_in_transaction_session_timeout for reasons not concerned with advisory locks, e.g. when it happens that connections hang due to communication (net) problems.

Collectives™ on Stack Overflow

How to force PostgreSQL function run sequentially

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related