0

I had a problem with a query in oracle 10g database when two users launch a process at the same time. This query is inserting volume data from another table.

I tried to simplify the example:

Tables structure:

Table 1 VAL1 | VAL2

Table 2 ID | VAL1 | VAL2

Query:

Insert into Table2 (id, VAL1,VAL2)
select t2.id + rownum, t1.VAL1, t1.VAL2 from Table1 t1, (select max(id) as id from table2) t2

problem description:

User1 launch the process, the query will insert 50000 record with id from 1 to 50000

User2 launch the process at the same time, select max(id) as id from table2 will not return the expected value which is 50000 as the rows inserted by user1 are not yet committed so the system will try to insert rows with the same Id.

Seems that oracle do not allow dirty reads like sql server or DB2, is there any way to allow reads from uncommitted rows? or another alternative for this?

S.Daineko
  • 1,790
  • 1
  • 20
  • 29
Martin Garcia
  • 1
  • 1

2 Answers2

3

The alternative, (and better, in the Oracle world) would be to use a sequence to generate your ID:

Insert into Table2 (id, VAL1,VAL2)
myseq.nextval, t1.VAL1, t1.VAL2 from Table1 t1;

This will guarantee a unique, ascending value for each iteration, in a multi-user, multi-process environment.

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • I concur that using sequences and letting the data base manage identity columns is the preferred and recommended way to go. DB2 supports them now too. – Jim Castro May 21 '20 at 16:02
0

No Oracle database has no such feature (whatever edition or version). If this is really needed you would need to use another database product.

pifor
  • 7,419
  • 2
  • 8
  • 16