dcsimg
Table locks held after executing select twice.
2 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Pete_Inman
Posted On:   Friday, April 5, 2002 12:59 AM

If I do a select on a table, I have a problem where the lock is still held on the table even after I've closed the statement object. For example sqlString = "select * from library/table" Statement st; ResultSet rs; Connection conn = DriverManager.getConnection("jdbc:as400://mcpas400"); st = conn.createStatement(); rs = st.executeQuery(sqlString); st.close The code above works fine, in that the data is selected, and the lock on tbe table released. If I then execute the lines below again in a loop for example, after the call to st.close, the lock on the table remains. st = conn.cre   More>>


If I do a select on a table, I have a problem where the lock is still held on the table even after I've closed the statement object.

For example


			
sqlString = "select * from library/table"
Statement st;
ResultSet rs;

Connection conn = DriverManager.getConnection("jdbc:as400://mcpas400");

st = conn.createStatement();
rs = st.executeQuery(sqlString);

st.close


The code above works fine, in that the data is selected, and the lock on tbe table released.

If I then execute the lines below again in a loop for example, after the call to st.close, the lock on the table remains.


			
st = conn.createStatement();
rs = st.executeQuery(sqlString);

st.close


If I change the case of the sql statement(sqlString) just before I execute it again, then it works and the lock is released. It seems to be getting something to do with the statement from cache somewhere.



Can anyone suggest what I'm doing wrong as it seems to be a fairly fundamental problem.



Pete

   <<Less

Re: Table locks held after executing select twice.

Posted By:   Etienne_Richards  
Posted On:   Thursday, May 23, 2002 09:10 AM

Pete,

I beleive that you are seeing the affect of an IBM optimization. If you execute the same sql twice you will see that there is an shared read lock on the files that are in the query (Memeber level), this is a pseudo lock. One way to test if this is a real lock is by trying to get an exclusive lock, when getting an exclusive lock you will see the pseudo lock go away automatically.

Pseudo locks are good things, they help repeated queries execute a lot faster.

Etienne

Re: Table locks held after executing select twice.

Posted By:   shautvas_t  
Posted On:   Wednesday, April 10, 2002 01:18 AM

intuitively i'd say that the SQLservice that serves your JDBC calls still runs after st.close(), cos of all the calls that were sent in the loop.

Note that subsequent calls will be sent (during loop) while previous calls are still being processed. Take in mind that all executeStatement() calls result in 2 SQL's being sent. One for database metadata and one for the actual data you want. Using PreparedStatement's avoids this and increases speed.

The PreparedStatement has to be created before the loop, and can be parametrized to make it especialy useful in loops...

About | Sitemap | Contact