比价系统定时任务卡死诡异问题

[huangyucai]

一、故障

9月16日下午机房停电，之后服务器重启，比价系统全量同步数据的定时任务执行速度慢，直到卡死，最后报错：

第一种：

org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [INSERT INTO pp_product_type SET 

product_type_id=?,name=?,type=?,parent_id=?,pub_url=?,order_key=? ON DUPLICATE KEY UPDATE 

name=?,type=?,parent_id=?,pub_url=?,order_key=?]; Lock wait timeout exceeded; try restarting transaction; nested exception is 

java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

第二种：

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [INSERT INTO pp_product SET 

product_id=?,name=?,brand_id=?,type_id=?,price=?,pic_path=?,pub_url=?, 

brand_name=?,model_name=?,series_id=?,variance_id=?,price_config=?,order_key=?,create_date=?,hot_new=?,variance_count=?,artpic_coun

t=?, 

download_count=?,article_count=?,visit_count=?,comment_count=?,comment_commend_count=?,comment_uncommend_count=?,emall_o

wned=?, 

forum_path=?,item_1=?,item_2=?,item_3=?,item_4=?,item_5=?,version=?,review_count=?,last_count_order=?,eyp_price_count=?,kzd_count=?

, price_100=?,change_rate_100=?,price_show=?,short_name=?,warranty=?,compat=?,mid_pic_path=?,video_count=?,vip_flag=?,item_6=?, 

comment_score=?,comment_total_vote=?,index_pic_path=?,index6_pic_path=?,index7_pic_path=?,concept=?,price_str=?,change_rate_str=? 

ON DUPLICATE KEY UPDATE name=?,brand_id=?,type_id=?,price=?,pic_path=?,pub_url=?, 

brand_name=?,model_name=?,series_id=?,variance_id=?,price_config=?,order_key=?,create_date=?,hot_new=?,variance_count=?,artpic_coun

t=?, 

download_count=?,article_count=?,visit_count=?,comment_count=?,comment_commend_count=?,comment_uncommend_count=?,emall_o

wned=?, 

forum_path=?,item_1=?,item_2=?,item_3=?,item_4=?,item_5=?,version=?,review_count=?,last_count_order=?,eyp_price_count=?,kzd_count=?

, price_100=?,change_rate_100=?,price_show=?,short_name=?,warranty=?,compat=?,mid_pic_path=?,video_count=?,vip_flag=?,item_6=?, 

comment_score=?,comment_total_vote=?,index_pic_path=?,index6_pic_path=?,index7_pic_path=?,concept=?,price_str=?,change_rate_str=?]; 

No operations allowed after statement closed.; nested exception is java.sql.BatchUpdateException: No operations allowed after statement 

closed.

但测试环境里跑定时任务并没有发现这类问题

二、分析
1、首先怀疑有执行慢的SQL语句，同步数据的定时任务大部分使用mysql的“insert ... ON DUPLICATE KEY UPDATE...”这样的语句
于是改造，将SQL改成insert 和 update两条语句，在执行前判断存在记录，如果不存在则insert，否则update。但问题并没有解决。定时
任务依旧会卡死

2、从第一种异常看，有锁表的现象，事务重新启动，第二种异常，statement 等待时间长被关闭导批量更新异常。 猜测事务未提交所致
比价系统是使用spring的aop管理事务的，我们试图改用手动管理事务：
a、使用了spring的TransactionTemplate，即在spring配置文件中配置一个TransactionTemplate的bean

<bean id="transactionTemplate"  class="org.springframework.transaction.support.TransactionTemplate">  
		<property name="transactionManager" ref="transactionManager" />
	</bean>
	 <tx:annotation-driven transaction-manager="transactionManager"/>

b、对定时任务的service方法加上去掉事务管理的注解

@Transactional(propagation=Propagation.NOT_SUPPORTED,readOnly=false)

c、通过 transactionTemplate方式手动控制提交事务

 Object object = transactionTemplate.execute(new TransactionCallback() {  
            public Object doInTransaction(TransactionStatus status) {  
                try {  
                    // 数据库操作 
                } catch (Exception e) {  
                    status.setRollbackOnly();  
                    e.printStackTrace();  
                }  
                return null;  
            }  
        });  

