ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: commitTransaction in a batch
Date Wed, 21 Jun 2006 21:51:56 GMT
Aha!  I think the problem is related to the EXTERNAL transaction manager.
When you use this transaction manager iBATIS doesn't do any commits or
rollbacks - it assumes the container is doing it.  So the
commitTransaction() method does nothing in this case.  Even though you've
sprinkled these calls to commitTransaction() throughout your batch, there is
really only one commit - the commit that WebLogic does at the end of the
facade method.  So WebLogic's transaction service is filling up because the
transaction gets to be too huge.

One way to fix this is to have a facade method that inserts 100 records, and
then call that method repeatedly from the client.  Then WebLogic can commit
after every facade method (this is a pretty good option).

Or you can tune the WebLogic transaction manager to give it more memory.

Or you can stop using container managed transactions and manage all
transactions yourself with iBATIS methods - but then you've removed the only
good argument for using EJBs and you might as well switch to Tomcat :).

Or you can find some other way to insert 100,000 records by not going
through the session facade (I would do this one or the first option).  I
don't know if you're remoting or not, but if you are the Serialization for
such a huge transaction would be really consuming.

Jeff Butler



On 6/21/06, jaybytez <jaybytez@gmail.com> wrote:
>
>
> Here are the details...could be something in the way I am configuring this
> (obviously).
>
> I am running WebLogic 8.1.4, Oracle 9.1, JDK 1.4.2.  I am pulling my
> Connections from JNDI and using the container to handle my transactions
> through a SessionFacade layer that has the transaction demarcation.  The
> transaction timeout level for this is 600 seconds.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMapConfig
>    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
>    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
>
> <sqlMapConfig>
>    <settings cacheModelsEnabled="true" enhancementEnabled="true"
>        lazyLoadingEnabled="true" errorTracingEnabled="true"
>        maxRequests="32" maxSessions="10"
>        maxTransactions="5" useStatementNamespaces="false" />
>
>    <transactionManager type="EXTERNAL" commitRequired="false">
>        <dataSource type="JNDI">
>            <property name="DataSource" value="jdbc/providerDb" />
>        </dataSource>
>    </transactionManager>
>
>    <!-- Identify all SQL Map XML files to be loaded by this
>        SQL map.  Notice the paths are relative to the classpath. -->
>    <sqlMap resource="ibatis/ProviderFooDAO.xml" />
>
> </sqlMapConfig>
>
> My ProviderDAO.xml looks like this.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap
>    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
>    "http://www.ibatis.com/dtd/sql-map-2.dtd">
>
> <sqlMap namespace="ProviderFacilityDAO">
>
> <insert id="batchInsertFacilityService"
>    parameterClass="com.foo.to.FacilityServiceTO">
>    <![CDATA[
>    INSERT INTO facility_service (provider_medicare_number,
> facility_service_code)
>    VALUES (#medicareId#, #serviceCode#)
>    ]]>
> </insert>
>
> <insert id="batchInsertFacilityServiceLookup"
>    parameterClass="string">
>    <![CDATA[
>                INSERT INTO facility_service_lookup (
> facility_service_code,
> facility_service_desc )
>                SELECT facility_service_lookup_seq.nextval, #value#
>        FROM dual
>    ]]>
> </insert>
>
> <resultMap id="serviceLookupResult" class="com.foo.to.ServiceTO">
>    <result property="serviceDesc" column="facility_service_desc"/>
>    <result property="serviceCode" column="facility_service_code"/>
> </resultMap>
>
> <select id="queryListFacilityServiceLookup"
> resultMap="serviceLookupResult">
>    <![CDATA[
>      SELECT facility_service_desc, facility_service_code
>      FROM facility_service_lookup
>    ]]>
> </select>
>
> <delete id="deleteFacilityService">
>    <![CDATA[
>    DELETE FROM facility_service
>    ]]>
> </delete>
>
> <delete id="deleteFacilityServiceLookup">
>    <![CDATA[
>    DELETE FROM facility_service_lookup
>    ]]>
> </delete>
>
> <update id="updateFacilityService">
>    <![CDATA[
>    ANALYZE TABLE facility_service COMPUTE STATISTICS FOR TABLE FOR ALL
> INDEXES FOR ALL INDEXED COLUMNS
>    ]]>
> </update>
>
> <update id="updateFacilityServiceLookup">
>    <![CDATA[
>        ANALYZE TABLE facility_service_lookup COMPUTE STATISTICS FOR TABLE
> FOR ALL
> INDEXES FOR ALL INDEXED COLUMNS
>    ]]>
> </update>
>
> </sqlMap>
>
> They run in this order.
> 1) I run the two delete statements.
> 2) I run the batchInsertFooServiceLookup
> 3) I query with the queryListFooServiceLookup to get my lookup table
> 4) I run batchInsertFooService
> 5) I run the two update/analyze statements
>
> The step #4 inserts the 100,000 records and this is where I get the
> OutOfMemory.  I use a DAO Proxy where I name my DAO interface methods by
> the
> same name of the ids in the sqlMap file.  Therefore developers don't have
> to
> code any DAO implementation, it just fully uses iBatis behind the scenes.
> Here is my Proxy class:
>
> public class DAOProxy implements InvocationHandler {
>    private static final Logger logger = Logger.getLogger(DAOProxy.class);
>    private static int INSERT = 0;
>    private static int DELETE = 1;
>    private static int UPDATE = 2;
>    private static int SELECT = 3;
>    private SQLExceptionConverter exceptionConverter;
>    private String sqlMapFileName;
>
>    /**
>     * Creates a new DAOProxy object.
>     *
>     * @param fileName param
>     */
>    public DAOProxy(String fileName) {
>        super();
>        sqlMapFileName = fileName;
>        exceptionConverter = new GenericExceptionConverterImpl();
>    }
>
>
>    /**
>     * Creates a new DAOProxy object.
>     *
>     * @param fileName param
>     */
>    public DAOProxy(String fileName, SQLExceptionConverter
> exceptionConverter) {
>        super();
>        sqlMapFileName = fileName;
>        this.exceptionConverter = exceptionConverter;
>    }
>
>    /**
>     * @param obj param
>     * @param method param
>     * @param args param
>     *
>     * @return returned
>     *
>     * @throws Throwable can be thrown
>     * @throws DataAccessException can be thrown
>     */
>    public Object invoke(Object obj, Method method, Object args[])
>      throws Throwable {
>        logger.debug("invoke");
>
>        SqlMapClient sqlMap =
> IbatisSqlMapConfig.getSqlMapInstance(this.sqlMapFileName);
>        Object returnValue = null;
>
>        String methodName = method.getName();
>        String ucaseMethodName = methodName.toUpperCase();
>
>        Object singleArgument = null;
>
>        //DEBUG
>        if(logger.isDebugEnabled()) {
>            logger.debug("Proxy Called");
>            logger.debug("Object:" + obj.getClass().getName());
>            logger.debug("Method:" + methodName);
>
>            //DEBUG
>            if(args != null) {
>                for(int i = 0; i < args.length; i++) {
>                    logger.debug("Arg:" + i + ":" + args [i]);
>                }
>            }
>        }
>
>        if(ucaseMethodName.startsWith("GET") ||
> ucaseMethodName.startsWith("SELECT") || ucaseMethodName.startsWith
> ("EXEC"))
> {
>            singleArgument = validateSingleArgument(args);
>            returnValue = queryForObject(sqlMap, methodName,
> singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("INSERT")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = insert(sqlMap, methodName, singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("UPDATE")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = update(sqlMap, methodName, singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("DELETE")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = delete(sqlMap, methodName, singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("BATCHINSERT")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = batch(sqlMap, INSERT, methodName,
> singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("BATCHUPDATE")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = batch(sqlMap, UPDATE, methodName,
> singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("BATCHDELETE")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = batch(sqlMap, DELETE, methodName,
> singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("QUERYLIST")) {
>            singleArgument = validateSingleArgument(args);
>            returnValue = queryForList(sqlMap, methodName, singleArgument);
>        }
>        else if(ucaseMethodName.startsWith("QUERYMAP")) {
>            validateMappedArguments(args);
>            returnValue = queryForMap(sqlMap, methodName, args[0], args[1],
> args[2]);
>        }
>        else {
>            throw new DataAccessException("dao method does not appear to be
> a get, insert, update or delete method.  don't know how to handle it");
>        }
>
>        if(logger.isDebugEnabled()) {
>            logger.debug("returnValue = " + returnValue);
>        }
>
>        return returnValue;
>    }
>
>
>    /**
>     * @param sqlMap
>     * @param methodName
>     * @param object
>     * @param object2
>     * @param object3
>     * @return
>     */
>    private Object queryForMap(SqlMapClient sqlMap, String methodName,
> Object argument, Object key, Object value) {
>        Map object = null;
>
>        try {
>            object = sqlMap.queryForMap(methodName, argument, (String)key,
> (String)value);
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>
>        return object;
>    }
>
>
>    /**
>     * @param args
>     * @return
>     */
>    private Object validateSingleArgument(Object[] args) {
>        Object argument = null;
>        if(args == null) {
>            argument = null;
>        }
>        else {
>            argument = args[0];
>        }
>        if((args != null) && (args.length > 1)) {
>            throw new DataAccessException("You must specify only 1(one)
> argument/parameter to be passed in.  This argument may be a Primitive, Map
> or Object (TO).");
>        }
>        return argument;
>    }
>
>    /**
>     * @param args
>     * @return
>     */
>    private void validateMappedArguments(Object[] args) {
>        if((args == null) || (args.length > 3)) {
>            throw new DataAccessException("You must specify exactly
> 3(three)
> arguments/parameters to be passed in.  The first argument may be a
> Primitive, Map or Object (TO), followed by a String for the key, and
> followed by a String for the value.");
>        }
>    }
>
>
>    /**
>     * @param sqlMap The SqlMapClient that is being used based on the
> database being hit.
>     * @param batchType param
>     * @param methodName The name of the DAO method being called.
>     * @param argument The Object being passed into the underlying
> Statement.
>     * @return returned
>     */
>    private Object batch(SqlMapClient sqlMap, int batchType, String
> methodName, Object argument) {
>        int commitSize = 100;
>        boolean commitBatch = false;
>        int totalRecordsUpdated = 0;
>        int commitBatchUpdated = 0;
>
>        try {
>            List list = (List)argument;
>            sqlMap.startTransaction();
>            sqlMap.startBatch();
>
>            for(int i = 0; i < list.size(); i++) {
>                if(batchType == INSERT) {
>                    sqlMap.insert(methodName, list.get(i));
>                }
>                else if(batchType == DELETE) {
>                    sqlMap.delete(methodName, list.get(i));
>                }
>                else if(batchType == UPDATE) {
>                    sqlMap.update(methodName, list.get(i));
>                }
>
>                if(i == (commitSize - 1)) {
>                    commitBatch = true;
>                }
>                else if(i == (list.size() - 1)) {
>                    commitBatch = true;
>                }
>
>                if(commitBatch) {
>                    commitBatchUpdated = sqlMap.executeBatch();
>                    sqlMap.commitTransaction();
>                    totalRecordsUpdated = totalRecordsUpdated +
> commitBatchUpdated;
>                    commitBatch = false;
>                }
>            }
>
>            //Currently iBatis does not return what the driver returns for
> batch
>            //counts.  iBatis converts the -2 Oracle values to 0.  And it
> only
>            //returns a full count.  Not an array of values.  The future
> version
>            //of iBatis will fix this with an executeBatchDetailed.  Once
> this
>            //occurs...use the validateBatchResults method and you can
> validate
>            //the totalRecordsUpdated against 0 and the size of the list.
>            validateBatchResult(methodName, new
> int[]{totalRecordsUpdated});
> //            validateBatchResults(methodName, totalRecordsUpdated);
> //            if(totalRecordsUpdated == 0) {
> //                throw new DataAccessException("No records were modified
> by
> the batch statement. " + methodName);
> //            }
> //            if(totalRecordsUpdated != list.size()) {
> //                throw new DataAccessException("Not all of the records
> were
> successfully updated/inserted/deleted in batch mode. " + methodName);
> //            }
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>        finally {
>            try {
>                sqlMap.endTransaction();
>            }
>            catch(SQLException e) {
>                exceptionConverter.rethrow(methodName, e);
>            }
>        }
>
>        return new int[] {totalRecordsUpdated};
>    }
>
>    /**
>     * @param sqlMap The SqlMapClient that is being used based on the
> database being hit.
>     * @param methodName The name of the DAO method being called.
>     * @param argument The Object being passed into the underlying
> Statement.
>     * @return returned
>     */
>    private Integer delete(SqlMapClient sqlMap, String methodName, Object
> argument) {
>        Integer integer = null;
>
>        try {
>            int record = sqlMap.delete(methodName, argument);
>            validateResult(methodName, record);
>            integer = new Integer(record);
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>
>        return integer;
>    }
>
>
>    /**
>     * @param sqlMap The SqlMapClient that is being used based on the
> database being hit.
>     * @param methodName The name of the DAO method being called.
>     * @param argument The Object being passed into the underlying
> Statement.
>     * @return returned
>     */
>    private Object insert(SqlMapClient sqlMap, String methodName, Object
> argument) {
>        Object object = null;
>
>        try {
>            object = sqlMap.insert(methodName, argument);
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>
>        return object;
>    }
>
>
>    /**
>     * @param sqlMap The SqlMapClient that is being used based on the
> database being hit.
>     * @param methodName The name of the DAO method being called.
>     * @param argument The Object being passed into the underlying
> Statement.
>     * @return returned
>     */
>    private Object queryForList(SqlMapClient sqlMap, String methodName,
> Object argument) {
>        Object object = null;
>
>        try {
>            object = sqlMap.queryForList(methodName, argument);
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>
>        return object;
>    }
>
>
>    /**
>     * @param sqlMap The SqlMapClient that is being used based on the
> database being hit.
>     * @param methodName The name of the DAO method being called.
>     * @param argument The Object being passed into the underlying
> Statement.
>     * @return returned
>     */
>    private Object queryForObject(SqlMapClient sqlMap, String methodName,
> Object argument) {
>        List returnValue = null;
>
>        try {
>            returnValue = sqlMap.queryForList(methodName, argument, 0, 2);
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>
>        if(returnValue == null) {
>            throw new NoUniqueRecordException("No unique record was
> returned
> for the query method: " + methodName);
>        }
>        else if(returnValue.size() > 1) {
>            throw new UniqueRecordViolationException("More than one unique
> record returned for the query method: " + methodName);
>        }
>
>        return returnValue;
>    }
>
>
>    /**
>     * @param sqlMap The SqlMapClient that is being used based on the
> database being hit.
>     * @param methodName The name of the DAO method being called.
>     * @param argument The Object being passed into the underlying
> Statement.
>     * @return returned
>     */
>    private Integer update(SqlMapClient sqlMap, String methodName, Object
> argument) {
>        Integer integer = null;
>
>        try {
>            int record = sqlMap.update(methodName, argument);
>            validateResult(methodName, record);
>            integer = new Integer(record);
>        }
>        catch(SQLException e) {
>            exceptionConverter.rethrow(methodName, e);
>        }
>
>        return integer;
>    }
>
>
>    /**
>     * The purpose of this method is to validate a batch result (int [])
> that
>     * contains a int describing whether or not the record failed to
> update/delete/insert.
>     * If one of the records failed, throw a DataAccessException so that a
> rollback
>     * occurs.
>     *
>     * @param methodName The name of the DAO method being called.
>     * @param records The result records from an execute batch.
>     *
>     * @throws DataAccessException Record failed to updated.
>     */
>    private void validateBatchResult(String methodName, int records[]) {
>        for(int counter = 0; counter < records.length; counter++) {
>            int result = records [counter];
>
>            if(result < 0) {
>                throw new DataAccessException("Update/delete/insert did not
> update the requested row/rows successfully. methodName -> " + methodName +
> "
> record number -> " + counter + " return code -> " + result);
>            }
>        }
>    }
>
>
>    /**
>     * The purpose of this method is to validate an execute result (int)
> that
>     * contains a int describing whether or not the record failed to
> update/delete/insert.
>     * If the record failed, throw a DataAccessException so that a rollback
>     * occurs.
>     *
>     * @param methodName The name of the DAO method being called.
>     * @param record The result record from an execute.
>     *
>     * @throws DataAccessException Record failed to updated.
>     */
>    private void validateResult(String methodName, int record) {
>        if(record <= 0) {
>            throw new DataAccessException("Update/delete/insert did not
> update the requested row/rows successfully. methodName -> " + methodName +
> "
> record -> " + record);
>        }
>    }
> }
>
> Thanks for your help and I hope I provided enough information...
>
> jay blanton
>
>
> --
> View this message in context:
> http://www.nabble.com/executeBatch-not-returning-int-of-records-updated.-t1819686.html#a4982934
> Sent from the iBATIS - User - Java forum at Nabble.com.
>
>

Mime
View raw message