From: | Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> |
---|---|
To: | Durumdara <durumdara(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Correct way for locking a row for long time without blocking another transactions (=nowait)? |
Date: | 2012-02-29 08:52:00 |
Message-ID: | CAP_rwwkRnSo1GyMiWSOcmfENju=bBeocsX7M-af05hMZeEn2Mw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | scheu_postgresql | 2012-02-29 09:25:51 | "invalid memory alloc request size" + "Could not open file "pg_clog/XXXX" |
Previous Message | Albe Laurenz | 2012-02-29 08:44:58 | Re: Yearly date comparison? |