Re: How to lock a row over a SELECT followed by an UPDATE

"Andrew Baker" <webmaster_at_vbusers.com> wrote in message news:c19b84e5.0407271054.54ae108a_at_posting.google.com...
| What is the best way to lock an individual row in the following
| scenerio:
|
| --TODO - LOCK THIS ROW
| -- Return the next id
| SELECT next_id
| INTO next_id_out
| FROM owner.my_id_table
| WHERE app_id = app_id_in;
|
| -- Update the next id on the table
| UPDATE owner.my_id_table
| SET next_id = next_id_out + 1
| WHERE app_id = app_id_in;
|
| I need to make sure that nothing changes the id table between me
| selecting the id and updating the table with the next available id.
|
| thanks
| andrew
|
| PS. yes I am new to oracle :)

First, make sure that you really want to do this -- if you're simply assigning surrogate key (ID) values, then you probably want to use a SEQUENCE object -- they avoid the serialization that this approach causes, but have the sometimes unwanted characteristic of allowing gaps between IDs (only a problem if you've got auditing requirements that disallow gaps in a series of IDs)

If you do need to have a table-based counter, here's the most reliable way to increment it:

    update owner.my_id_table
    set next_id = next_id +1
    where app_id = app_id_in

however, if the row is locked by another process (which is likely executing the same statement and has delayed committing or rolling back) then this process will hang until the other process's transaction completes. if you want to return control to this process rather than wait on a lock, you need to do one of the following before you update statement:

    select next_id
    into next_id_out
    from owner.my_id_table
    where app_id = app_id_in
    for update nowait;

    select next_id
    into next_id_out
    from owner.my_id_table
    where app_id = app_id_in
    for update wait 5;

the first raises an oracle error immediately if it cannot lock the row, the second waits up to 5 seconds to obtain the lock. the second syntax takes whatever number of seconds you want, but unfortunately the number of seconds has to be specified in a literal (not with a bind variable)

++ mcs Received on Wed Jul 28 2004 - 13:05:06 CEST