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

From: Durumdara <durumdara(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Correct way for locking a row for long time without blocking another transactions (=nowait)?
Date: 2012-02-28 09:26:09
Message-ID: CAEcMXhnzPiu+frtLDXdjUYjN+aLPGPafDcA=js9RKC5-YwVNNQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message bboett 2012-02-28 09:48:13 how to create data on the fly?
Previous Message chinnaobi 2012-02-28 06:36:08 archive_cleanup_command recovery.conf Standby server error