Re: Should we use some lock when update qoh of InventoryItem
I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
The entity engine supports updates that include a WHERE clause - so it would be possible to update only when the lastUpdatedStamp matches. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 7:31 AM, Yaocl wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
Did you mean storeByCondition method. With it I must check the updated row count, if equals 0 retry again. SELECT FOR UPDATE will block one transaction to wait until the end of another transaction. On Wed, Aug 06, 2014 at 14:44, Adrian Crum adrian.c...@sandglass-software.com Wrote: The entity engine supports updates that include a WHERE clause - so it would be possible to update only when the lastUpdatedStamp matches. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 7:31 AM, Yaocl wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
Yes, I meant storeByCondition. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 8:08 AM, YaoCL wrote: Did you mean storeByCondition method. With it I must check the updated row count, if equals 0 retry again. SELECT FOR UPDATE will block one transaction to wait until the end of another transaction. On Wed, Aug 06, 2014 at 14:44, Adrian Crum adrian.c...@sandglass-software.com Wrote: The entity engine supports updates that include a WHERE clause - so it would be possible to update only when the lastUpdatedStamp matches. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 7:31 AM, Yaocl wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
As a note: we only SELECT FOR UPDATE when creating banks indexes, see SequenceUtil.SequenceBank.fillBank() which is a peculiar case. Jacques Le 06/08/2014 09:08, YaoCL a écrit : Did you mean storeByCondition method. With it I must check the updated row count, if equals 0 retry again. SELECT FOR UPDATE will block one transaction to wait until the end of another transaction. On Wed, Aug 06, 2014 at 14:44, Adrian Crum adrian.c...@sandglass-software.com Wrote: The entity engine supports updates that include a WHERE clause - so it would be possible to update only when the lastUpdatedStamp matches. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 7:31 AM, Yaocl wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
We have run into this problem in the past with the inventory and other entities when running multiple application servers under high load. We worked around this issue by creating a custom class that gets the Connection from the entity engine. Then we built a custom JDBC query to do a SELECT for UPDATE that guarantees no other process can update the record until the transaction is committed. I'm not sure this is the recommended way to do this in ofbiz but it worked for us in isolated situations. Brett On Wed, Aug 6, 2014 at 12:31 AM, Yaocl chunlin...@gmail.com wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
What concerns me about that approach is liveness. The approach I had in mind was (pseudo code): while updating do calculations update entity value was original value updated? if yes, exit while continue while Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:44 PM, Brett Palmer wrote: We have run into this problem in the past with the inventory and other entities when running multiple application servers under high load. We worked around this issue by creating a custom class that gets the Connection from the entity engine. Then we built a custom JDBC query to do a SELECT for UPDATE that guarantees no other process can update the record until the transaction is committed. I'm not sure this is the recommended way to do this in ofbiz but it worked for us in isolated situations. Brett On Wed, Aug 6, 2014 at 12:31 AM, Yaocl chunlin...@gmail.com wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
Yes, the select for update approach does have a potential for locked conditions. We used it rarely and only in those situations where we had to guarantee there was one and only one record. Brett On Wed, Aug 6, 2014 at 9:31 AM, Adrian Crum adrian.c...@sandglass-software.com wrote: What concerns me about that approach is liveness. The approach I had in mind was (pseudo code): while updating do calculations update entity value was original value updated? if yes, exit while continue while Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:44 PM, Brett Palmer wrote: We have run into this problem in the past with the inventory and other entities when running multiple application servers under high load. We worked around this issue by creating a custom class that gets the Connection from the entity engine. Then we built a custom JDBC query to do a SELECT for UPDATE that guarantees no other process can update the record until the transaction is committed. I'm not sure this is the recommended way to do this in ofbiz but it worked for us in isolated situations. Brett On Wed, Aug 6, 2014 at 12:31 AM, Yaocl chunlin...@gmail.com wrote: I can only consider a solution use SELECT FOR UPDATE, lock the inventory item then SELECT ... sum(...) FROM inventory_item_detail GROUP BY , then update the inventory item. But it seems EntityEngine not support FOR UPDATE. -- YaoCL On Wed, Aug 6, 2014 at 1:41 PM, Adrian Crum adrian.c...@sandglass-software.com wrote: This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Should we use some lock when update qoh of InventoryItem
Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
Re: Should we use some lock when update qoh of InventoryItem
This has been discussed on the dev mailing list, and there are differing opinions. I agree this is a bug and should be fixed, but others believe it is highly unlikely two people will be modifying the same invoice at the same time - so it is not a problem. Adrian Crum Sandglass Software www.sandglass-software.com On 8/6/2014 3:01 AM, YaoCL wrote: Hi, When create a new InventoryItemDetail the InventoryItem will be updated by eeca. But If two threads create InventoryItemDetails simultaneously. Because we use ReadCommitted Isolation level, Neither will see InventoryItemDetails created by other threads before transaction commit. updateInventoryItemFromDetail service will get incorrect qoh, and InventoryItem will be updated. Two threads will all be committed successfully. I can confirm the behavior by set a breakpoint in org.ofbiz.minilang.method.entityops.StoreValue.exec(MethodContext) to emulate above process. The result can be checked by this SQL. select * from inventory_item t1 left join ( select inventory_item_id, sum(quantity_on_hand_diff) as qoh, sum(available_to_promise_diff) as atp from inventory_item_detail group by inventory_item_id) t2 on t1.inventory_item_id = t2.inventory_item_id where t1.quantity_on_hand_total t2.qoh; — YaoCL
