MySQL increment and avoid incorrect referencing

Question

So I'm creating a quote (estimate) generating module on my online store.

Well I have created one, but I want to get advice on something bothering me.

The quote generation happens by a customer adding products to their cart, and then opting to generate a PDF-quote instead of checking out with the products.

The quote-number (document number) is stored in the database, and this obviously gives each quote a unique reference number, for obvious reasons.

Now as soon as a successful attempt to generate a quote takes place, the following code pulls the current reference number, increments it by 1, and then updates the database entry with the newly incremented reference number:

$wbquery = "select wq.configuration_key, wq.configuration_value from " . TABLE_CONFIGURATION . " wq where wq.configuration_key='" . $wbkey . "'"; 
$wbadd = $db->Execute($wbquery);

$newwb = $wbadd->fields['configuration_value'] + 1;

$wbdone = "update " . TABLE_CONFIGURATION . " set configuration_value = '" . $db->prepare_input(trim($newwb)) . "' where configuration_key = '" . $wbkey . "'"; 
$db->Execute($wbdone);

$docno = $qtype.date('y').sprintf('%05d', $newwb);

bof TLDR;

So here, we get the currently saved reference number, and then add 1 to it. Then we update the reference number in the database with that value.

We also assign the reference number to the variable $docno, because we're printing it into the PDF, and apart from just the reference number, we're adding a prefix, $qtype (which is 2 letters) and date('y') which is a 2 number representation of the current year. We're also forcing the reference number to add zeros infront of the reference number to ensure it is at least 5 digits long.

eof TLDR;

How do I ensure that if by some measure or stretch of the imagination, that if 2 customers happen to generate a quote and precisely the same time, that there isn't a slight conflict.

If customer A generates a quote at the same time as customer B, is there an off-chance that customer A's attempt will pull the current reference number, and customer B's attempt will also pull the same reference number, and incorrectly share the same reference?

Or, is there an off-chance that the update back to the database might happen out of turn, and incorrectly do the increment?

The value in the database is in a column that is type TEXT - and does not have any auto-increment properties.

The thing is, I don't expect this to ever happen, the quote-system will be used both by customers and admin staff - but I just want to ensure that...it never happens.

Any advice? Thanks.

P.S. - I do have a safeguard in place, that adds a random MD5 hash to the filename that the pdf-quote is saved as. This will ensure that a customer who generates a quote, will never be mistakenly served a quote generated by another customer - should the quotes share the same reference number. Originally, it saved the quote using the basic $docno - but I realized this could pose a privacy-risk, which is when I added the MD5 unique hash to the filename.

Answer 1

You are asking about a "race condition" (if you want to do more research).

By the sound of things, you don't want to do this incrementing in your php. It will serve you to let mysql do this with its own auto-incrementing.

So my answer/advice is, redesign your table structure so that quote id is not TEXT and that it is AUTO_INCREMENT.

Otherwise, you will have to go to greater trouble to safeguard against race condtions.