[jira] Commented: (DERBY-210) Network Server will leak prepared statements if not explicitly closed by the user until the connection is closed

["Deepa Remesh (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-210?page=comments#action_12362709 ] 

Deepa Remesh commented on DERBY-210:
------------------------------------

I don't have a patch ready for the rest of the problem. But I have narrowed it down to this:

The only other reference holding on to the PreparedStatement is in the ResultSet object which remains alive till the PreparedStatement is closed. ResultSet does not get garbage-collected even after ResultSet.close() is called. It gets ready for grabage-collection only when the statement which created it is closed. This is because client driver holds references to ResultSets in a Hashtable 'positionedUpdateCursorNameToResultSet_' in SectionManager object. All ResultSets get added to this Hashtable  though only ResultSets from positioned update statements are retrieved and used from this table. Also, only ResultSets of positioned update statements get removed from the HashTable. Other ResultSets remain in the Hashtable. So ResultSet objects cannot get finalized even after ResultSet.close() is called. Because ResultSet has reference to the PreparedStatement object, it also does not get garbage-collected.

However, when PreparedStatement.close() is called, the section used by the statement is freed and gets re-used by new statements. Server cursor names are associated with a section and so the new statement uses the same server cursor name as previous statement (which has been closed). And ResultSet from new statement gets added to the HashTable with same cursor name as used by previous statement. So the Hashtable value gets over-written and previous ResultSet is now free for garbage collection. And the previous PreparedStatement is also free.

To verify this, I added following line to the method Statement.resetCursorNameAndRemoveFromWhereCurrentOfMappings

//Also remove resultset mapping for other cursors
agent_.sectionManager_.removeCursorNameToResultSetMapping(cursorName_,
                    section_.getServerCursorName());

It was only doing this:
agent_.sectionManager_.removeCursorNameToResultSetMapping(cursorName_,
                    section_.getServerCursorNameForPositionedUpdate());

With this change and  changes from derby-210-patch1.diff, prepared statements get garbage collected even if the user does not explicitly close them. I could run the attached repro derbyStress.java with 40000 prepared statements. I also successfully ran derbynetclientmats.

Adding the above line seems to solve the problem. But there seems to be some extra work going on in client driver in just adding and removing "all" result sets to the Hashtable 'positionedUpdateCursorNameToResultSet_'. I think this can be avoided. I am trying to understand why positionedUpdateCursorNameToResultSet_ is needed. In the meantime, I'd appreciate any feedback to know that I am in right direction.

> Network Server will leak prepared statements if not explicitly closed by the user until the connection is closed
> ----------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-210
>          URL: http://issues.apache.org/jira/browse/DERBY-210
>      Project: Derby
>         Type: Bug
>   Components: Network Client
>     Reporter: Kathey Marsden
>     Assignee: Deepa Remesh
>  Attachments: derby-210-patch1.diff, derby-210-patch1.status, derbyStress.java
>
> Network server will not garbage collect prepared statements that are not explicitly closed by the user.  So  a loop like this will leak.
> ...
> PreparedStatement ps;
>  for (int i = 0 ; i  < numPs; i++)
> 	{
> 	 ps = conn.prepareStatement(selTabSql);
> 	 rs =ps.executeQuery();
> 	 while (rs.next())
> 	{
> 	    rs.getString(1);
> 	}
> 	rs.close();
> 	// I'm a sloppy java programmer
> 	//ps.close();
> 	}
> 			
> To reproduce run the attached program 
> java derbyStress
> Both client and server will grow until the connection is closed.
>  
> It is likely that the fix for this will have to be in the client.  The client does not send protocol to close the prepared statement, but rather reuses the PKGNAMCSN on the PRPSQLSTT request once the prepared statement has been closed. This is how the server knows to close the old statement and create a new one.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira