READ COMMITTED database isolation level in oracle

Question

I'm working on a web app connected to oracle. We have a table in oracle with a column "activated". Only one row can have this column set to 1 at any one time. To enforce this, we have been using SERIALIZED isolation level in Java, however we are running into the "cannot serialize transaction" error, and cannot work out why.

We were wondering if an isolation level of READ COMMITTED would do the job. So my question is this:

If we have a transaction which involves the following SQL:

SELECT *
FROM MODEL;

UPDATE MODEL
SET ACTIVATED = 0;

UPDATE MODEL
SET ACTIVATED = 1
WHERE RISK_MODEL_ID = ?;

COMMIT;

Given that it is possible for more than one of these transactions to be executing at the same time, would it be possible for more than one MODEL row to have the activated flag set to 1 ?

Any help would be appreciated.

Answer 1

your solution should work: your first update will lock the whole table. If another transaction is not finished, the update will wait. Your second update will guarantee that only one row will have the value 1 because you are locking the table (it doesn't prevent INSERT statements however).

You should also make sure that the row with the RISK_MODEL_ID exists (or you will have zero row with the value '1' at the end of your transaction).

To prevent concurrent INSERT statements, you would LOCK the table (in EXCLUSIVE MODE).

Answer 2

You could consider using a unique, function based index to let Oracle handle the constraint of only having a one row with activated flag set to 1.

CREATE UNIQUE INDEX MODEL_IX ON MODEL ( DECODE(ACTIVATED, 1, 1, NULL));

This would stop more than one row having the flag set to 1, but does not mean that there is always one row with the flag set to 1.

Answer 3

If what you want is to ensure that only one transaction can run at a time then you can use the FOR UPDATE syntax. As you have a single row which needs locking this is a very efficient approach.

declare
    cursor c is 
        select activated
        from model
        where activated = 1
        for update of activated;
    r c%rowtype;
begin
    open c;
    --  this statement will fail if another transaction is running
    fetch c in r;
    ....
    update model
    set activated = 0
    where current of c;

    update model
    set activated = 1
    where risk_model_id = ?;

    close c;

    commit;
end;
/

The commit frees the lock.

The default behaviour is to wait until the row is freed. Otherwise we can specify NOWAIT, in which case any other session attempting to update the current active row will fail immediately, or we can add a WAIT option with a polling time. NOWAIT is the option to choose to absolutely avoid the risk of hanging, and it also gives us the chance to inform the user that someone else is updating the table, which they might want to know.

This approach is much more scalable than updating all the rows in the table. Use a function-based index as WW showed to enforce the rule that only one row can have ACTIVATED=1.