當前位置：首頁 > news >正文

杭州灣新區(qū)建設局網站seo優(yōu)化便宜

news 2025/7/1 21:46:29

杭州灣新區(qū)建設局網站,seo優(yōu)化便宜,大型門戶網站建設,中企動力科技股份有限公司電話文章目錄死鎖（dead lock）示例 1問題排查查看連接的線程查看相關的表查看最近一次的死鎖信息查看服務器的鎖信息查看正在使用的表如何盡可能地避免死鎖死鎖（dead lock） 兩個及以上的事務各自持有對方需要的鎖，導致雙方…

文章目錄

- 死鎖（dead lock）
- - 示例 1
- 問題排查
- - 查看連接的線程
  - 查看相關的表
  - 查看最近一次的死鎖信息
  - 查看服務器的鎖信息
  - 查看正在使用的表
- 如何盡可能地避免死鎖

死鎖（dead lock）

兩個及以上的事務各自持有對方需要的鎖，導致雙方都無法繼續(xù)執(zhí)行，只能互相等待（循環(huán)等待）。因為雙方都在等待對方釋放鎖，而各自又無法釋放鎖，從而導致了死循環(huán)。這就是死鎖（dead lock）。

當兩個事務鎖定了多張表的多條記錄（如 UPDATE 或者 SELECT … FOR UPDATE），且它們各自鎖定的順序相反時，就會出現(xiàn)死鎖。當 sql 語句鎖定了多行記錄和間隙時，也容易出現(xiàn)死鎖。

關于 InnoDB 鎖的類型和兼容性，可參考 https://blog.csdn.net/lamp_yang_3533/article/details/129291575

產生死鎖的四個條件：

互斥條件：一個資源每次只能被一個進程（線程）使用。
請求與保持條件：一個進程（線程）因請求資源而阻塞時，對已獲得的資源保持不放。
不剝奪條件：進程（線程）已獲得的資源，在末使用完之前，不能被剝奪。
循環(huán)等待條件：多個進程（線程）之間形成一種頭尾相接的循環(huán)等待的資源爭用關系。

死鎖檢測功能 innodb_deadlock_detect 默認是開啟的。當出現(xiàn)死鎖時，InnoDB 就會檢測到狀態(tài)，并及時回滾導致死鎖的多個事務中的一個。

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)

如果關閉了死鎖檢測功能，InnoDB 就依賴鎖等待超時時間 innodb_lock_wait_timeout，當鎖等待超時（默認 50 秒）后，對事務進行回滾。

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)

說明： 以下示例基于 MySQL 8.0，如果是之前的版本會有所不同。

示例 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 '用戶所屬的部門id',`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用戶的狀態(tài): 1啟用, 2禁用',`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',PRIMARY KEY (`id`),KEY `department_id_index` (`department_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';# 插入部分測試數(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');

首先，在第一個連接中，開啟事務 A。

# 事務 A
BEGIN;
UPDATE t SET department_id=3 WHERE id BETWEEN 3 AND 7;

由于在 MySQL 8.0 中，只要持有鎖（不管是否出現(xiàn)鎖爭用），locks 表中就會記錄數(shù)據(jù)。

故可以先簡單看一下事務中鎖的信息。

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)

然后，在第二個連接中，開啟事務 B。

# 事務 B
BEGIN;
UPDATE t SET status=2 WHERE id=9;

繼續(xù)看一下事務中鎖的信息。

+---------------+--------+-------+---------+------+------+---------+--------+
| 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)鎖爭用的情況。

然后，在事務 B 中，繼續(xù)執(zhí)行下面的 SQL。

UPDATE t SET status=2 WHERE id=3;

查看事務中的鎖信息，如下：

+---------------+--------+-------+---------+------+------+---------+--------+
| 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)，目前已經出現(xiàn)資源競爭，事務 B 處于鎖等待（wait）狀態(tài)。

最后，在事務 A 中，繼續(xù)執(zhí)行下面的 SQL。

UPDATE t SET status=2 WHERE id=9;

出現(xiàn)雙方循環(huán)等待，立即產生死鎖，事務報錯。

事務 B 報錯信息如下：

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

再來看看事務中鎖的信息。

+---------------+--------+-------+---------+------+------+---------+--------+
| 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)

由于死鎖，事務 B 被 InnoDB 回滾了。故現(xiàn)在只剩下事務 A 的鎖，它可以正常提交事務了。

這里，我們利用下面語句查看最新監(jiān)測到的死鎖信息。

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)

問題排查

死鎖出現(xiàn)的前提條件是鎖等待，或者說是鎖資源的爭用。

不僅需要對死鎖問題進行排查處理，還需要對長時間的鎖等待問題，進行排查和分析。

鎖等待會阻塞當前的線程，甚至對其他的線程造成影響，降低了系統(tǒng)的并發(fā)性能，線程資源和其持有的鎖遲遲不能釋放，不僅造成了資源的浪費，還有可能導致服務器崩潰。

下面是一些常常會用到的分析手段。

查看連接的線程

