Suppose that you create a table called table1 in a database with log.

User A wants to modify a record of that table
I want nobody can modify that record while User A is doing it.

When User A decides to change the record I do the following:
1. BEGIN A TRANSACTION
2. SELECT FOR UPDATE of the record
3. FETCH the record

If other user wants to change this record and makes a SELECT FOR UPDATE of the record, he gets a error message.

The problem is that User A needs to take data from other tables to change the record of table1, so when I make a new SELECT (from other tables) the lock disappears and anyone can make a "select for update" of the record in table1.

How can I hold this record locked while making SELECTs in other tables.

Thanks.

As long as you stay in your transaction and
your update cursor is positioned on this row,
the lock will be held.

So simply don't move your update cursor away.

You can issue the select's on the other tables
in the same transaction.

>As long as you stay in your transaction and
>your update cursor is positioned on this row,
>the lock will be held.
>So simply don't move your update cursor away.

When I make a SELECT FOR UPDATE of the row I position an update cursor on this row. After this, if I make a select of other table to get some data the cursor moves, pointing to the row of this new table.

The only way I have found to hold the update cursor is to use Repeteable Read as isolation mode.

Thanks

This is not correct. An update cursor must be based
on a simple select. An this select could only include
a single table.
So you simply have to declare a second cursor for
the select on the other table. This second cursor works independently from your first update cursor, so you
don't have to move your first cursor away from that row.

If for any other reason you need to move your first
update cursor away from that row and want to ensure
that the row will still be locked, there are three possibilities
to get the job done:

1) set isolation to repeatable read
-> this places a shared lock on each row fetched and
the lock will be held until the end of the transaction

2) set isolation to cursor stability retain update locks
-> this syntax is also possible with 'committed read'.
It ensures that update locks are not removed if
you move the update cursor away from that row.

3) make a dummy update of the row
-> this would place an exclusive lock on the row,
you could move your cursor away from that row,
the exclusive lock is held until the end of the
transaction

Good luck.