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

Поиск
Список
Период
Сортировка
От Durumdara
Тема Correct way for locking a row for long time without blocking another transactions (=nowait)?
Дата
Msg-id CAEcMXhnzPiu+frtLDXdjUYjN+aLPGPafDcA=js9RKC5-YwVNNQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Correct way for locking a row for long time without blocking another transactions (=nowait)?  (Filip Rembiałkowski <plk.zuber@gmail.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: chinnaobi
Дата:
Сообщение: archive_cleanup_command recovery.conf Standby server error
Следующее
От: bboett@free.fr
Дата:
Сообщение: how to create data on the fly?