unique sequence per field with php and mysql

Question

In a given table, I have to fields: pos (point of sale) and voucher_number.

Both of them are grouped in a unique index

I need to have an unique sequence per pos, for example

ID	date	pos	voucher_number
1	2021-01-01 11:45:37	1	1
2	2021-01-01 17:22:45	1	2
3	2021-01-02 15:08:02	2	1
4	2021-01-02 15:08:02	1	3
5	2021-01-03 10:37:24	2	2
6	2021-01-03 10:37:24	3	1
7	2021-01-03 10:37:24	1	4

Different users can create different vouchers at the same time, so my concern is how to ensure that the sequence keeps it's natural order and doesn't repeat or skip numbers

The code that I'm working on is wrapped inside a transation and I was thinking of doing somethig like this (pseudo):

//At this point a transaction has already been started

function save_voucher($voucher_data)
{
    //Notice the SELECT ... FOR UPDATE
    $max_voucher_number = select max(voucher_number) as max_voucher_number from vouchers where pos = $voucher_data["pos"] for update;

    $voucher_data["voucher_number"] = $max_voucher_number + 1;
    
    //I can't alter save_voucher() in parent class
    //But inside it would perform something like:
    //insert into (pos, voucher_number) values ($voucher_data["pos"], $voucher_data["voucher_number"]);
    return parent::save_voucher($voucher_data);
}

//After the method execution, a commit or rollback happens;

Or even something like:

//At this point a transaction has already been started

function save_voucher()
{
    //I can't alter save_voucher() in parent class
    $new_voucher_id = parent::save_voucher();

    //Now, after a voucher had been created I could get the max sequence
    //Notice the SELECT ... FOR UPDATE
    $max_voucher_number = select max(voucher_number) as max_voucher_number from vouchers where pos = $pos for update;

    //Then, I could update that voucher
    update vouchers set voucher_number = $max_voucher_number + 1 where id = $new_voucher_id;

    return $new_voucher_id;
}

//After the method execution, a commit or rollback happens;

Would the above guarantee that I will have a unique sequence per pos? Would a possible concurrency will affect the sequence in any way?

I'm using MySQL with all tables using InnoDB

Answer 1

The answer is yes, I can safely get a unique sequence per pos.

I've run two kind of tests ir order to see what could happen in two users try to do the same thing at the same time.

Before I continue, I should mention that I had to get rid of the unique index. If the unique exists I get an error (deadlock) and the second connection fails. I will deal with this in another post...

Test #1

I've isolated the test case in a file named test_a.php and duplicated it in test_b.php. Then I've set a cron like this

*/5 * * * * cd /path/to/test/ && php test_a.php >> /var/log/test_a.log 2>&1
*/5 * * * * cd /path/to/test/ && php test_b.php >> /var/log/test_b.log 2>&1

That would generate a correct sequence as expected. I could even put a sleep() on test_a and test_b will wait. I'm not sure if the jobs runs simultaneously at the nano second, but they get me closer to the actual scenario.

Test #2

I replicated Franciso's answer: using two terminal instances with the MySQL client opened in each one.

On the first terminal you run the SQL:

START TRANSACTION;
SELECT ... FOR UPDATE;
# Do not COMMIT to keep the transaction alive

On the second terminal the same thing:

START TRANSACTION;
SELECT ... FOR UPDATE;
# Do not COMMIT to keep the transaction alive

Since you create a select for update on the first terminal the query above will wait until the select for update transaction is committed or it will timeout.

Go back to the first terminal and commit the transaction:

COMMIT;

Check the second terminal and you will see that the query was executed.

Although this example is not concurrent, it shows the behaviour of SELECT...FOR UPDATE