File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Database locks Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database locks" Watch "Database locks" New topic
Author

Database locks

Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
I am using a DB2 database. Currently our users are using an application we have put out to them for our order entry system.
After a couple of weeks, we noticed there were a couple of times our backup didn't finish due to some tables that still had a lock held on them.
After doing a bit of searching, we found that a couple of users still had the application up, and had not closed out of it before they left.
Then I did some research and found that once I access a table to get data,
(no updates, just select statements), a lock was put on that table, until I closed out of the app entirely.
I used stored procedures such as this to access data:

I'm a bit confused on how this is working. Should there even be a lock on the table when I'm only accessing data?
If so, it should be releasing the lock after the statement runs!
Could someone push me in the right direction?
Thanks!
Paul Knox
Greenhorn

Joined: Aug 05, 2003
Posts: 2
Hi,
Looks to me like a cursor is being left open in your application or the PL/SQL, do you have you a finally clause in your EJB where you close the statement and the connection ?
Just a little push there ....
Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
I do have a finally clause wherever I am looping through a result set in
my application.
It says if the connection(CallableStatement) is not null, to close it.
I have been doing a little research of my own and found something that
indicated in DB2, the default isolation of cursor stability, the row lock (which is default in DB2) is held as long as the cursor is positioned on the row unless you update it.
So if I issued 'SELECT field FROM table FOR UPDATE' I would not see any row locks held.
If I declare a cursor and then fetch a row (which is what I am doing), then I see the row lock because the cursor would still be sitting on the row.
I looked at the DB2 reference and found there is also a FOR READ ONLY clause that I can put on the end of my statement, that may work. I just wanted to know if anyone else is familiar with the two statements FOR UPDATE, and FOR READ ONLY and if they could tell me which would be better to use.
Thanks!
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
Jeniffer,
If this action was being peformed in the transaction context of an EJB, then I would suggest you take a look at the isolation level settings in the EJB's properties. This would be listed under the properties for the remote methods of the bean.
What you have experienced is the default behaviour of Db2. U can (and probably have to), override this with what u need to achieve.


Best Regards,<br />Nagendra Prasad.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Database locks
 
Similar Threads
Windward Java Engine Review
How to retrieve the first record in a database table
MySQL tricks for managing test data
SQL injection?
Implementing scanner into a simple login page