I have registration form for users. They can registrate to the tour and I need to generate unique UID for each of their registration. I use ID as primary key and its autoincrement, but its only for system purpose only. I need to generate another UID which is used for pairing payments from bank account.

The format of UID I need to generate is {TOUR_NUMBER}{REGISTRATION_NUMBER}, so the numerical series can be like 59001, 59002, 59003 and so on (59 is number of the tour).

Currently I use this scenario.

1) count how many users registered to certain tour already
2) increment that number
3) with while() check if that incremented number is not already used
4a) if not, save the registration
4b) if it is, increment number again and continue with 3)

Problem is, that if there is many users at the same time, system generate the same UID for more users. I've tried to start transaction before point 1) and commit them after 4a) but it did not help.

--- EDIT ---

Maybe I did not explain my problem sufficiently. Let me try it again. Here are simplified tables I have. Tour_id and registration_id columns are auto-incremented.

payment_id is generated from tour_id and incremented number of total users registrated to the certain tour. The payment_id format can not be changed, it must be the way it is now, unfortunatelly. The value is used primarly for the payment pairing so it must be connected to certain user from the beginning and saved within user's tour registration.

I just need to ensure, that the payment_id will not be the same, which happend now from time to time, because delays in executing queries in my scenario above I guess. If it happends, the table looks like the table below, which I need to avoid - George and John have the same payments_id because they did registration at the same time. And 1002 is missing.