-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 
Author Message
 Post subject: Optimist locking - two apps accessing same db and records
PostPosted: Thu Aug 12, 2010 12:22 pm 
Beginner
Beginner

Joined: Mon Aug 23, 2004 12:44 am
Posts: 25
I've got two web applications, each is bundled in it's own ear and each ear is deployed on a separate (glassfish) server instance. T
EAR1 (with WAR1) => server1
EAR2 (with WAR2) => server2

Both web applications access the same (Oracle) database.
In both applications I'm using Hibernate's optimistic version number-based locking approach.
Everything worked fine.

However due to a change in requirements it turns out that at some point in time both applications could try and update the very same database record.
The following sequence is executed within each application, possible at the same time :
- Start txn (Spring declarative txn + JTATransactionManager)
- Execute hibernate HQL select to fetch the record (e.g. a Person),
- Update the statuscode of the retrieved Person,
- Execute Hibernate saveOrUpdate of the updated Person object.
- Commit txn.

What will happen if both of these applications indeed modify the same Person at the same time ? E.g. app1 changes statucode to X and app2 to Y.

Will an exception be thrown in one of the applications indicating the Person has been updated in another transaction, or not, and will the updates made by one of the applications be lost ?
That is, does the optimistic version-number-based locking feature, only function when n transactions in the same application are modifying the same record, or should the optimistic locking feature also work in the described deployment configuration (two seperate apps) ?

What are my options ?
Any advice welcome.


Kind regards,
EDH


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Thu Aug 12, 2010 1:01 pm 
Beginner
Beginner

Joined: Mon Aug 23, 2004 12:44 am
Posts: 25
I can't immediately see the relationship with my question. Can you be a bit more specific ?
Thanks,
EDH


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Thu Aug 12, 2010 1:23 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
The first transaction that commits will succeed. The other will fail with an exception (StaleStateException I think). It shouldn't matter if the transactions live in the same or different processes.


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Thu Aug 12, 2010 2:17 pm 
Beginner
Beginner

Joined: Mon Aug 23, 2004 12:44 am
Posts: 25
Ok. The reason I'm confused is because of this: http://www.comp.dit.ie/btierney/oracle1 ... htm#i17841
And more specifically Oracle Database always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in timeā€”the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.
A consistent result set is provided for every query, guaranteeing data consistency, with no action on the user's part. The SQL statements SELECT, INSERT with a subquery, UPDATE, and DELETE all query data, either explicitly or implicitly, and all return consistent data. Each of these statements uses a query to determine which data it will affect (SELECT, INSERT, UPDATE, or DELETE, respectively).


That is, if at the time both txn's execute their saveOrUpdate statement and if at that time neither of these txn's committed, they both work on the same version number, hibernate won't complain, and at commit time, the commit of both txns will succeed, losing an update.

Or am I totally wrong ?

Thanks,
EDH


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Thu Aug 12, 2010 5:03 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Now you've made me confused as well...

This was how I thought it worked: The first transaction will lock the updated row and block the second transaction until the lock is released. The lock is released when the transaction commits. So, the version number should have been updated by the first transaction and when the second proceeds there is no longer any matching record and Hibernate will not throw an exception... The confusing part is if the second update query still sees the old version number or if the where condition in the update statement is re-evaluated...

Maybe you can set up a test case and tell us about the results?


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Fri Aug 13, 2010 3:12 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
I made a simple test case inside my own app. I don't have any Oracle database but have tested with MySQL and PostgreSQL. Both end up with Hibernate throwing a StaleObjectStateException in the last transaction that updates. The test uses two threads (T1 and T2) that both load a single entity (E) and try to update it. The time synchonization is like this:

Code:
Time    Action
-----   -------
0        T1 starts and load E
0        T2 starts and load E
0        T1 wait for two seconds
0        T2 wait for four seconds
2        T1 modifies E and flushes the session --> UPDATE is sent to DB
2        T1 wait for five seconds
4        T2 modifies E and flushes the session --> A second UPDATE is sent to DB but is blocked by T1
7        T1 commits
7        StaleObjectStateException in T2


The end result is that the update made in T1 is saved. Stack trace in T2 is:

Code:
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [net.sf.basedb.core.data.GroupData#23]                                                                                     
        at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1934)                     
        at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2578)                     
        at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2478)             
        at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2805)                     
        at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:114)                                         
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:268)                                                       
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:260)                                               
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:180)                                               
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)     
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)                         
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)                                                         
        at net.sf.basedb.core.HibernateUtil$4.run(HibernateUtil.java:1891)                                                     
        at java.lang.Thread.run(Thread.java:619)     


So, I would say that at least in MySQL and PostgreSQL the optimistic locking works as expected (the first one that update wins, the second one gets an error). Unfortunately I can't test with Oracle because (1) I don't have any Oracle database server, (2) my application doesn't work in Oracle.


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Fri Aug 13, 2010 4:21 am 
Beginner
Beginner

Joined: Mon Aug 23, 2004 12:44 am
Posts: 25
Thanks for testing this out !

I'll try and setup a testcase against Oracle. However, it won't be for today I'm afraid. :-( I'll do my best.

Two questions though.
- In your scenario you explicitely write "...and flushes the session". Do you mean that within your code you programmatically flush the session ?
- Do you think the behaviour will be the same if these transactions would run within different hibernate apps (that accessi the same db and rows) ?

Coming back to Oracle. The following part still worries me:
Quote:
Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE
The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:

The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.

The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.

A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.

In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle Database automatically converts this lock to a row exclusive table lock.


As I understand the above piece of text, when I execute (pseudocode) an update Person set version=new version number, ... where personSK='x' and version = old version number. The where part, which is an implicit query, will execute and won't block other readers. So if I've got two txns trying to update that same Person, they both read '0' as the current version number in the database, both increment the version to '1' and then commit ...

Does that make sense ?


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Fri Aug 13, 2010 5:38 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Quote:
Do you mean that within your code you programmatically flush the session ?


Yes, otherwise I can't control that both threads flush (=update) before the first one tries to commit. And that is the important thing about this test case.

Quote:
Do you think the behaviour will be the same if these transactions would run within different hibernate apps (that accessi the same db and rows) ?


Yes. This is all happening in the database and is not really anything Hibernate can do differently.


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Fri Aug 13, 2010 6:08 am 
Beginner
Beginner

Joined: Mon Aug 23, 2004 12:44 am
Posts: 25
Quote:
Yes, otherwise I can't control that both threads flush (=update) before the first one tries to commit. And that is the important thing about this test case.

Ok, but won't this programmatic flushing interfere with things when having a Spring declarative txn management, JtaTransactionManager, Hibernate, appserver (Glassfish) setting ?


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Fri Aug 13, 2010 6:17 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Quote:
Ok, but won't this programmatic flushing interfere with things when having a Spring declarative txn management, JtaTransactionManager, Hibernate, appserver (Glassfish) setting ?


It's a test case. There is no Spring, no transaction manager, etc. only a pure java command line program using Hibernate.


Top
 Profile  
 
 Post subject: Re: Optimist locking - two apps accessing same db and records
PostPosted: Fri Aug 13, 2010 6:23 am 
Beginner
Beginner

Joined: Mon Aug 23, 2004 12:44 am
Posts: 25
Quote:
It's a test case. There is no Spring, no transaction manager, etc. only a pure java command line program using Hibernate.

Ok, I see.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.