Lock rows as readonly

[OOelsner]

Hi.

I have a question about locks.
How can I prevent updates, if another users has a readonly cursor on the row ?

Example
I have two transactions which holds a cursor (read) on one row.
How can I prevent that a third transaction updates this row ?

Thanks in advance
Olaf Oelsner

[jmodic]

The one who wants to lock his rows must open a cursor with FOR UPDATE option, eg

SELECT ..... FROM .... WHERE ... FOR UPDATE;

This will lock all rows that meet the WHERE conditions.

[julian]

Start the 3rd transaction wih SET TRANSACTION READ ONLY;
Thus it will not be able to update the rows.

[OOelsner]

Thanks for your answers.

The first and second transaction do only read operations.

The third transaction should to check if the rows are in use by another transaction, before it tries to update.

In other words:
How can the a transaction check whether rows are currently red by another transactions.

[jmodic]

Originally posted by OOelsner
In other words:
How can the a transaction check whether rows are currently red by another transactions.

No way to find this that I'm aware of, unless the rows are locked by the reading transaction.

[julian]

No way to find this that I'm aware of, unless the rows are locked by the reading transaction.

I also think so. And moreover, if Jurij doesn't know a way, few are the people that might know!