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

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

Answer 2

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.