Optimistic locking based on timestamps

[James McGill-5]

We are having problems with the optimistic locking.   With "enable-lock" set
on an Entity, updates in GenericDAO use a timestamp to do locking.
There are a number of issues with this.  The biggest one is that it's not a
synchronized operation, so there's potential for a race condition within
customUpdate, which we are actually seeing in production.
I added code to introduce the "FOR UPDATE" expression when reading the
timestamp.  This brings up another issue, that the timestamp field in MySQL
has resolution only to the second.  So even if you don't have contention on
the optimistic lock SELECT, you still have to be lucky that your
transactions are more than one second apart.

I realize this is a fairly difficult problem to address, in general, and
that "fixing" many concurrency issues leads to risks of deadlock.  But we
are seeing errors in data where the "last update wins."

Has anyone else had concurrency problems when multiple threads are updating
entities?  Are there any locking provisions in the Delegator that would
allow us to prevent this kind of problem?

--
James McGill
Phoenix AZ

[Jacques Le Roux]

This is why I prefer to use PostGres but that's another story and of course the same problem could occur at the ms level, 1000 time
less though...

Jacques

From: "James McGill" <[hidden email]>

> We are having problems with the optimistic locking.   With "enable-lock" set
> on an Entity, updates in GenericDAO use a timestamp to do locking.
> There are a number of issues with this.  The biggest one is that it's not a
> synchronized operation, so there's potential for a race condition within
> customUpdate, which we are actually seeing in production.
> I added code to introduce the "FOR UPDATE" expression when reading the
> timestamp.  This brings up another issue, that the timestamp field in MySQL
> has resolution only to the second.  So even if you don't have contention on
> the optimistic lock SELECT, you still have to be lucky that your
> transactions are more than one second apart.
>
> I realize this is a fairly difficult problem to address, in general, and
> that "fixing" many concurrency issues leads to risks of deadlock.  But we
> are seeing errors in data where the "last update wins."
>
> Has anyone else had concurrency problems when multiple threads are updating
> entities?  Are there any locking provisions in the Delegator that would
> allow us to prevent this kind of problem?
>
> --
> James McGill
> Phoenix AZ
>

[James McGill-5]

On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
[hidden email]> wrote:

> This is why I prefer to use PostGres but that's another story and of course
> the same problem could occur at the ms level, 1000 time less though...
>
> Jacques
>


I was hoping you would post to tell me I was wrong, and  point out the
locking semantics in the delegator that the application can use.

My current plan is to extend the delegator and minilang so that "findOne"
and <entity-one> can have a "for update" parameter, so that at least the
application can decide to do a select for update, to introduce some locking
to avoid concurrency bugs.

Right now, it's fairly common to for us to issue inventory items until the
quantity goes below zero, because there's no way to regulate concurrency
between two threads that want to issue.   There are many parts of the system
where this might not be such a problem, but on InventoryItem it's a
potential nightmare.

What do you think about my idea of giving the delegator a "select for
update"  option?


--
James McGill
Phoenix AZ

[Jacques Le Roux]

From: "James McGill" <[hidden email]>

> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> [hidden email]> wrote:
>
>> This is why I prefer to use PostGres but that's another story and of course
>> the same problem could occur at the ms level, 1000 time less though...
>>
>> Jacques
>>
>
>
> I was hoping you would post to tell me I was wrong, and  point out the
> locking semantics in the delegator that the application can use.

Sorry I have no time to do that tonight, hopefully someone will jump in though...

Jacques

> My current plan is to extend the delegator and minilang so that "findOne"
> and <entity-one> can have a "for update" parameter, so that at least the
> application can decide to do a select for update, to introduce some locking
> to avoid concurrency bugs.
>
> Right now, it's fairly common to for us to issue inventory items until the
> quantity goes below zero, because there's no way to regulate concurrency
> between two threads that want to issue.   There are many parts of the system
> where this might not be such a problem, but on InventoryItem it's a
> potential nightmare.
>
> What do you think about my idea of giving the delegator a "select for
> update"  option?
>
>
> --
> James McGill
> Phoenix AZ
>

[David E. Jones-2]

