mysql資料庫死鎖分析,mysql資料庫死鎖解決

本文目錄一覽：

• 1、用sql語句，怎麼解決mysql資料庫死鎖
• 2、怎麼統計mysql中有多少個死鎖？
• 3、如何查看MySQL資料庫的死鎖信息
• 4、mysql發生死鎖怎麼解決

用sql語句，怎麼解決mysql資料庫死鎖

MySQL死鎖問題的相關知識是本文我們主要要介紹的內容，接下來我們就來一一介紹這部分內容，希望能夠對您有所幫助。

1、MySQL常用存儲引擎的鎖機制

MyISAM和MEMORY採用表級鎖(table-level locking)

BDB採用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖

InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖

2、各種鎖特點

表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖衝突的概率最高，並發度最低

行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高

頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般

3、各種鎖的適用場景

表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用

行級鎖則更適合於有大量按索引條件並發更新數據，同時又有並發查詢的應用，如一些在線事務處理系統

4、死鎖

是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。

表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。

5、死鎖舉例分析

在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。

在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。

例如，一個表db。tab_test，結構如下：

id：主鍵;

state：狀態;

time：時間;

索引：idx_1(state，time)

出現死鎖日誌如下：

?***(1) TRANSACTION:

?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read

?mysql tables in use 1, locked 1

?LOCK WAIT 3 lock struct(s), heap size 320

?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update

?update tab_test set state=1064,time=now() where state=1061 and time date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)

?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)

?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting

?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

?*** (2) TRANSACTION:

?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499

?mysql tables in use 1, locked 1

?3 lock struct(s), heap size 320, undo log entries 1

?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)

?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)

?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap

?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)

?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting

?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;

?*** WE ROLL BACK TRANSACTION (1)

?(回滾了任務1，以解除死鎖)

原因分析：

當「update tab_test set state=1064,time=now() where state=1061 and time date_sub(now(), INTERVAL 30 minute)」執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。

假設「update tab_test set state=1067,time=now () where id in (9921180)」幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。

這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死鎖就產生了。

6、解決辦法

拆分第一條sql，先查出符合條件的主鍵值，再按照主鍵更新記錄：

?select id from tab_test where state=1061 and time date_sub(now(), INTERVAL 30 minute);

?update tab_test state=1064,time=now() where id in(……);

怎麼統計mysql中有多少個死鎖？

1，查看資料庫的隔離級別：

mysql select @@tx_isolation;

2，去查看先當前庫的線程情況：

mysql show processlist;

沒有看到正在執行的慢SQL記錄線程，再去查看innodb的事務表INNODB_TRX，看下裡面是否有正在鎖定的事務線程，看看ID是否在show full processlist裡面的sleep線程中，如果是，就證明這個sleep的線程事務一直沒有commit或者rollback而是卡住了，我們需要手動kill掉。

mysql SELECT * FROM information_schema.INNODB_TRX;

如果有記錄,則找到trx_mysql_thread_id這個欄位對應的id, 將其kill掉。假如id=100

mysql-kill 100

SELECT CONCAT_WS(”,’kill’,’ ‘,t.trx_mysql_thread_id,’;’)a FROM information_schema.INNODB_TRX t;

4，總結分析

表數據量也不大，按照普通的情況來說，簡單的update應該不會造成阻塞的，mysql都是autocommit，不會出現update卡住的情況，去查看下autocommit的值。

mysql select @@autocommit;

1表示自動提交。0表示不自動提交。

如果你發現自己的資料庫autocommit=0，將它改正吧。

解除死鎖的兩種方法：

（1）終止（或撤銷）進程。終止（或撤銷）系統中的一個或多個死鎖進程，直至打破循環環路，使系統從死鎖狀態中解除出來。

（2）搶佔資源。從一個或多個進程中搶佔足夠數量的資源，分配給死鎖進程，以打破死鎖狀態。

如何查看MySQL資料庫的死鎖信息

1 使用終端或命令提示符登錄到MySQL，輸入命令： mysql -h xxxx.xxx.xxx -P 3306 -u username -p password 2 在MySQL客戶端下輸入命令： show engine innodb status \G; 3 在列印出來的信息中找到「LATEST DETECTED DEADLOCK」一節內容 4 分析其中的內容，我們就可以知道最近導致死鎖的事務有哪些

mysql發生死鎖怎麼解決

可直接在mysql命令行執行:show engine innodb status\G; 查看造成死鎖的sql語句,分析索引情況,然後優化sql然後show processlist; 另外可以打開慢查詢日誌,linux下打開需在…