Getting lock on DB from two different JVM

[Binit Kumar]

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.

[arvind kushwaha]

Just synchronize the method which having database code. so that at a time one server only access that code.

[Tim Moores]

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]

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

[Rob Spoor]

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.

[Vic Hood]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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):

SQL> create table test22 as  
  2  select level id, 'NOT STARTED' status from dual
  3  connect by level <=5;

Table created.

SQL> select * from test22;

	ID STATUS
---------- -----------
	 1 NOT STARTED
	 2 NOT STARTED
	 3 NOT STARTED
	 4 NOT STARTED
	 5 NOT STARTED

Session 1

SQL> update test22 set status = 'IN_PROCESS' 
  2  where id = 1;

1 row updated.

Session 2

SQL> update test22 set status = 'IN_PROCESS'
  2  where id = 3;

1 row updated.

Session 1

SQL> update test22 set status = 'STOPPED'
  2  where id = 3;

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

Session 2

SQL> update test22 set status = 'COMPLETED'
  2  where id = 1;

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

Back to session 1:

update test22 set status = 'STOPPED'
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

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

[Binit Kumar]

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):

SQL> create table test22 as  
  2  select level id, 'NOT STARTED' status from dual
  3  connect by level <=5;

Table created.

SQL> select * from test22;

	ID STATUS
---------- -----------
	 1 NOT STARTED
	 2 NOT STARTED
	 3 NOT STARTED
	 4 NOT STARTED
	 5 NOT STARTED

Session 1

SQL> update test22 set status = 'IN_PROCESS' 
  2  where id = 1;

1 row updated.

Session 2

SQL> update test22 set status = 'IN_PROCESS'
  2  where id = 3;

1 row updated.

Session 1

SQL> update test22 set status = 'STOPPED'
  2  where id = 3;

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

Session 2

SQL> update test22 set status = 'COMPLETED'
  2  where id = 1;

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

Back to session 1:

update test22 set status = 'STOPPED'
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

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.