Lock wait timeout exceeded Jta + Atomikos Mysql Connection Pool

Atomikos 3.8.0, MySQL 5.1.22, Spring 3.2.0

Hello,

I have the following context xml for Jta, Atomikos and 2 Atomikos Datasource beans (batch and uymDS):

Code:

---

<bean id="setMyAtomikosSystemProps"
                class="org.springframework.beans.factory.config.MethodInvokingFactoryBean">
                <property name="targetObject">
                        <!-- System.getProperties() -->
                        <bean
                                class="org.springframework.beans.factory.config.MethodInvokingFactoryBean">
                                <property name="targetClass" value="java.lang.System" />
                                <property name="targetMethod" value="getProperties" />
                        </bean>
                </property>
                <property name="targetMethod" value="putAll" />
                <property name="arguments">
                        <!-- The new Properties -->
                        <util:properties>
                                <prop key="com.atomikos.icatch.file">/opt/etl/monitorPrepFtpFilesMvc/jta.properties</prop>
                                <prop key="com.atomikos.icatch.hide_init_file_path">true</prop>
                        </util:properties>
                </property>
        </bean>

        <bean id="userTransactionService" class="com.atomikos.icatch.config.UserTransactionServiceImp"
                init-method="init" destroy-method="shutdownForce">
                <constructor-arg>
                        <!-- IMPORTANT: specify all Atomikos properties here -->
                        <props>
                                <prop key="com.atomikos.icatch.service">
                                        com.atomikos.icatch.standalone.UserTransactionServiceFactory
                                </prop>
                        </props>
                </constructor-arg>
                <!-- <property name="initialLogAdministrators">
                        <list>
                                <ref bean="localLogAdministrator" />
                        </list>
                </property> -->
        </bean>

        <bean id="atomikosTransactionManager" class="com.atomikos.icatch.jta.UserTransactionManager"
                init-method="init" destroy-method="close" depends-on="userTransactionService">

                <!-- IMPORTANT: disable startup because the userTransactionService above
                        does this -->
                <property name="startupTransactionService" value="false" />

                <!-- when close is called, should we force transactions to terminate or
                        not? -->
                <property name="forceShutdown" value="false" />
        </bean>

        <!-- Also use Atomikos UserTransactionImp, needed to configure Spring -->
        <bean id="atomikosUserTransaction" class="com.atomikos.icatch.jta.UserTransactionImp"
                depends-on="userTransactionService">
                <property name="transactionTimeout" value="300" />
        </bean>

        <bean id="transactionManager"
                class="org.springframework.transaction.jta.JtaTransactionManager"
                depends-on="userTransactionService">
                <property name="transactionManager" ref="atomikosTransactionManager" />
                <property name="userTransaction" ref="atomikosUserTransaction" />
                <property name="allowCustomIsolationLevels" value="true" />
        </bean>

        <!-- batch -->
        <util:properties id="batchDbProperties"
                location="classpath:/META-INF/batchdb.properties" />
        <bean id="dataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean"
                init-method="init" destroy-method="close" depends-on="setMyAtomikosSystemProps">
                <property name="uniqueResourceName" value="dataSource" />
                <property name="xaDataSourceClassName"
                        value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
                <property name="xaProperties" ref="batchDbProperties" />
                <property name="minPoolSize" value="10" />
                <property name="maxPoolSize" value="20" />
                <property name="borrowConnectionTimeout" value="30" />
                <property name="testQuery" value="select 1" />
                <property name="maintenanceInterval" value="60" />
        </bean>

        <!-- uym -->
        <util:properties id="uymDbProperties"
                location="classpath:/META-INF/uymdb.properties" />
        <bean id="uymDS" class="com.atomikos.jdbc.AtomikosDataSourceBean"
                init-method="init" destroy-method="close" depends-on="setMyAtomikosSystemProps">
                <property name="uniqueResourceName" value="uymDS" />
                <property name="xaDataSourceClassName"
                        value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
                <property name="xaProperties" ref="uymDbProperties" />
                <property name="minPoolSize" value="10" />
                <property name="maxPoolSize" value="20" />
                <property name="borrowConnectionTimeout" value="30" />
                <property name="testQuery" value="select 1" />
                <property name="maintenanceInterval" value="60" />
        </bean>

---

The Dao bean is

Code:

---

<bean id="uymFileJdbcDao" class="com.xxx.domain.uym.UymFileJdbcDao">
                <property name="dataSource" ref="uymDS" />
</bean>

---

and the Dao implementation and pertinent code is

Code:

---

public class UymFileJdbcDao extends JdbcDaoSupport implements UymFileDao {

