ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Multiple queries.
Date Tue, 15 Feb 2005 05:58:22 GMT
If i am understanding you correctly you need to execute a select that
you do not want to be in a transaction. Once the select is started you
want to process each row in their own transaction. Correct?

If that is true then this might be one of those JDBC is better kinda
things or a hybrid of JDBC and ibatis.

The IBatis architecture works in a single transaction to a thread
approach. If the transaction is not started by you then it will be
started by iBatis. The only exception would be if you managed the
transaction yourself external to ibatis and used the EXTERNAL
transaction manager. However, even with the EXTERNAL you would not be
able to accomplish your query without having started a transaction.

In the case you specified above the transaction is already started
with the queryWithRowHandler and stored on the thread. So, by the time
your RowHandler tries to call a startTransaction it already exists.

I'm not sure of how the behavior you are wanting would be accomplished
cleanly with iBatis. I would recommend that you look at coding this
one in JDBC. The hybrid approach would be to open a resultset outside
of ibatis that retrieves your large data set. As you iterate through
the resultset you can use ibatis to manage the smaller transaction
based calls to the database.

Maybe someone else has a more creative solution. But, this is all i
could come up with.


On Mon, 14 Feb 2005 21:34:11 -0600, David Putnam <dputnam@wellsfargo.com> wrote:
> All, 
> I am trying to perform an operation using SqlMaps.  I am performing one
> query to get a list of modified rows in one table, then for each row I want
> to perform an update or insert on another table within a transaction.  I am
> used to transactions only existing when I say they should, so I am using
> explicit transactions. 
> What seems to be occurring is that once the inner transaction starts, the
> outer query stops behaving like a cursor and fetches the entire result set
> into memory. In one version, SqlMap.startTransaction() complained that
> another transaction was already in progress. 
> So here is a snippet of my current main: 
>         Date laststart = new GregorianCalendar(2005,0,1,0,0,0).getTime(); 
>         CustomerLinkTask newLinkTask = new CustomerLinkTask(sqlMap, process,
> maxDeadlockRetry); 
>         try { 
>             sqlMap.queryWithRowHandler("modifiedCustomers", laststart,
> newLinkTask); 
>         } catch (SQLException e) { 
>             logger.error(e); 
>         } 
>         return 0; 
> The modifiedCustomers query returns all customers modified since the
> timestamp specified.  The sqlMap, and a deadlock retry are passed on to the
> CustomerLinkTask.  Here is the handleRow function for the CustomerLinkTask: 
>     public void handleRow(Object row) { 
>         Customer customer = (Customer) row; 
>         try { 
>             sqlMap.startTransaction(); 
>         }catch (SQLException e) { 
>             logger.error(e); 
>         } 
>         Investor investor = null; 
>         investor = getLinkedInvestor(customer); 
>         if (investor == null) { 
>             // if the investor is not linked, see if we can find an existing
> one 
>             investor = findExistingInvestor(customer); 
>             if (investor == null) { 
>                 // if we can find an existing investor we will need to
> create one 
>                 investor = createInvestor(customer); 
>                 createInvestorLink(investor, customer); 
>             } else { 
>                 // create a link to the investor we found 
>                 createInvestorLink(investor, customer); 
>                 updateInvestor(investor, customer); 
>             } 
>         } else { 
>             // update the linked investor 
>             updateInvestor(investor, customer); 
>         } 
>         process.setLastItem(process.getLastItem() + 1); 
>         try { 
>             sqlMap.commitTransaction(); 
>         } catch (SQLException e) { 
>             logger.error(e); 
>         } finally { 
>             try { 
>                 sqlMap.endTransaction(); 
>             } catch (SQLException e) { 
>                 logger.error(e); 
>             } 
>         } 
>     } 
> getLinkedInvestor and findExistingInvestor are queries, createInvestor,
> createInvestorLink and updateInvestor insert or update the database and
> should be within a transaction. 
> When I have done similar things in perl with DBI, I opened multiple
> connections to the database. However, when I tried using connections
> (SqlMapClient.openConnection()), I did not get two independent connections
> to the database. 
> Am I approaching this wrong?  I cannot have the transaction started outside
> the rowHandler, as I expect to process 1.4 million rows. Much to large for a
> single transaction. 
> Thanks 
> David Putnam 
> Wells Fargo Funds Management, LLC 
> One Hundred Heritage Reserve 
> Menomonee Falls, WI 53051-4400 
> MAC:   N9882-030 
> Voice: 414-359-3678 
> This transmission may contain information which is confidential, proprietary
> and privileged.  If you are not the individual or entity to which it is
> addressed, note that any review, disclosure, copying, retransmission or
> other use is strictly prohibited.  If you received this transmission in
> error, please notify the sender immediately and delete the material from
> your system.  This transmission is for informational purposes only, and is
> not intended as an offer or solicitation for the purchase or sale of any
> financial instrument or as an official confirmation of any transaction.  Any
> information regarding specific investments or other products is not
> warranted as to completeness or accuracy and is subject to change without
> notice.

View raw message