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

You'll also need to include a default value for if `pos` doesn't exist in there yet, too. It is technically possible (although probably very rare) for a voucher to be entered into the system between the `SELECT` and `INSERT`. If that happens, your transaction would fail, because of a duplicate key, so you are technically still safe (assuming you are handling that, too). If someone else performs a `DELETE` between those two, however, that's where the sequence would get out of order. You could solve that with a [lock](https://stackoverflow.com/a/4227040/231316) if it is a major concern. — Chris Haas, Jan 19 '21 at 18:03
I would also say (and please don't take this as judgement) that this has a "code smell" of trying to do something that might not really need to be done. I have a friend that had a very similar requirement once and years later he was kicking himself because he was backed into a corner. If this truly is a requirement, than so be it, but if you can rearchitect to make the system more resilient (join tables maybe, or not caring about sequential) you might find your life easier in the future. — Chris Haas, Jan 19 '21 at 18:06
@ChrisHaas for duplicates I can catch the duplicate key error thrown by the unique key index. — Matías Cánepa, Jan 19 '21 at 18:30
`DELETE`s are not an issue because vouchers can't be deleted. — Matías Cánepa, Jan 19 '21 at 18:30
wouldn't the `SELECT ...FOR UPDATE` lock the table so I can perform a safe `INSERT` immediately after? — Matías Cánepa, Jan 19 '21 at 18:31
Sorry, I meant to [link to the answer](https://stackoverflow.com/a/4227957/231316) above that one that just talked about locking in general. If you want to guarantee that no code runs between a `SELECT` and an `INSERT`, you can't lock specific things, so you'd need to lock the table itself for writing. Readers should still have full access and other writers should automatically get in unless the lock timeout is hit. But if you aren't deleting, I would recommend just handling the failed transaction because of a duplicate `INSERT`. — Chris Haas, Jan 19 '21 at 18:40
You really want to stay away from locking and steer towards using transactions, if necessary. — tadman, Jan 21 '21 at 17:52
@tadman but isn't `SELECT...FOR UPDATE` inside in transation meant for this scenario? — Matías Cánepa, Jan 21 '21 at 23:02
You want to be careful about how long you hold that open for. Even inside a transaction it can logjam things that need to write to those particular records. — tadman, Jan 21 '21 at 23:18
it shoud last almost nothing, it's a select and update just after the main insert — Matías Cánepa, Jan 21 '21 at 23:38

score 0 · Accepted Answer · answered Jan 26 '21 at 01:01

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

unique sequence per field with php and mysql

1 Answers1

Linked