Select and insert at the same time

Question

So, i need to get max number of field called chat_id and after that i need to increment it by one and insert some data in that field, so the query should look something like this:

SELECT MAX(`chat_id`) FROM `messages`;

Lets say it returns me 10 now i need to insert new data

INSERT INTO `messages` SET `chat id` = 11 -- other data here....

So it would work the way i want but my question is what if betwen that time while i'm incrementing and inserting new record other user gonna do the same? than there would already be record with 11 id and it could mess my data is there a way to make sure that the right id goes where i need, btw i can't user auto increment for this.

EDIT as i said i cannot use auto increment because that table already have id field with auto increment, this id is for different porpuse, also it's not unique and it can't be unique

EDIT 2 Solved it by redoing my whole tables structure since no one gave me better ideas

Answer 1

Don't try to do this on your own. You've already identified one of the pitfalls of that approach. I'm not sure why you're saying you can't use auto increment here. That's really the way to go.

CREATE TABLE messages (
    chat_id INT NOT NULL AUTO_INCREMENT,
    ....
)

Answer 2

If you cannot use an auto-increment primary key then you will either have to exclusively lock the table (which is generally not a good idea), or be prepared to encounter failures.

Assuming that the chat_id column is UNIQUE (which it should be from what you 're saying), you can put these two queries inside a loop. If the INSERT succeeds then everything is fine, you can break out of the loop and continue. Otherwise it means that someone else managed to snatch this particular id out of your hands, so repeat the process until successful.

At this point I have to mention that you should not actually use a totally naive approach in production code (e.g. you might want to put an upper limit in how many iterations are possible before you give up) and that this solution will not work well if there is a lot of contention for the database (it will work just fine to ensure that the occasional race does not cause you problems). You should examine your access patterns and load before deciding on this.