MySQL死鎖產生的原因和解決方法
前言
最近經常碰到mysql又死鎖了導致業(yè)務報錯。今天我們就來聊聊死鎖以及怎么解決
鎖類型
mysql鎖級別：頁級、表級、行級
- 表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。
- 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。
- 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般
死鎖產生原因和示例
1、產生原因：
死鎖是指兩個或兩個以上的進程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用，它們都將無法推進下去.此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產生了死鎖，這些永遠在互相等待的進程稱為死鎖進程。表級鎖不會產生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB。
死鎖的關鍵在于：兩個(或以上)的Session加鎖的順序不一致。
那么對應的解決死鎖問題的關鍵就是：讓不同的session加鎖有次序
2、產生示例：
案例一
需求：將投資的錢拆成幾份隨機分配給借款人。
起初業(yè)務程序思路是這樣的：
投資人投資后，將金額隨機分為幾份，然后隨機從借款人表里面選幾個，然后通過一條條select for update 去更新借款人表里面的余額等。
例如兩個用戶同時投資，A用戶金額隨機分為2份，分給借款人1，2
B用戶金額隨機分為2份，分給借款人2，1
由于加鎖的順序不一樣，死鎖當然很快就出現(xiàn)了。
對于這個問題的改進很簡單，直接把所有分配到的借款人直接一次鎖住就行了。
Select * from xxx where id in (xx,xx,xx) for update
在in里面的列表值mysql是會自動從小到大排序，加鎖也是一條條從小到大加的鎖
第一個會話：
注意：關閉掉自動提交set autocommit=0;
mysql> select * from goods where goods_id in (2,3) for update; +----+--------+------+---------------------+ | good_id | goods_name | price | +----+--------+------+---------------------+ | 2 | bbbb | 1.00 | | 3 | vvv | 3.00 | +----+--------+------+---------------------+
第二個會話:
select * from goods where goods_id in (3,4,5) for update;
鎖等待中……
案例二
在開發(fā)中，經常會做這類的判斷需求：根據字段值查詢（有索引），如果不存在，則插入；否則更新。
以id為主鍵為例，目前還沒有id=22的行
注意：關閉掉自動提交set autocommit=0;
第一個會話:
select * from goods where goods_id=22 for update;
第二個會話:
select * from goods where goods_id=23 for update;
再在第一個會話:
insert into goods values(22,'ac',11.5);
鎖等待中……
再到第二個會話:
insert into goods values(23,'bc',23.0);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
當對存在的行進行鎖的時候(主鍵)，mysql就只有行鎖。
當對未存在的行進行鎖的時候(即使條件為主鍵)，mysql是會鎖住一段范圍
鎖住的范圍為：
(無窮小或小于表中鎖住id的最大值，無窮大或大于表中鎖住id的最小值)
如一：如果表中目前有已有的id為（11 ， 12）
那么就鎖住（12，無窮大）
如二：如果表中目前已有的id為（11 ， 30）
那么就鎖住（11，30）
對于這種死鎖的解決辦法是：
insert into goods(xx,xx) on duplicate key update `xx`='XX';
用mysql特有的語法來解決此問題。因為insert語句對于主鍵來說，插入的行不管有沒有存在，都會只有行鎖
死鎖檢查處理
正常情況下，死鎖發(fā)生時，權重最小的連接將被kill并回滾。但是為了找出語句來優(yōu)化，啟用可啟用死鎖將死鎖信息記錄下來。
#step 1：窗口一 mysql> start transaction; mysql> update aa set name='aaa' where id = 1; #step 2：窗口二 mysql> start transaction; mysql> update bb set name='bbb' where id = 1; #step 3：窗口一 mysql> update bb set name='bbb';
查看方式
#step 4：窗口三 #是否自動提交 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+
查看當前鏈接
#查看當前連接 mysql> show processlist; mysql> show full processlist; mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 4 | root | localhost | test | Sleep | 244 | | NULL | | 5 | root | localhost | test | Sleep | 111 | | NULL | | 6 | root | localhost | NULL | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+
- id列:一個標識，你要kill 一個語句的時候很有用。
- user列: 顯示當前用戶，如果不是root，這個命令就只顯示你權限范圍內的sql語句。
- host列:顯示這個語句是從哪個ip 的哪個端口上發(fā)出的。可用來追蹤出問題語句的用戶。
- db列:顯示這個進程目前連接的是哪個數據庫。
- command列:顯示當前連接的執(zhí)行的命令，一般就是休眠（sleep），查詢（query），連接（connect）
- time列:此這個狀態(tài)持續(xù)的時間，單位是秒。
這個命令中最關鍵的就是state列，mysql列出的狀態(tài)主要有以下幾種：
Checking table
正在檢查數據表（這是自動的）。
Closing tables
正在將表中修改的數據刷新到磁盤中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁盤空間是否已經滿了或者磁盤是否正處于重負中。
Connect Out
復制從服務器正在連接主服務器。
Copying to tmp table on disk
由于臨時結果集大于tmp_table_size，正在將臨時表從內存存儲轉為磁盤存儲以此節(jié)省內存。
Creating tmp table
正在創(chuàng)建臨時表以存放部分查詢結果。
deleting from main table
服務器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個表。
deleting from reference tables
服務器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。
Flushing tables
正在執(zhí)行FLUSH TABLES，等待其他線程關閉數據表。
Killed
發(fā)送了一個kill請求給某線程，那么這個線程將會檢查kill標志位，同時會放棄下一個kill請求。MySQL會在每次的主循環(huán)中檢查kill標志位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請求會在鎖釋放時馬上生效。
Locked
被其他查詢鎖住了。
Sending data
正在處理SELECT查詢的記錄，同時正在把結果發(fā)送給客戶端。
Sorting for group
正在為GROUP BY做排序。
Sorting for order
正在為ORDER BY做排序。
Opening tables
這個過程應該會很快，除非受到其他因素的干擾。例如，在執(zhí)ALTER TABLE或LOCK TABLE語句行完以前，數據表無法被其他線程打開。正嘗試打開一個表。
Removing duplicates
正在執(zhí)行一個SELECT DISTINCT方式的查詢，但是MySQL無法在前一個階段優(yōu)化掉那些重復的記錄。因此，MySQL需要再次去掉重復的記錄，然后再把結果發(fā)送給客戶端。
Reopen table
獲得了對一個表的鎖，但是必須在表結構修改之后才能獲得這個鎖。已經釋放鎖，關閉數據表，正嘗試重新打開數據表。
Repair by sorting
修復指令正在排序以創(chuàng)建索引。
Repair with keycache
修復指令正在利用索引緩存一個一個地創(chuàng)建新索引。它會比Repair by sorting慢些。
Searching rows for update
正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關的記錄之前就完成了。
Sleeping
正在等待客戶端發(fā)送新請求.
System lock
正在等待取得一個外部的系統(tǒng)鎖。如果當前沒有運行多個mysqld服務器同時請求同一個表，那么可以通過增加--skip-external-locking參數來禁止外部系統(tǒng)鎖。
Upgrading lock
INSERT DELAYED正在嘗試取得一個鎖表以插入新記錄。
Updating
正在搜索匹配的記錄，并且修改它們。
User Lock
正在等待GET_LOCK()。
Waiting for tables
該線程得到通知，數據表結構已經被修改了，需要重新打開數據表以取得新的結構。然后，為了能的重新打開數據表，必須等到所有其他線程關閉這個表。以下幾種情況下會產生這個通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
Waiting for handler insert
INSERT DELAYED已經處理完了所有待處理的插入操作，正在等待新的請求。
查看當前正在被鎖的事務
#查看當前正在被鎖的事務（鎖請求超時后則查不到） mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+ | 130718495:65:3:4 | 130718495 | X | RECORD | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 | | 130718496:65:3:4 | 130718496 | X | RECORD | `test`.`bb` | GEN_CLUST_INDEX | 65 | 3 | 4 | 0x000000000300 | +------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+
查看當前等待鎖的事務
#查看當前等待鎖的事務（鎖請求超時后則查不到） mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 130718499 | 130718499:65:3:4 | 130718500 | 130718500:65:3:4 | +-------------------+-------------------+-----------------+------------------+
查看當前未提交的事務
#查看當前未提交的事務（如果死鎖等待超時,事務可能還沒有關閉） mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
主要看箭頭指向的這幾個字段，如果有阻塞數據（不為0的就是阻塞的）,找到后在根據下圖這個字段：try_mysql_thread_id 作為這條數據的主鍵id執(zhí)行這個sql進行刪除： kill id ;(殺死對應id的進程).假設這里try_mysql_thread_id=14的這條數據是鎖了。我們執(zhí)行 kill 14刪除就不再鎖表了。
查看正在被訪問的表
#查看正在被訪問的表 mysql> show OPEN TABLES where In_use > 0; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | test | bb | 1 | 0 | +----------+-------+--------+-------------+
死鎖異常
#step 3：窗口一 （若第三步中鎖請求太久，則出現(xiàn)鎖超時而終止執(zhí)行） mysql> update bb set name='bbb'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #"窗口一" 鎖請求超時前，執(zhí)行第五步，使死鎖產生，則該連接 "窗口二" 執(zhí)行終止，"窗口一" 順利執(zhí)行 #step 5：窗口二 mysql> update aa set name='aa'; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查看最近一個死鎖情況
mysql> SHOW ENGINE INNODB STATUS;
相關參數配置
死鎖日志
#死鎖記錄只記錄最近一個死鎖信息，若要將每個死鎖信息都保存到錯誤日志，啟用以下參數： mysql> show variables like 'innodb_print_all_deadlocks'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_print_all_deadlocks | OFF | +----------------------------+-------+
鎖等待超時
#上面 【step 3：窗口一】若一直請求不到資源，默認50秒則出現(xiàn)鎖等待超時。 mysql> show variables like 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+ ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #設置全局變量 鎖等待超時為60秒（新的連接生效） #mysql> set session innodb_lock_wait_timeout=50; mysql> set global innodb_lock_wait_timeout=60;
事務回滾
#上面測試中，當事務中的某個語句超時只回滾該語句，事務的完整性屬于被破壞了。為了回滾這個事務，啟用以下參數： mysql> show variables like 'innodb_rollback_on_timeout'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_rollback_on_timeout | OFF | +----------------------------+-------+
最終參數設置如下：(重啟服務重新連接測試)
[mysqld] log-error =/var/log/mysqld3306.log innodb_lock_wait_timeout=60 #鎖請求超時時間(秒) innodb_rollback_on_timeout = 1 #事務中某個語句鎖請求超時將回滾真?zhèn)€事務 innodb_print_all_deadlocks = 1 #死鎖都保存到錯誤日志
Command='Sleep'
表示連接休眠，如果太多，可以手動刪除
#若手動刪除堵塞會話，刪除 Command='Sleep' 、無State、無Info、trx_weight 權重最小的。 show processlist; SELECT trx_mysql_thread_id,trx_state,trx_started,trx_weight FROM INFORMATION_SCHEMA.INNODB_TRX;
總結
上面是小編總結的mysql死鎖相關的內容，尤其是死鎖檢查處理，是比較實用的，小伙伴們可以參考。
到此這篇關于MySQL死鎖產生的原因和解決方法的文章就介紹到這了,更多相關MySQL死鎖內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
MySQL默認是不區(qū)分大小寫的,這個涉及到collate字符集與校驗規(guī)則方面的知識,下面會講到,這里我們就來詳解MySQL查詢時區(qū)分字符串中字母大小寫的方法.2016-05-05
這篇文章主要介紹了MySQL varchar(n)能存儲幾個漢字,MySQL中varchar(n)表示n個字符，無論漢字和英文，Mysql都能存入n個字符，僅是實際字節(jié)長度有所區(qū)別，本文給大家介紹的非常詳細，需要的朋友可以參考下2023-05-05
大家好，本篇文章主要講的是CentOS7環(huán)境下安裝MySQL5.5數據庫，感興趣的同學趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
MySQL中的GROUP_CONCAT()函數詳解與實戰(zhàn)應用小結(示例詳解)
本文介紹了MySQL中的GROUP_CONCAT()函數,詳細解釋了其基本語法、應用示例以及ORDERBY和SEPARATOR參數的使用方法,此外,還提到了該函數的性能限制和注意事項,感興趣的朋友一起看看吧2025-02-02
本文給大家匯總介紹了mysql的23個基礎的知識點，這些都是學習mysql的必備知識，小伙伴們可以參考下。2015-09-09
這篇文章主要介紹了MySQL如何快速修改表的表結構，幫助大家更好的理解和學習MySQL，感興趣的朋友可以了解下2020-08-08
不知道有沒有人碰到過這樣惡心的問題：兩張表連接查詢并limit，SQL效率很高，但是加上order by以后，語句的執(zhí)行時間變的巨長，效率巨低。下邊就來看看這個問題需要如何解決2013-02-02
出現(xiàn)錯誤mysql Table ''performance_schema...解決辦法
這篇文章主要介紹了解決出現(xiàn)錯誤mysql Table 'performance_schema.session_variables' doesn't exist的相關資料,需要的朋友可以參考下2017-04-04
解決mysql ERROR 1045 (28000)-- Access denied for user問題
這篇文章主要介紹了mysql ERROR 1045 (28000)-- Access denied for user解決方法,需要的朋友可以參考下2018-03-03
這篇文章主要介紹了MySQL如何建表及導出建表語句，文章圍繞主題的相關資料展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-05-05