Oracle + Last insert ID question

Databases
1

I know I can do this:

SELECT something_seq.NextVal As LastInsertID FROM Dual";

But does this work same way in MySQL = it takes the last inserted ID from the corresponding thread? Meaning if we happen to have many inserts at exactly same time does this return the correct id or do I have to start locking tables or something.

-Z-

2

If you select using nextval you won’t get the last sequence number used (assuming post insert). You can try currval, but there is no guarantee that another insert hasn’t occured between your last insert and the call to currval (assuming you are running these as independent queries).

A better approach (IMHO) would be to use packages/procedures… then you can do things like use the RETURNING clause to return the sequence value you just inserted.

We don’t write any SQL in our PHP applications, it is all package/proc calls.

Cheers,
Keith.

3

When I was using Oracle, we’d run the query you specified above:


SELECT mytable_seq.nextval MyTableID FROM DUAL

before we did any inserts. Then we’d set a variable in the application and use that for our insert(s).

4

It still doesn’t solve the problem and I strongly suggest you to change it. If someone makes an insert to the table right after you have got this Id (meaning before your insert) you are using wrong id.

Keith: Thanks, I go for it.

-Z-