The delegator.getNextSubSeqId method currently will look into the table for all rows which shared the same primary key values except the subsequence ID field and returned the next available subsequence ID field value. However, if another process came in to ask for the same subsequence ID value, it will give the same value back again. It does not guarantee primary key uniqueness.
For example, if you are reserving inventory for inventory item ID 10000, delegator.getNextSubSeqId might return the next inventoryItemSeqId of 10. If you are reserving inventory for many items on a large order, however, your transaction would not be closed until all the inventory reservations are completed. In the meantime, if another order came in and inventory has to be reserved against inventory item ID 10000 again, delegator.getNextSubSeqId will give inventoryItemSeqId of 10 again. When both transactions try to commit, one of them will inevitably run into a foreign key collision.
We can think of two possible solutions for this problem:
1. Make inventoryItemSeqId the primary key of InventoryItemDetail table, and use delegator.getNextSeqId and SequenceValueItem to generate its values. InventoryItemDetail was still have an inventoryItemId and be related to InventoryItem.
2. Create a cache for subsequence ID values. The key of the cache would be a Map of the primary key fields for InventoryItemDetail minus inventoryItemSeqId, and the value of the cache would be the maximum available inventoryItemSeqId. The cache would be set to timeout or clear, every 30 minutes or so, and during that time it would be able to tell delegator.getNextSubSeqId what the maximum available value would be based on both what's in the database and what other processes may have been assigned. This method is more complicated and not as sure, but it would still allow us to have subsequence IDs of 1, 2, 3, 4, etc.
If anyone has other suggestions, please let me know.
Bug
Open
Minor
delegator.getNextSubSeqId does not guarantee primary key uniqueness
This fix is a variation on your #1, but doesn't change the primary key, which I don't think we want to right now and we may get a more reliable sub-sequence in the future.
It isn't a common case, but for now sub-sequenced entities that may be written to by more than one thread should use the global sequencing instead.
Unfortunately #2 is not a good solution as that would aggravate the problem with multiple app servers instead of helping it.
What is a good solution is a another question....