On Aug 12, 2010, at 3:21 PM, James McGill wrote:

> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> [hidden email]> wrote:
>
>> This is why I prefer to use PostGres but that's another story and of course
>> the same problem could occur at the ms level, 1000 time less though...
>>
>> Jacques
>>
>
>
> I was hoping you would post to tell me I was wrong, and  point out the
> locking semantics in the delegator that the application can use.
>
> My current plan is to extend the delegator and minilang so that "findOne"
> and <entity-one> can have a "for update" parameter, so that at least the
> application can decide to do a select for update, to introduce some locking
> to avoid concurrency bugs.
>
> Right now, it's fairly common to for us to issue inventory items until the
> quantity goes below zero, because there's no way to regulate concurrency
> between two threads that want to issue.   There are many parts of the system
> where this might not be such a problem, but on InventoryItem it's a
> potential nightmare.
>
> What do you think about my idea of giving the delegator a "select for
> update"  option?

Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.

As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.

I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another).

I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.

-David

[Adrian Crum-2]

I recently committed some code that provides a workaround for the sub-second resolution issue:

http://ci.apache.org/projects/ofbiz/site/javadocs/org/ofbiz/entity/jdbc/JdbcValueHandler.TimestampJdbcValueHandler.html

-Adrian


--- On Thu, 8/12/10, James McGill <[hidden email]> wrote:

> From: James McGill <[hidden email]>
> Subject: Optimistic locking based on timestamps
> To: [hidden email]
> Date: Thursday, August 12, 2010, 1:15 PM
> We are having problems with the
> optimistic locking.   With "enable-lock" set
> on an Entity, updates in GenericDAO use a timestamp to do
> locking.
> There are a number of issues with this.  The biggest
> one is that it's not a
> synchronized operation, so there's potential for a race
> condition within
> customUpdate, which we are actually seeing in production.
> I added code to introduce the "FOR UPDATE" expression when
> reading the
> timestamp.  This brings up another issue, that the
> timestamp field in MySQL
> has resolution only to the second.  So even if you
> don't have contention on
> the optimistic lock SELECT, you still have to be lucky that
> your
> transactions are more than one second apart.
>
> I realize this is a fairly difficult problem to address, in
> general, and
> that "fixing" many concurrency issues leads to risks of
> deadlock.  But we
> are seeing errors in data where the "last update wins."
>
> Has anyone else had concurrency problems when multiple
> threads are updating
> entities?  Are there any locking provisions in the
> Delegator that would
> allow us to prevent this kind of problem?
>
> --
> James McGill
> Phoenix AZ
>

[Matt Warnock]

We also determined a few months ago that some ofbiz code apparently
nests transactions, which MySQL does not support, at least as of 5.5,
which we were looking at then IIRC.
--
Matt Warnock <[hidden email]>
RidgeCrest Herbals, Inc.

On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:

> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>
> > On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> > [hidden email]> wrote:
> >
> >> This is why I prefer to use PostGres but that's another story and of course
> >> the same problem could occur at the ms level, 1000 time less though...
> >>
> >> Jacques
> >>
> >
> >
> > I was hoping you would post to tell me I was wrong, and  point out the
> > locking semantics in the delegator that the application can use.
> >
> > My current plan is to extend the delegator and minilang so that "findOne"
> > and <entity-one> can have a "for update" parameter, so that at least the
> > application can decide to do a select for update, to introduce some locking
> > to avoid concurrency bugs.
> >
> > Right now, it's fairly common to for us to issue inventory items until the
> > quantity goes below zero, because there's no way to regulate concurrency
> > between two threads that want to issue.   There are many parts of the system
> > where this might not be such a problem, but on InventoryItem it's a
> > potential nightmare.
> >
> > What do you think about my idea of giving the delegator a "select for
> > update"  option?
>
> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
>
> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
>
> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another).
>
> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
>
> -David

[David E. Jones-2]

What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.

-David


On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:

