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

[Filip Rembiałkowski]

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

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Correct way for locking a row for long time without blocking another transactions (=nowait)?

[Durumdara]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Filip Rembiałkowski]

On Tue, Feb 28, 2012 at 10:26 AM, Durumdara durumd...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Durumdara]

Dear Filip!

2012/2/28 Filip Rembiałkowski plk.zu...@gmail.com:
 On Tue, Feb 28, 2012 at 10:26 AM, Durumdara durumd...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general