wood burning stoves*
The moose likes Threads and Synchronization and the fly likes Getting lock on DB from two different JVM Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » Threads and Synchronization
Bookmark "Getting lock on DB from two different JVM" Watch "Getting lock on DB from two different JVM" New topic
Author

Getting lock on DB from two different JVM

Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
I am facing a problem which goes as below:
I have a standalone program which calls a servlet which gets some data from DB to work upon i.e. do some activity on it.
Initially the status of the status column for data from DB is "NOT_STARTED", later when the data from DB starts getting processed one by one, the status is changed to "IN_PROCESS" and once completed it changes to "COMPLETED" or "STOPPED" as according to the result.
NOw my problem starts here:
Now i am required to run the stand alone program from two different Servers i.e. two different JVM's , but my current set up creates a deadlock kind of situation and both JVM's try to get the lock on the same data in DB as the query for both the JVM's is same i.e. "SELECT * FROM XYZ WHERE STATUS="NOT_STARTED"......" and hence one of the JVM keeps on failing as the same data is being used by other JVM.
I want to create a kind of lock on the data fetched by one server so that the other server should not be accessing the same data.
NOTE: The idea behind having the two servers is to increase the efficiency, as the stand-alone process uses a client program to hit a web application running on the server. Also I cannot go for Multiple threading as there are already many threads running on one JVM.


AWADHAMBIKA(I)
arvind kushwaha
Ranch Hand

Joined: Aug 12, 2011
Posts: 32
Just synchronize the method which having database code. so that at a time one server only access that code.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
arvind kushwaha wrote:Just synchronize the method which having database code. so that at a time one server only access that code.

Synchronization would help if both calls were from within the same JVM; since they are from different JVMs, it doesn't.
Vic Hood
Ranch Hand

Joined: Jan 05, 2011
Posts: 477

I would suggest that you write some code to delay the DB access of one of the JVM's that way you can be sure that a deadlock wont arise in the future


Learning and Learning!-- Java all the way!
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

No you can't. Delays are no way of solving problems, they may merely decrease the chance they occur. If for whatever reason the other process gets delayed you have the same issue.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Vic Hood
Ranch Hand

Joined: Jan 05, 2011
Posts: 477

Well Rob you have a point, I was suggesting off the top of my head. Would it be feasible (programatically) to use a flag that makes the second JVM fire only after the first has fired. I have no clue whether/ how this can be done though.
Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
I appreciate all the posts but as Rob is saying, this will only delay but not wary off the deadlock issue. Also i have to maintain the optimization level of the program. Delaying a call i think will hamper the same, also i don't know how much time will it take to process the data by one server as it is dependent on many other system's response. So giving some abstract delay time will delay my programs response.
Kindly correct me if i am wrong anywhere and please suggest some way out.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
SELECT statements should not cause a deadlock; are you certain this is the cause? Even if two JVMs write simultaneously to the DB, using transactions should handle any problems that may arise (along with proper exception handling in the Java code, of course).
Jacob Bogers
Greenhorn

Joined: Jan 02, 2012
Posts: 6

Hi
,
Databases are build to handle concurrency from different "clients" (=any process/whatever that connects to the DB). Thats why Larry Ellison is so rich, he made a product thats very very good at this,

Why Databases "should" never lock on DML (especially select wich is not DML in reality but considered part of the commandset that does)

http://en.wikipedia.org/wiki/Isolation_%28computer_science%29

Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
Tim,
By deadlock, here i mean is that, the SELECT query from both the JVMs try to get lock on the same rows in the DB as i start both JVMs simultaneously, and whoever wins moves ahead whereas other one, as according to the check, fails and displays appropriate message as according to the code.
So, what i am trying to acheive is that, somehow, if one JVM is working on a set of rows, the other JVM should not try to access the same rows in the DB.
Also, i forgot to mention one point, my SELECT query does not run for one record everytime, rather, it runs once and fetches 3000 records at once and creates a DTO for each row and pass on to the next step.
So the next JVM comes and again gets a lock on the same 3000 records or leaving 4 or 5 which might have got completed during the start of second JVM, so the second JVM starts failing, as the other JVM has alreday worked on those records and have modified some data used for validation whether the loans have been worked upon.

Kindly suggest, if there is any other way that i can take.
Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
Jacob Bogers wrote:Hi
,
Databases are build to handle concurrency from different "clients" (=any process/whatever that connects to the DB). Thats why Larry Ellison is so rich, he made a product thats very very good at this,

Why Databases "should" never lock on DML (especially select wich is not DML in reality but considered part of the commandset that does)

http://en.wikipedia.org/wiki/Isolation_%28computer_science%29



Jacob,
There is something called Phantom Read, which i am worried about, as also mentioned in the Article link you shared. My SELECT queries are same for both the JVMs and they will fetch the same records as there is no WHERE condition in them. Kindly correct me if have misunderstood.
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
Binit Kumar wrote:By deadlock, here i mean is that, the SELECT query from both the JVMs try to get lock on the same rows in the DB

Again: SELECTs do not cause anything to lock, so there can't be a deadlock.

There is something called Phantom Read, which i am worried about

Phantom reads can only occur if there are operations that alter data, not with SELECTs.
Jacob Bogers
Greenhorn

Joined: Jan 02, 2012
Posts: 6

Binit Kumar wrote:
Jacob Bogers wrote:Hi
,
Databases are build to handle concurrency from different "clients" (=any process/whatever that connects to the DB). Thats why Larry Ellison is so rich, he made a product thats very very good at this,

Why Databases "should" never lock on DML (especially select wich is not DML in reality but considered part of the commandset that does)

