You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Si Chen (JIRA)" <ji...@apache.org> on 2008/02/12 23:18:08 UTC
[jira] Created: (OFBIZ-1636) delegator.getNextSubSeqId does not
guarantee primary key uniqueness
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.
[jira] Commented: (OFBIZ-1636) delegator.getNextSubSeqId does not
guarantee primary key uniqueness
Posted by "David E. Jones (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-1636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12570508#action_12570508 ]
David E. Jones commented on OFBIZ-1636:
---------------------------------------
This is a known bug with a fix already in place in the trunk. See InventoryService.xml line 287 for a comment on it. The fix is a bit of a kludge since it abandons the nice sub-sequence and uses a global sequence instead.
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....
> 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.
[jira] Commented: (OFBIZ-1636) delegator.getNextSubSeqId does not
guarantee primary key uniqueness
Posted by "Si Chen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-1636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12570820#action_12570820 ]
Si Chen commented on OFBIZ-1636:
--------------------------------
thanks for your feedback. I think the fix you suggested is a pretty good one and probably the best course for now.
> 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.
[jira] Commented: (OFBIZ-1636) delegator.getNextSubSeqId does not
guarantee primary key uniqueness
Posted by "Jacques Le Roux (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-1636?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782986#action_12782986 ]
Jacques Le Roux commented on OFBIZ-1636:
----------------------------------------
Should we not close this issue now (and maybe reopen a new one with clear goal "new sub-sequence solution")?
> 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: Minor
>
> 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.
[jira] Updated: (OFBIZ-1636) delegator.getNextSubSeqId does not
guarantee primary key uniqueness
Posted by "Jacopo Cappellato (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-1636?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jacopo Cappellato updated OFBIZ-1636:
-------------------------------------
Priority: Minor (was: Critical)
David has provided a workaround for this, so I've changed the priority from Critical to Minor.
Or we can just close the issue?
> 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: Minor
>
> 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.