大家好,我是考100分的小小码 ,祝大家学习进步,加薪顺利呀。今天说一说Mysql事务超时「建议收藏」,希望您对编程的造诣更进一步.
本文概览:介绍了超时有关的概念:@Transaction的timeout、mybatis的timeout、mysql的innodb_lock_wait_timeout。
1 问题
1.1 背景
在一个事务中完成解析一个大文件,分批存入到数据库。遇到问题,执行时间比较长,就讨论了事务超时的问题,担心执行时间太长,事务超时自动回滚了。
为了考虑这个问题,需要考虑如下超时相关的设置:
- 一个事务的超时时间。spring的@Transactional
- 一个stametn的执行时间。包括mybais的timeout、jdbc的timeout和mysql中的innodb_lock_wait_timeout
- 一个connection的持续时间。myql中的wait_timeout
1.2 问题的结果
我们目的时时间执行时间过长不会被中止。所以:
- 就不需要设置@Transactional了,因为@Transaction设置的目的是为了在一个事务执行时间超过某一个预知时抛出异常。
- 对于mybatis的timeout也不需要设置,因为这个设置也是为了在某一个statemnet时间太久时被中止。
- 对于myql的innodb_lock_wait_timeout,这个是针对两个事务同时更新同一行记录时,等待的最长时间,因为我们都是新增数据,所以也不需要考虑。
- 对于connection的持续时间wait_timeout。这个时间是这个connection执行最近一个statement到当前的时间间隔,因为我们这个没解析100条记录就执行一次insert,所以两次insert的时间间隔很短,所以也不会超时。
综上,我们不需要额外的进行设置。
2 Spring的 @Transactional的超时
2.1 作用
定义一个事务的超时时间,一个事务中包含很多statement。
2.2 源码实现
1. spring实现超时
(1)根据timeout+当前时间点 赋值给一个deadLine。
(2)每一次执行sql,就会获取到一个statement时,计算liveTime =(deadline- 当前时间),分如下两种情况处理:
- 如果liveTime>0,此时就执行stament.setQueryTimeout(liveTime);
- 如果liveTime < 0,此时就抛出异常
(3)总结
spring实现超时通过deadLine和jdbc的stament#setQueryTime两种策略来判断超时。
2、源码分析
如果选择DataSourceTransactionManager,事务内所有的sql操作必须通过JdbcTemplate执行才能使timeout设置正常工作,通过myBatis执行的sql操作将无法应用超时设置。
(1)在开启事务时将注解中timeout赋值给connectionHolder
1 2 |
DataSourceTransactionManager#doBegin txObject.getConnectionHolder().setTimeoutInSeconds(timeout); |
(2)在ResourceHolderSuport中提供了超时校验
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public long getTimeToLiveInMillis() throws TransactionTimedOutException{ if (this.deadline == null) { throw new IllegalStateException(“No timeout specified for this resource holder”); } long timeToLive = this.deadline.getTime() – System.currentTimeMillis(); // 校验timeout checkTransactionTimeout(timeToLive <= 0); return timeToLive; } // 如果超时抛出异常 private void checkTransactionTimeout(boolean deadlineReached) throws TransactionTimedOutException { if (deadlineReached) { setRollbackOnly(); throw new TransactionTimedOutException(“Transaction timed out: deadline was ” + this.deadline); } } |
(3)查看那些地方用到了上述接口
在DataSourceUtils中的applyTransactionTimeout和applyTiemou用到了上述接口,如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
public static void applyTransactionTimeout(Statement stmt, DataSource dataSource) throws SQLException { applyTimeout(stmt, dataSource, 0); }
public static void applyTimeout(Statement stmt, DataSource dataSource, int timeout) throws SQLException { Assert.notNull(stmt, “No Statement specified”); Assert.notNull(dataSource, “No DataSource specified”); ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource); if (holder != null && holder.hasTimeout()) { // 设置statment的超时时间 stmt.setQueryTimeout(holder.getTimeToLiveInSeconds()); } else if (timeout > 0) { // No current transaction timeout -> apply specified value. stmt.setQueryTimeout(timeout); } } |
(4)再查看用到DataSourceUtils的applyTimeout和applyTransactionTimeout的地方
- 用到applyTimeout的地方。JdbcTemplate#applyStatementSettings。
- 用到applyTransactionTimeout的地方,TransactionAwareDataSourceProxy#TransactionAwareInvocaitonHandler.invoke
(5)暂时没有发现使用TransactionAwareDataSourceProxy的地方,所以只需要再查看使用JdbcTemplate#applyStatementSettings地方:在JdbcTemplate的如下3个函数中使用。所以可以理解使用@Transactional的timeout的时候,必须要使用jdbcTemplate实现dao,而不能通过mybatis。
1 2 3 4 5 6 |
public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action){ } public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action){ } public <T> T execute(StatementCallback<T> action) throws DataAccessException { } |
2.3 复现@Transactional的超时
由上分析,使用@Transactional的timeout属性时,需要使用JDBCtemplate实现dao,而不能使用Mybatis。JdbcTemplate配置可以参考: JDBCtemplate实例 。
1. 使用JDBC定义一个dao
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@Service public class StudentDaoWithJdbcTemplate { @Resource private JdbcTemplate jdbcTemplate; public void update() { jdbcTemplate.execute(new StatementCallback<Integer>() { public Integer doInStatement(Statement stmt) throws SQLException, DataAccessException { stmt.execute(“update student set name=’success511′ where id = 1;”); return 1; } }); } } |
2、设置事务超时的service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
@Service public class TimeOutService { private static final Logger logger = LoggerFactory.getLogger(TimeOutService.class); @Resource private StudentDaoWithJdbcTemplate studentDaoWithJdbcTemplate;
/** * 测试注解@Transactional的超时timeout—jdbcTemlate实现dao */ @Transactional(value = “transactionManager”, timeout = 1) public void testAnnotationTransactionalTimeOutWithJdbcTemplate() { try { Thread.sleep(3000); logger.info(“sleep 3s end”); } catch (Exception e) { } studentDaoWithJdbcTemplate.update(); } } |
3、写一个测试函数
1 2 3 4 5 6 7 |
/** * 测试@Transactional的超时属性—Template */ @Test public void testAnnotationTransactionalServiceTestWithJdbcTemplate(){ timeOutService.testAnnotationTransactionalTimeOutWithJdbcTemplate(); } |
执行结果为
org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was Thu Dec 08 10:22:39 CST 2016
3 Mybatis层的timeout
3.1 作用
就是设置一个stament的执行时间,包括update、delte、select和select。
3.2 源码实现
通过jdbc的statement的setQueryTimeout来实现。如下在BaseStatemntHandler中setStatementTimeout来设置超时。
1 2 3 4 5 6 7 8 9 10 |
// 设置超时属性 protected void setStatementTimeout(Statement stmt) throws SQLException { Integer timeout = mappedStatement.getTimeout(); Integer defaultTimeout = configuration.getDefaultStatementTimeout(); if (timeout != null) { stmt.setQueryTimeout(timeout); } else if (defaultTimeout != null) { stmt.setQueryTimeout(defaultTimeout); } } |
3.3 复现Mybatis超时
(1)mapper.xml
设置超时时间为“1”。如下
1 2 3 4 5 6 7 8 |
<update id=”updateCheckTimeOut” parameterType=”int” timeout=”1″> UPDATE student SET name = ‘timeout’ WHERE id = #{id} </update> |
(2)dao接口
1 2 3 4 5 6 7 8 |
@Repository public interface StudentDao { /** * 测试mybatis中timeout字段 * @param id */ void updateCheckTimeOut(int id); } |
(3)service
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
@Service public class TimeOutService { private static final Logger logger = LoggerFactory.getLogger(TimeOutService.class); @Resource private StudentDao studentDao; /** * 测试mybatis配置的超时,这里还需要开启事务,通过利用不同事务中同时更新同一条语句时,需要等待行锁,来实现增长sql的执行时间 */ @Transactional(value = “transactionManager”) public void testMybatisTimeOut() { studentDao.updateCheckTimeOut(1); try { Thread.sleep(3000); logger.info(“sleep 3s end”); } catch (Exception e) { } } } |
(4)测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
/** * 测试mybais的timeout */ @Test public void testMybatisTimeOut(){ Thread t1 = new Thread(new Runnable() { public void run() { timeOutService.testMybatisTimeOut(); } }); Thread t2 = new Thread(new Runnable() { public void run() { timeOutService.testMybatisTimeOut(); } }); t1.start(); t2.start(); // 为了查看子线程执行情况,所以这里使用while-true while(true){ } } |
结果为:
### SQL: UPDATE student SET name = ‘timeout1’ WHERE id = ?
### Cause: java.sql.SQLException: Query execution was interrupted
4 JDBC的timeout
介绍了JDBC的query timeout 和 socket timeout。
4.1 QueryTiemout
4.1.1 作用
就是设置stament执行时间。
4.1.2 配置
1、 默认情况下为0,即不生效。
2、 设置
通过Statement的setQueryTimeout来设置。如下
1 |
void setQueryTimeout(int seconds) throws SQLException; |
4.1.3 Statement的QueryTimeout处理过程
在执行stament时,会同时建立一个检查超时的线程,如果statment执行超时了,这个线程会向mysql的server端发送一个取消执行的请求,而不是等执行完了statment才统计时间,判断是否超时。具体流程如下:
- 通过调用Connection的createStatement()方法创建statement
- 调用Statement的executeQuery()方法
- statement通过自身connection将query发送给MySQL数据库
- statement创建一个新的timeout-execution线程用于超时处理
- 5.1版本后改为每个connection分配一个timeout-execution线程
- 向timeout-execution线程进行注册
- 达到超时时间
- TimerThread调用JtdsStatement实例中的TsdCore.cancel()方法
- timeout-execution线程创建一个和statement配置相同的connection
- 使用新创建的connection向超时query发送cancel query(KILL QUERY “connectionId”)
4.2 JDBC Socket Timeout
4.2.1 作用
1、作用
在数据库被突然停掉或是发生网络错误(由于设备故障等原因)时,如果没有设置socket timeout的话,应用在数据库返回结果前会无期限地等下去,这种连接被称为dead connection。为了能够避免应用在发生网络错误时产生无休止等待的情况,所以需要设置socket timeout。
注意:socket timeout的值必须要高于statement timeout,否则,在网络正常的情况下,socket timeout将会先生效而statement timeout就失效。
2、socket timeout包含两种timeout:
(1)socket连接时的timeout:connectionTimeout
设置之后会抛出异常 java.net.SocketTimeoutException: connect timed out
(2)socket读写时的timeout:socketTimeout
设置之后会抛出异常 :java.net.SocketTimeoutException: Read timed out
4.2.2 配置
1、默认情况
connectTimeout和socketTimeout都是0,为0时表示不生效。
2、必须需要同时设置connectTimeout和sockTimeout,如下两种方式
(1)通过URL方式设置
1 |
jdbc:mysql://xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000 |
(2)spring在xml时设置dataSource时,如下在配置duridDataSource的connectionTimeout和socketTimeout属性,可以理解为druidDataSource通过这两个属性来拼接成上述URL中的connectionTimeout和socketTimeout。
1 2 3 4 5 6 7 8 9 10 11 |
<bean id=”transDataSource” class=”com.alibaba.druid.pool.DruidDataSource” init-method=”init” destroy-method=”close”> ………. <property name=”connectProperties”> <props> <prop key=”connectTimeout”>1000</prop> <prop key=”socketTimeout”>1000</prop> </props> </property> ………….. </bean> |
4.2.3 JDBC的conetionTimeout和socketTimeout的底层实现
1、socket连接时的timeout:connectionTimeout。
通过Socket.connect(SocketAddress endpoint, int timeout)设置
1 2 3 4 5 6 7 8 9 10 11 |
socket = new Socket(); long t1 = 0; try { t1 = System.currentTimeMillis(); // 设置connect timeout 为2000毫秒 socket.connect(new InetSocketAddress(“www.ss.ssss”, 8080), 2000); } catch (IOException e) { long t2 = System.currentTimeMillis(); e.printStackTrace(); System.out.println(“Connect failed, take time -> ” + (t2 – t1) + “ms.”); } |
2、socket读写时的timeout:socketTimeout
通过Socket.setSoTimeout(int timeout)设置。
1 2 3 4 5 6 7 8 9 |
socket = new Socket(); socket.connect(new InetSocketAddress(“localhost”, 8080)); // 设置so timeout 为2000毫秒 socket.setSoTimeout(2000); System.out.println(“Connected.”); in = socket.getInputStream(); System.out.println(“reading…”); t1 = System.currentTimeMillis(); in.read(); |
注意:如果不设置此属性,那么如果server端突然中断,那么客户端的就会一直阻塞在in.read上面。
4.2.4 遇到过的一个问题
1、 问题描述
之前在做一个项目时,程序运行在进行查询数据库时经常hang住了。
2、分析
在hang住的时候通过jstack打印的堆栈信息如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
“main” prio=10 tid=0x00007ff27400a000 nid=0x7d88 runnable [0x00007ff2792d5000] java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:152) at java.net.SocketInputStream.read(SocketInputStream.java:122) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) – locked <0x00000006bca6d6c0> (a com.mysql.jdbc.util.ReadAheadInputStream) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2331) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2776) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2765) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3301) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1853) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1976) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2497) – locked <0x00000006bca69b00> (a java.lang.Object) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2426) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383) – locked <0x00000006bca69b00> (a java.lang.Object) at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation(ConnectionImpl.java:3074) at com.alibaba.druid.filter.FilterChainImpl.connection_getTransactionIsolation(FilterChainImpl.java:347) at com.alibaba.druid.filter.FilterAdapter.connection_getTransactionIsolation(FilterAdapter.java:871) at com.alibaba.druid.filter.FilterChainImpl.connection_getTransactionIsolation(FilterChainImpl.java:344) at com.alibaba.druid.filter.FilterAdapter.connection_getTransactionIsolation(FilterAdapter.java:871) at com.alibaba.druid.filter.FilterChainImpl.connection_getTransactionIsolation(FilterChainImpl.java:344) at com.alibaba.druid.filter.FilterAdapter.connection_getTransactionIsolation(FilterAdapter.java:871) at com.alibaba.druid.filter.FilterChainImpl.connection_getTransactionIsolation(FilterChainImpl.java:344) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.getTransactionIsolation(ConnectionProxyImpl.java:260) |
由上面的堆栈信息,发现是卡在了socketInputStream.read上面,此时只要配置socketTimeout就可以终止hang住的程序了。即通过配置druidDataSource的connectionTimeout和socketTimeout两个属性来实现,此时就不会hang住了,而是会报如下异常:
1 2 3 4 5 6 7 8 9 |
Caused by: java.net.SocketTimeoutException: Read timed out at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:152) at java.net.SocketInputStream.read(SocketInputStream.java:122) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2911) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2116) |
3、总结
访问数据库时必须配置这两个属性。可以在配置连接池dataSource时,配置connectionTimeout和socketTimeout两个属性来实现。或者直接通过URL指定这两个属性
1 |
jdbc:mysql://xxx.xx.xxx.xxx:3306/database?connectTimeout=60000&socketTimeout=60000 |
5 Mysql层的indb_lock_wait_timeout
5.1 理解
1. 作用
它只针对 行锁(row lock),不同事务同时更新同一行的时候,等待其他事务提交的时间,所以一个事务中如果新增数据,那么不会受到事务超时影响。
2.变量说明
(1)变量说明
- innodb_lock_wait_timeout(超时时间),事务等待行锁的时间。默认是50s.
(2)查看上面的两个变量
5 Mysql层的indb_lock_wait_timeout5.1 理解1. 作用 它只针对 行锁(row lock),不同事务同时更新同一行的时候,等待其他事务提交的时间,所以一个事务中如果新增数据,那么不会受到事务超时影响。 2.变量说明 (1)变量说明
(2)查看上面的两个变量
如下结果 (3)设置超时变量
3. 一个相关的变量 innodb_rollback_on_timeout ,事务等待超时之后,进行回滚的策略。默认是off,表示只回滚到上一个statement,每执行一个sql语句(一个satement)就有一个保存点,此时是回滚到上一个保存点。如果这个值改为on,那此时事务就进行整体回滚,即执行了rollback。 5.2 复现5.2.1 在终端复现1. 实现复现事务超时 (1)不发生事务超时的情况 开启两个终端,分别执行事务,此时事务2可以执行commit,也可以一直等待下去不会出现事务超时,因为此时事务2都是新增数据,不会涉及到行锁(行锁只发生在更新数据或者删除数据)。 (2)发生事务超时
(3)比较 innodb_rollback_on_timeoue为false和ture的两种情况 上面的情况下是在 innodb_rollback_on_timeout=false的情况下进行的,那么 innodb_rollback_on_timeout=true时,则表示回滚了整个事务。此时再执行一个sql,就跟原来的事务没有关系了。 5.2.2 在代码中事务超时的复现1、mysql设置
2、代码 (1)Dao接口
(2)事务函数
(3)测试
(4)查看连接线程show processlit (5)等待10s之后,抛出异常如 org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction ### The error may involve dao.datasource1.StudentDao.updateById-Inline ### The error occurred while setting parameters ### SQL: UPDATE student SET name = ‘success24’ WHERE id = ? ### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction 5.3 mysql 其他时间变量的解释1. wait_timeout 参考:Mysql事务超时 就是一connection的sleep多长时间,通过show processlist来查看一个连接的sleep时间,这个时间是这个connection执行最近一个statement到当前的时间间隔。这个时间就是设置开一个客户端,不做任何操作的时候的连接保持时间。 2. 慢查询查询时间 6 各种TimeOut之间关系上面结果timeout的关系,可以通过中一个http://www.importnew.com/2466.html图来表示。 (1)Spring、Mybatis和JDBC级别的超时并没有更改mysql的属性。可以查看三者的实现:
(2)只有mysql层没有超时的情况下。上层的JDBC或者spring层的timeout才有意义。 7 参考文献【1】JDBC的timeout http://www.importnew.com/2466.html 【2】@Tranasaction 的timeout生效 http://www.cnblogs.com/iceJava/p/4500794.html 【3】java SocketTimout https://my.oschina.net/shipley/blog/715196 (全文完) |
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/11597.html