> We also determined a few months ago that some ofbiz code apparently
> nests transactions, which MySQL does not support, at least as of 5.5,
> which we were looking at then IIRC.
> --
> Matt Warnock <[hidden email]>
> RidgeCrest Herbals, Inc.
>
> On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
>> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>>
>>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
>>> [hidden email]> wrote:
>>>
>>>> This is why I prefer to use PostGres but that's another story and of course
>>>> the same problem could occur at the ms level, 1000 time less though...
>>>>
>>>> Jacques
>>>>
>>>
>>>
>>> I was hoping you would post to tell me I was wrong, and  point out the
>>> locking semantics in the delegator that the application can use.
>>>
>>> My current plan is to extend the delegator and minilang so that "findOne"
>>> and <entity-one> can have a "for update" parameter, so that at least the
>>> application can decide to do a select for update, to introduce some locking
>>> to avoid concurrency bugs.
>>>
>>> Right now, it's fairly common to for us to issue inventory items until the
>>> quantity goes below zero, because there's no way to regulate concurrency
>>> between two threads that want to issue.   There are many parts of the system
>>> where this might not be such a problem, but on InventoryItem it's a
>>> potential nightmare.
>>>
>>> What do you think about my idea of giving the delegator a "select for
>>> update"  option?
>>
>> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
>>
>> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
>>
>> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another).
>>
>> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
>>
>> -David
>

[Matt Warnock]

I'd have to look back at the archives, but I was emailing with someone
who posted a log showing that OFBiz entered one transaction, then
entered another at a lower level of code.  MySQL burped at that point
and rolled back the outside transaction.  I was not able to replicate
the problem under Derby or PostgreSQL, but it seemed odd to me that
OFBiz would nest one transaction inside another.  Still, the OFBiz trace
logs pretty clearly showed (at least so I thought) that was what had
happened.  I found the docs that said that MySQL would not do that, and
told him so.  I don't know OFBiz well enough yet to remember where the
code burped, and as I said, it never did it for me.

It may have been his custom code or something-- I certainly would hope
nested transactions are not "normal" in OFBiz, as support for them is
anything but universal.  I'm not even sure Postgres supports them in any
"standard" way. I looked for my email, can't find it, but perhaps
someone who knows how/where to search the message archives might be able
to turn it up.  I'm going by memory, which is a bit dodgy.
--
Matt Warnock <[hidden email]>
RidgeCrest Herbals, Inc.

On Thu, 2010-08-12 at 18:26 -0600, David E Jones wrote:

> What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.
>
> -David
>
>
> On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:
>
> > We also determined a few months ago that some ofbiz code apparently
> > nests transactions, which MySQL does not support, at least as of 5.5,
> > which we were looking at then IIRC.
> > --
> > Matt Warnock <[hidden email]>
> > RidgeCrest Herbals, Inc.
> >
> > On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
> >> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> >>
> >>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> >>> [hidden email]> wrote:
> >>>
> >>>> This is why I prefer to use PostGres but that's another story and of course
> >>>> the same problem could occur at the ms level, 1000 time less though...
> >>>>
> >>>> Jacques
> >>>>
> >>>
> >>>
> >>> I was hoping you would post to tell me I was wrong, and  point out the
> >>> locking semantics in the delegator that the application can use.
> >>>
> >>> My current plan is to extend the delegator and minilang so that "findOne"
> >>> and <entity-one> can have a "for update" parameter, so that at least the
> >>> application can decide to do a select for update, to introduce some locking
> >>> to avoid concurrency bugs.
> >>>
> >>> Right now, it's fairly common to for us to issue inventory items until the
> >>> quantity goes below zero, because there's no way to regulate concurrency
> >>> between two threads that want to issue.   There are many parts of the system
> >>> where this might not be such a problem, but on InventoryItem it's a
> >>> potential nightmare.
> >>>
> >>> What do you think about my idea of giving the delegator a "select for
> >>> update"  option?
> >>
> >> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
> >>
> >> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
> >>
> >> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another).
> >>
> >> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
> >>
> >> -David
> >

[James McGill-5]

On Thu, Aug 12, 2010 at 3:19 PM, David E Jones <[hidden email]> wrote:

>
> As Jacques mentioned chances are you'll still have a better experience with
> Postgres when it comes to concurrency issues, in the way they manage
> transactions and locking in addition to the timestamp resolution issue.
>

