MySQL id sequence

Question

Is this a correct way for id generation in MySQL ?

INSERT INTO Picture (PictureId,First_pick,Title,Description,File_Name,Is_Vertical)VALUES
((SELECT max(pictureid)+1 FROM Picture),0,?,?,?,?)

I mean if it is guaranted that PictureId will be unique when this query is run by many threads ?

I can't modify table structure. Should I use any specific locks, index or transaction isolation level ?

Regards, Michal

Answer 1

You would usually use an AUTO_INCREMENT field that will take care of those things for you: Manual

But the question is good nevertheless, I would like to hear a deep answer from a mySQL expert on this. How reliable would it be to do it in the described way?

Answer 2

If you really cannot use an AUTO_INCREMENT then you should definitely lock the table for writing.

Answer 3

Unless you've locked the table adequately - which could cause problems by preventing other threads from accessing it - your proposed approach would be susceptible to race conditions if multiple threads attempt to insert records simultaneously.