加入收藏 | 设为首页 | 会员中心 | 我要投稿 西双版纳站长网 (https://www.0691zz.com.cn/)- 数据计算、IT业界、服务器、教程、云日志!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

关于innodb_rollback_on_timeout测验

发布时间:2022-06-26 17:02:47 所属栏目:MySql教程 来源:互联网
导读:innodb_rollback_on_timeout innodb_lock_wait_timeout 最开始看到innodb_rollback_on_timeout 时,没有好好理解transaction timeout的意思.以为只要是连接超时就会触发,今天研究了一下.只能说巨坑; 以下测试.隔离级别皆为RR模式.测试过程. 实验一: innodb_ro
  innodb_rollback_on_timeout
  innodb_lock_wait_timeout
  最开始看到innodb_rollback_on_timeout 时,没有好好理解transaction timeout的意思.以为只要是连接超时就会触发,今天研究了一下.只能说巨坑;
  以下测试.隔离级别皆为RR模式.测试过程.
  
  实验一:
  innodb_rollback_on_timeout =on
  innodb_lock_wait_timeout=20    
  ---------------------------------------|----------------------------------------|------------------------------------------------
  session 1                             |            session 2                   |         session  3   
  ---------------------------------------|----------------------------------------|------------------------------------------------
   begin;                               |                                        |select * from information_schema.innodb_trx
                                         |                                        |      order by trx_started;   
  ---------------------------------------|----------------------------------------|------------------------------------------------
  insert into t8 values (20,'ddd');      |                                        |       1   
  ---------------------------------------|----------------------------------------|------------------------------------------------
                                         |            begin;                     |      2   
  ---------------------------------------|----------------------------------------|------------------------------------------------
                                         | update t8 set test1='yyy' where id =19;|           2   
  ---------------------------------------|----------------------------------------|------------------------------------------------
                                         |                                        |       2   
  update t8 set test1='xxx' where id =19;|                                        |
  ---------------------------------------|----------------------------------------|------------------------------------------------
                 堵塞                    |                                        |          2
  ---------------------------------------|----------------------------------------|------------------------------------------------
  20s后超时                              |                                        |       1   
  事务结束;                              |    commit;                             |     0
  ---------------------------------------|----------------------------------------|------------------------------------------------     
  
 
  select * from t8;    
  20这个id没有;19被改为yyy;    
    
    
  实验二:
  innodb_rollback_on_timeout =off
  innodb_lock_wait_timeout=20   
  ---------------------------------------|---------------------------------------|-----------------------------------------------
                   session 1             |                   session 2          |          session  3   
  ---------------------------------------|---------------------------------------|-----------------------------------------------
   begin;                               |                                       | select * from information_schema.innodb_trx
                                         |                                       |        order by trx_started;   
  ---------------------------------------|---------------------------------------|-----------------------------------------------
  insert into t8 values (20,'ddd');      |                                       |              1   
  ---------------------------------------|---------------------------------------|-----------------------------------------------
                                         |                   begin;              |             2   
                                         |update t8 set test1='yyy' where id =19;|
  ---------------------------------------|---------------------------------------|-----------------------------------------------
  update t8 set test1='xxx' where id =19;|                                       |               2
  ---------------------------------------|---------------------------------------|-----------------------------------------------
  堵塞                                   |                                       |               2     
  ---------------------------------------|---------------------------------------|-----------------------------------------------
  20s后超时                              |                                       |              2   
  事务未结束                           |                                       |
  ---------------------------------------|---------------------------------------|-----------------------------------------------
      commit;                            |                       commit;         |               0   
  ---------------------------------------|---------------------------------------|-----------------------------------------------
    
  select * from t8;    
  20这个id存在;19被改为yyy;  
  
  结果就是:如果innodb_rollback_on_timeout 为on,事务在innodb_lock_wait_timeout超时后,当前sql会报超时,并使整个事务都回滚,然后结束事务;
           如果innodb_rollback_on_timeout 为off(默认值),事务在innodb_lock_wait_timeout超时后,当前sql会超时,并终止,回滚当前sql,但整个事务不回滚,事务不结束,必须显示的回滚或提交;
   一句话:innodb_rollback_on_timeout 没什么卵用.纯碎就是坑;

(编辑:西双版纳站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读