Select for update / deadlock possibility?

From: Durumdara <durumdara(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Select for update / deadlock possibility?
Date: 2018-01-02 08:22:57
Message-ID: CAEcMXhnS+h8Luy7eosgkVHJgXZorjGQZ+9OP6TZrzkZWXCFgxg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Members!

I have to ask something that not clear for me from description, and I can't
simulate it.

Is "select for update" atomic (as transactions) or it isn't?

I want to avoid the deadlocks.

If it's atomic, then I don't need to worry about concurrent locks.
But I think it's not.

This is an example for deadlock:

a.) select * from test where id in (1, 3, 4)
b.) select * from test where id in (2, 4, 5)
c.) select * from test where id in (5, 1, 6)

If it's not atomic, then:

- a locks 1.
- b locks 2.
- c locks 5.
- a locks 3.
- b locks 4.
- c try to lock 1, but it locked by a
- a try to lock 4, but it locked by b
- b try to lock 5, but it locked by c

DEADLOCK!!!

As I read select for update doesn't support timeout.
I've found two timeout that could be affects on it.
Which one I need to redefine temporarily?

lock_timeout (integer)
statement_timeout (integer)

Somebody wrote statement_timeout, but why PG have lock_timeout then?

Thank you for your help!

Regards
dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alexander Farber 2018-01-02 09:28:14 Re: Selecting a JSON object of arrays from a PostgreSQL table
Previous Message Jov 2018-01-02 07:59:46 Re: Returning Values from INSERT ON CONFLICT DO NOTHING