MySQL 數(shù)據(jù)庫的死鎖、事務日志

死鎖和事務日志也是數(shù)據(jù)庫事務繞不開的部分，本小節(jié)將重點介紹數(shù)據(jù)庫的死鎖和事務日志，讓大家對死鎖和事務日志能有基本的認識。

1. 死鎖

死鎖是指兩個或多個事務在執(zhí)行過程中，因爭奪資源而造成的一種互相等待的現(xiàn)象，若無外力作用，它們都將無法推進下去。當多個事務嘗試以不同的順序鎖定資源，或者多個事務同時鎖定同一個資源，都有可能產(chǎn)生死鎖。

場景：兩個事務同時處理 customer 表

兩個事務同時執(zhí)行了第一條 update 語句，更新并鎖定了該行數(shù)據(jù)，緊接著又都執(zhí)行第二條 update 語句，此時發(fā)現(xiàn)該行已經(jīng)被對方鎖定，然后兩個事務都等待對方釋放鎖，同時又持有對方需要的鎖，陷入死循環(huán)，需要外力介入才能解除死鎖。

mysql> CREATE TABLE `customer` (
  `id` int(11) NOT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `first_name` varchar(30) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `balance` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

事務1：
start transaction;
update customer set balance = 100 where id = 1;
update customer set balance = 200 where id = 2;
commit;

事務2：
start transaction;
update customer set balance = 300 where id = 2;
update customer set balance = 400 where id = 1;
commit;

為了解決死鎖問題，數(shù)據(jù)庫實現(xiàn)了各種死鎖檢測和死鎖超時機制。越復雜的存儲引擎，越能檢測到死鎖的循環(huán)以來，并返回錯誤，這是一種比較有效的辦法。還有一種解決死鎖的辦法是：當鎖等待超時后，放棄鎖請求。

InnoDB 存儲引擎可以自動檢測事務的死鎖，并回滾一個或幾個事務來防止死鎖。但是有些場景 InnoDB是無法檢測到死鎖的，比如在同一事務中使用 InnoDB 之外的存儲引擎、lock tables 設定表鎖定的語句，此時要通過設置 innodb_lock_wait_timeout 這個系統(tǒng)參數(shù)來解決。通過鎖等待超時來解決死鎖問題，通常不是好的辦法，因為很有可能導致大量事務的鎖等待。當發(fā)生鎖等待超時，數(shù)據(jù)庫會拋出如下報錯信息：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  

調(diào)整 innodb_lock_wait_timeout 的方法有兩種：

• 臨時：在MySQL中直接用命令行執(zhí)行

  -- innodb_lock_wait_timeout的默認值為50秒
  mysql> show variables like 'innodb_lock_wait_timeout';
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | innodb_lock_wait_timeout | 50    |
  +--------------------------+-------+
  1 row in set (0.00 sec)
  
  mysql> set innodb_lock_wait_timeout=51;
  Query OK, 0 rows affected (0.00 sec)
  

• 永久：將以下兩個參數(shù)添加至配置文件 my.cnf，并重啟 MySQL：

  innodb_lock_wait_timeout=50
  

我們在程序設計時，也要盡可能的減小死鎖發(fā)生的概率。以下是針對 InnoDB 存儲引擎減小死鎖發(fā)生概率的一些建議：

• 類似業(yè)務模塊，盡可能按照相同的訪問順序來訪問，防止產(chǎn)生死鎖；
• 同一個事務中，盡可能做到一次鎖定需要的所有資源，減少死鎖發(fā)生概率；
• 同一個事務中，不要使用不同存儲引擎的表，比如 MyISAM 和 InnoDB 表出現(xiàn)在同一事務中；
• 盡可能控制事務的大小，減少鎖定的資源量和鎖定時間長度；
• 對于容易產(chǎn)生死鎖的業(yè)務模塊，嘗試升級鎖顆粒度，通過表級鎖減少死鎖發(fā)生概率。

2. 事務日志

使用事務日志可以提高事務的安全性和效率：

• 修改表數(shù)據(jù)時，只需要在內(nèi)存中進行修改，再持久化到磁盤上的事務日志，而不用每次都將修改的數(shù)據(jù)持久化到磁盤。事務日志持久化后，內(nèi)存中所修改的數(shù)據(jù)可以慢慢再刷到磁盤，這種方式稱為預寫式日志，修改數(shù)據(jù)需要寫兩次磁盤；

• 效率快很多，因為事務日志采用追加方式，寫日志的操作只是磁盤上一小塊區(qū)域的順序IO，不像隨機IO需要在磁盤多個地方移動磁頭；

• 萬一數(shù)據(jù)庫發(fā)生崩潰，可以通過已經(jīng)持久化的事務日志，來自動恢復數(shù)據(jù)。

3. 小結(jié)

本小節(jié)主要介紹了死鎖和事務日志。需要重點關注的是，死鎖的基本概念、以及減小死鎖發(fā)生概率的幾種方法。