The idea of migrating a production system from MySQL to Postgres stops my
heart.   We depend on MySQL's live replication feature for one thing, and
the only issue we've had so far that makes us even consider the database, is
because the delegator is giving us a data integrity problem.

I'm happy to have the confirmation that the problem is not just because
we're doing it wrong.  We are working a solution into the delegator so that
the application can request locks, since it's a pretty unusual case where it
is actually a problem.  Unfortunately, the problem manifests with inventory
counts, which for us is a pretty big issue.

Reading ahead, I'm really interested in the nested transaction problem.

Since you're strongly suggesting Postgres, do you have any advice for
migrating a live system between databases, hopefully from an Entity
perspective?

--
James McGill
Phoenix AZ

[David E. Jones-2]

There isn't really any such things as a "nested" transaction. That implies some sort of hierarchy of transactions, ie you begin some sort of sub-transaction that is part of another transaction. I've only ever seen the concept in database theory, but in real practice most databases don't support them, and I don't think JDBC supports them either.

What OFBiz does in many places is pause an active transaction, begin an independent transaction, and when that transaction is done then resume the original transaction. The database doesn't even know there is any relationship between the transactions, and in fact there is none except for in the application the both transactions are being managed by the same thread.

This happens in many places in OFBiz (search for any service with require-new-transaction=true), and works fine on MySQL. This type of code is easy to mess up, ie lock a resource in the paused transaction and try to use it in the second transaction and you'll get a lock. It's not technically a "dead lock" and the database has no way to detect the dependency because it doesn't know the two transactions are related, it just waits until a timeout is hit. However, that is independent of the database, ie it would happen with any database. In spite of other problems I've seen with MySQL, this is not something I've seen any issues with.

-David


On Aug 12, 2010, at 7:01 PM, Matt Warnock wrote:

> I'd have to look back at the archives, but I was emailing with someone
> who posted a log showing that OFBiz entered one transaction, then
> entered another at a lower level of code.  MySQL burped at that point
> and rolled back the outside transaction.  I was not able to replicate
> the problem under Derby or PostgreSQL, but it seemed odd to me that
> OFBiz would nest one transaction inside another.  Still, the OFBiz trace
> logs pretty clearly showed (at least so I thought) that was what had
> happened.  I found the docs that said that MySQL would not do that, and
> told him so.  I don't know OFBiz well enough yet to remember where the
> code burped, and as I said, it never did it for me.
>
> It may have been his custom code or something-- I certainly would hope
> nested transactions are not "normal" in OFBiz, as support for them is
> anything but universal.  I'm not even sure Postgres supports them in any
> "standard" way. I looked for my email, can't find it, but perhaps
> someone who knows how/where to search the message archives might be able
> to turn it up.  I'm going by memory, which is a bit dodgy.
> --
> Matt Warnock <[hidden email]>
> RidgeCrest Herbals, Inc.
>
> On Thu, 2010-08-12 at 18:26 -0600, David E Jones wrote:
>> What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.
>>
>> -David
>>
>>
>> On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:
>>
>>> We also determined a few months ago that some ofbiz code apparently
>>> nests transactions, which MySQL does not support, at least as of 5.5,
>>> which we were looking at then IIRC.
>>> --
>>> Matt Warnock <[hidden email]>
>>> RidgeCrest Herbals, Inc.
>>>
>>> On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
>>>> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>>>>
>>>>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
>>>>> [hidden email]> wrote:
>>>>>
>>>>>> This is why I prefer to use PostGres but that's another story and of course
>>>>>> the same problem could occur at the ms level, 1000 time less though...
>>>>>>
>>>>>> Jacques
>>>>>>
>>>>>
>>>>>
>>>>> I was hoping you would post to tell me I was wrong, and  point out the
>>>>> locking semantics in the delegator that the application can use.
>>>>>
>>>>> My current plan is to extend the delegator and minilang so that "findOne"
>>>>> and <entity-one> can have a "for update" parameter, so that at least the
>>>>> application can decide to do a select for update, to introduce some locking
>>>>> to avoid concurrency bugs.
>>>>>
>>>>> Right now, it's fairly common to for us to issue inventory items until the
>>>>> quantity goes below zero, because there's no way to regulate concurrency
>>>>> between two threads that want to issue.   There are many parts of the system
>>>>> where this might not be such a problem, but on InventoryItem it's a
>>>>> potential nightmare.
>>>>>
>>>>> What do you think about my idea of giving the delegator a "select for
>>>>> update"  option?
>>>>
>>>> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
>>>>
>>>> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
>>>>
>>>> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another).
>>>>
>>>> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
>>>>
>>>> -David
>>>
>

