Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Unhappy Unanswered: Locking to modify

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

  3. #3
    Join Date
    Jun 2003
    Posts
    4

    Question

    >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

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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.

    Best regards

    Eric
    --
    IT-Consulting Herber
    WWW: http://www.herber-consulting.de
    Email: eric@herber-consulting.de

    ***********************************************
    Download the IFMX Database-Monitor for free at:
    http://www.herber-consulting.de/BusyBee
    ***********************************************

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •