MySQL InnoDB加鎖逾時復原機制

標籤：pac   idt   start   family   table   poi   als   選擇   應用程式   

add by zhj: 看來我對MySQL的理解還有待深入，水還是挺深的啊，MySQL給記錄加鎖時，可以通過innodb_lock_wait_timeout參數設定逾時時間，

如果加鎖等待超過這個時間，就會復原，但復原的話有兩種方式：第一種：復原當前加鎖的這條語句；第二種：復原整個事務。這兩種方式是通過參數

innodb_rollback_on_timeout來控制的。如果是OFF，表示加鎖逾時復原時，只復原加鎖逾時的那條SQL語句；如果是ON，表示復原整個事務。預設

是OFF。在《MySQL Admin Cookbook》一書中，作者強烈建議該參數設定為ON，但其實OFF也沒關係，你可以在應用程式中捕獲那個加鎖逾時，然

後應用程式去執行ROLLBACK，這樣就可以保證原子性，只要你沒執行COMMIT，就不會破壞原子性。Django應該就是這麼做的。我個人拙見是：感覺

設定為ON，依靠資料庫本身自動完成復原更好一些。

 

原文：http://www.cnblogs.com/hustcat/archive/2012/11/18/2775487.html

1、innodb_rollback_on_timeout變數

下面是MySQL官方手冊關開innodb_rollback_on_timeout變數的說明：

In MySQL 5.0.13 and up, InnoDB rolls back only the last statement on a transaction timeout by default. If --innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as before MySQL 5.0.13). This variable was added in MySQL 5.0.32.

該變數預設值為OFF，如果事務因為加鎖逾時，會復原上一條語句執行的操作。如果設定ON，則整個事務都會復原。

 

下面通過一個樣本來驗證上面這段話。

2、樣本

(1) innodb_rollback_on_timeout為OFF

 

Session 1	Session 2
mysql> create table tt(c1 int primary key, c2 int)engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into tt values(1, 1); Query OK, 1 row affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where c1=1 lock in share mode; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | +----+------+ 1 row in set (0.00 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> insert into tt values(10,10); Query OK, 1 row affected (0.00 sec)   mysql> delete from tt where c1=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from tt; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | | 10 |   10 | +----+------+ 2 rows in set (0.00 sec)   mysql> rollback; Query OK, 0 rows affected (0.01 sec)   mysql> select * from tt; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | +----+------+ 1 row in set (0.00 sec)
mysql> select * from tt; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | +----+------+ 1 row in set (0.00 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> insert into tt values(10,10); Query OK, 1 row affected (0.00 sec)   mysql> delete from tt where c1=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> commit; Query OK, 0 rows affected (0.02 sec)   mysql> select * from tt; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | | 10 |   10 | +----+------+ 2 rows in set (0.00 sec)

 

session2因為加鎖逾時，交易回復到上一條語句。 

 

(2) innodb_rollback_on_timeout為ON

 

Session 1	Session 2
mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> select * from tt where c1=1 lock in share mode; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | +----+------+ 1 row in set (0.00 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec)   mysql> insert into tt values(11,11); Query OK, 1 row affected (0.00 sec)   mysql> delete from tt where c1=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from tt; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | | 10 |   10 | +----+------+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)   mysql> select * from tt; +----+------+ | c1 | c2   | +----+------+ |  1 |    1 | | 10 |   10 | +----+------+ 2 rows in set (0.00 sec)

 

session2加鎖逾時，整個交易回復。

 

3、總結

innodb_rollback_on_timeout為OFF，事務會復原到上一個儲存點，InnoDB在執行每條SQL語句之前，都會建立一個儲存點，參見代碼：

 

int row_insert_for_mysql(                                                /* out: error code or DB_SUCCESS */          byte*                 mysql_rec,       /* in: row in the MySQL format */          row_prebuilt_t*     prebuilt)  /* in: prebuilt struct in MySQL                                                handle */ { 。。。          savept = trx_savept_take(trx); 。。。

 

如果事務因為加鎖逾時，相當於復原到上一條語句。但是報錯後，事務還沒有完成，使用者可以選擇是繼續提交，或者復原之前的操作，由使用者選擇是否進一步提交或者復原事務。

innodb_rollback_on_timeout為ON，整個事務都會復原。這可以從row_mysql_handle_errors函數中得到驗證。

 ibool
row_mysql_handle_errors(
/*====================*/
                /* out: TRUE if it was a lock wait and
                we should continue running the query thread */
    ulint*        new_err,/* out: possible new error encountered in
                lock wait, or if no new error, the value
                of trx->error_state at the entry of this
                function */
    trx_t*        trx,    /* in: transaction */
    que_thr_t*    thr,    /* in: query thread */
    trx_savept_t*    savept)    /* in: savepoint or NULL */
{
...
else if (err == DB_DEADLOCK //發生死結
           || err == DB_LOCK_TABLE_FULL
           || (err == DB_LOCK_WAIT_TIMEOUT
               && row_rollback_on_timeout)) {
        /* Roll back the whole transaction; this resolution was added
        to version 3.23.43 */

        trx_general_rollback_for_mysql(trx, FALSE, NULL); //事務全部復原
                
    } else if (err == DB_OUT_OF_FILE_SPACE
           || err == DB_LOCK_WAIT_TIMEOUT) {

        ut_ad(!(err == DB_LOCK_WAIT_TIMEOUT
                && row_rollback_on_timeout));

               if (savept) { //復原到上一個儲存點
            /* Roll back the latest, possibly incomplete
            insertion or update */

            trx_general_rollback_for_mysql(trx, TRUE, savept);
        }
        /* MySQL will roll back the latest SQL statement */
... 

 

問題：innodb_rollback_on_timeout為OFF，事務的原子性被破壞了嗎？

答：NO，從樣本中可以看到，事務只是回退上一條語句的狀態，而整個事務實際上沒有完成(提交或者復原)，而作為應用程式在檢測這個錯誤時，應該選擇是提交或者復原事務。如果嚴格要求事務的原子性，當然是執行ROLLBACK，復原事務。

MySQL InnoDB加鎖逾時復原機制（轉）