[rajsaini]

> Since you're strongly suggesting Postgres, do you have any advice for
> migrating a live system between databases, hopefully from an Entity
> perspective?
>    
May be export/import of the data in entity-engine-xml help the
migration. You do not need to deal with the DMBS specific queries and
OFBiz already handles the table creation using entity models.

Thanks,

Raj

[Jacques Le Roux]

You may try that, remember to Create/Remove All Foreign Keys
https://demo-trunk.ofbiz.apache.org/webtools/control/view/checkdb
Else you will get hung. Note that this is maybe not enough...

Jacques

From: "Raj Saini" <[hidden email]>

>> Since you're strongly suggesting Postgres, do you have any advice for
>> migrating a live system between databases, hopefully from an Entity
>> perspective?
>>    
> May be export/import of the data in entity-engine-xml help the
> migration. You do not need to deal with the DMBS specific queries and
> OFBiz already handles the table creation using entity models.
>
> Thanks,
>
> Raj
>
>

[Matt Warnock]

Then apparently the stuff that is supposed to separate the "unrelated"
transactions in the same database connection wasn't working in this
case, as the log file clearly indicated that MySQL wasn't seeing the
inner transaction in a separate connection, but saw a nested "begin"
statement within the same connection, causing the outer transaction to
abort.

As I say, it may have been some custom code.  Perhaps he just forgot to
set "require-new-transaction" on a custom service or such.  I do recall
that the inner transaction was in a separate service, or at least a
separately called code block, so the transactions were not nested within
a single code segment.  And you're right, that would not be a MySQL
issue in that case.  

--
Matt Warnock <[hidden email]>
RidgeCrest Herbals, Inc.

On Thu, 2010-08-12 at 20:49 -0600, David E Jones wrote:

