I've got a problem with simultaneous placement of an order in a webshop.
I will start off with a general description of the problem, and provide details after that.
Orders are stored in the table 'orders'. Each order has a BillNumber, which is obtained via a mysql routine (which basically takes the next available number). After The billnumber is obtained, the order is written away. However it looks like simultaneous calls of the billnumber routine sometimes give the same billnumber, when the order is not yet written away.
Here is the routine that provides the next available billnumber for the current year:
DELIMITER $$ CREATE DEFINER=`test`@`%` FUNCTION `NEW_BILL_NUMBER`() RETURNS bigint(20) SQL SECURITY INVOKER BEGIN SET @factuurlength := '00000'; SET @temp := (IFNULL((SELECT MAX(BillNumber) FROM orders), CAST(CONCAT('4',YEAR(NOW()),@factuurlength) AS UNSIGNED))); SET @temp2 := CAST(CONCAT('4',YEAR(NOW()),@factuurlength) AS UNSIGNED); RETURN IF(@temp > @temp2,@temp,@temp2) + 1; END
And here is the 'orders' table structure:
CREATE TABLE `orders` ( `OrderID` int(10) unsigned NOT NULL AUTO_INCREMENT, `UserID` int(10) unsigned NOT NULL, `BillNumber` bigint(20) NOT NULL, `Remark` text, `DateAndTime` datetime DEFAULT NULL, PRIMARY KEY (`OrderID`) ) ENGINE=InnoDB AUTO_INCREMENT=3681 DEFAULT CHARSET=latin1$$
For completion's sake, here is an example of the query called when saving an order:
INSERT INTO orders (UserID, BillNumber, Remark, DateAndTime) VALUES(34, (select NEW_BILL_NUMBER()), "test", "2011-04-20 11:12:36")
How would i go about to prevent identical billnumbers being provided by the routine?