File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Creating my own database locking mechanism from my app - how? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Creating my own database locking mechanism from my app - how?" Watch "Creating my own database locking mechanism from my app - how?" New topic

Creating my own database locking mechanism from my app - how?

David Rocks
Ranch Hand

Joined: Apr 24, 2001
Posts: 162
i have a problem that i do not think can be solved ( given the limitations by my superiors). I just though that it would be good to see what other people thought before i told them i could not do it.
I was told to write an app that called a db2 database and retreived a large result set of data. The app then iterates over the set and does some work(sends mq messages). Every time it completes an iteration it calls the database and updates a flag in the row to say "i have been fulfilled".
Now it now seems that every process in this company has to be doubled up and my argument is that without a major enhancement cannot take place.
I believe that if one application grabs the result set then the other can get it at the same time. Even if i out on read locks, which i am not alloed to do, when one process grabs the result set then moves on to the work cycle it drops the lock and another process can take the same result set. basically i cannot crete a lock before read and hold it until after the writing phase.
My solutions were to have a connection manager process beside the database that the apps looked up but that is in essence a single process which they don't want. I ma told that it would be near impossible to get a major database enhancement done. I looked at semiphores but they have to be read before written so it does not really cure the problem. It is just the same as the larger problem.
Does anybody know of a possible soulution.
Thanks in advance for any help.
thanks in advance
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
I am intrigued... cannot say I have an answer directly.. but i have been thinking on this for a while.. it has all the bearings of a catch-22 situation...
first thoughts are:
- Since we are dealing with large record sets, would some thing of this nature help... loop through the result and first create a vector.. close the result set. No updates are performed here...
Now loop thru the vector created.. perform ancillary activities (sendmail etc)... and then based on success of that job, issue an update to the table. Simple single SQL statement.. or a single update to an entity bean.
The advantages are that locking can be minimised... U are only reading first. Not doing any updates at all.
The disadvantage is that u need a large footprint of the result set in memory and this might lead to complications.
- Process a small part of a result set at a time. Even if the condition is satisfied by 10000 records, pick , say, only the first 100. Finish processing them and then issue the same query and pick the rest... and so on. i.e divide and conquer.
- check if database isolation levels can be modified to allow uncommitted reads etc... (Not very certain on this point...)
as i said earlier.. not a sureshot black and white answer... but some thoughts... let me know what your thoughts are.. perhaps we might end up getting a solution!!

Best Regards,<br />Nagendra Prasad.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I think that you need to play with the transaction isolation level on your Connection object ( what Nagendra suggested ). I know that DB2 drivers can be a little finicky ( throw Exceptions ) when you increase to higher isolation levels depending on your DB2 settings though.
Consider Paul's rocket mass heater.
subject: Creating my own database locking mechanism from my app - how?
It's not a secret anymore!