> There isn't really any such things as a "nested" transaction. That
>  implies some sort of hierarchy of transactions, ie you begin some sort
>  of sub-transaction that is part of another transaction. I've only ever
>  seen the concept in database theory, but in real practice most
>  databases don't support them, and I don't think JDBC supports them
>  either.
>
> What OFBiz does in many places is pause an active transaction, begin an
>  independent transaction, and when that transaction is done then resume
>  the original transaction. The database doesn't even know there is any
>  relationship between the transactions, and in fact there is none
>  except for in the application the both transactions are being managed
>  by the same thread.
>
> This happens in many places in OFBiz (search for any service with
>  require-new-transaction=true), and works fine on MySQL. This type of
>  code is easy to mess up, ie lock a resource in the paused transaction
>  and try to use it in the second transaction and you'll get a lock.
>  It's not technically a "dead lock" and the database has no way to
>  detect the dependency because it doesn't know the two transactions are
>  related, it just waits until a timeout is hit. However, that is
>  independent of the database, ie it would happen with any database. In
>  spite of other problems I've seen with MySQL, this is not something
>  I've seen any issues with.
>
> -David
>
>
> On Aug 12, 2010, at 7:01 PM, Matt Warnock wrote:
>
> > I'd have to look back at the archives, but I was emailing with someone
> > who posted a log showing that OFBiz entered one transaction, then
> > entered another at a lower level of code.  MySQL burped at that point
> > and rolled back the outside transaction.  I was not able to replicate
> > the problem under Derby or PostgreSQL, but it seemed odd to me that
> > OFBiz would nest one transaction inside another.  Still, the OFBiz trace
> > logs pretty clearly showed (at least so I thought) that was what had
> > happened.  I found the docs that said that MySQL would not do that, and
> > told him so.  I don't know OFBiz well enough yet to remember where the
> > code burped, and as I said, it never did it for me.
> >
> > It may have been his custom code or something-- I certainly would hope
> > nested transactions are not "normal" in OFBiz, as support for them is
> > anything but universal.  I'm not even sure Postgres supports them in any
> > "standard" way. I looked for my email, can't find it, but perhaps
> > someone who knows how/where to search the message archives might be able
> > to turn it up.  I'm going by memory, which is a bit dodgy.
> > --
> > Matt Warnock <[hidden email]>
> > RidgeCrest Herbals, Inc.
> >
> > On Thu, 2010-08-12 at 18:26 -0600, David E Jones wrote:
> >> What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.
> >>
> >> -David
> >>
> >>
> >> On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:
> >>
> >>> We also determined a few months ago that some ofbiz code apparently
> >>> nests transactions, which MySQL does not support, at least as of 5.5,
> >>> which we were looking at then IIRC.
> >>> --
> >>> Matt Warnock <[hidden email]>
> >>> RidgeCrest Herbals, Inc.
> >>>
> >>> On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
> >>>> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> >>>>
> >>>>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> >>>>> [hidden email]> wrote:
> >>>>>
> >>>>>> This is why I prefer to use PostGres but that's another story and of course
> >>>>>> the same problem could occur at the ms level, 1000 time less though...
> >>>>>>
> >>>>>> Jacques
> >>>>>>
> >>>>>
> >>>>>
> >>>>> I was hoping you would post to tell me I was wrong, and  point out the
> >>>>> locking semantics in the delegator that the application can use.
> >>>>>
> >>>>> My current plan is to extend the delegator and minilang so that "findOne"
> >>>>> and <entity-one> can have a "for update" parameter, so that at least the
> >>>>> application can decide to do a select for update, to introduce some locking
> >>>>> to avoid concurrency bugs.
> >>>>>
> >>>>> Right now, it's fairly common to for us to issue inventory items until the
> >>>>> quantity goes below zero, because there's no way to regulate concurrency
> >>>>> between two threads that want to issue.   There are many parts of the system
> >>>>> where this might not be such a problem, but on InventoryItem it's a
> >>>>> potential nightmare.
> >>>>>
> >>>>> What do you think about my idea of giving the delegator a "select for
> >>>>> update"  option?
> >>>>
> >>>> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
> >>>>
> >>>> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
> >>>>
> >>>> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another).
> >>>>
> >>>> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
> >>>>
> >>>> -David
> >>>
> >

[Matt Warnock]

Synchronization is the one area that postgres has traditionally trailed
mysql, but I know I have seen presentations on sync tools (but have not
used them) for postgres including:

www.slony.info
http://eradman.com/posts/pgsql-sync.html

There was another promising project whose name I can't now recall, and
Google isn't presently helping.  Perhaps after some sleep.
--
Matt Warnock <[hidden email]>
RidgeCrest Herbals, Inc.

On Fri, 2010-08-13 at 11:08 +0530, Raj Saini wrote:

> > Since you're strongly suggesting Postgres, do you have any advice for
> > migrating a live system between databases, hopefully from an Entity
> > perspective?
> >    
> May be export/import of the data in entity-engine-xml help the
> migration. You do not need to deal with the DMBS specific queries and
> OFBiz already handles the table creation using entity models.
>
> Thanks,
>
> Raj
>

[rajsaini]

PostgreSQL 9.0 is going to have built in streaming replication feature.
It is in beta 4 which is final beta. You can find more about streaming
replication at
http://www.depesz.com/index.php/2010/02/01/waiting-for-9-0-streaming-replication/

Thanks,

Raj

On 13/08/10 12:21, Matt Warnock wrote:

> Synchronization is the one area that postgres has traditionally trailed
> mysql, but I know I have seen presentations on sync tools (but have not
> used them) for postgres including:
>
> www.slony.info
> http://eradman.com/posts/pgsql-sync.html
>
> There was another promising project whose name I can't now recall, and
> Google isn't presently helping.  Perhaps after some sleep.
>    

[Jacques Le Roux]

+1

