How to generate invoice number correctly?

Question

MySQL

Probably the question is as old as the world, but I can not find a definite answer for myself. There is a small web project in PHP and MySQL. I need to generate invoices for users at checkout. I can't use AUTO_INCREMENT because the counter needs to be reset every year. So far I have found such a solution: make a separate table with counters by years and, when creating a new invoice, take the number from there and increase it by 1. But the following problem arises: if you make requests to create a new invoice, select the number and increase the number in the transaction, then how to be with parallel requests. if one of the transactions decides to rollback the state? Can anyone come across such tasks and can share a link to good material or experience?

Answer 1

The recording will not be completed - that's okay, the invoice numbers must be consistent and that's it.
Clarification: chronologically sequential, but this is implicitly performed if the date of the document is assigned along with the number.

optimistic locking

No. We need a pessimistic one.

Pessimistic blocking is similar to Murphy's principle. She assumes that if something bad can happen, it will definitely happen. Unlike pessimistic locking, optimistic locking assumes that during the update of a record in the database, we will be the only one who changes it. In most cases, it is, so optimism is justified. However, during the UPDATE we check to see if the record has changed since it was read. And if it has changed, then we must read the latest version of the record from the database and repeat our operation with it.

Answer 2

before insert triggers

Answer 3

Unfortunately one of the comments was deleted (for reasons unknown to me), but there was a suggestion to use optimistic locking. Who thinks about this? It is interesting to me who as processes locks? Just return an error message to the user, or try to run the request multiple times?