Paul, Good it now works. However it seems weird to me that it was this change which helped. Do you use InnoDB tables? In MySQL READ COMMITTED is a lower isolation level than REPEATABLE READ. I am wondering how could lowering the isolation level improve the consistency?
In this case I would try SELECT FOR UPDATE (EntityManager.lock in JPA terms) - that would cause the 2nd thread to wait until the 1st does its job and execute find+calculate+persist without losing anything. I am afraid that in MySQL > > 1) EJB finds the ACCOUNT row for the given account id using a simple find > > 2) Updates a field on the ACCOUNT row to get a lock on the row and does > > em.persist and em.flush will not prevent the 2nd thread from reading ACCOUNT (maybe except if you set SERIALIZABLE isolation). The same thing with BALANCE. You can simulate it with two mysql command line sessions. Hope I helped you anyways... Cheers, Milosz > Ok, I found the error of my ways... I've been so used to Oracle that I > completely forgot that MySQL defaults its transaction isolation to > REPEATABLE READ... hence the behaviour experienced as the second thread is > not being given the data that was committed after its transaction was > started. I can thus confirm that REPEATABLE READ works on MySQL ;-) When I > was looking in the DB to see what the balance was after step 3, I was using > Toad which purposely changes the connections isolation level to READ > COMMITTED so it can see data recently committed. > > I changed the isolation level to READ COMMITTED and the world is a happy > place again! Its weird that MySQL defaults to RR when Oracle and SQL Server > both default to RC. Certainly something to watch out for! > > Regards > Paul > > > > > PaulCB wrote: > > > > Hi, > > > > I'm using OpenJPA in Glassfish.with MySQL. I have 2 tables, ACCOUNT and > > BALANCE with a 1 to many relationship. I run a stress test with 30 threads > > calling into a web service (implemented as a CMP EJB). The web service > > accepts an account id and an amount to deduct from each balance related to > > the ACCOUNT: > > > > 0) Container starts TX > > 1) EJB finds the ACCOUNT row for the given account id using a simple find > > 2) Updates a field on the ACCOUNT row to get a lock on the row and does > > em.persist and em.flush > > 3) Finds the BALANCE rows for the ACCOUNT row and gets each balance and > > calculates what the new balance should be. This find is done using a > > simple JPQL query against the BALANCE table. The code also logs the > > balances found. > > 4) Updates the BALANCE rows with the new calculated balance via the entity > > objects setters and does em.persist and em.flush > > 5) logs the new balances and commits the transaction > > > > My logging shows that the locking on step 2 prevents multiple threads > > trying to do steps 2-5 simultaniously, thus avoiding dirty reads > > (supposedly..). However, if I log the balances at step 3 and step 5, every > > now an then (say once in 10 calls), the value logged at step 3 is not the > > same as that logged by the previous thread at step 5. It seems that > > sometimes step 3 shows the values prior to the updates made by the > > previous thread. If I make the thread sleep at step 3 for a few seconds > > giving me time to check in the DB to see what values are in the BALANCE > > table, the DB has the correct "new" values but the query at 3 shows the > > "old" values. I have tried doing several em.refresh calls on the ACCOUNT > > and BALANCE entity objects at step 3, looked up the balances using entity > > relationships and all sorts, but to no avail. > > > > My first impression was that this was L2 caching related, but my > > persistence.xml has both data and query caching set to false. This problem > > really worries me as it makes me feel I cant trust the results returned by > > OpenJPA. These are financial transactions and under load, money is > > "disappearing" as dirty balances are used to calculate a new balance. E.g. > > if an account has $10 and 2 threads try to simultaniously deduct $1, my > > account should do this: > > > > $10 - $1 = $9 > > 9$ - $1 = $8 > > > > NOT... > > > > $10 - $1 = $9 > > $10 - $1 = $9 > > > > Although i wouldn't mind being the owner of this account ;-) > > > > Does anyone have any clues on what to look into on this??? If i run the > > same test on my laptop, I cant reproduce the results, but if I move to a > > server with multiple multi core CPU's, then it is easy to reproduce. > > > > Thanks > > Paul > > > > -- > View this message in context: > http://n2.nabble.com/Dirty-Data-Under-Concurrency-tp1592091p1592597.html > Sent from the OpenJPA Users mailing list archive at Nabble.com. > >
