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

死鎖和事務(wù)日志也是數(shù)據(jù)庫(kù)事務(wù)繞不開(kāi)的部分，本小節(jié)將重點(diǎn)介紹數(shù)據(jù)庫(kù)的死鎖和事務(wù)日志，讓大家對(duì)死鎖和事務(wù)日志能有基本的認(rèn)識(shí)。

1. 死鎖

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

場(chǎng)景：兩個(gè)事務(wù)同時(shí)處理 customer 表

兩個(gè)事務(wù)同時(shí)執(zhí)行了第一條 update 語(yǔ)句，更新并鎖定了該行數(shù)據(jù)，緊接著又都執(zhí)行第二條 update 語(yǔ)句，此時(shí)發(fā)現(xiàn)該行已經(jīng)被對(duì)方鎖定，然后兩個(gè)事務(wù)都等待對(duì)方釋放鎖，同時(shí)又持有對(duì)方需要的鎖，陷入死循環(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

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

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

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

InnoDB 存儲(chǔ)引擎可以自動(dòng)檢測(cè)事務(wù)的死鎖，并回滾一個(gè)或幾個(gè)事務(wù)來(lái)防止死鎖。但是有些場(chǎng)景 InnoDB是無(wú)法檢測(cè)到死鎖的，比如在同一事務(wù)中使用 InnoDB 之外的存儲(chǔ)引擎、lock tables 設(shè)定表鎖定的語(yǔ)句，此時(shí)要通過(guò)設(shè)置 innodb_lock_wait_timeout 這個(gè)系統(tǒng)參數(shù)來(lái)解決。通過(guò)鎖等待超時(shí)來(lái)解決死鎖問(wèn)題，通常不是好的辦法，因?yàn)楹苡锌赡軐?dǎo)致大量事務(wù)的鎖等待。當(dāng)發(fā)生鎖等待超時(shí)，數(shù)據(jù)庫(kù)會(huì)拋出如下報(bào)錯(cuò)信息：

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

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

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

  -- innodb_lock_wait_timeout的默認(rèn)值為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)
  

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

  innodb_lock_wait_timeout=50
  

我們?cè)诔绦蛟O(shè)計(jì)時(shí)，也要盡可能的減小死鎖發(fā)生的概率。以下是針對(duì) InnoDB 存儲(chǔ)引擎減小死鎖發(fā)生概率的一些建議：

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

2. 事務(wù)日志

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

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

• 效率快很多，因?yàn)槭聞?wù)日志采用追加方式，寫(xiě)日志的操作只是磁盤(pán)上一小塊區(qū)域的順序IO，不像隨機(jī)IO需要在磁盤(pán)多個(gè)地方移動(dòng)磁頭；

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

3. 小結(jié)

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