Avoiding Dups from Concurrent Submissions

PHP
1

Here is a bonus question for anyone up to a challenge!! :cool:

My website allows users to post Comments beneath Articles.

Comments are stored in the “article_comment” table which looks like this…


- id (PK)
- article_id (UK1)(FK)
- member_id (UK2)(FK)
- comment_no
- body
- created_on (UK3)

To make the display a little more user-friendly, my PHP generates a “Comment No” like… #1, #2, #3,…

But today I discovered a design flaw that could really cause issues once my site goes live…

When a member is ready to post a Comment, my script first runs this query…


	// Build query.
	$q2 = "SELECT MAX(comment_no) AS lastCommentNo
			FROM article_comment
			WHERE article_id = ?";

And then after that, my script does this…


	// Check # of Records Returned.
	if (isset($lastCommentNo)){
		// Maximum Found.
		// Increment CommentNo.
		$commentNo = $lastCommentNo + 1;

	}else{
		// Maximum Not Found.
		// Assume No Comments Exist.
		$commentNo = 1;
	}

Finally, it tries to insert the new Comment…


	// Build query.
	$q3 = "INSERT INTO article_comment(article_id, member_id, comment_no, body, created_on)
				VALUES(?, ?, ?, ?, NOW())";

Here is the problem…

Let’s say that Amy, Cindy and Mary all decide to post a Comment at the same time.

On each of their separate computers, the instance of my code first determines that $lastCommentNo = 15.

So far, so good!

Next each instance of my code calculates $commentNo = 16

Still, in and of itself not an issue. (Of course you see where this is heading…)

Now let’s say that Amy is quickest, and hits “Submit” first. So here instance INSERTS a new Comment with a comment_no = 16.

Just what we want.

However, when Cindy and then Mary post their Comments a few seconds later, each of their instances also tries to INSERT a Comment with a comment_no = 16 and I have one of two issues…

1.) If I place a Unique Index on “comment_no”, then each of their submittals fail!! (And Cindy and Mary don’t care about the back-end dups, they just think my website sucks for refusing their Comments, and so they leave!)

2.) If I allow “comment_no” to remain unindexed as it is now, then I end up with 3 Comments all claiming to be “Comment #16”?!

Now, my database could care less, because all of the keys are still legitimate.

But obviously this a case where a poor design is ultimately failing to produce the desired outcome!

So how do I fix this problem??

At first, I thought maybe I needed a “Database Transaction”, but the problem is more complicated than that!!

Really what I need is for Cindy’s instance and Mary’s instance to be “aware” that since they checked for $lastCommentNo, it got updated by Amy’s submission!!

Conceptually I know what I need, but programmatically, this one is over my head!! :blush:

Help is appreciated!!

Sincerely,

Debbie

2

Can we assume that this query and variable $commentNo is being set on POST and not being entered into the form as a hidden field?

  // Build query.
    $q2 = "SELECT MAX(comment_no) AS lastCommentNo
            FROM article_comment
            WHERE article_id = ?";

If so I doubt there would be a problem.