In MySQL, is it possible to look at a column, calculate the Max value for said column, add one to that value, do an INSERT and guarantee that if 10 other people are performing the same activity, there are 1.) No Collisions and 2.) No Failures??
When users want to add a Comment, I need a way to find the maximum “comment_no” for a given “article_id”, create a +1 value, INSERT the new Comment, but also make sure that competing requests aren’t crashing at the same time.
(FWIW, “comment_no” is a “pretty” number I calculate for display purposes only - as opposed to some ugly AutoIncrement number…)
So, in an ideal world, MySQL would somehow “queue” requests - among users - to add a new Comment, making sure everyone gets a unique article_id + comment_no without anything failing.
I am assuming - but have no way to ever verify this through my own testing - that if 2 or more people submitted a Comment at the exact same moment, that MySQL would take the first request, start the Transaction, and thus “lock” everyone else out, and so everyone else would get sent down the THEN branch of my code which happens when the INSERT fails.
(If the Transaction “locks” things, then won’t User2’s INSERT fail?)
I thought the purpose of transactions was to ensure that if you run multiple queries, that you had a way to ensure they all ran, and if not, then you could roll things back?
Example #1: A user created a record in the ORDER table and several records in the ORDER_DETAILS table, and you need to ensure they all complete successfully.
Example #2: You withdraw $100 from Rudy’s bank account and you deposit it into Debbie’s bank account as a Memorial Day gift! (It would be a bummer if you debited Rudy’s account and didn’t credit Debbie’s account!!)
(I guess I didn’t know that another benefit of using a transaction is that it creates a “queue” of requests, and processes them one at a time until they are all done - or your database server runs out of memory?!)
It’s just a 1,024 character Comment, so not very much time!
If things go as I hope, I can see up to 5 people submitting their Comment at the exact same moment.
To be clear…
So all I need to do is wrap my two queries in a MySQL Transaction, and I am guaranteed that my system-generated “comment_no” will always be unique and that if multiple users do end up INSERTing at the exact same moment, that MySQL will easily handle things, right??
P.S. I’m still not seeing that $100 in my bank account… Do you think the transaction failed? :goof:
I am under the impression that if a dozen posters hit the send button at the same time all the transactions would arrive at the server and form a queue. Each transaction would then start in a repeat loop. If the transaction failed it would be tried again at least a dozen times. No doubt failure would be recorded in the error log. After this transaction has completed the next request would be processed, etc.
Databases have been used and refined be some very clever kiddies for a long, long time.
>>> what are you, on a phone?
No a Nexus 7 which I find incredibly difficult to position the cursor and edit a post, far easier to post a message.
>>> you meant multiple, yes?
>>> yes, but there’s a MAX()+1 thing going on, so you need transactions to prevent race conditions
Yes a transaction would prevent a possible race condition or use a unique index on transaction+position. If the update fails then try updating again and repeat (for about a dozen times) until successful or log the failed update error.