delegator.getNextSubSeqId does not guarantee primary key uniqueness
-------------------------------------------------------------------
Key: OFBIZ-1636
URL: https://issues.apache.org/jira/browse/OFBIZ-1636
Project: OFBiz
Issue Type: Bug
Components: framework
Reporter: Si Chen
Priority: Critical
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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
