Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> How to increased by exactly 1 ?

How to increased by exactly 1 ?

From: Den <dd_at_hotmail.net>
Date: Fri, 21 Jan 2005 17:04:51 +0800
Message-ID: <41f0c5b4_1@rain.i-cable.com>


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 - 03:04:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US