Re: How to increased by exactly 1 ?

There may be better solutions. Here's my idea (bearing in mind that updates and deletes may be quite a bit trickier).

DROP TABLE T1
/
CREATE TABLE T1 AS SELECT 1 next_id FROM DUAL /
DROP TABLE t2
/
CREATE TABLE t2 (ID NUMBER PRIMARY KEY, val VARCHAR2(1)) /
CREATE OR REPLACE TRIGGER t2_trigger BEFORE INSERT ON t2 FOR EACH ROW BEGIN IF :NEW.ID IS NOT NULL THEN
RAISE VALUE_ERROR;
END IF; SELECT next_id
INTO :NEW.ID
FROM t1
FOR UPDATE;

-- if you'd prefer error messages to
-- blocking behavior, make previous line:
-- FOR UPDATE NOWAIT;

UPDATE t1
SET next_id = :NEW.ID+1;

END;
/

Den wrote:
> How can I guarantee a column's value is increased by exactly 1 for
every new
> row added ?
>
> I cannot use sequence and .nextval to achieve this as there is no
guarantee
> the value is increased by exactly 1.
>
> I suppose serializable transactions should do the job and it seems
not. If
> I get the "select max()" values at start of each txn and do an add 1
to it
> manually, I found that the 2 "select max()" at start of each
concurrent
> serializable txn returns the same max value. And, adding 1 to the
max value
> only means the 2 txn has the same max+1 values. Even worse, it seems
that
> the 2 concurrent serializable txn can commit successfully without any
> complaint about unable to serialize the access!
>
> The test I've done is as follows:
>
> Txn A:(1) select max(), (2) update to max()+1, (3) idle, (4) commit
> Txn B:(1) select max(), (2) idle, (3) update to max()+1, (4) commit
>
> The result is successful commits on both txns and a column with 2
rows
> inserted both having the same max()+1 value.
>
> What should I do to correct this?
Received on Fri Jan 21 2005 - 09:48:05 CST