A Typical Concurrent Access Problem

I know this is a typical problem and just want to know what are the possible solutions:

DECLARE
     v_id number;
BEGIN
    SELECT MAX(id) INTO v_id FROM table;
...

    INSERT INTO TABLE (id) VALUES (v_id+1);
....

END; This approach of finding new id value would cause 2 duplicated id values if 2 txns execute the procedure simultaneously - both get the same value for MAX(id) at the beginning and insert the same value +1 to table for id.

How do we overcome this problem? The requirement dictate that we need to try reusing the lowest available id if some rows get deleted. That means we cannot use sequence to generate the id - which keeps on increasing.

Even if we set the transaction to serializable, 2 simultaneous SELECT MAX(id) still get the same value which still cause duplicated values. Received on Thu Jun 09 2005 - 00:04:20 CDT