一. 概述 通常來說，死鎖都是應用設計問題，通過調整業務流程，資料庫對象設計，事務大小，以及訪問資料庫的sql語句，絕大部分死鎖都可以避免，下麵介紹幾種避免死鎖的常用 方法. 1. 在應用中，如果不同的程式併發操作多個表，應儘量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。按順序對錶進行 ...
一. 概述
通常來說，死鎖都是應用設計問題，通過調整業務流程，資料庫對象設計，事務大小，以及訪問資料庫的sql語句，絕大部分死鎖都可以避免，下麵介紹幾種避免死鎖的常用 方法.
1. 在應用中，如果不同的程式併發操作多個表，應儘量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。按順序對錶進行操作，是很常用的一種避免死鎖的操作。 比如：有二個不一樣的存儲過程，同時在對一個表進行複雜的刪改操作。這種情況可以考慮先讓一個執行完成，再讓另一個在執行。
2. 在程式中以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。比如常見的就是多線程下在程式中lock鎖住，在進程下保持串列處理。
3. 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排它鎖，而不是先申請共用鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其它事務可能又已經獲得了相同記錄的共用鎖，從而造成鎖衝突。 我理解是在事務中首先將要更新的記錄，以select .. for update方式獲得排它鎖， 在事務里處理完邏輯後就可以直接更新而不用考慮鎖衝突。 代碼如下：
SET autocommit=0 -- 將要更新的數據先獲得排它鎖 SELECT * FROM city WHERE city_id=103 FOR UPDATE; -- 邏輯處理 .... -- 最後更新可以避免鎖衝突 UPDATE city SET cityname='杭州' WHERE city_id=103; COMMIT;
4. 在預設級別Repeatable read下, 如果兩個線程同時對相同條件記錄用 select .. for update 加排它鎖，在沒有符合該條件記錄情況下，兩個線程都會加鎖成功。當一個程式發現記錄不存在，就試圖插入一條新數據，如果兩個線程都這麼做，就會出現死鎖。這是因為在Repeatable read下產生了間隙鎖。這種情況下，將隔離級別改成Read commited,就可避免問題。 如下圖表格 貼出了二個隔離級別下產生鎖的差異。
5. 當在Repeatable read下，如果兩個線程都先執行select .. for update。 在判斷是否存在符合條件的記錄，如果沒有，就插入記錄，此時，只有一個線程能插入成功，另一個線程會出現鎖等待， 當第1個線程提交後，第2個線程如因為主鍵值重覆，會出現異常。但卻獲得了一個排它鎖， 需要執行rollback釋放排它鎖。避免影響其它事務。
總結：儘管通過上面介紹和sql 優化等措施，可以大大減少死鎖，但死鎖很難完全避免。因此。 在程式設計中總是捕獲並處理死鎖異常是一個很好的編程習慣。在程式異常里或commit或rollback。
二. 檢查死鎖產生的原因
如果出現死鎖，可以用SHOW ENGINE INNODB STATUS 命令來確定最後一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發死鎖的sql語句，事務已經獲得的鎖，正在等待什麼鎖，以及被回滾的事務等，以此分析死鎖產生的原因和改進措施。
-- 查看最後一個死鎖 SHOW ENGINE INNODB STATUS;
LATEST DETECTED DEADLOCK ------------------------ 2018-08-02 18:07:45 0x7f3a12209700 *** (1) TRANSACTION: TRANSACTION 35489574, ACTIVE 114 sec STARTING INDEX READ mysql TABLES IN USE 1, locked 1 LOCK WAIT 4 LOCK struct(s), HEAP size 1136, 2 ROW LOCK(s) MySQL thread id 2634494, OS thread handle 139887387092736, QUERY id 109768880 172.168.18.202 root Sending DATA -- 因為會話2 已獲得排他鎖， 些語句 等待 SELECT * FROM cityNew WHERE city_id=103 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS SPACE id 479 page NO 3 n bits 72 INDEX GEN_CLUST_INDEX of TABLE `test`.`cityNew` trx id 35489574 lock_mode X waiting *** (2) TRANSACTION: TRANSACTION 35489577, ACTIVE 8 sec STARTING INDEX READ, thread declared inside INNODB 5000 mysql TABLES IN USE 1, locked 1 4 LOCK struct(s), HEAP size 1136, 3 ROW LOCK(s) MySQL thread id 2634624, OS thread handle 139887388956416, QUERY id 109768953 172.168.18.202 root statistics -- 死鎖 SELECT * FROM city WHERE city_id=103 FOR UPDATE *** (2) HOLDS THE LOCK(S): RECORD LOCKS SPACE id 479 page NO 3 n bits 72 INDEX GEN_CLUST_INDEX of TABLE `test`.`cityNew` trx id 35489577 lock_mode X *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS SPACE id 477 page NO 3 n bits 80 INDEX PRIMARY of TABLE `test`.`city` trx id 35489577 lock_mode X LOCKS rec but NOT gap waiting *** WE ROLL BACK TRANSACTION (2) ------------
