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

On Wed, Feb 29, 2012 at 8:18 AM, Durumdara <durumdara(at)gmail(dot)com> wrote:

> 2012/2/28 Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>:

>> 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?

SET statement_timeout TO DEFAULT;

>> 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?

Yes, you _can_ get into long waiting siutuation this way.

> 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?
>

yes I think so.