http://en.wikipedia.org/wiki/Isolation_%28computer_science%29



Jacob,
There is something called Phantom Read, which i am worried about, as also mentioned in the Article link you shared. My SELECT queries are same for both the JVMs and they will fetch the same records as there is no WHERE condition in them. Kindly correct me if have misunderstood.


If you are using Oracle (my speciality) then you can see which session is blocking wich or wich database objects are locked v$lock and v$access.. Numerous SQL scripts floating around on the internet that can query the database dictionary and show you this information,

You didnt tell us what DB you are using, we all here assume it is an entreprise level product. But lets say for argument sake you are right and one session on youe db is blocking another,..., then its pretty sure your code is doing something more then just a mere SELECT.

Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
Tim Moores wrote:
Binit Kumar wrote:By deadlock, here i mean is that, the SELECT query from both the JVMs try to get lock on the same rows in the DB

Again: SELECTs do not cause anything to lock, so there can't be a deadlock.

There is something called Phantom Read, which i am worried about

Phantom reads can only occur if there are operations that alter data, not with SELECTs.


Tim,
You are right, SELECT stmt never causes Deadlock and as you are guessing, you are right, my standalone application does DML operations on the data fetched from DB and that is where the competition between my JVM's start and the issue is as discussed earlier.
Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
Jacob Bogers wrote:
Binit Kumar wrote:
Jacob Bogers wrote:Hi
,
Databases are build to handle concurrency from different "clients" (=any process/whatever that connects to the DB). Thats why Larry Ellison is so rich, he made a product thats very very good at this,

Why Databases "should" never lock on DML (especially select wich is not DML in reality but considered part of the commandset that does)

http://en.wikipedia.org/wiki/Isolation_%28computer_science%29



Jacob,
There is something called Phantom Read, which i am worried about, as also mentioned in the Article link you shared. My SELECT queries are same for both the JVMs and they will fetch the same records as there is no WHERE condition in them. Kindly correct me if have misunderstood.


If you are using Oracle (my speciality) then you can see which session is blocking wich or wich database objects are locked v$lock and v$access.. Numerous SQL scripts floating around on the internet that can query the database dictionary and show you this information,

You didnt tell us what DB you are using, we all here assume it is an entreprise level product. But lets say for argument sake you are right and one session on youe db is blocking another,..., then its pretty sure your code is doing something more then just a mere SELECT.



Jacob,
I am using ORACLE and my problem is more of the coding side rather than DB side. My session is not getting blocked, rather it is the competing situation that i have to ward off.
Today i thought of a solution, i will modify the query to get the data from DB for both servers i.e. one will get first X rownums and the other other next rownums i.e. i am trying for DB level solution.
However if any one can give JAVA level solution, it will also be much appreciated.
Jacob Bogers
Greenhorn

Joined: Jan 02, 2012
Posts: 6

I am using ORACLE and my problem is more of the coding side rather than DB side. My session is not getting blocked, rather it is the competing situation that i have to ward off.


Dude, you are totally misdiagnosing the problem (if there is any). Please consult your in house DBA before preceding any further.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Binit Kumar wrote:
Today i thought of a solution, i will modify the query to get the data from DB for both servers i.e. one will get first X rownums and the other other next rownums i.e. i am trying for DB level solution.


Someone has just told you in this thread - simple queries that get the data do not cause deadlocks.
The problem is with order of your updates (or maybe with select with for update clause). Something like this:
Session 1: Update X
Session 2: Update Y
Session 1: Update Y --> session hangs and wait for changes of Y to be commited or rolled back in the other session
Session 2: Update X --> deadlock detected - one session must give up (while the second session is still waiting for commit or rollback in the other session)

Simple example (on Oracle 11):


Session 1

Session 2

Session 1Session 1 hangs and is waiting for release of the lock of id=3

Session 2Session 2 hangs and is waiting for release of the lock of id=1

Back to session 1:Session 2 is stopped, but Session 2 is still waiting for release of the lock of id=1
Binit Kumar
Greenhorn

Joined: Aug 22, 2010
Posts: 9
Ireneusz Kordal wrote:
Binit Kumar wrote:
Today i thought of a solution, i will modify the query to get the data from DB for both servers i.e. one will get first X rownums and the other other next rownums i.e. i am trying for DB level solution.


Someone has just told you in this thread - simple queries that get the data do not cause deadlocks.
The problem is with order of your updates (or maybe with select with for update clause). Something like this:
Session 1: Update X
Session 2: Update Y
Session 1: Update Y --> session hangs and wait for changes of Y to be commited or rolled back in the other session
Session 2: Update X --> deadlock detected - one session must give up (while the second session is still waiting for commit or rollback in the other session)

Simple example (on Oracle 11):


Session 1

Session 2

Session 1Session 1 hangs and is waiting for release of the lock of id=3

Session 2Session 2 hangs and is waiting for release of the lock of id=1

Back to session 1:Session 2 is stopped, but Session 2 is still waiting for release of the lock of id=1


Irene, you got the problem correctly. Sorry for posting this late, but i came with a solution like this:
I got the data from first server and changed the status of the Data to IN_QUEUE in one of the columns where status is stored in the table in DB.
So when the second server comes it will not pick up those rows as they are not in NOT_STARTED status, as my query is intended to pick.
Also i introduced some delay b/w the first and second sever startup to ward off any possibility of the race for the data lock in DB.

This worked and now my standalones are running perfectly fine on two servers simultaneously.

Kindly let me know in case of any better suggestions.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting lock on DB from two different JVM