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!
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 ....
Joined: Feb 28, 2001
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!
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.