Is generator="increment" good enough for sequential invoice number?

Question

I need to write a invoice generating software in ColdFusion 9 using ORM (Hibernate).

One of the requirements is that the invoice number must be sequential without gap, which means no invoice number can be skipped.

The database identity value (i.e. generator="native") does not work as it skips a number if transaction failed to commit.

According to the CF9 documentation:

increment: This algorithm generates identifiers of type long, short, or int by incrementing a counter maintained by ORM. This is commonly used when auto-generation for the primary key is not enabled in the table and you want ORM to generate the primary key. This should be used when a single instance of ColdFusion is the only process to insert data into the table.

Does Hibernate's increment generator guarantee no skipping on failed transaction? Is it good enough?

If not, how should I go about doing that? insert invoice number by a thread-safe sequential number generator at postInsert()?

Answer 1

Looking at the documentation for Hibernate, it appears that there is a decent amount of flexibility with respect to configuring this generator. The basic principle is that it will look up the largest value of the ID from the database, and add 1 to it. There should be no gaps in the numbers. You should be mindful of concurrency issues, however.

Answer 2

generator=”increment” is NOT good enough for guarantee no-skipping sequential invoice number on failed transaction.

Just tested with:

// foo.cfc
/** @generator increment */
property numeric id;

// test.cfm
f1 = entityNew("foo");
f2 = entityNew("foo");
entitySave(f1);
ormflush();
eneitySave(f2);
throw();

Then I browse to test.cfm and refresh a few times. ID column in Foo table is all odd numbers because the f2 was assigned the even number, but not saved.