Auto incrementing invoice numbers (getting Integrity constraint violation errors)

Hi, I have a table "invoices", with a unique INT column "number". This is because I need to send financial invoice data to a bookkeeping API, and the number needs to be pre-generated already and incremental.

However I can not AUTO INCREMENT the column because I don't want to screw with the ID being standard AUTO INCREMENTAL.

I tried the following code (simplified, in reality a lot more data is added to the entry):

$saved = false;

while ( !$saved ) {
			$invoice = new Invoice();
			$number = Invoice::max('number') + 1;

			if ( !Invoice::where('number', $number)->exists() ) {
				$invoice->number = $number;
				$saved = $invoice->save();
			}	
}

This code worked, until recently I started getting integrity constraint violations. I figure it's because I can not avoid the possibility that 2 invoices are generated at exactly the same time. Somehow, the system tries to insert records with duplicate numbers.

Is there a better, more watertight way to do this?

Thanks

sub1ms

4 years ago

Best Answer

Level 2

I am curious about the fears of letting the table auto increment itself. I would see that being the most fail-safe option to certify a new invoice number. You had mentioned it needs to be pre-generated, but it looks as if you are generating the number on the fly still. Can you share what you feel might be some of the drawbacks you see in this scenario with the built-in auto increment?

Now to answer your question more directly, I agree with you that it sounds like you may have contention on the table with double inserting. Unfortunately, the only way to solve that would have a single queue thread/process lock that can force actions to process in a FIFO (First In, First Out) format.

1 like

Sjon

4 years ago

Level 1

@sub1ms Thank you for your answer. After some more research I did believe that a single thread queue process is the best way to avoid the race conditions. I implemented this and so far it is working to prevent integrity issues. The drawback of this solution is that it does not allow for invoices to be generated on the fly being immediately available to do other stuff with. We have to wait for the queue process.

Why I'd rather not use the DB Auto increment column, is because while being a good solution to fix the immediate issue, I can think of scenarios where for example I would need several different UIDs in the same table or an INT is not suitable. That's why I was exploring different solutions (for future-proof reasons).

jlrdw

4 years ago

Level 75

Check this post: https://laracasts.com/discuss/channels/laravel/how-generate-unique-invoice-number-and-avoid-race-condition

Please or to participate in this conversation.