Re: Select for update / deadlock possibility?

Dear Jeff!

So. I start this question from more far.
I need to protect some resources.
All modifications started with StartTransaction.
Then I try to lock the articles by ids (to prevents other client's
modifications).
After that I insert / modify needed data.
Then I commit or rollback.

The locks will vanish on the end of the transaction, so resources
accessable again for different session.

If A session locks 1. articles, B session waits for the end of the
transaction of A.

From the help I didn't know that these row locks are created by one by one
- so it could cause deadlock on unended waiting.

In this flame they talk about statement_timeout:
https://www.postgresql.org/message-id/200402161053.11142.xzilla%40users.sourceforge.net

Here lock_timeout:
https://stackoverflow.com/questions/20963450/controlling-duration-of-postgresql-lock-waits

And you deadlock_timeout... :-) :-) :-)

Thanks

dd

2018-01-02 15:02 GMT+01:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> On Tue, Jan 2, 2018 at 3:22 AM, Durumdara <durumdara(at)gmail(dot)com> wrote:
>
>> 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.
>>
>
>
> It is atomic, but you do have to worry about deadlocks. Being atomic
> doesn't mean it can't deadlock, it just means that if it does deadlock, all
> the work in the transaction is rolled back together.
>
>
>>
>>
>> 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
>>
>
> There is no obligation for it to lock rows in the order they appear in the
> IN-list. Maybe that is why you can't simulate it.
>
>
>
>>
>> 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)
>>
>
> Deadlocks are automatically detected and one session is dealt an ERROR to
> resolve them. So deadlock_timeout is the only timeout you need care about.
>
>
>>
>> Somebody wrote statement_timeout, but why PG have lock_timeout then?
>>
>
> They do different things, and give you different error messages when they
> fire so that you know more about what the problem was (I'm too slow, versus
> I'm stuck behind someone else).
>
> Cheers,
>
> Jeff
>
>