测试发现速度依旧很慢，最后抛相同的异常
尝试把控制事务提交的代码改成如下：

DefaultTransactionDefinition def = new DefaultTransactionDefinition();
					def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
					def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

					if (isExists > 0) {// 更新
						TransactionStatus status = transactionManager
								.getTransaction(def);
						try {
							simpleJdbcTemplate.update(updSql, getRecordMap(rs));
							transactionManager.commit(status);
						} catch (Exception e) {
							transactionManager.rollback(status);
							e.printStackTrace();
					}

异常依旧在
期间，请DBA检查并没有发现慢的语句，但偶尔发现会锁表

3、估计在执行定时任务时，使用的是spring的SimpleJdbcTemplate，事务可能未提交，于是调整直接用jdbc来执行数据库操作
数据源用配置jndi的数据源，方法代码如下

	Context ctx = new InitialContext();
	ctx = new InitialContext();
	DataSource ds =(DataSource)ctx.lookup("java:comp/env/jdbc/priceparity");
	return  ds.getConnection();

采用jdbc执行一次提交一次事务：
conn.setAutoCommit(false);
执行
conn.commit();
但是执行速度越来越慢，执行时间成2倍数增长，最后达到约120秒每次， 线程卡死，并抛出异常：

 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). 

Transaction resolution unknown.

4、因为spring配置service包下的类均会开启事务， 写在service包下的类会受到影响，而且数据的链接用的是jndi的配置，配有一定的活动链接数，在执

行时间较长的任务时，Connection可能被重置或关闭。
鉴于此，我们将定时任务的业务类写在另外一个包job下，连接数据库直接用jdbc的方式：

Class.forName("com.mysql.jdbc.Driver").newInstance();
String url="jdbc:mysql://192.168.75.100:3315/priceparity_new?user=priceparity_app&password=priceparity_app";
return DriverManager.getConnection(url);

每次执行insert和update后都提交事务
执行产品信息同步任务，发现不会卡死，每次执行时间为约为0.2秒，速度依旧慢，产品数据总量越有28万，但是能完成执行，不会抛出异常
将同步爬虫数据的定时任务也按照这种方法改造，执行速度可以约5毫秒每条。

问题的根本原因暂时未找到，初步判定原因
1、jndi方式的数据库连接在长时间内会被关闭或者回收
2、spring事务管理的配置引起
3、mysql数据库端的问题。

[chenchongqi]

这个初步的判断是这样的：

• 数据库那边缺省设置是autocommit = 0，目的是应用这边如果有异常的时候能够完整回滚，否则大家每个sql一执行就提交了，无法保证完整回滚。
• autocommit关掉的状况下，我们是通过aop配置来commit的，并且在autocommit=0时，select也要显式commit，但是之前的配置里，有些查询的方法aop没覆盖到，没有commit或者rollback就丢回了连接池，这样有可能导致这个定时任务执行update时拿到的数据库连接池里的链接是有残留未提交事务的，这个通过在dba那里查innodb_trx表也可以看到有些长时间存在的事务没有提交。
• 同时这个定时任务本身在每个循环里也有些查询的操作，估计这些查询的事务堆积后，做update和insert操作时会有锁的影响或者需要等链接资源才可以执行，这也是为什么在开发环境没试出来的原因，开发环境没有什么查询的操作在同时进行。
• 用最原始的jdbc配置建立连接后，拿到的应该是干净的链接了，所以会有明显的好转。

---

但是为了以后容易维护，我不太赞成在代码里存在这种与众不同的数据库操作方式，最好是节后回来彻底搞清楚并修正过来，统一用geli的框架来操作。

当然还是要检查一下查询数据库的地方，保证所有的都有commit。

还有就是，不建议在代码里去设置autocommit参数，因为你代码里不知道目前的环境是autocommit=0还是=1，设置了跑完了你没有复位，这个数据库连接就被你改了丢回链接池里，影响了其他地方的使用。

相关参考：
 http://www.taobaodba.com/html/1239_gdb_show_uncommit_trx_table_list.html