I use transactions in MySQL to store orders. Each order has OrderID (BIGINT), which looks like this: XXXXXX0001, with last four digits incrementing (1620200001, 1620200002, 1620200003, ...).
The transaction works as follows:
start transaction
get new OrderID (increment by 1)
do some stuff
commit/rollback
Saving the transaction can take up to several seconds and if multiple orders are created in very short timespan, duplicate OrderID's can be inserted into database. Before first order is commited, second is assigned the same OrderID, which, at the moment is next in line.
What is best way to prevent this? Having UNIQUE OrderID does not solve it (there would be rollback in second order). I could get rid of transaction and save OrderID quicker, but this leads to other potential problems and does not entirely solve this (just reduces chances of problem happening).
Any help would be appreciated.
Read about AUTO_INCREMENT. Search for it in the manual on CREATE TABLE. It's a long page, but AUTO_INCREMENT is documented about 1/4 of the way down the page.
Briefly, you just declare the primary key with a column option:
CREATE TABLE mytable (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
...other columns...
);
The initial value is 1, or you can make it start at a higher value:
ALTER TABLE mytable AUTO_INCREMENT=1620200001;
A table with an auto-increment column ensures that each concurrent transaction gets a unique, increasing value. There is no race condition, because the INSERT acquires a brief table-lock during which it increments the value. Unlike transaction-based locks, the auto-increment table lock is released immediately. So concurrent sessions don't have to wait for your transaction to finish.
Auto-increment is guaranteed to be unique. That is, the same value will not be allocated to multiple sessions. However, it's not guaranteed to allocate consecutive values. Also, it may allocate a value to one session, but that session decides to rollback its transaction. The value it had allocated is NOT returned to any kind of queue of values, because there has probably other sessions that have allocated the next few values in the meantime. So it's possible to "lose" values and then your table has "gaps" or non-consecutive values.
Do not worry about gaps. These could also happen even if the values were consecutive, because you might delete a row later.