CODEWITHSUNDEEP
javajdbc
rubixibuc
rubixibuc

Reputation: 7397

Executing statements while a result set is open (JDBC/JAVA)

Is it safe or recommended to execute independent statements while you have a result open? Does it matter if they are attached to a different connection or the same one as the result set? I'm particularly concerned with how the result holds locks if any, which could cause deadlock.

Ex.

while(resultSet.next()) {
     Execute separate statements in here ( same or different connection )
}

Also is a result set backed by an underlying cursor or something else?

Ty

Upvotes: 0

Views: 2177

Answers (3)

brettw
brettw

Reputation: 11114

There are several questions here.

First, generally "yes" it is possible and common to run other SQL statements while iterating over a ResultSet. And yes, ResultSets are backed by a cursor.

It is also possible to create a deadlock doing this, so you just need to be aware of that. If the SQL being executed inside of your loop is not modifying rows in the same table as the ResultSet, then you should ensure that the ResultSet is created with a concurrency mode of CONCUR_READ_ONLY, and in general try to use TYPE_FORWARD_ONLY.

For example:

Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                     ResultSet.CONCUR_READ_ONLY);

If you use CONCUR_READ_ONLY and TYPE_FORWARD_ONLY in general locks that block writes should not be generated. Using the same Connection object is also recommended because then both the cursor and the SQL that is modifying other objects are within the same transaction and are therefore less likely to cause a deadlock.

Upvotes: 4

Jules
Jules

Reputation: 15199

It is perfectly safe. You have two basic choices:

  • Use a different Connection object.
  • Use the same Connection object.

The primary difference is in how transactions are handled.

  • Different Connections are always in different transactions
  • Different statements in the same Connection can be made to be in the same transaction if you set auto commit mode to false.

Two statements that are not in the same transaction are guaranteed by the SQL server not to interfere with each other (usually this means that the server will hold onto copies of any modified data if an older transaction might still need it). It is also down to the server how it performs locking (if at all) but it must guarantee that no deadlock can occur. Usually it does this by a process called serialization, which involves storing transactions in a log until they can be guaranteed to execute without deadlock.

Upvotes: 0

Nidhish Krishnan
Nidhish Krishnan

Reputation: 20751

Its safe according to my point of view..

The ResultSet is usually linked to the Statement which is usually linked to the Connection. A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Close ResultSet when finished

Close ResultSet object as soon as you finish working with ResultSet object even though Statement object closes the ResultSet object implicitly when it closes, closing ResultSet explicitly gives chance to garbage collector to recollect memory as early as possible because ResultSet object may occupy lot of memory depending on query.

ResultSet.close();

Upvotes: 0

Related Questions