aspose file tools*
The moose likes JDBC and the fly likes Connection.close() and preparedStatement.close() in Connection.TRANSACTION_SERIALIZABLE mode. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection.close() and preparedStatement.close() in Connection.TRANSACTION_SERIALIZABLE mode." Watch "Connection.close() and preparedStatement.close() in Connection.TRANSACTION_SERIALIZABLE mode." New topic
Author

Connection.close() and preparedStatement.close() in Connection.TRANSACTION_SERIALIZABLE mode.

Kumar Jaya
Ranch Hand

Joined: Jan 12, 2009
Posts: 45
Hello,

I am having a scenario where I will have to open a connection in Serializable level, so that I want to lock the entire table that my code works on. Now I have three statments to execute, and I am using prepared statements. I have a few questions regarding the connection api and prepared statments api

say my code looks like this..,

Connection con = null;
PreparedStatement ps = null;

try {
con = DM.getConnection(...);
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
ps = con.prepareStatement("Select .....");
ps.execute();
// Application Code Block - 1
ps = con.prepareStatement("Insert ....");
ps.executeUpdate();
// Application Code Block - 2
ps = con.prepareStatement("Update....");
ps.executeUpdate();
//Application Code Block - 3


} catch () {

} finally {
if (con!= null)
con.close();
}

1) should I include a ps.close(); statement at every Application Code Block end before doing a ps = con.prepareStatement()?, or one ps.close()? will do at the finally block??
2) I suppose doing a con.prepareStatement() will return a new statement object every time? so what will happen to my old statement object: The statement api says -> "Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources. " I believe that if that statement holds certain resources(tables , locks on them) those resources will be tied up till you close the statement object or till its been garbage collected?? If that true I should always do a ps.close(); at the end of each Application Code Block?? But what will happen to my transaction isolation level, which holds a lock on the table, will that too be released if I do a ps.close()?
3) One final thing in that api. what they mean by resources anyway? do resources mean tables or locks, if so closing the statement will release the lock, wont it? Will calling a con.close() close any statement objects associated with that connection??

Regards
Thanks in advance.
xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
Jaya, First resouces in terms of JDBC means resultset, perpared statement, statement , connection etc. Below are answers to your questions.

1 ) con = DM.getConnection(...);
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
ps = con.prepareStatement("Select .....");
ps.execute();
// Application Code Block - 1 // Need to close ps ps.close();
ps = con.prepareStatement("Insert ....");

ps.executeUpdate();
// Application Code Block - 2 // Need to close ps ps.close();
ps = con.prepareStatement("Update....");

ps.executeUpdate();
//Application Code Block - 3
// // Need to close ps ps.close();
In above code block you have opened three perpared statement, this will result to cursor leakage. Prepared statement is not like java varaible. I agree you have defined only one prepared statement and you are re-using it again and again. but you are not closing earlier prepared statment.

At statmentent 1 you have first opened prepared statement, again at in block 2 you are re-using same prepared statement but not closing first one. Result of this up to this point there are 2 open prepared statment. And if you are running this code inside loop , then you may face cursor leakage.

As per JDBC, whenever you close connection, then associated prepared statment, statement, resultset will be automatically closed but good practice is to close all before closing connection. Let suppose you have passed connection as input argument to any method and in that method you are using above type of code in loop then you may face cursor leakage.

Same logic is applicable to statement, resultset and connection also.

Hope this will help you.

-Sunil
Kumar Jaya
Ranch Hand

Joined: Jan 12, 2009
Posts: 45
Hello Sunil,

Thanks for the reply. I will do that, but I got one other question, if I use the ps.close() after every ps.execute() or ps.executeUpdate() what will happen to my transaction isolation level which is Serializable here?? Will ps.close() release the lock on the row it has inserted or updated before con.commit() is called?? that was my third question

3) One final thing in that api. what they mean by resources anyway? do resources mean tables or locks, if so closing the statement will release the lock, wont it? Will calling a con.close() close any statement objects associated with that connection??

Regards
jaya



xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
As per my understanding, ps. ps.executeUpdate() will not release any lock. lock will be released either you have comitted your transation, closed your connection or rolled backed. So closing prepared statement will not have any affect on lock if you have not comit/ closed your connection.

calling a con.close() close any statement objects associated with that connection ??

Resultset, preparedstatement, statement all are created from connection. If you have closed the connection, automatically all above will be closed as parent is not available.

closing the statement will release the lock ?

Almost all databases, if you close the connection, it will automatically comit your changes. Whenever you comit your changes, lock will be released. Finally it depends upon database vendor or applicationi server implementation ( if you are using connection pool).

what they mean by resources anyway? do resources mean tables or locks

yes it is related to tables and locks. For futher information you need to search in Google.

-Sunil
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection.close() and preparedStatement.close() in Connection.TRANSACTION_SERIALIZABLE mode.