Обсуждение: Correct way for locking a row for long time without blocking another transactions (=nowait)?

Hi!

In FireBird I can set the transaction to "nowait".
When I want to protect a row for long time (showing a dialog, and on
closing I release the row), I need to do this:

trans.StartTransaction();
sql.execute('update thetable set afield = afield where idfield = anyvalue');

This is locking the row with id "anyvalue".

If anyone trying to so something with this row (update, delete) from
another transaction, the FireBird generates an error to show: the row
is locked.

On the dialog closing I simply do commit or rollback what is
eliminates the lock on row.

I search for same mechanism in PG...

But: as I see the Lock Table (where I can set nowait) is for only
short transactions, because it is not define the row, it is holding
the table fully.

Another way is when I starting a transaction and update a row, and
waiting, but then all other transactions are trying to update this row
are waiting for me... (they are blocked!).

I want to protect the row, but other transactions mustn't blocked on
this, they rather generate an error after 200 msec (for example), when
they are saw the row locked.

Maybe the solution is the usage of advisory locks, but advisory locks
are don't preventing the changes on the real record, if a procedure or
sql statement don't checking this adv lock existance, it is is simply
overwrite my data...
Or we must use beforeupdate and beforedelete trigger what first
everytime checking the existence of advisory lock by ID?

Hmmm...

Thanks for your every idea:
    dd

On Tue, Feb 28, 2012 at 10:26 AM, Durumdara <durumdara@gmail.com> wrote:
> Hi!
>
> In FireBird I can set the transaction to "nowait".
> When I want to protect a row for long time (showing a dialog, and on
> closing I release the row), I need to do this:
>
> trans.StartTransaction();
> sql.execute('update thetable set afield = afield where idfield = anyvalue');
>
> This is locking the row with id "anyvalue".
>
> If anyone trying to so something with this row (update, delete) from
> another transaction, the FireBird generates an error to show: the row
> is locked.
>
> On the dialog closing I simply do commit or rollback what is
> eliminates the lock on row.
>
> I search for same mechanism in PG...
>
> But: as I see the Lock Table (where I can set nowait) is for only
> short transactions, because it is not define the row, it is holding
> the table fully.
>
> Another way is when I starting a transaction and update a row, and
> waiting, but then all other transactions are trying to update this row
> are waiting for me... (they are blocked!).
>
> I want to protect the row, but other transactions mustn't blocked on
> this, they rather generate an error after 200 msec (for example), when
> they are saw the row locked.
>
> Maybe the solution is the usage of advisory locks, but advisory locks
> are don't preventing the changes on the real record, if a procedure or
> sql statement don't checking this adv lock existance, it is is simply
> overwrite my data...
> Or we must use beforeupdate and beforedelete trigger what first
> everytime checking the existence of advisory lock by ID?


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;

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

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.

Naturally, you wil have to catch all kinds of locking /timeout errors
in application code (or in a stored procedure).


HTH,
Filip

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

On Wed, Feb 29, 2012 at 8:18 AM, Durumdara <durumdara@gmail.com> wrote:

> 2012/2/28 Filip Rembiałkowski <plk.zuber@gmail.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.