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

Question

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 1

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 |
+------+-------+--------+