Re: Select for update / deadlock possibility?

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