jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes Database locks 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 "Database locks" Watch "Database locks" New topic

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?
Paul Knox

Joined: Aug 05, 2003
Posts: 2
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.
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
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
jQuery in Action, 3rd edition