show processlist;可以顯示哪些線程正在運行。如果當前用戶有 SUPER 權限，就可以看到所有線程。

如果有線程正在 UPDATE 或者 INSERT 某張表，那么進程的 state 為 Updating 或者 Sending data。

show processlist; 只會列出前 100 條數(shù)據(jù)，如果想列出所有結果，可以使用 show full processlist;

Id 表示線程 id（客戶端連接的 id）。可使用 select connection_id(); 獲取。
User 表示客戶端連接用的是哪個用戶。
Host 表示客戶端連接到了哪個 host 主機。
db 表示客戶端連接的是哪個數(shù)據(jù)庫?？墒褂?select database(); 獲取。
Command 表示客戶端連接當前操作的命令類型。
Time 表示客戶端連接當前操作的命令持續(xù)了多長時間，單位秒。
info 表示客戶端連接當前操作的具體是什么命令。
state 表示當前操作命令的狀態(tài)。

關于 state 的常見狀態(tài)值如下：

Sending data 表示正在處理 select 查詢，同時正在把查詢結果發(fā)送給客戶端。
Updating 表示正在搜索匹配的行，并修改它們。
Locked 表示被其他的 sql 操作鎖住了。
User lock 表示正在等待 get_lock()。
Upgrading lock 表示正在升級鎖。
Searching rows for update 表示正在搜索符合條件的行以備更新。
Removing duplicates 表示正在執(zhí)行 select distinct，對查詢結果去重。
Sorting for order 表示正在進行 order by 排序。
Creating tmp table 表示正在創(chuàng)建臨時表，來存放子查詢的臨時結果集。
Copying tmp table on disk 表示臨時結果集大于 tmp_table_size，正在將臨時表從內存存儲轉換為磁盤存儲，以節(jié)省內存。

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)

當然，也可直接用下面的 SQL 語句查表。

select * from information_schema.processlist;

查看相關的表

注意：從 MySQL 8.0 開始，data_locks 取代了 innodb_locks，data_lock_waits 取代了 innodb_lock_waits，并移到了數(shù)據(jù)庫 performance_schema。

information_shcema.innodb_trx 表存儲了當前的事務（進行中的）信息。

SELECT * FROM information_schema.innodb_trx;

performance_schema.data_locks 存儲了當前的鎖信息。

SELECT * FROM performance_schema.data_locks;

performance_schema.data_lock_waits 存儲了當前的鎖等待信息。

SELECT * FROM performance_schema.data_lock_waits;

information_schema.processlist 存儲了當前的連接信息。

select * from information_schema.processlist;

除了單獨查看上面幾張表的詳細信息外，還可以聯(lián)表查詢需要的字段，這樣會更直觀。

# 查看線程和鎖的信息
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 查看最近一次檢測到的死鎖信息（LATEST DETECTED DEADLOCK）。

show engine innodb status\G;

如果死鎖問題很頻繁，想要查看更多的死鎖信息。可以開啟 innodb_print_all_deadlocks 選項，將所有的死鎖信息記錄到 MySQL 服務器的錯誤日志。

查看服務器的鎖信息

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)

可重點關注下面幾個參數(shù)：

Innodb_row_lock_current_waits 當前正處于鎖等待狀態(tài)的數(shù)量；
Innodb_row_lock_time 服務器從啟動到現(xiàn)在，鎖等待的總時長，單位毫秒
Innodb_row_lock_time_avg 服務器從啟動到現(xiàn)在，鎖等待的平均時長，單位毫秒
Innodb_row_lock_time_max 服務器從啟動到現(xiàn)在，鎖等待最久的一次時長，單位毫秒
Innodb_row_lock_waits：服務器從啟動到現(xiàn)在，鎖等待的總次數(shù)。

如果鎖等待的總次數(shù)和鎖等待的平均時長比較大，可以考慮排查優(yōu)化。

查看正在使用的表

查看哪些表是打開的，正在被線程使用。

in_use 表示有多少線程正在使用某張表，可以用來過濾。

show open tables where in_use>0;show open tables from db_name where in_use>0;

一般只用于查看哪些表被很多線程打開使用。

如何盡可能地避免死鎖

合理設計和使用索引，采用復合索引時，將區(qū)分度高的放在最前面，避免全表掃描。
避免大事務，盡量將大事務拆分為多個小事務來處理，盡可能快地提交事務。
更新多張表或多條記錄時，確保每次操作的順序一致。
減少更新或插入語句的數(shù)據(jù)量（行數(shù)），這樣就可減少鎖定的行數(shù)和間隙，也可減少事務執(zhí)行的時長。
盡量使用唯一索引（如主鍵）去查詢和更新索引。
高并發(fā)的系統(tǒng)中，盡量不要手動（顯式）加鎖。如少用顯式的排他鎖（X）select ... for update 和共享鎖（S）select ... lock in share mode。
在服務器高峰期，不要執(zhí)行耗時的復雜腳本，不要修改表結構，不要進行批量數(shù)據(jù)修復。

查看全文

http://www.risenshineclean.com/news/30770.html