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