1

I'm trying to create a system for ordering and create a unique serial number to distinguish the order, it's working well until one time there is an order at same time (the difference is just in seconds, about 10 seconds) and then the unique serial number become same (the serial number is increment from the last serial number in db).
I'm creating the id based on some format and it have to be reseted per month so I can't use uniqid().

Do you guys have any idea about this? I read about some db locking but when I tried this solution "Can we lock tables while executing a query in Zend-Db" it's also didn't worked.

---EDIT---

The format is

projectnumber-year-number of order this months

the number of order this months is 4 digits started from 0001 until 9999 after 9999 it will start again from A001 ... A999 B001 ... ZZZZ

and this is the column
| order_id | varchar(17) | utf8_general_ci | NO | PRI |

I hope this make it more clear now :)

Thanks!

Community
  • 1
  • 1
Niko Adrianus Yuwono
  • 11,012
  • 8
  • 42
  • 64

2 Answers2

2

Primarily I'd look into using AUTO_INCREMENT primary key - see manual for details.

If that's not possible and you are using InnoDB you should be able to create the order in a Transaction. In your application you can then detect if there were duplicates and re-issue a new ID as needed. Using transaction will ensure that there is no residual data left in the database if your order creation fails.

EDIT based on the additional information:

I'd add an AUTO_INCREMENT primary key and use a separate "OrderName" column for the desired format. That should allow you to do the following, for example:

UPDATE orders o 
 JOIN (
  SELECT 
   year(o2.dte) y,
   month(o2.dte) m,
   min(o2.Order_ID) minid 
  FROM orders o2 GROUP BY y,m) AS t ON (t.m=month(dte) AND t.y=year(dte)) 
SET o.OrderName=CONCAT('p-n-',year(o.dte),"-",o.Order_ID-t.minid);

id column is int PRIMARY KEY AUTO_INCREMENT and will ensure that the orders are always in correct order and will not require locking. In this example CONCAT will dictate your order number format. You can run this UPDATE in a trigger, if you wish, to ensure that the OrderName is immediately populated. Of course if you run this in a trigger, you don't need to repopulate the whole table.

vhu
  • 12,244
  • 11
  • 38
  • 48
  • yes I already tried to detect if there is any duplicate or not, but it's not helping, I guess because the time difference is too little? Thanks! – Niko Adrianus Yuwono Apr 17 '14 at 10:23
  • You can insert 1000 rows within 1 second without having a duplicate... use the mechanics mysql offers to establish this: primary key + auto increment. You don't even need locking. locking used the wrong way is gonna kill your business ^^ – Daniel W. Apr 17 '14 at 10:24
  • @DanFromGermany yes usually I used that for the unique identifier, but this time it's a request from the client. Thanks for the suggestion though! – Niko Adrianus Yuwono Apr 17 '14 at 10:35
  • @nayoso you **HAVE to ensure data consistency**, **especially** when it comes to subject of *[buying|money|customers]*, you HAVE to use, **not only for consistency** but also for **foreign key support**. So you would implement the **internal order_id** - `integer` + `primary key` + `auto increment`, and to **satisfy** your client, you would also **implement a second column** called **order_external_id** `varchar` + `unique index` and put into this column your custom value. – Daniel W. Apr 17 '14 at 10:38
  • @DanFromGermany yes, but they want it to be reseted once permonth. I guess I have to talk about this again to my client. and how can I prevent the order_external_id to be unique? if I'm doing the checking based on the primary key? Thanks :) – Niko Adrianus Yuwono Apr 17 '14 at 10:42
  • @vhu your answer is giving me some idea, I'm voting it up! and if it's working I'll mark it as correct – Niko Adrianus Yuwono Apr 17 '14 at 10:47
  • @nayoso, see the example above; if you `OrderName` prefix is unique (e.g. ProjectNumber-year-month) then `id` being unique will ensure that all `OrderName` are unique as well. In this example, if you omit month from the prefix (e.g. use only ProjectNumber-year) there will be duplicates. – vhu Apr 17 '14 at 10:47
  • yes I understand about that, I'll try it, thanks for the help both of you and @DanFromGermany ! – Niko Adrianus Yuwono Apr 17 '14 at 10:50
0

Seem we must use transaction with Serializable locking. It will prevent read and write from other session until transaction complete.

Please view at here

http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

hoangvu68
  • 845
  • 2
  • 13
  • 28