Adam Adam - 10 months ago 94
MySQL Question

PHP & MySQL: How to create unique `year-month-number` keys?

I have to create unique id's for invoices. Each invoice id is of the form

year-month-number
which consists of the current year & month of the invoice and an incrementing number, which is starting at 1 (gaps are not allowed).

So for example, if we have 4 invoices in January 2017 I would have the following 4 invoice id's:

2017-1-1
2017-1-2
2017-1-3
2017-1-4


Now I want to create an app that creates these unique id's. In particular I want to make sure that even when 2 people request an invoice number at the exact same time, they should get different id's.

I am using InnoDB and I have the following table

book

year | month | number |
------------------------
2017 | 7 | 2 |
2017 | 6 | 5 |
2017 | 5 | 6 |


If an invoice has not been created for a
year-month
pair, then no entry is in the database. The primary key is the
year-month
pair and
number
is an
auto increment index
.

Assume I would compute the next invoice id like this:

$stmt = $db->prepare('INSERT INTO book(year,month,number)
VALUES (?,?,1)
ON DUPLICATE KEY UPDATE number= LAST_INSERT_ID(number+1)');
$stmt->bind_param('ii', $year, $month);
$stmt->execute();
echo 'Next invoice id: ' . $year . '-' . $month . - . $db->insert_id;


Explanation:
$db->insert_id;
returns the column number because its an
auto increment column
and
LAST_INSERT_ID(number+1)
increases the
number
that has been inserted last (also maybe by a different user? I am not sure about it and I have problems finding that out in the docs http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id)

Would this code really work, or does it maybe create multiple identical id's if people execute this code simultaneously?

Edit
Assume the number of current month/year is 5.
Why is it not possible that 2 people compute an invoice at the same time, so that both queries upgrade the number 5 to 6 at the same time? In this case, they would both get the invoice id '2017-11-6' right?

Answer Source

For questions like this, you can try it—by opening two terminal windows and using the mysql client.

mysql1> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      5 |
+------+-------+--------+

Start a transaction in two concurrent sessions:

mysql1> begin;

mysql2> begin;

Session 1 does the IODKU and increments the number (but does not commit yet, because begin implicitly takes us out of autocommit mode):

mysql11> insert into book values (2017, 5, 0) 
  on duplicate key update number = last_insert_id(number+1);

mysql1> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      6 |
+------+-------+--------+

Session 2 still sees the original number value, because of repeatable-read transaction isolation. But once it tries to do it's own increment, it waits because session 1 still has the row locked.

mysql2> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      5 |
+------+-------+--------+
mysql12> insert into book values (2017, 5, 0) 
  on duplicate key update number = last_insert_id(number+1);
-- waits for lock

Commit in session 1:

mysql1> commit;

Now the IODKU in session 2 finishes, and we can see it incremented the number a second time:

mysql2> select * from book;
+------+-------+--------+
| year | month | number |
+------+-------+--------+
| 2017 |     5 |      7 |
+------+-------+--------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download