[jira] Commented: (IBATIS-162) Unexpected behavior in RowHandler method when doing a nested query causing closed connection objects

["Jayson Minard (JIRA)" <ib...@incubator.apache.org>]

    [ http://issues.apache.org/jira/browse/IBATIS-162?page=comments#action_12314909 ] 

Jayson Minard commented on IBATIS-162:
--------------------------------------

are you saying to start a transaction around the outer read?  Or around the code in the handleRow?  If the first, that isn't possible.  It can be millions of records being read and we cannot hold open the inner write transactions for more than a second or two maximum otherwise in a highly transacted environment you would add so much contetion and blocking that your database would fall to its knees.  We have to do the inner work in seperate transactions and keep them small and short.  The outer read can continue for a much longer period of time.

> Unexpected behavior in RowHandler method when doing a nested query causing closed connection objects
> ----------------------------------------------------------------------------------------------------
>
>          Key: IBATIS-162
>          URL: http://issues.apache.org/jira/browse/IBATIS-162
>      Project: iBatis for Java
>         Type: Bug
>   Components: SQL Maps
>     Versions: 2.1.0
>     Reporter: Jayson Minard

>
> as explained in a message between our engineering staff, when you perform a nested query in iBatis during a handleRow() method, the closure of the connection object terminates the outer query.  It only works in the default iBatis connection pool due to what appears to be dangerous behavior.  Misunderstanding, or real bug?  See full explaination below:
> --- snip ---
> If you just want the summary, here it is: Don't use RowHandler's with your iBATIS select queries.  If you want to know why, feel free to read on.
>  
> When using iBATIS, you have the option of providing a RowHandler to the query selection method, which will apply the same code to every row returned in the result set.  To describe it better, here's a sample of how the code normally works and then how it might work with a row handler.
>  
> NO ROW HANDLER:
>    1. Submit select query
>    2. Get a connection from the pool, if you have not explicitly started a transaction
>    3. A prepared statement and result set are created implicitly
>    4. Iterate through result set
>       a) Place each object produced into a list
>    5. Close the previously created prepared statement and result set
>    6. Return the connection to the pool, if you have not explicitly started a transaction
>    7. Return the list to the calling method
>    8. Iterate through the resulting list
>        a) Apply processing to each object
>  
>  
> WITH ROW HANDLER
>    1. Submit select query
>    2. Get a connection from the pool and start an implicit transaction, if you have not explicitly started a transaction
>    3. A prepared statement and result set are created implicitly
>    4. Iterate through result set
>       a) Apply processing to each object as the result set is read
>          -- The handler accepts the result object and the result list.  You can do any processing you wish and add (or not add) anything you want to the List
>          -- This may include another database query 
>    5. Close the previously created prepared statement and result set
>    6. Commit the implicit transaction and return the connection to the pool, if you have not explicitly started a transaction
>    7. Return the list to the calling method
>    8. Use or ignore the list, depending on the process
>  
>  
> Alot of people have varying opinions as to which is the better methodology.  Some people might be prone to use RowHandler's because they give more of an OOP approach.  This is all fine, diversity is a good thing. Now here's the problem with RowHandler approach used in iBATIS:
>  
> Let's say you haven't explicitly started a transaction and your RowHandler.handleRow() method performs a query of its own.  Here's what will happen with the first record:
>  
>   1. iBATIS calls next() on the result set
>   2. The result object is passed to the rowHandler
>   3. The row handler submits a query
>   4. An implicit transaction is started
>   5. The query is processed
>   6. The implicit transaction is commited and the connection returned to the pool
>   7. iBATIS calls next() on the result set, which is no longer valid because the connection has been return to the pool
>  
>  
> You cannot continue to use a result set or statement belonging to a connection that has been returned to the pool, this is bad form.  In the case of the Abebooks connection pool, an exception will actually be thrown, because the connection is treated as having been closed by the connection pool data source.
>  
> So why didn't this issue make itself known when we were using the connection pool?  The iBATIS connection pool behaves a bit differently, as they just have a flag external to the connection indicating that it is returned to the pool.  This prevents any new statements from being created against the connection.  However, since close() is not called, the result set doesn't know that the connection is invalid.  For this reason, the code was able to sort of get away with this type of behaviour, though it did provide a risk of unusial behaviour.
>  
> So, in summary, the moral of the story is to avoid using RowHandlers with iBATIS.  I'll add something to our confluence pages on this.
>  

-- 
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