Select for update / deadlock possibility?

Shouldn't isolation level also matter ?  What is the isolation level you are using ?

Hello!

Read Committed.

I extend the example: the concurrent connections are in transactions.

begin
select ... for update;
end;

Regards
dd

2018-01-02 12:31 GMT+01:00 Rakesh Kumar <rakeshkumar464@mail.com>:

On Tue, Jan 2, 2018 at 3:22 AM, Durumdara <durumdara@gmail.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

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:
/messages/by-id/200402161053.11142.xzilla@users.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.janes@gmail.com>:

Jeff Janes <jeff.janes@gmail.com> writes:

On Tue, Jan 2, 2018 at 3:22 AM, Durumdara <durumdara@gmail.com> wrote:

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

It is atomic, but you do have to worry about deadlocks.

I think by "atomic" the OP intends "all the row locks are magically
acquired at the same instant". Which they aren't, they're taken one
at a time. So if you have different transactions trying to lock
overlapping sets of rows, there's a risk of deadlock. Which will
be detected and one transaction will fail, but still you might wish
to avoid that.

The usual rule for that is "be sure all transactions acquire locks
in the same order". So just throw an "order by id" type of clause
into the SELECT FOR UPDATE, and you should be fine.

Personally, I'd still code the application to retry on deadlock
failures, just for robustness.

regards, tom lane