How to Ensure Gap Free and Safe Invoice Number Generation (Legal Issues)

Question

Background and System View

We have implemented a Billing system in a distributed environment. There are 4 terminals that generate around 2 bills per minute per terminal. We use Mysql as backend and C#, winforms as our client tech.

The most important constraint in any billing system is that the invoice number must be sequential. To do that I run a query similar to

In pseudo-code

let x ="SELECT count(*) from Orders where IsInvoiceGenerated=1 and FinancialYear=val

new invoicenum = x + 1;

The Problem Everything was running ok till 411th invoice, after which the system suddenly skipped 2 invoices and generated Invoice 414. We investigated the system state and found that system was not tampered externally and we also inferred that nobody accessed database from workbench. This is a major issue since it also has legal ramifications.

Can you please suggest the best way to ensure that billing number always remains sequential.?

Answer 1

Here is a solution I came up with:

CREATE TABLE `inv` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `invNo` int(10) DEFAULT NULL,
  `invName` varchar(100) DEFAULT NULL,
  `cratetedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE     CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

and create a new invoice with this query

INSERT INTO `inv` (`invNo`, `invName`) SELECT (SELECT MAX(invNo)+1 FROM `inv` FOR UPDATE) AS `invNo`, 'Invoice 1';

Using SELECT FOR UPDATE will acquire a write lock on the table hence simultaneous insert will be block, where at the same time there will be no restriction on read. So the only bottleneck could be invoice creation which will happen only one at a time, and I think that is acceptable.

Now only concern is what will happen if my server stopped, crashed, network lost or something extraordinary happened and the code doesn't get a chance to finish the transaction, and it could end up with a deadlock.

Frankly I am not sure how to handle this last situation, but I was reading somewhere that we can use MySql wait_timeout property for this but not sure how to use it. However I am using Spring for my server code and using @Transactional timeout property, not sure if that will cover me.

Answer 2

I have been working on this topic for half a day now, and the best solution I could came up with is the following:

I create a table where I store the next invoice number. It starts from 1. I also assign a name so I can handle more sequences with the same table.

CREATE TABLE invoice_numbers (name VARCHAR(50), number int4 DEFAULT 1); 
INSERT INTO invoice_numbers VALUES ('main');

I create the invoices in draft mode, draft invoices do not have an invoice number. Draft invoices can be deleted as well. Once the final check is done, the invoice can be finalized. Finalized invoices are immutable, of course. The finalization process starts a transaction selects the appropriate line from the invoice_numbers table for update. This ensures only one process can have access to the record at any given time. I assign the number to the invoice and increase the number by one. Finally I commit the transaction.

START TRANSACTION;
SELECT number FROM invoice_numbers WHERE name='main' FOR UPDATE;

assign the number to the invoice and set its status to finalized;

UPDATE invoice_numbers SET number = number + 1 WHERE name='main';
COMMIT;

Answer 3

Hum it maybe much simpler.

• BEGIN;
• save invoice without a number but precise created_at datetime
• calculate how many invoices have been created before this created_at datetime & autoincrement pk
• update number in invoice
• COMMIT;

No need to lock anything.

Answer 4

Before I start, I'd just like to apologize to @Grumbler85 - you were right. This question bugged me for a while and I'll try to answer it as best to my knowledge as I can.

Both transactions and locking are insufficient solutions.

Reasons: Locks aren't good because once you lock a table, you have to unlock it. Unlock might fail, we all know the unstable nature of networks and computers in general. Bottom line is - you'd have to use your C# application to issue locks and unlocks. Every time you generate an invoice, you'd have to lock the table that is being used as a counter, forcing every other MySQL session to wait until you release the lock. From my experience, within a few days you'd have to hire an administrator whose job would be to release the locks.

Transactions aren't good enough because each transaction operates on a snapshot of the data (simplified explanation, transaction isolation level can be modified). That means that 1 transaction can calculate that invoice number must be 6, while another transaction would calculate also that invoice number must be 6.

What you could do is make the invoice_number unique so if 2 (or more) transactions try to insert the same number, you'd get an exception for at least 1 of them, thus preventing gaps but failing the invoice creation.

Using auto_increment is also not an option. Auto_increment is just a simple counter. That means that auto_increment doesn't "reuse" numbers dropped for some reason - reason being that an error occurred and transaction couldn't be saved, effectively making the auto_increment calculated for that record to be lost.

So what options are there? Personally, I would create a simple service which would run at predefined time-intervals which would update invoices that haven't got invoice_number set. The service wouldn't offer concurrent access, and there would always be one connection active which would work on a set of already inserted invoices.

It is true that there are laws in place (in certain countries, such as England) which specify that there MUST be a sequenced invoice numbering, I was wrong about that as well. Source: http://www.hmrc.gov.uk/vat/managing/charging/vat-invoices.htm and excerpt from the source:

an invoice number which is unique and follows on from the number of the previous invoice - if you spoil or cancel a serially numbered invoice, you must keep it to show to a VAT officer at your next VAT inspection

The final option is that you are satisfied with invoice creation failure if two or more transactions acquire the same invoice number, which means you'd have to implement a way of re-running the failed transaction (which is everything but simple).

Answer 5

To create a unique number, you should store the current number in a table and then, when you create a new invoice you have to perform the following steps:

1. Start a transaction
2. Get the number from the table
3. Set the number to your invoice
4. Set number + 1 in the table from before
5. commit