Spring JPA with Hibernate fiscal sequence generation

Question

In my App I'm modelling an Invoice. In my country (Italy) every invoice must have a unique sequential number without holes, that every year have to restart from 1.

I thought long and hard about the best way to implement it but I have not found a good guide about this. For now I have a JpaRepository where I've my custom synchronized save() method in which I get the last id used:

SELECT MAX(numero) FROM Invoice WHERE YEAR(date) = :year

The problem of this approach is that is not very safe because the developer should know that the save should be done only with that particular service.

Instead I'd like more an approach that is hidden to the developer. I thought to use a @Prepersist method in a @EntityListeners. This sounds good but do get entity manager inside this class is not so simple....so maybe is not the optimal place...

Finally I thought about Hibernate Interceptor....

Please give me some hints. The problem seems a quite generic problem; so maybe there is yet a good practice to follow.

Thanks

Answer 1

This problem can be broken down into the following requirements:

1. Sequentially unique: Generate numbers in a sequence, starting from a given value (say, 1000001) and then always incrementing by a fixed value (say, 1).
2. No gaps: There must not be any gaps between the numbers. So, if the first number generated is 1000001, the increment is 1 and 200 numbers have been generated so far, the latest number should be 1000201.
3. Concurrency: Multiple processes must be able to generate the numbers at the same time.
4. Generation at creation: The numbers must be generated at the time of creation of a record.
5. No exclusive locks: No exclusive locks should be required for generating the numbers.

Any solution can only comply with 4 out of these 5 requirements. For example, if you want to guarantee 1-4, each process will need to take locks so that no other process can generate and use the same number that it has generated. Therefore, imposing 1-4 as requirements will mean that 5 will have to be let gone of. Similarly, if you want to guarantee 1, 2, 4 and 5, you need to make sure that only one process (thread) generates a number at a time because uniqueness cannot be guaranteed in a concurrent environment without locking. Continue this logic and you will see why it is impossible to guarantee all of these requirements at the same time.

Now, the solution depends on which one out of 1-5 you are willing to sacrifice. If you are willing to sacrifice #4 but not #5, you can run a batch process during idle hours to generate the numbers. However, if you put this list in front of a business user (or a finance guy), they will ask you to comply with 1-4 as #5 is a purely technical issue (to them) and therefore they would not want to be bothered with it. If that is the case, a possible strategy is:

• Perform all possible computation required to generate an invoice upfront, keeping the invoice number generation step as the very last step. This will ensure that any exceptions that can occur, happen before the number is generated and also to make sure that a lock is taken for a very short amount of time, thereby not affecting the concurrency or performance of the application too much.
• Keep a separate table (for example, DOCUMENT_SEQUENCE) to keep a track of the last generated number.
• Just before saving an invoice, take an exclusive row-level lock on the sequence table (say, isolation level SERIALIZABLE), find the required sequence value to use and save the invoice immediately. This should not take too much time because reading a row, incrementing its value and saving a record should be a short enough operation. If possible, make this short transaction a nested transaction to the main one.
• Keep a decent-enough database timeout so that concurrent threads waiting for a SERIALIZABLE lock do not time out too fast.
• Keep this whole operation in a retry loop, retrying at least 10 times before giving up completely. This will ensure that if the lock queue builds up too fast, the operations are still tried a few times before giving up totally. Many commercial packages have retry count as high as 40, 60 or 100.

In addition to this, if possible and allowed by your database design guidelines, put a unique constraint on the invoice number column so that duplicate values are not stored at any cost.

Spring gives you all the tools to implement this.

• Transactions: Through the @Transactional annotation.
• Serialization: Through the isolation attribute of the @Transactional annotation.
• Database access: Through Spring JDBC, Spring ORM and Spring Data JPA.
• Retries: Through Spring Retry.

I have a sample app that demonstrates using all these pieces together.