innodb_rollback_on_timeout 
innodb_lock_wait_timeout
最開(kāi)始看到innodb_rollback_on_timeout 時(shí),沒(méi)有好好理解transaction timeout的意思.以為只要是連接超時(shí)就會(huì)觸發(fā),今天研究了一下.只能說(shuō)巨坑;
以下測(cè)試.隔離級(jí)別皆為RR模式.測(cè)試過(guò)程.

創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站制作、成都網(wǎng)站建設(shè)、外貿(mào)營(yíng)銷(xiāo)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的章貢網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

 
實(shí)驗(yàn)一:
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后超時(shí)                              |                                        |       1   
事務(wù)結(jié)束;                              |    commit;                             |     0
---------------------------------------|----------------------------------------|------------------------------------------------     
 

select * from t8;    
20這個(gè)id沒(méi)有;19被改為yyy;    
  
  
實(shí)驗(yàn)二:
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后超時(shí)                              |                                       |              2   
事務(wù)未結(jié)束                           |                                       |
---------------------------------------|---------------------------------------|-----------------------------------------------
    commit;                            |                       commit;         |               0   
---------------------------------------|---------------------------------------|-----------------------------------------------
  
select * from t8;    
20這個(gè)id存在;19被改為yyy;  

結(jié)果就是:如果innodb_rollback_on_timeout 為on,事務(wù)在innodb_lock_wait_timeout超時(shí)后,當(dāng)前sql會(huì)報(bào)超時(shí),并使整個(gè)事務(wù)都回滾,然后結(jié)束事務(wù);
         如果innodb_rollback_on_timeout 為off(默認(rèn)值),事務(wù)在innodb_lock_wait_timeout超時(shí)后,當(dāng)前sql會(huì)超時(shí),并終止,回滾當(dāng)前sql,但整個(gè)事務(wù)不回滾,事務(wù)不結(jié)束,必須顯示的回滾或提交;
 一句話:innodb_rollback_on_timeout 沒(méi)什么卵用.純碎就是坑;