Re: Correct way for locking a row for long time without blocking another transactions (=nowait)?

Dear Filip!

2012/2/28 Filip Rembiałkowski <plk.zuber@gmail.com>:
> On Tue, Feb 28, 2012 at 10:26 AM, Durumdara <durumdara@gmail.com> wrote:
> Just some loose comments.
>
> http://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-ROWS
>
> A way to explicitly lock given row without updating it:
> SELECT whatever FROM thetable WHERE id=123 FOR UPDATE NOWAIT;

Thanks, that was I have been searching for this time.

>
> A way to force error when any statement takes more than 200 msec:
> SET statement_timeout TO '200ms';

As I see that is not needed here.
Only for normal updates.

And how I can "reset" statement_timeout after this command to "default" value?

>
> The waiting that you observed is normal - there is no way in
> PostgreSQL to force _other_ transactions into NOWAIT mode. All
> transactions that do not want to wait, should use explicit locking
> with NOWAIT option.

If I understand it well, I must follow NOWAIT schema for update to
avoid long updates (waiting for error).

1.) I starting everywhere with select for update nowait
2.) Next I make update command
3.) Commit

So if I starting with point 2, I got long blocking because of waiting
for release row lock?

But as I remember in PGSQL there is the read committed iso-level the default.
This meaning that rows are same in on "select for", after they can change.

May the solution is if PGSQL support that:

create temporary table tmp_update as
select id from atable
where ...

select * from atable for update nowait
where id in (select id from tmp_update)

update atable set value = 1
where id in (select id from tmp_update)

Is this correct?


Thanks for it:
   dd