Should we close an opened cursor?
Lokesh Kumar Boddepalli asked whether an opened cursor must be closed. David said open cursors use server resources and may hold locks, and may need freeing along with the prepared statement. Marcus Haarmann reported unclosed Java cursors grew memory until an instance died. Madison Pruet noted close releases locks but only FREE releases the memory.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
If we open a cursor, should that be closed? If we don't close it, will it impact performance?
Depends if you still need to process the rows within the cursor. Leaving the cursor open will use up resources on the server and may also hold locks. You may also need to free the cursor and free any associated prepared statement. Regards, David. > On 10 July 2017 at 14:53 LOKESH KUMAR BODDEPALLI <lokeshkumarb19@outlook.com> wrote: > > > If we open a cursor, should that be closed? If we don't close it, will it > impact performance? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hi, this practise will consume memory on the server side, which is never freed until the application is terminating. Unless you do this by purpose (you want to reuse the prepared cursors), I would close the cursors. Or - more problematic - it might block some rows in the database because it is a "for update" cursor and the transaction handling is not clear. For long lasting applications, we encountered sometimes that a non-closed cursor did kill the Informix instance after some weeks because of always growing memory consumption, which was resolved when we ran through the application for unclosed statements. That was a Java application dealing with PreparedStatements and ResultSets which were not closed properly. And the connections came from a pool of connections which were open very long. Depending on the environment you are working in, there are some mechanisms maybe which offer auto-closing to prevent something like this (some Java frameworks do this). Marcus Haarmann Von: "LOKESH KUMAR BODDEPALLI" <lokeshkumarb19@outlook.com> An: "ids" <ids@iiug.org> Gesendet: Montag, 10. Juli 2017 15:53:16 Betreff: Should we close an opened cursor? [39537] If we open a cursor, should that be closed? If we don't close it, will it impact performance? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Closing a cursor will remove any locks on the rows, pages, and or tables that the open cursor might hold. However closing a cursor will not free the memory in the server associated with the cursor. In order to free that memory you must free the cursor. Madison Pruet Retired and Loving it On Monday, July 10, 2017 7:53 AM, LOKESH KUMAR BODDEPALLI <lokeshkumarb19@outlook.com> wrote: If we open a cursor, should that be closed? If we don't close it, will it impact performance? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.