Thread: Select for update / deadlock possibility?
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?
Which one I need to redefine temporarily?
Somebody wrote statement_timeout, but why PG have lock_timeout then?
Thank you for your help!
Regards
dd
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>:
Shouldn't isolation level also matter ? What is the isolation level you are using ?
On Tue, Jan 2, 2018 at 3:22 AM, Durumdara <durumdara@gmail.com> wrote:
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.
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.
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:
Here lock_timeout:
https://stackoverflow.com/questions/20963450/controlling-duration-of-postgresql-lock-waits
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>:
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 cThere 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.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
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