Calculate MAX()+1 and INSERT all-in-one

Databases
1

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.

Is that possible, and if so, how??

Sincerely,

Debbie

2

What happens if a dozen posters all post at the same time and you decide that the first post (spam) should be deleted. The first thread displayed post would then be #2.

How about dynamically numbering the posts in their display order and using the unique ID as a link reference.

I think this is the method that most other forums adopt.

3

yes, two ways

  1. http://dev.mysql.com/doc/refman/5.0/en/commit.html

  2. use an auto_increment

4

I don’t see how a Transaction would meet my needs…

If I had a Transaction, and 3 users tried to submit a Comment at the same time, then 2 of those users would get an error message which is completely unacceptable…

Debbie

5

Yes, that could happen, and I am fine with that.

Bad idea for this reason…

The thread starts off with Comment #1, #2, #3, and #4.

John comes along later, and posts Comment #5 and in the body says, “I totally agree with what you said in Comment #3, Debbie!”

Then the Admin decides that Comment #1 is spam, and when my PHP regenerates the Comment #'s, what John was calling “Comment #3” is now labeled as “Comment #2”!

If I do things my way, each record has a permanent Comment No that for the most part is much more user-friendly.

Sure there could be Comment #2, #3, #4 or Comment #1, #2, #3, #5, but that is better than Comment ID 30250, Comment ID 30311, Comment ID 30312, Comment ID 30329…

Debbie

6

OK, point taken.

Try a select and count() all the posts related to that particular thread and insert count()+1, name, date, memo, etc

7

how do you know this??

what is the error message??

as far as i know, they would just wait until your transaction is finished

8

One major issue with trying to understand and solve this potential future problem, is that it is impossible to recreate. (Even da Manual can’t help me there, @r937 ; ) :wink:

I am all for testing the hell out of my code, but I don’t know how to simulate 2 or more users clicking “Submit Comment” at the exact same moment?! :-/

To answer your question, Rudy, my assumption of a Transaction error is based on some code I wrote for another script this Winter. Here is a PHP snippet…


	// Verify Insert.
	if (mysqli_stmt_affected_rows($stmt3)==1){
		// PM-Recipient Insert Succeeded.
		// Continue processing...
							
	}else{
		// PM-Recipient Insert Failed.

		// Rollback Transaction.
		mysqli_rollback($dbc);

		// Turn On AutoCommit.
		mysqli_autocommit($dbc, TRUE);

		$_SESSION['resultsCode'] = 'SEND_PM_PRIVATE_MSG_NOT_SENT_3535';

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?)

Sincerely,

Debbie

9

well as you know i don’t do php so i cannot comment on your code at all

i believe innodb has a timeout level, which means how long a request for an innodb table will wait before it is kicked out

i’m confident that START TRANSACTION and ROLLBACK or COMMIT will do just fine

but perhaps you should wait until an actual mysql dba answers

that may be a long wait, by the way :slight_smile:

10

I thought a “collision” (e.g. additional INSERTS on an already locked row) would cause a Transaction to fail.

So you are saying that is wrong, and within reason, MySQL will “queue” things and just wait for Transaction #1 to finish, before Transaction #2 starts, huh?

(Definitely moving into “advanced” territory for poor ol’ Debbie!) :shifty:

Debbie

11

that’s the whole concept, and why transactions were invented

you have to balance

  1. how long a transaction takes to execute (microseconds?)

  2. how many competing transactions arrive within that time span

even on a forum as busy as this one, you’d hardly ever notice even if it did happen

12

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!!) :lol:

(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??

Sincerely,

Debbie

P.S. I’m still not seeing that $100 in my bank account… Do you think the transaction failed? :goof:

13

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.

14

nice one, john

the mysql engineers are way smarter than most of us

15

So Transactions to Debbie’s rescue!

Thanks for clarifying things guys.

Sincerely,

Debbie

16

Before you go down the transaction route, take some time to investigate why they are used.

Normally it is to update multi pole tables. I get the distinct (no pun intended) impression only a single comments table is updated.

17

what are you, on a phone?

you meant multiple, yes?

yes, but there’s a MAX()+1 thing going on, so you need transactions to prevent race conditions

18

@r937 ;

>>> 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.

>>> 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.

Many thanks for your comment in post #14.