Constraint violation with INSERT INTO ... SELECT ...

Hi,

I'm developing an application that stores data in a database. We have many customers for this application, and each customer has its own database server - some uses Oracle, some has MSSQL and some has other types of databases.

A typical customer has between 5 and 50 persons using our application at the same time, so we have to take care when different persons access the same database tables simultaneously.

We often use a number-field with a unique index to identify a row in a table.
When we insert something into the table, we do it like this:

INSERT INTO testtable (uniquenumber, field1, field2, ...)   SELECT MAX(uniquenumber) + 1, value1, value2, ...   FROM testtable

This works fine for MSSQL, but it sometimes fails for Oracle with the message: "ORA-00001: unique constraint (<index-name>) violated", because two users have inserted something at the same time.

Is there some way to make Oracle perform such a command as an atomic action, the way MSSQL apparently does?

Regards,
Asger Received on Wed Nov 22 2006 - 10:02:39 CST