Should we use some lock when update qoh of InventoryItem
Locked
 
|
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 > |
|
|
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 <[hidden email]> 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 > <[hidden email]> 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 >>> >> |
|
|
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 <[hidden email]> 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 >> <[hidden email]> 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 <[hidden email]> 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 >>> <[hidden email]> 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
Administrator
|
In reply to this post by YaoCL
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 <[hidden email]> 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 >>> <[hidden email]> 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 >>>>> > > > |
|
|
In reply to this post by YaoCL
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 <[hidden email]> 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 > <[hidden email]> 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 <[hidden email]> 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 >> <[hidden email]> 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 >>>> >>> >> > |
|
|
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 < [hidden email]> 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 <[hidden email]> 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 >>> <[hidden email]> 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 >>>>> >>>>> >>>> >>> >> |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |