溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

關(guān)于innodb_rollback_on_timeout測(cè)試

發(fā)布時(shí)間：2020-08-07 12:07:24 來源：ITPUB博客 閱讀：244 作者：yangjustins 欄目：MySQL數(shù)據(jù)庫

 

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

 
實(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沒有;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 沒什么卵用.純碎就是坑;

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手