You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by YaoCL <ch...@gmail.com> on 2014/08/06 04:01:41 UTC
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
Posted by Adrian Crum <ad...@sandglass-software.com>.
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 <ad...@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
>>> <ad...@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
Posted by Jacques Le Roux <ja...@les7arts.com>.
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 <ad...@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
>>> <ad...@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
Posted by YaoCL <ch...@gmail.com>.
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 <ad...@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
>> <ad...@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
Posted by Adrian Crum <ad...@sandglass-software.com>.
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
> <ad...@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
Posted by Brett Palmer <br...@gmail.com>.
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.crum@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 <ch...@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
>>> <ad...@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
Posted by Adrian Crum <ad...@sandglass-software.com>.
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 <ch...@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
>> <ad...@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
Posted by Brett Palmer <br...@gmail.com>.
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 <ch...@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
> <ad...@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
Posted by Yaocl <ch...@gmail.com>.
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
<ad...@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
Posted by Adrian Crum <ad...@sandglass-software.com>.
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
>