看懂這篇文章-你就懂了數(shù)據(jù)庫死鎖產(chǎn)生的場景和解決方法

　　一、什么是死鎖

　　 加鎖(Locking)是數(shù)據(jù)庫在并發(fā)訪問時保證數(shù)據(jù)一致性和完整性的主要機(jī)制。任何事務(wù)都需要獲得相應(yīng)對象上的鎖才能訪問數(shù)據(jù)，讀取數(shù)據(jù)的事務(wù)通常只需要獲得讀鎖(共享鎖)，修改數(shù)據(jù)的事務(wù)需要獲得寫鎖(排他鎖)。當(dāng)兩個事務(wù)互相之間需要等待對方釋放獲得的資源時，如果系統(tǒng)不進(jìn)行干預(yù)則會一直等待下去，也就是進(jìn)入了死鎖(deadlock)狀態(tài)。

　　二、死鎖產(chǎn)生的場景

       1.數(shù)據(jù)庫表準(zhǔn)備

　　1.1. 創(chuàng)建數(shù)據(jù)庫表

#創(chuàng)建數(shù)據(jù)庫
create database chuanzhi;
#選擇數(shù)據(jù)庫
use chuanzhi;
#創(chuàng)建測試表
CREATE TABLE `tb_heima` (
  `id` INT NOT NULL,
  `username` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
);
#插入測試數(shù)據(jù)
INSERT INTO `tb_heima` (`id`, `username`) VALUES ('1', '傳智教育-教育行業(yè)A股IPO第一股');

　　2.死鎖場景演示

　　2.1. 打開一個dos窗口

　　- mysql -u賬號 -p密碼，進(jìn)入mysql命令行輸入：

　　- 開啟一個事務(wù)，mysql> begin;

　　- 執(zhí)行更新語句，mysql> update tb_heima set username = 'heima' where id =1;

　　注意：此時更新是成功的，但并未提交事務(wù)。

　　2.2.再打開一個dos窗口

　　- 登錄mysql,開啟另一個事務(wù)操作同上。

　　- 再次執(zhí)行更新語句，mysql> update tb_heima set username = 'heima' where id =1;

　　注意：此時第二個窗口處于等待中，需要等待第一個窗口釋放寫鎖才能執(zhí)行成功。

　　- 等待一段時候后，第二個窗口報錯了

　　注意：由于第一個窗口未提交事務(wù)，導(dǎo)致第二個窗口在等待一段時候后，超過鎖定等待超時。

　　三、解決死鎖問題

　　通過mysql客戶端工具連接，按照以下步驟執(zhí)行。

　　- select from information_schema.innodb_lock_waits; 鎖等待的信息，可以看到堵塞和被堵塞者

　　注意：超過鎖定等待超時后，此條記錄會消失，也就是說正在阻塞的記錄在此表才能查看到。

　　- select * from information_schema.innodb_trx; 查詢鎖事務(wù)狀態(tài)信息

　　注意：查詢出死鎖后，得到trx_mysql_thread_id死鎖的id。

　　- kill sessionId; 殺掉有問題的session

kill 15;
kill 16;

　　- 再次執(zhí)行select * from information_schema.innodb_trx; # 查詢鎖事務(wù)狀態(tài)信息

　　注意：確認(rèn)此表是否存在鎖事務(wù)記錄，如果沒有則說明死鎖問題已經(jīng)解決。

　　四、如何避免死鎖

       1.對于數(shù)據(jù)庫的多表操作時，盡量按照相同的順序進(jìn)行處理，盡量避免同時鎖定兩個資源，如操作A和B兩張表時，總是按先A后B的順序處理， 必須同時鎖定兩個資源時，要保證在任何時刻都應(yīng)該按照相同的順序來鎖定資源。

　　2. 所有的update和delete操作必須走唯一索引

　　3. SQL語句中不要使用太復(fù)雜的關(guān)聯(lián)多表的查詢;使用“執(zhí)行計劃”對SQL語句進(jìn)行分析，對于有全表掃描的SQL語句，建立相應(yīng)的索引進(jìn)行優(yōu)化。

　　4. 把SELECT放在Update語句前

　　5. 避免事務(wù)中的用戶等待交互