MySQL: Insert Max(col)+1 - without race condition - message inbox

0
votes

I have an inbox messages (innodb) table set up where "id" is the primary index, auto-increment. Each row is an individual message. I have a second column called "conversation_id" which is an id for a group of messages between sender/receiver. All messages within the conversation will have the same conversation_id number.

Problem: How do I do a MySQL Insert where I find the largest "conversation_id" number and add 1 to it (Max(col) + 1) - and not have the race condition? The race condition is a big issue since hundreds of people will be sending messages every minute, and I do not want multiple users claiming the same "conversation_id" if the send messages all at the same time.

1
mysql
Might it be better to create a conversations table with its own auto-incrementing primary key instead? You can then use the id from conversations as the conversation_id in messages. A conversations table would also allow you to store any attributes that relate to the whole conversation instead of just to a single message. - Phil Ross
@Phil Ross - Thanks a lot! This seems like the simplest and most powerful solution. Kind regards - peppy

1 Answers

0
votes

This is a typical example of a stackoverflow question which is asking an answer instead of a question.

You cannot "find the largest conversation_id number and add 1 to it (Max(col) + 1) - and not have the race condition". This is not how you are supposed to do it.

Instead, your question should be "how to add rows to a table in such a way that each new row receives a unique id, and so that the ids are sequentially ordered."

The right way of solving this problem in any decent RDBMS is by using what is known as a SEQUENCE. (Look it up.)

MySQL does not support sequences, so the solution available to you if you have to be using MYSQL is an identity ("auto_increment") column. (Look it up.)