I have a query that on every user purchase gets currently highest receipt_counter number from receipts table in order to create new receipt. receipt_counter is not unique in the table because it resets every year.
receipt_counter is just an integer that is used in generating receipt_label that looks like "pos_id"-"receipt_counter".
There is a possibility that people can buy a product simultaneously on the same point of sale (pos_id).
Function that gets new receipt_counter looks like this:
SELECT (MAX(receipt_counter) + 1) as next_receipt_counter FROM receipts
The problem is when multiple people are buying a product simultaneously, which triggers generating new receipt (along with receipt number), sometimes a collision occurs (multiple people get same receipt number) because there is some delay between retrieving receipt counter and inserting new receipt into DB.
Is there a best practice to deal with this kind of problems? Do I need to use some kind of deadlock, or is my initial idea flawed and I need to change tactic for generating receipt counter all together?
EDIT: receipt_counter needs to be a sequential number without gaps.
there is some delay between retrieving receipt counter and inserting new receipt into DB
You can change your software in order to instead or retrieving the ID without creating the actual receipt, it creates the receipt (with "pending" state or something like this) and then retrieve its ID. In the moment you currently create the receipt, you would just set its status to "active" or something.
Doing it this way you get rid of this time gap between getting and ID and storing the record, which in my point of view, is the main source of your problems.