目錄
- 前言:
- 1.了解鎖等待與死鎖
- 2.現(xiàn)象復(fù)現(xiàn)及處理
- 總結(jié):
前言:
在 MySQL 運維過程中,鎖等待和死鎖問題是令各位 DBA 及開發(fā)同學(xué)非常頭痛的事。出現(xiàn)此類問題會造成業(yè)務(wù)回滾、卡頓等故障,特別是業(yè)務(wù)繁忙的系統(tǒng),出現(xiàn)死鎖問題后影響會更嚴重。本篇文章我們一起來學(xué)習(xí)下什么是鎖等待及死鎖,出現(xiàn)此類問題又應(yīng)該如何分析處理呢?
1.了解鎖等待與死鎖
出現(xiàn)鎖等待或死鎖的原因是訪問數(shù)據(jù)庫需要加鎖,那你可能要問了,為啥要加鎖呢?原因是為了確保并發(fā)更新場景下的數(shù)據(jù)正確性,保證數(shù)據(jù)庫事務(wù)的隔離性。
試想一個場景,如果你要去圖書館借一本《高性能MySQL》,為了防止有人提前把這本書借走,你可以提前進行預(yù)約(加鎖),這把鎖可以怎么加?
- 封鎖圖書館(數(shù)據(jù)庫級別的鎖)
- 把數(shù)據(jù)庫相關(guān)的書都鎖?。ū砑墑e的鎖)
- 只鎖 MySQL 相關(guān)的書(頁級別的鎖)
- 只鎖《高性能MySQL》這本書(行級別的鎖)
鎖的粒度越細,并發(fā)級別越高,實現(xiàn)也更復(fù)雜。
鎖等待也可稱為事務(wù)等待,后執(zhí)行的事務(wù)等待前面處理的事務(wù)釋放鎖,但是等待時間超過了 MySQL 的鎖等待時間,就會引發(fā)這個異常。等待超時后的報錯為“Lock wait timeout exceeded...”。
死鎖發(fā)生的原因是兩個事務(wù)互相等待對方釋放相同資源的鎖,從而造成的死循環(huán)。產(chǎn)生死鎖后會立即報錯“Deadlock found when trying to get lock...”。
2.現(xiàn)象復(fù)現(xiàn)及處理
下面我們以 MySQL 5.7.23 版本為例(隔離級別是 RR ),來復(fù)現(xiàn)下上述兩種異?,F(xiàn)象。
mysql> show create table test_tb\G
*************************** 1. row ***************************
Table: test_tb
Create Table: CREATE TABLE `test_tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` varchar(50) NOT NULL DEFAULT '',
`col2` int(11) NOT NULL DEFAULT '1',
`col3` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_col1` (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test_tb;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | fdg | 1 | abc |
| 2 | a | 2 | fg |
| 3 | ghrv | 2 | rhdv |
+----+------+------+------+
3 rows in set (0.00 sec)
# 事務(wù)一首先執(zhí)行
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_tb where col1 = 'a' for update;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 2 | a | 2 | fg |
+----+------+------+------+
1 row in set (0.00 sec)
# 事務(wù)二然后執(zhí)行
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update test_tb set col2 = 1 where col1 = 'a';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
出現(xiàn)上種異常的原因是事務(wù)二在等待事務(wù)一的行鎖,但事務(wù)一一直沒提交,等待超時而報錯。InnoDB 行鎖等待超時時間由 innodb_lock_wait_timeout 參數(shù)控制,此參數(shù)默認值為 50 ,單位為秒,即默認情況下,事務(wù)二會等待 50s ,若仍拿不到行鎖則會報等待超時異常并回滾此條語句。
對于 5.7 版本,出現(xiàn)鎖等待時,我們可以查看 information_schema 中的幾張系統(tǒng)表來查詢事務(wù)狀態(tài)。
- innodb_trx 當前運行的所有事務(wù)。
- innodb_locks 當前出現(xiàn)的鎖。
- innodb_lock_waits 鎖等待的對應(yīng)關(guān)系
# 鎖等待發(fā)生時 查看innodb_trx表可以看到所有事務(wù)
# trx_state值為LOCK WAIT 則代表該事務(wù)處于等待狀態(tài)
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 38511
trx_state: LOCK WAIT
trx_started: 2021-03-24 17:20:43
trx_requested_lock_id: 38511:156:4:2
trx_wait_started: 2021-03-24 17:20:43
trx_weight: 2
trx_mysql_thread_id: 1668447
trx_query: update test_tb set col2 = 1 where col1 = 'a'
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 38510
trx_state: RUNNING
trx_started: 2021-03-24 17:18:54
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 1667530
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 4
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
# innodb_trx 字段值含義
trx_id:事務(wù)ID。
trx_state:事務(wù)狀態(tài),有以下幾種狀態(tài):RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started:事務(wù)開始時間。
trx_requested_lock_id:事務(wù)當前正在等待鎖的標識,可以和 INNODB_LOCKS 表 JOIN 以得到更多詳細信息。
trx_wait_started:事務(wù)開始等待的時間。
trx_weight:事務(wù)的權(quán)重。
trx_mysql_thread_id:事務(wù)線程 ID,可以和 PROCESSLIST 表 JOIN。
trx_query:事務(wù)正在執(zhí)行的 SQL 語句。
trx_operation_state:事務(wù)當前操作狀態(tài)。
trx_tables_in_use:當前事務(wù)執(zhí)行的 SQL 中使用的表的個數(shù)。
trx_tables_locked:當前執(zhí)行 SQL 的行鎖數(shù)量。
trx_lock_structs:事務(wù)保留的鎖數(shù)量。
trx_isolation_level:當前事務(wù)的隔離級別。
# sys.innodb_lock_waits 視圖也可看到事務(wù)等待狀況,且給出了殺鏈接的SQL
mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2021-03-24 17:20:43
wait_age: 00:00:22
wait_age_secs: 22
locked_table: `testdb`.`test_tb`
locked_index: idx_col1
locked_type: RECORD
waiting_trx_id: 38511
waiting_trx_started: 2021-03-24 17:20:43
waiting_trx_age: 00:00:22
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 1668447
waiting_query: update test_tb set col2 = 1 where col1 = 'a'
waiting_lock_id: 38511:156:4:2
waiting_lock_mode: X
blocking_trx_id: 38510
blocking_pid: 1667530
blocking_query: NULL
blocking_lock_id: 38510:156:4:2
blocking_lock_mode: X
blocking_trx_started: 2021-03-24 17:18:54
blocking_trx_age: 00:02:11
blocking_trx_rows_locked: 3
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 1667530
sql_kill_blocking_connection: KILL 1667530
sys.innodb_lock_waits 視圖整合了事務(wù)等待狀況,同時給出殺掉堵塞源端的 kill 語句。不過是否要殺掉鏈接還是需要綜合考慮的。
死鎖與鎖等待稍有不同,我們同樣也來簡單復(fù)現(xiàn)下死鎖現(xiàn)象。
# 開啟兩個事務(wù)
# 事務(wù)一執(zhí)行
mysql> update test_tb set col2 = 1 where col1 = 'a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 事務(wù)二執(zhí)行
mysql> update test_tb set col2 = 1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 回到事務(wù)一執(zhí)行 回車后 此條語句處于鎖等待狀態(tài)
mysql> update test_tb set col1 = 'abcd' where id = 3;
Query OK, 1 row affected (5.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 回到事務(wù)二再執(zhí)行 此時二者相互等待發(fā)生死鎖
mysql> update test_tb set col3 = 'gddx' where col1 = 'a';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
發(fā)生死鎖后會選擇一個事務(wù)進行回滾,想查明死鎖原因,可以執(zhí)行 show engine innodb status 來查看死鎖日志,根據(jù)死鎖日志,結(jié)合業(yè)務(wù)邏輯來進一步定位死鎖原因。
在實際應(yīng)用中,我們要盡量避免死鎖現(xiàn)象的發(fā)生,可以從以下幾個方面入手:
- 事務(wù)盡可能小,不要將復(fù)雜邏輯放進一個事務(wù)里。
- 涉及多行記錄時,約定不同事務(wù)以相同順序訪問。
- 業(yè)務(wù)中要及時提交或者回滾事務(wù),可減少死鎖產(chǎn)生的概率。
- 表要有合適的索引。
- 可嘗試將隔離級別改為 RC 。
總結(jié):
本篇文章簡單介紹了鎖等待及死鎖發(fā)生的原因,其實真實業(yè)務(wù)中發(fā)生死鎖還是很難分析的,需要一定的經(jīng)驗積累。本篇文章只是面向初學(xué)者,希望各位對死鎖能夠有個初印象。
以上就是MySQL鎖等待與死鎖問題分析的詳細內(nèi)容,更多關(guān)于MySQL鎖等待與死鎖的資料請關(guān)注腳本之家其它相關(guān)文章!
您可能感興趣的文章:- java排查死鎖示例
- Java檢測死鎖案例
- 詳解通過SQL進行分布式死鎖的檢測與消除
- 如何解決Java多線程死鎖問題
- postgresql表死鎖問題的排查方式
- 死鎖詳解