The trouble is, I need to generate this id in the first place, and as I said I'd rather not use a sequence. Thus the max(id)+1 I would not do this. It makes life more complicated. Why not use a sequence? Then you are guaranteed atomicy. I am no Oracle expert, but other database I have used let you retreive the next number in the sequence (before the row has been added) while simultaneously locking the table against other inserts for the duration of the transaction.
I have very limited rights on the DB right now, and in particular I can't create sequences or other objects! Asking for this permission would probably smash my deadline :( Hence my looking for a reasonably safe alternative.
But what I want to make sure is that a transaction implies atomic operation, ie nobody else can perform another select max(id)+1 before the select of the first caller is performed. Is that OK with my current setup ? No, if you must do this then you will need to explicitly lock the table against inserts *before* you look up the next id (you can use the lock SQL statement for this). Then if another thread does the same operation concurrently, one will block the other and they will be executed serially instead of in parallel.
I think I need to lock the table against reads, not inserts. What I must ensure is that no more select's max(id)+1 can be done before the subsequent insert is completed. I don't know how to use LOCK to do that, anyone ? thanks -- Software is like sex : it's better when it's free -- Linus Torvalds http://renaudguerin.com --