关于innodb_rollback_on_timeout测试

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 没什么卵用.纯碎就是坑;