So, we're processing credit card payments, and each payment needs a unique reference ID. We are using MySQL.
There is a payments table which has an auto incrementing primary key id, processing_date, created_at, updated_at. My issue is that this record cannot be persisted until after the payment has been processed (this process cannot be changed due the applications workflow). Due to this we cannot simply use the primary key as it would not exist until after the payment has been processed and have since added a reference_id column with a unique constraint.
My question is, is there a reliable method of generating a sequential (or close to) number based on an existing table column (i.e. using something like MAX(reference_id) + 1), processing the payment (using curl) then inserting the payment record into the table with the previously generated reference_id whilst ensuring uniqueness. Essentially reserving the previously generated reference_id with the database.
You could add another column to your payments table as a status / confirmed flag. When the payment starts you insert the values and set the status to "started" (or whatever) this way you can use your auto generated id.
When the payment is completed you can then set the flag to "completed". If a payment is rejected delete the row.
To optimise this you could also create a housekeeping procedure that runs every night and deletes rows with a status of "started" that are over 2 days old or something similar.