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