        static final Logger logger = LoggerFactory.getLogger(UymFileJdbcDao.class);
        private static final String[] COLUMNNAMES = {"history_log_id", "da_org_id", "abbr", "name", "path",
                        "mtime", "size", "type", "extension", "row_cnt", "status" };
        private static final String table = " file ";
        private static final String SQL_INSERT = "INSERT INTO" + table + "("
                        + "history_log_id, da_org_id, abbr, name, path, "
                        + "mtime, size, type, extension, row_cnt, "
                        + "status) "
                        + "VALUES ("
                        + "?,?,?,?,?,?,?,?,?,?,"
                        + "?" + ")";
       
        private RowMapper<UymFile> rowMapper = new UymFileRowMapper();
               
        public long insertRecord(UymFile record) {
                KeyHolder keyHolder = new GeneratedKeyHolder();
                final Integer getHistoryLogId = record.getHistoryLogId();
                final Integer getDaOrgId = record.getDaOrgId();
                final String getAbbr = record.getAbbr();
                final String getName = record.getName();
                final String getPath = record.getPath();
                final String getMtime = record.getMtime();
                final String getSize = record.getSize();
                final String getType = record.getType();
                final String getExtension = record.getExtension();
                final Integer getRowCnt = record.getRowCnt();
                final Integer getStatus = record.getStatus();
               
                int row = getJdbcTemplate().update(new PreparedStatementCreator() {
                        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                                PreparedStatement ps = conn.prepareStatement(SQL_INSERT, COLUMNNAMES);
                                ps.setInt(1, getHistoryLogId);
                                ps.setInt(2, getDaOrgId);
                                ps.setString(3, getAbbr);
                                ps.setString(4, getName);
                                ps.setString(5, getPath);
                                ps.setString(6, getMtime);
                                ps.setString(7, getSize);
                                ps.setString(8, getType);
                                ps.setString(9, getExtension);
                                ps.setInt(10, getRowCnt);
                                ps.setInt(11, getStatus);
                                return ps;
                        }
                }, keyHolder);
               
                long rowId = 0;
                if (row > 0)
                        rowId = keyHolder.getKey().longValue();
                return rowId;
                       
        }
}

---

The job step is

Code:

---

<batch:step id="step7">
        <batch:tasklet>
                <batch:chunk reader="filePathItemReader" processor="filePathItemProcessor"
                        writer="filePathItemWriter" commit-interval="1" />
                        <batch:listeners>
                                <batch:listener ref="stepExecutionListener" />
                </batch:listeners>
        </batch:tasklet>
</batch:step>

---

So for each file collected it will read in the file details, convert it to an UymFile and write it to the database.

The filePathItemWriter has:

Code:

---

@Override
        public void write(List<? extends UymFile> items) throws Exception {
                for (UymFile file : items) {
                        long newId = dao.insertRecord(file);
                        updateRecordWithFile(file, newId);
                }
        }

        private void updateRecordWithFile(UymFile file, long Id) throws SQLException, IOException {
                Connection conn = dao.getDataSource().getConnection();
                File filepath = new File(file.getPath());
                fis = new FileInputStream(filepath);
                ps = conn.prepareStatement(INSERT_FILE_SQL);
                ps.setBinaryStream(1, fis, (int) filepath.length());
                ps.setLong(2, Id);
                ps.executeUpdate();
                ps.close();
                fis.close();
                System.out.println("In the writer... " + file.getName() + " id: " + Id);
        }

---

If I don't include the updateRecordWithFile I do not get the java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction.

My understanding was that by setting jta as the transaction manager, using atomikos beans, mysql default as commit true and commit-interval == 1 that commits would occur with each insert / update. I don't have to put @Transactional - I am leaving it to the Jta to decide....

Please advise - thanks.

You are messing around with connections yourself and you aren't managing those connection and as such those connections remain hanging...

So basically the code in your FilePathItemWriter is wrong...

Thank you Marten for putting me straight.

I included the following in UymFileJdbcDao and then replaced updateRecordWithFile(file, newId) with updateFile(newId, File) - initial and file inserts going in with no locking issues:

Code:

---

private static final String SQL_UPDATE_FILE = "UPDATE" + table + " SET " + "file = ? " + "WHERE id = ?";

public int updateFile(long id, UymFile record) {
        int row = 0;
        final long Id = id;
        final File filepath = new File(record.getPath());
        final String[] COLUMNNAME = { "file" };
        try {
                final FileInputStream fis = new FileInputStream(filepath);
                row = getJdbcTemplate().update(new PreparedStatementCreator() {
                        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                        PreparedStatement ps = conn.prepareStatement(SQL_UPDATE_FILE, COLUMNNAME);
                        ps.setBinaryStream(1, fis, (int) filepath.length());
                        ps.setLong(2, Id);
                        return ps;
                        }
                });

                try {
                        fis.close();
                } catch (IOException e) {
                        e.printStackTrace();
                }
        } catch (FileNotFoundException e) {
                e.printStackTrace();
        }
        return row;
}

---