杭州灣新區(qū)建設(shè)局網(wǎng)站seo優(yōu)化便宜
文章目錄
- 死鎖(dead lock)
- 示例 1
- 問(wèn)題排查
- 查看連接的線程
- 查看相關(guān)的表
- 查看最近一次的死鎖信息
- 查看服務(wù)器的鎖信息
- 查看正在使用的表
- 如何盡可能地避免死鎖
死鎖(dead lock)
兩個(gè)及以上的事務(wù)各自持有對(duì)方需要的鎖,導(dǎo)致雙方都無(wú)法繼續(xù)執(zhí)行,只能互相等待(循環(huán)等待)。因?yàn)殡p方都在等待對(duì)方釋放鎖,而各自又無(wú)法釋放鎖,從而導(dǎo)致了死循環(huán)。這就是死鎖(dead lock)。
當(dāng)兩個(gè)事務(wù)鎖定了多張表的多條記錄(如 UPDATE 或者 SELECT … FOR UPDATE),且它們各自鎖定的順序相反時(shí),就會(huì)出現(xiàn)死鎖。當(dāng) sql 語(yǔ)句鎖定了多行記錄和間隙時(shí),也容易出現(xiàn)死鎖。
關(guān)于 InnoDB 鎖的類型和兼容性,可參考 https://blog.csdn.net/lamp_yang_3533/article/details/129291575
產(chǎn)生死鎖的四個(gè)條件:
- 互斥條件:一個(gè)資源每次只能被一個(gè)進(jìn)程(線程)使用。
- 請(qǐng)求與保持條件:一個(gè)進(jìn)程(線程)因請(qǐng)求資源而阻塞時(shí),對(duì)已獲得的資源保持不放。
- 不剝奪條件:進(jìn)程(線程)已獲得的資源,在末使用完之前,不能被剝奪。
- 循環(huán)等待條件:多個(gè)進(jìn)程(線程)之間形成一種頭尾相接的循環(huán)等待的資源爭(zhēng)用關(guān)系。
死鎖檢測(cè)功能 innodb_deadlock_detect
默認(rèn)是開(kāi)啟的。當(dāng)出現(xiàn)死鎖時(shí),InnoDB 就會(huì)檢測(cè)到狀態(tài),并及時(shí)回滾導(dǎo)致死鎖的多個(gè)事務(wù)中的一個(gè)。
mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)
如果關(guān)閉了死鎖檢測(cè)功能,InnoDB 就依賴鎖等待超時(shí)時(shí)間 innodb_lock_wait_timeout
,當(dāng)鎖等待超時(shí)(默認(rèn) 50 秒)后,對(duì)事務(wù)進(jìn)行回滾。
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)
說(shuō)明: 以下示例基于 MySQL 8.0,如果是之前的版本會(huì)有所不同。
示例 1
CREATE TABLE `t` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',`name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用戶名稱',`department_id` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '用戶所屬的部門(mén)id',`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用戶的狀態(tài): 1啟用, 2禁用',`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',PRIMARY KEY (`id`),KEY `department_id_index` (`department_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';# 插入部分測(cè)試數(shù)據(jù)
INSERT t (`id`, `name`, `department_id`, `status`) VALUES
('1', 'test', '8', '1'),
('2', 'test', '1', '1'),
('3', 'test', '8', '1'),
('7', 'test', '2', '1'),
('8', 'test', '2', '1'),
('9', 'test', '8', '1');
首先,在第一個(gè)連接中,開(kāi)啟事務(wù) A。
# 事務(wù) A
BEGIN;
UPDATE t SET department_id=3 WHERE id BETWEEN 3 AND 7;
由于在 MySQL 8.0 中,只要持有鎖(不管是否出現(xiàn)鎖爭(zhēng)用),locks 表中就會(huì)記錄數(shù)據(jù)。
故可以先簡(jiǎn)單看一下事務(wù)中鎖的信息。
SELECT trx_mysql_thread_id AS sql_thread_id,trx_id, OBJECT_NAME as `table`, INDEX_NAME as `index`, LOCK_DATA as data, LOCK_MODE as mode, LOCK_STATUS as status, LOCK_TYPE as type
FROM performance_schema.data_locks as lk
LEFT JOIN information_schema.innodb_trx as trx ON trx.trx_id=lk.ENGINE_TRANSACTION_ID;
+---------------+--------+-------+---------+------+------+---------+--------+
| sql_thread_id | trx_id | table | index | data | mode | status | type |
+---------------+--------+-------+---------+------+------+---------+--------+
| 30 | 28220 | t | NULL | NULL | IX | GRANTED | TABLE |
| 30 | 28220 | t | PRIMARY | 3 | X | GRANTED | RECORD |
| 30 | 28220 | t | PRIMARY | 7 | X | GRANTED | RECORD |
| 30 | 28220 | t | PRIMARY | 8 | X | GRANTED | RECORD |
+---------------+--------+-------+---------+------+------+---------+--------+
4 rows in set (0.00 sec)
然后,在第二個(gè)連接中,開(kāi)啟事務(wù) B。
# 事務(wù) B
BEGIN;
UPDATE t SET status=2 WHERE id=9;
繼續(xù)看一下事務(wù)中鎖的信息。
+---------------+--------+-------+---------+------+------+---------+--------+
| sql_thread_id | trx_id | table | index | data | mode | status | type |
+---------------+--------+-------+---------+------+------+---------+--------+
| 33 | 28226 | t | NULL | NULL | IX | GRANTED | TABLE |
| 33 | 28226 | t | PRIMARY | 9 | X | GRANTED | RECORD |
| 30 | 28220 | t | NULL | NULL | IX | GRANTED | TABLE |
| 30 | 28220 | t | PRIMARY | 3 | X | GRANTED | RECORD |
| 30 | 28220 | t | PRIMARY | 7 | X | GRANTED | RECORD |
| 30 | 28220 | t | PRIMARY | 8 | X | GRANTED | RECORD |
+---------------+--------+-------+---------+------+------+---------+--------+
6 rows in set (0.00 sec)
還未出現(xiàn)鎖爭(zhēng)用的情況。
然后,在事務(wù) B 中,繼續(xù)執(zhí)行下面的 SQL。
UPDATE t SET status=2 WHERE id=3;
查看事務(wù)中的鎖信息,如下:
+---------------+--------+-------+---------+------+------+---------+--------+
| sql_thread_id | trx_id | table | index | data | mode | status | type |
+---------------+--------+-------+---------+------+------+---------+--------+
| 33 | 28234 | t | NULL | NULL | IX | GRANTED | TABLE |
| 33 | 28234 | t | PRIMARY | 9 | X | GRANTED | RECORD |
| 33 | 28234 | t | PRIMARY | 3 | X | WAITING | RECORD |
| 30 | 28229 | t | NULL | NULL | IX | GRANTED | TABLE |
| 30 | 28229 | t | PRIMARY | 3 | X | GRANTED | RECORD |
| 30 | 28229 | t | PRIMARY | 7 | X | GRANTED | RECORD |
| 30 | 28229 | t | PRIMARY | 8 | X | GRANTED | RECORD |
+---------------+--------+-------+---------+------+------+---------+--------+
7 rows in set (0.00 sec)
可發(fā)現(xiàn),目前已經(jīng)出現(xiàn)資源競(jìng)爭(zhēng),事務(wù) B 處于鎖等待(wait)狀態(tài)。
最后,在事務(wù) A 中,繼續(xù)執(zhí)行下面的 SQL。
UPDATE t SET status=2 WHERE id=9;
出現(xiàn)雙方循環(huán)等待,立即產(chǎn)生死鎖,事務(wù)報(bào)錯(cuò)。
事務(wù) B 報(bào)錯(cuò)信息如下:
[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction
再來(lái)看看事務(wù)中鎖的信息。
+---------------+--------+-------+---------+------+------+---------+--------+
| sql_thread_id | trx_id | table | index | data | mode | status | type |
+---------------+--------+-------+---------+------+------+---------+--------+
| 30 | 28229 | t | NULL | NULL | IX | GRANTED | TABLE |
| 30 | 28229 | t | PRIMARY | 3 | X | GRANTED | RECORD |
| 30 | 28229 | t | PRIMARY | 7 | X | GRANTED | RECORD |
| 30 | 28229 | t | PRIMARY | 8 | X | GRANTED | RECORD |
| 30 | 28229 | t | PRIMARY | 9 | X | GRANTED | RECORD |
+---------------+--------+-------+---------+------+------+---------+--------+
5 rows in set (0.00 sec)
由于死鎖,事務(wù) B 被 InnoDB 回滾了。故現(xiàn)在只剩下事務(wù) A 的鎖,它可以正常提交事務(wù)了。
這里,我們利用下面語(yǔ)句查看最新監(jiān)測(cè)到的死鎖信息。
SHOW ENGINE INNODB STATUS\G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-02-28 17:25:14 0x45c
*** (1) TRANSACTION:
TRANSACTION 28234, ACTIVE 50 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 33, OS thread handle 11904, query id 557 localhost 127.0.0.1 root updating
UPDATE t SET status=2 WHERE id=3
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `mytest`.`t` trx id 28234 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 00: len 4; hex 00000003; asc ;;1: len 6; hex 000000006e45; asc nE;;2: len 7; hex 01000001260281; asc & ;;3: len 10; hex 74657374202020202020; asc test ;;4: len 1; hex 03; asc ;;5: len 1; hex 81; asc ;;6: len 5; hex 99af790294; asc y ;;7: len 5; hex 99af791609; asc y ;;*** (2) TRANSACTION:
TRANSACTION 28229, ACTIVE 65 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 30, OS thread handle 1116, query id 574 localhost 127.0.0.1 root updating
UPDATE t SET status=2 WHERE id=9
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `mytest`.`t` trx id 28229 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 00: len 4; hex 00000003; asc ;;1: len 6; hex 000000006e45; asc nE;;2: len 7; hex 01000001260281; asc & ;;3: len 10; hex 74657374202020202020; asc test ;;4: len 1; hex 03; asc ;;5: len 1; hex 81; asc ;;6: len 5; hex 99af790294; asc y ;;7: len 5; hex 99af791609; asc y ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 80 index PRIMARY of table `mytest`.`t` trx id 28229 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 8; compact format; info bits 00: len 4; hex 00000009; asc ;;1: len 6; hex 000000006e4a; asc nJ;;2: len 7; hex 02000001160280; asc ;;3: len 10; hex 74657374202020202020; asc test ;;4: len 1; hex 08; asc ;;5: len 1; hex 82; asc ;;6: len 5; hex 99af790294; asc y ;;7: len 5; hex 99af791618; asc y ;;*** WE ROLL BACK TRANSACTION (1)
問(wèn)題排查
死鎖出現(xiàn)的前提條件是鎖等待,或者說(shuō)是鎖資源的爭(zhēng)用。
不僅需要對(duì)死鎖問(wèn)題進(jìn)行排查處理,還需要對(duì)長(zhǎng)時(shí)間的鎖等待問(wèn)題,進(jìn)行排查和分析。
鎖等待會(huì)阻塞當(dāng)前的線程,甚至對(duì)其他的線程造成影響,降低了系統(tǒng)的并發(fā)性能,線程資源和其持有的鎖遲遲不能釋放,不僅造成了資源的浪費(fèi),還有可能導(dǎo)致服務(wù)器崩潰。
下面是一些常常會(huì)用到的分析手段。
查看連接的線程
show processlist;
可以顯示哪些線程正在運(yùn)行。如果當(dāng)前用戶有 SUPER 權(quán)限,就可以看到所有線程。
如果有線程正在 UPDATE 或者 INSERT 某張表,那么進(jìn)程的 state 為 Updating 或者 Sending data。
show processlist;
只會(huì)列出前 100 條數(shù)據(jù),如果想列出所有結(jié)果,可以使用 show full processlist;
- Id 表示線程 id(客戶端連接的 id)??墒褂?
select connection_id();
獲取。 - User 表示客戶端連接用的是哪個(gè)用戶。
- Host 表示客戶端連接到了哪個(gè) host 主機(jī)。
- db 表示客戶端連接的是哪個(gè)數(shù)據(jù)庫(kù)??墒褂?
select database();
獲取。 - Command 表示客戶端連接當(dāng)前操作的命令類型。
- Time 表示客戶端連接當(dāng)前操作的命令持續(xù)了多長(zhǎng)時(shí)間,單位秒。
- info 表示客戶端連接當(dāng)前操作的具體是什么命令。
- state 表示當(dāng)前操作命令的狀態(tài)。
關(guān)于 state 的常見(jiàn)狀態(tài)值如下:
- Sending data 表示正在處理 select 查詢,同時(shí)正在把查詢結(jié)果發(fā)送給客戶端。
- Updating 表示正在搜索匹配的行,并修改它們。
- Locked 表示被其他的 sql 操作鎖住了。
- User lock 表示正在等待 get_lock()。
- Upgrading lock 表示正在升級(jí)鎖。
- Searching rows for update 表示正在搜索符合條件的行以備更新。
- Removing duplicates 表示正在執(zhí)行 select distinct,對(duì)查詢結(jié)果去重。
- Sorting for order 表示正在進(jìn)行 order by 排序。
- Creating tmp table 表示正在創(chuàng)建臨時(shí)表,來(lái)存放子查詢的臨時(shí)結(jié)果集。
- Copying tmp table on disk 表示臨時(shí)結(jié)果集大于 tmp_table_size,正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)換為磁盤(pán)存儲(chǔ),以節(jié)省內(nèi)存。
mysql> show full processlist;
+----+-----------------+-----------------+--------+---------+-------+------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+---------+-------+------------------------+----------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 11355 | Waiting on empty queue | NULL |
| 8 | root | localhost:63023 | NULL | Sleep | 11271 | | NULL |
| 13 | root | localhost:63066 | mytest | Sleep | 10490 | | NULL |
| 20 | root | localhost:63458 | mytest | Sleep | 143 | | NULL |
| 23 | root | localhost:63470 | mytest | Sleep | 9971 | | NULL |
| 24 | root | localhost:63480 | mytest | Sleep | 8050 | | NULL |
| 25 | root | localhost:63485 | mytest | Sleep | 9583 | | NULL |
| 27 | root | localhost:63504 | mytest | Sleep | 30 | | NULL |
| 30 | root | localhost:63601 | mytest | Sleep | 63 | | NULL |
| 32 | root | localhost:63891 | mytest | Query | 0 | starting | show full processlist |
| 33 | root | localhost:63965 | mytest | Query | 34 | updating | UPDATE t SET status=2 WHERE id=9 |
+----+-----------------+-----------------+--------+---------+-------+------------------------+----------------------------------+
11 rows in set (0.00 sec)
當(dāng)然,也可直接用下面的 SQL 語(yǔ)句查表。
select * from information_schema.processlist;
查看相關(guān)的表
注意:從 MySQL 8.0 開(kāi)始,data_locks 取代了 innodb_locks,data_lock_waits 取代了 innodb_lock_waits,并移到了數(shù)據(jù)庫(kù) performance_schema。
information_shcema.innodb_trx 表存儲(chǔ)了當(dāng)前的事務(wù)(進(jìn)行中的)信息。
SELECT * FROM information_schema.innodb_trx;
performance_schema.data_locks 存儲(chǔ)了當(dāng)前的鎖信息。
SELECT * FROM performance_schema.data_locks;
performance_schema.data_lock_waits 存儲(chǔ)了當(dāng)前的鎖等待信息。
SELECT * FROM performance_schema.data_lock_waits;
information_schema.processlist 存儲(chǔ)了當(dāng)前的連接信息。
select * from information_schema.processlist;
除了單獨(dú)查看上面幾張表的詳細(xì)信息外,還可以聯(lián)表查詢需要的字段,這樣會(huì)更直觀。
# 查看線程和鎖的信息
SELECT trx_mysql_thread_id AS sql_thread_id,trx_id, BLOCKING_ENGINE_TRANSACTION_ID AS blocked_by_trx_id,OBJECT_NAME as `table`, INDEX_NAME as `index`, LOCK_DATA as data, LOCK_MODE as mode, LOCK_STATUS as status, LOCK_TYPE as type,trx_state,trx_started,trx_wait_started,trx_query
FROM performance_schema.data_locks as lk
LEFT JOIN information_schema.innodb_trx as trx ON trx.trx_id=lk.ENGINE_TRANSACTION_ID
LEFT JOIN performance_schema.data_lock_waits as lkw ON lkw.REQUESTING_ENGINE_TRANSACTION_ID=trx.trx_id;
+---------------+--------+-------------------+-------+---------+------+------+---------+--------+-----------+---------------------+---------------------+----------------------------------+
| sql_thread_id | trx_id | blocked_by_trx_id | table | index | data | mode | status | type | trx_state | trx_started | trx_wait_started | trx_query |
+---------------+--------+-------------------+-------+---------+------+------+---------+--------+-----------+---------------------+---------------------+----------------------------------+
| 33 | 28248 | 28239 | t | NULL | NULL | IX | GRANTED | TABLE | LOCK WAIT | 2023-02-28 20:01:15 | 2023-02-28 20:01:15 | UPDATE t SET status=2 WHERE id=9 |
| 33 | 28248 | NULL | t | PRIMARY | 9 | X | WAITING | RECORD | LOCK WAIT | 2023-02-28 20:01:15 | 2023-02-28 20:01:15 | UPDATE t SET status=2 WHERE id=9 |
| 30 | 28239 | NULL | t | NULL | NULL | IX | GRANTED | TABLE | RUNNING | 2023-02-28 19:04:41 | NULL | NULL |
| 30 | 28239 | NULL | t | PRIMARY | 9 | X | GRANTED | RECORD | RUNNING | 2023-02-28 19:04:41 | NULL | NULL |
+---------------+--------+-------------------+-------+---------+------+------+---------+--------+-----------+---------------------+---------------------+----------------------------------+
4 rows in set (0.00 sec)
查看最近一次的死鎖信息
show engine innodb status
查看最近一次檢測(cè)到的死鎖信息(LATEST DETECTED DEADLOCK)。
show engine innodb status\G;
如果死鎖問(wèn)題很頻繁,想要查看更多的死鎖信息??梢蚤_(kāi)啟 innodb_print_all_deadlocks
選項(xiàng),將所有的死鎖信息記錄到 MySQL 服務(wù)器的錯(cuò)誤日志。
查看服務(wù)器的鎖信息
show status like '%lock%';
mysql> show status like '%lock%';
+------------------------------------------+--------+
| Variable_name | Value |
+------------------------------------------+--------+
| Com_lock_instance | 0 |
| Com_lock_tables | 0 |
| Com_unlock_instance | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 26 |
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 596291 |
| Innodb_row_lock_time_avg | 37268 |
| Innodb_row_lock_time_max | 51895 |
| Innodb_row_lock_waits | 16 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6698 |
| Key_blocks_used | 0 |
| Locked_connects | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_metadata_lock_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_lock_stat_lost | 0 |
| Table_locks_immediate | 409 |
| Table_locks_waited | 0 |
+------------------------------------------+--------+
21 rows in set (0.00 sec)
可重點(diǎn)關(guān)注下面幾個(gè)參數(shù):
- Innodb_row_lock_current_waits 當(dāng)前正處于鎖等待狀態(tài)的數(shù)量;
- Innodb_row_lock_time 服務(wù)器從啟動(dòng)到現(xiàn)在,鎖等待的總時(shí)長(zhǎng),單位毫秒
- Innodb_row_lock_time_avg 服務(wù)器從啟動(dòng)到現(xiàn)在,鎖等待的平均時(shí)長(zhǎng),單位毫秒
- Innodb_row_lock_time_max 服務(wù)器從啟動(dòng)到現(xiàn)在,鎖等待最久的一次時(shí)長(zhǎng),單位毫秒
- Innodb_row_lock_waits:服務(wù)器從啟動(dòng)到現(xiàn)在,鎖等待的總次數(shù)。
如果鎖等待的總次數(shù)和鎖等待的平均時(shí)長(zhǎng)比較大,可以考慮排查優(yōu)化。
查看正在使用的表
查看哪些表是打開(kāi)的,正在被線程使用。
in_use 表示有多少線程正在使用某張表,可以用來(lái)過(guò)濾。
show open tables where in_use>0;show open tables from db_name where in_use>0;
一般只用于查看哪些表被很多線程打開(kāi)使用。
如何盡可能地避免死鎖
- 合理設(shè)計(jì)和使用索引,采用復(fù)合索引時(shí),將區(qū)分度高的放在最前面,避免全表掃描。
- 避免大事務(wù),盡量將大事務(wù)拆分為多個(gè)小事務(wù)來(lái)處理,盡可能快地提交事務(wù)。
- 更新多張表或多條記錄時(shí),確保每次操作的順序一致。
- 減少更新或插入語(yǔ)句的數(shù)據(jù)量(行數(shù)),這樣就可減少鎖定的行數(shù)和間隙,也可減少事務(wù)執(zhí)行的時(shí)長(zhǎng)。
- 盡量使用唯一索引(如主鍵)去查詢和更新索引。
- 高并發(fā)的系統(tǒng)中,盡量不要手動(dòng)(顯式)加鎖。如少用顯式的排他鎖(X)
select ... for update
和 共享鎖(S)select ... lock in share mode
。 - 在服務(wù)器高峰期,不要執(zhí)行耗時(shí)的復(fù)雜腳本,不要修改表結(jié)構(gòu),不要進(jìn)行批量數(shù)據(jù)修復(fù)。