Morevoer MySql seems to sleep since it's Oracle, surprised?
On the other hand there is now http://askmonty.org/wiki/MariaDB

I also like Postgres for its licence (MIT/BSD style hence ASL2 compatilble) when MySql is GPL and still MariaDB, even if it's not a
big deal regarding OFBiz...

Currently in Postgres http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Jacques

From: "Raj Saini" <[hidden email]>

> PostgreSQL 9.0 is going to have built in streaming replication feature. It is in beta 4 which is final beta. You can find more
> about streaming replication at http://www.depesz.com/index.php/2010/02/01/waiting-for-9-0-streaming-replication/
>
> Thanks,
>
> Raj
>
> On 13/08/10 12:21, Matt Warnock wrote:
>> Synchronization is the one area that postgres has traditionally trailed
>> mysql, but I know I have seen presentations on sync tools (but have not
>> used them) for postgres including:
>>
>> www.slony.info
>> http://eradman.com/posts/pgsql-sync.html
>>
>> There was another promising project whose name I can't now recall, and
>> Google isn't presently helping.  Perhaps after some sleep.
>>
>

[Brett]

David,

I like postgres for the reason you have mentioned.

The number one reason we use MySQL in some of our deployments is because its
replication solution is very good.  You can replicate your data from the
database very quickly.  We do this for database backups in production as
well as creating reporting servers.

I have not been able to find a good replication solution for postgres.  Its
been a while since I looked but the last time I investigated replication I
couldn't find any.


Brett





On Thu, Aug 12, 2010 at 4:19 PM, David E Jones <[hidden email]> wrote:

>
> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>
> > On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> > [hidden email]> wrote:
> >
> >> This is why I prefer to use PostGres but that's another story and of
> course
> >> the same problem could occur at the ms level, 1000 time less though...
> >>
> >> Jacques
> >>
> >
> >
> > I was hoping you would post to tell me I was wrong, and  point out the
> > locking semantics in the delegator that the application can use.
> >
> > My current plan is to extend the delegator and minilang so that "findOne"
> > and <entity-one> can have a "for update" parameter, so that at least the
> > application can decide to do a select for update, to introduce some
> locking
> > to avoid concurrency bugs.
> >
> > Right now, it's fairly common to for us to issue inventory items until
> the
> > quantity goes below zero, because there's no way to regulate concurrency
> > between two threads that want to issue.   There are many parts of the
> system
> > where this might not be such a problem, but on InventoryItem it's a
> > potential nightmare.
> >
> > What do you think about my idea of giving the delegator a "select for
> > update"  option?
>
> Adding a for-update option is a good idea, and is something I have
> incorporated into the Moqui design.
>
> As Jacques mentioned chances are you'll still have a better experience with
> Postgres when it comes to concurrency issues, in the way they manage
> transactions and locking in addition to the timestamp resolution issue.
>
> I honestly don't know why so many people like MySQL compared to Postgres,
> but I know that many people do.  Maybe it's the greater marketing budget of
> corporate-driven MySQL versus the more community-driven Postgres. It's also
> a shame that when SAP DB was scavenged for useful things to go into MySQL
> that it wasn't done the other way around, ie take useful things from MySQL
> and put them into SAP DB. Of course, I haven't looked into the state of
> either code base before this was done, but I do know which organization
> acquired the other and that probably drove the direction for the software
> (it's bad marketing to come out and say you're tossing most of your main
> software stack to go forward with another).
>
> I could certainly be wrong, and if any MySQL fans out there want to help me
> understand why maybe it will even make it through my shield of bias.
>
> -David
>
>

[Matt Warnock]

Here is some data on Postgres replication:
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
It was Bucardo I was looking at a year ago or so, it looked promising.
--
Matt Warnock <[hidden email]>
RidgeCrest Herbals, Inc.

On Fri, 2010-08-13 at 10:43 -0600, Brett Palmer wrote:

