Uploaded image for project: 'iBatis for Java [READ ONLY]'
  1. iBatis for Java [READ ONLY]
  2. IBATIS-162

Unexpected behavior in RowHandler method when doing a nested query causing closed connection objects

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Won't Fix
    • 2.1.0
    • None
    • Core
    • None

    Description

      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.

      Attachments

        Activity

          People

            cbegin Clinton Begin
            jminard Jayson Minard
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: