Simultaneous order placement

[stevija]

Hello,

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.

**The problem**
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?

Best regards,
Chris

[urtrivedi]

I faced same problem, I guess your billnumber column may remain blank for sometime.
So I created on more table say billmaster (billnumber pk). If you want to generate bill number every year then (billnumber pk, year pk).

Now in your billnumber generation. you fetch max number from billmaster table then insert it in billmaster table, return that billnumber to calling insert statment.

I am sure duplication will never happen again.

[debasisdas]

why not simply try

BillNumber = max(BillNumber) +1

[urtrivedi]

if you are not keeping billnumber blank and if you can insert billnumber with orderid, then simply define billnumber as unique key. Then it will not allow duplicate billnumbers.

[tomato.pgn]

or u can also do one thing just make the field in db autoincrement if it is only numeric...otherwise just add a query to find max billid and increment it by 1.....

[stevija]

Hello, thank you all for your suggestions.

@urtrivedi:
About your suggestion with the billmaster table, won't that have the same problem? For example when 2 simultaneous calls are made to aquire the max number, and the second aquires the same max, because the first is not written away yet.
Correct me if I'm misunderstanding you.

@debasisdas, tomato.pgn
adding a query to get the max + 1 is what I currently have. But the max can be the same in this way with 2 simultaneous calls if the insert statement is not executed yet, but the 'select max' statement is.
Making it an auto_increment field might have merit though

[urtrivedi]

simple solution is make billnumber uniquekey

[debasisdas]

Why not check for existence of the value generated (using +1 technique) once before inserting. If it exists increment the same further (+1) and insert.

[stevija]

I agree making it unique ensures no duplicates in the table, but it still does not exclude the billnumber-routine potentially providing the same billnumber at simultaneous requests.

When making billnumber unique, the second order would simply not be inserted and be lost.

[debasisdas]

the second order would simply not be inserted and be lost.

that is when my previous solution will come handy

[smantscheff]

Bracket the whole order process with START TRANSACTION and COMMIT respectively ROLLBACK in case of any errors.

[stevija]

Why not check for existence of the value generated (using +1 technique) once before inserting. If it exists increment the same further (+1) and insert.

With your suggestion every order would go through the following three steps.
A. Get the billnumber + 1
B. Check before insert if the billnumber exists
C. Insert the order

Example:
The current newest billnumber = 50

User 1 executes Step A (and gets the number 51)
User 2 executes Step A (and gets the number 51)
User 1 executes Step B (51 is not present yet, so allowed = true)
User 2 executes Step B (51 is not present yet, so allowed = true)
User 1 executes Step C (the order with billnumber 51 is inserted)
User 2 executes Step C (the order with billnumber 51 is inserted)

So even with the extra check it's still possible to get the same number, right?

[debasisdas]

yes, now use suggestion by smantscheff

i.e.---commit after every transaction.

as per your situations

User 1 executes Step C (the order with billnumber 51 is inserted)
commit
User 2 executes Step C (the order with billnumber 51 will not be insert as it is duplicate and 52 will be inserted.

[stevija]

Hmm, transactions seem to be just what I need :)

Steps I'll make for the solution:
- Making the billnumber field unique
- Make a transaction of the order process, that loops until succesful

Thanks for the help guys! It's appreciated

Best regards,
Chris