> David,
>
> I like postgres for the reason you have mentioned.
>
> The number one reason we use MySQL in some of our deployments is because its
> replication solution is very good.  You can replicate your data from the
> database very quickly.  We do this for database backups in production as
> well as creating reporting servers.
>
> I have not been able to find a good replication solution for postgres.  Its
> been a while since I looked but the last time I investigated replication I
> couldn't find any.
>
>
> Brett
>
>
>
>
>
> On Thu, Aug 12, 2010 at 4:19 PM, David E Jones <[hidden email]> wrote:
>
> >
> > On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> >
> > > On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> > > [hidden email]> wrote:
> > >
> > >> This is why I prefer to use PostGres but that's another story and of
> > course
> > >> the same problem could occur at the ms level, 1000 time less though...
> > >>
> > >> Jacques
> > >>
> > >
> > >
> > > I was hoping you would post to tell me I was wrong, and  point out the
> > > locking semantics in the delegator that the application can use.
> > >
> > > My current plan is to extend the delegator and minilang so that "findOne"
> > > and <entity-one> can have a "for update" parameter, so that at least the
> > > application can decide to do a select for update, to introduce some
> > locking
> > > to avoid concurrency bugs.
> > >
> > > Right now, it's fairly common to for us to issue inventory items until
> > the
> > > quantity goes below zero, because there's no way to regulate concurrency
> > > between two threads that want to issue.   There are many parts of the
> > system
> > > where this might not be such a problem, but on InventoryItem it's a
> > > potential nightmare.
> > >
> > > What do you think about my idea of giving the delegator a "select for
> > > update"  option?
> >
> > Adding a for-update option is a good idea, and is something I have
> > incorporated into the Moqui design.
> >
> > As Jacques mentioned chances are you'll still have a better experience with
> > Postgres when it comes to concurrency issues, in the way they manage
> > transactions and locking in addition to the timestamp resolution issue.
> >
> > I honestly don't know why so many people like MySQL compared to Postgres,
> > but I know that many people do.  Maybe it's the greater marketing budget of
> > corporate-driven MySQL versus the more community-driven Postgres. It's also
> > a shame that when SAP DB was scavenged for useful things to go into MySQL
> > that it wasn't done the other way around, ie take useful things from MySQL
> > and put them into SAP DB. Of course, I haven't looked into the state of
> > either code base before this was done, but I do know which organization
> > acquired the other and that probably drove the direction for the software
> > (it's bad marketing to come out and say you're tossing most of your main
> > software stack to go forward with another).
> >
> > I could certainly be wrong, and if any MySQL fans out there want to help me
> > understand why maybe it will even make it through my shield of bias.
> >
> > -David
> >
> >

[Brett]

James,

We have run into this same problem on MySQL and ofbiz.  We worked around the
problem by creating a custom method that got a direction connection from the
transaction manager.  Then we wrote a custom SELECT for UPDATE on that
connection.  We needed this functionality because we had multiple
application servers hitting the same database and ran into concurrency
problems without it.

I would like to see the optimistic locking feature enhanced in ofbiz.  Maybe
we could move away from timestamps and use an increasing unique ID as a
replacement.  This is definitely a problem with MySQL.  We may move away
from MySQL if we can find a good replication solution from Postgres.


Brett

On Thu, Aug 12, 2010 at 2:15 PM, James McGill <
[hidden email]> wrote:

> We are having problems with the optimistic locking.   With "enable-lock"
> set
> on an Entity, updates in GenericDAO use a timestamp to do locking.
> There are a number of issues with this.  The biggest one is that it's not a
> synchronized operation, so there's potential for a race condition within
> customUpdate, which we are actually seeing in production.
> I added code to introduce the "FOR UPDATE" expression when reading the
> timestamp.  This brings up another issue, that the timestamp field in MySQL
> has resolution only to the second.  So even if you don't have contention on
> the optimistic lock SELECT, you still have to be lucky that your
> transactions are more than one second apart.
>
> I realize this is a fairly difficult problem to address, in general, and
> that "fixing" many concurrency issues leads to risks of deadlock.  But we
> are seeing errors in data where the "last update wins."
>
> Has anyone else had concurrency problems when multiple threads are updating
> entities?  Are there any locking provisions in the Delegator that would
> allow us to prevent this kind of problem?
>
> --
> James McGill
> Phoenix AZ
>