Re: How to increased by exactly 1 ?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 21 Jan 2005 11:08:33 -0800
Message-ID: <1106334513.539742.284470@c13g2000cwb.googlegroups.com>

Den wrote:
> The system need to auto generate the Control Code column for the
items. The
> business rule is that the system should have an option to assign the
lowest
> next available Control Code for the new item created.
>

<snip>

I think your best bet is to lock the table just before insert and commit immediately afterwards OR use another table to generate the sequential value... as suggested by Casey. Here is what I tested on a 10g database. Created a table and attempted to insert a new row based on the MAX() + 1 counter value. I am doing the inserts via a procedure and there are two background jobs that try to insert a new row at exactly the same time. Background jobs send trans_id A and B respectively.

create table foo (counter number, trans_id varchar2(1), timestamp date);

create or replace procedure foo_proc (
trans_id_ in varchar2)
is
counter_ number;
begin

-- set transaction isolation level serializable;
-- set transaction isolation level read committed; -- Default
-- lock table foo in exclusive mode;
-- select nvl(max(counter), 0) + 1 into counter_ from foo;

insert into foo values (counter_, trans_id_, sysdate); commit;
end foo_proc;
/

With Serializable:
SQL> select counter, trans_id, to_char(timestamp, 'HH24:MI:SS') timestamp from foo;

COUNTER T TIMESTAM
---------- - --------
1 A 16:55:05
1 B 16:55:05

SQL> truncate table foo;

With Read Committed:
SQL> select counter, trans_id, to_char(timestamp, 'HH24:MI:SS') timestamp from foo;

COUNTER T TIMESTAM
---------- - --------
1 A 16:57:03
1 B 16:57:03

SQL> truncate table foo;

With table locked in Exclusive mode:
SQL> select counter, trans_id, to_char(timestamp, 'HH24:MI:SS') timestamp from foo;

COUNTER T TIMESTAM
---------- - --------
2 B 16:58:04
1 A 16:58:04

SQL> truncate table foo;

And then using a before insert trigger:
create or replace trigger foo_trg
before insert on foo
for each row
declare
counter_ number;
begin
select nvl(max(counter), 0) + 1 into counter_ from foo; :new.counter := counter_;
end foo_trg;
/

SQL> select counter, trans_id, to_char(timestamp, 'HH24:MI:SS') timestamp from foo;

COUNTER T TIMESTAM
---------- - --------
1 B 17:03:02
1 A 17:03:02

Do you allow hard deletes on this table? Plus is the counter column updateable? If one or both of these is true then you may run into issues.

Regards
/Rauf Received on Fri Jan 21 2005 - 13:08:33 CST