成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

故障解析丨一次死鎖問題的解決

作者：igoodful
開發 前端
根據binlog中部分SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8697'該GTID的事務信息，可恢復T2，但T1執行的語句由于被回滾了，則不會記錄到binlog，可開啟general log日志獲取排查。

背景

業務端遇到報錯為"Deadlock found when trying to get lock; try restarting transaction"則表明有死鎖發生

名稱

配置

數據庫版本

GreatSQL 8.0.26

隔離級別

Read-Commited

innodb status 日志

greatsql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2024-01-28 16:55:38 140737023727360 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 41 srv_active, 0 srv_shutdown, 17830 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
-------------
RW-LATCH INFO
-------------
Total number of rw-locks 132361
OS WAIT ARRAY INFO: reservation count 11180
OS WAIT ARRAY INFO: signal count 11177
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-28 16:53:40 140735053358848
*** (1) TRANSACTION:
TRANSACTION 37616, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1192, 1 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140737023432448, query id 652 127.0.0.1 root update
insert into info values (50,11)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37616 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37616 lock mode S waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;


*** (2) TRANSACTION:
TRANSACTION 37615, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1192, 2 row lock(s), undo log entries 2
MySQL thread id 15, OS thread handle 140737024022272, query id 653 127.0.0.1 root update
insert into info values (60,8)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37615 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 5 n bits 80 index uk_name of table `apple`.`info` trx id 37615 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 4; hex 80000028; asc    (;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

查看表結構

greatsql> show create table info \G
*************************** 1. row ***************************
       Table: info
Create Table: CREATE TABLE `info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

梳理 innodb status 日志

  • 整理如下：

事務

T1

T2

操作

insert into info values (50,11)

insert into info values (60,8)

關聯的對象

表apple.info的唯一索引 uk_name

表apple.info的唯一索引 uk_name

持有的鎖

lock mode S waitingheap no 7 11，40（十六進制為8，28）

lock_mode X locks rec but not gapheap no 7 11，40（十六進制為8，28）

等待的鎖

lock mode S waitingheap no 7 11，40（十六進制為8，28）

lock_mode X locks gap before rec insert intention waitingheap no 7 11，40（十六進制為8，28）

  • 首先事務T2獲取到了uk_name中記錄11的 lock x,rec not not gap 鎖
  • 事務T1嘗試獲取uk_name中記錄11的lock s, next key lock，由于T2持有了記錄的獨占鎖，因此被T1堵塞
  • 事務T2嘗試獲取uk_name中記錄11的lock x, gap before rec,insert intention，但被堵塞

獲取業務歷史SQL語句

通過系統表方式

通過performance_schema.threads、performance_schema.events_statements_history、performance_schema.events_statements_history_long等系統表獲取歷史SQL

  • 根據GreatSQL thread id獲得線程id
greatsql> select PROCESSLIST_ID,THREAD_ID,THREAD_OS_ID from  performance_schema.threads where processlist_id in (15,16);
+----------------+-----------+--------------+
| PROCESSLIST_ID | THREAD_ID | THREAD_OS_ID |
+----------------+-----------+--------------+
|             15 |        61 |         5714 |
|             16 |        62 |         5719 |
+----------------+-----------+--------------+
2 rows in set (0.00 sec)
  • 根據線程id獲得線程歷史SQL
greatsql> select THREAD_ID,EVENT_ID,CURRENT_SCHEMA,SQL_TEXT,MESSAGE_TEXT,EVENT_NAME,SOURCE from performance_schema.events_statements_history where thread_id in (61,62) order by THREAD_ID,EVENT_ID;
+-----------+----------+----------------+---------------------------------+--------------------------------------------------------------------+--------------------------+---------------------------------+
| THREAD_ID | EVENT_ID | CURRENT_SCHEMA | SQL_TEXT                        | MESSAGE_TEXT                                                       | EVENT_NAME               | SOURCE                          |
+-----------+----------+----------------+---------------------------------+--------------------------------------------------------------------+--------------------------+---------------------------------+
|        61 |     3762 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3807 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3852 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3897 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3942 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     3987 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     4032 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        61 |     4077 | apple          | begin                           | NULL                                                               | statement/sql/begin      | init_net_server_extension.cc:94 |
|        61 |     4100 | apple          | insert into info values (40,11) | NULL                                                               | statement/sql/insert     | init_net_server_extension.cc:94 |
|        61 |     4569 | apple          | insert into info values (60,8)  | NULL                                                               | statement/sql/insert     | init_net_server_extension.cc:94 |
|        62 |     3215 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3260 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3305 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3350 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3395 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3440 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3485 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3530 | apple          | NULL                            | NULL                                                               | statement/com/Field List | init_net_server_extension.cc:94 |
|        62 |     3575 | apple          | begin                           | NULL                                                               | statement/sql/begin      | init_net_server_extension.cc:94 |
|        62 |     3598 | apple          | insert into info values (50,11) | Deadlock found when trying to get lock; try restarting transaction | statement/sql/insert     | init_net_server_extension.cc:94 |
+-----------+----------+----------------+---------------------------------+--------------------------------------------------------------------+--------------------------+---------------------------------+
20 rows in set (0.00 sec)
  • 觀察show engine innodb status中的GreatSQL thread id 16和GreatSQL thread id 15
  • 通過performance_schema.threads獲取THREAD_ID
  • 通過performance_schema.events_statements_history獲取THREAD_ID執行的歷史SQL以及執行時間

最終可復現出如下業務SQL：

事務

T1

T2

語句

begin;

begin;

語句


insert into info values (40,11);

語句

insert into info values (50,11);


語句


insert into info values (60,8);

通過解析binlog

$ mysqlbinlog -vv --base64-output=decode-rows  bin.000030

SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8696'/*!*/;
# at 10314
#240128 16:52:35 server id 1024  end_log_pos 10390 CRC32 0x59edb313         Query        thread_id=18        exec_time=0        error_code=0
SET TIMESTAMP=1706431955/*!*/;
BEGIN
/*!*/;
# at 10390
#240128 16:52:35 server id 1024  end_log_pos 10442 CRC32 0xc03dea61         Table_map: `apple`.`info` mapped to number 370
# at 10442
#240128 16:52:35 server id 1024  end_log_pos 10486 CRC32 0x670e0c66         Write_rows: table id 370 flags: STMT_END_F
### INSERT INTO `apple`.`info`
### SET
###   @1=30 /* INT meta=0 nullable=0 is_null=0 */
###   @2=30 /* INT meta=0 nullable=0 is_null=0 */
# at 10486
#240128 16:52:35 server id 1024  end_log_pos 10517 CRC32 0xab4e0d89         Xid = 598
COMMIT/*!*/;
# at 10517
#240128 19:22:12 server id 1024  end_log_pos 10596 CRC32 0x4f4cf08e         GTID        last_committed=30        sequence_number=36        rbr_only=yes        original_committed_timestamp=1706440932450590        immediate_commit_timestamp=1706440932450590 transaction_length=378
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1706440932450590 (2024-01-28 19:22:12.450590 CST)
# immediate_commit_timestamp=1706440932450590 (2024-01-28 19:22:12.450590 CST)
/*!80001 SET @@session.original_commit_timestamp=1706440932450590*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8697'/*!*/;
# at 10596
#240128 16:53:16 server id 1024  end_log_pos 10672 CRC32 0xf222c003         Query        thread_id=15        exec_time=0        error_code=0
SET TIMESTAMP=1706431996/*!*/;
BEGIN
/*!*/;
# at 10672
#240128 16:53:16 server id 1024  end_log_pos 10724 CRC32 0x20cb8c86         Table_map: `apple`.`info` mapped to number 370
# at 10724
#240128 16:53:16 server id 1024  end_log_pos 10768 CRC32 0xd8f53958         Write_rows: table id 370 flags: STMT_END_F
### INSERT INTO `apple`.`info`
### SET
###   @1=40 /* INT meta=0 nullable=0 is_null=0 */
###   @2=11 /* INT meta=0 nullable=0 is_null=0 */
# at 10768
#240128 16:53:40 server id 1024  end_log_pos 10820 CRC32 0x23f22580         Table_map: `apple`.`info` mapped to number 370
# at 10820
#240128 16:53:40 server id 1024  end_log_pos 10864 CRC32 0x182ecdef         Write_rows: table id 370 flags: STMT_END_F
### INSERT INTO `apple`.`info`
### SET
###   @1=60 /* INT meta=0 nullable=0 is_null=0 */
###   @2=8 /* INT meta=0 nullable=0 is_null=0 */
# at 10864
#240128 19:22:12 server id 1024  end_log_pos 10895 CRC32 0x57fd1d3c         Xid = 650
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

根據binlog中部分SET @@SESSION.GTID_NEXT= 'e319a624-b2ce-11ee-9aac-00163e62ca8a:8697'該GTID的事務信息，可恢復T2，但T1執行的語句由于被回滾了，則不會記錄到binlog，可開啟general log日志獲取排查

事務

T1

T2

語句

begin;

begin;

語句


insert into info values (40,11);

語句

insert into info values (50,11);


語句


insert into info values (60,8);

分析死鎖

  • T1、T2開啟了一個事務
  • 隨后T2執行了插入(40,11)的insert語句：insert into info values (40,11)
  • T1執行了插入(50,11)的insert語句：insert into info values (50,11) 進行唯一性沖突檢查，嘗試獲取LOCK_S
  • 然后T1所在的連接會將T2中的隱式鎖轉換為顯示鎖，此時T2將獲取Lock X, Rec_not_gap。由于T2的Lock X, Rec_not_gap與T1的LOCK S不兼容，因此T1被堵塞
  • 隨后，T2又執行了(60,8)的insert語句：insert into info values (60,8) 由于其插入的唯一索引值是8，因此不存在主鍵沖突，直接執行樂觀插入操作。執行樂觀插入時，需要檢查其它事務是否堵塞insert操作。其核心是獲取待插入記錄的下一個值（這里剛好是10），并獲取該記錄上的所有鎖，與需要添加的鎖判斷是否存在沖突。
  • T1持有了記錄11的LOCK_S鎖與T2的LOCK_X、LOCK_INSERT_INTENTION不兼容，因此T2被T1堵塞
  • 死鎖形成。

解決

? 適當的減少Unique索引

? 避免插入重復的值（唯一索引所在列）

責任編輯：武曉燕 來源： GreatSQL社區
死鎖日志binlog
相關推薦

2011-05-06 10:32:06

硬盤鍵盤

2022-01-10 10:26:30

Kubernetes抓包環境

2023-10-11 22:24:00

DubboRedis服務器

2011-04-13 09:21:30

死鎖SQL Server

2019-03-15 16:20:45

MySQL死鎖排查命令

2019-06-19 08:59:52

數據庫死鎖堆棧

2019-04-15 13:15:12

數據庫MySQL死鎖

2010-07-30 16:10:45

UPS設備燒毀故障分析

2021-12-12 18:12:13

Hbase線上問題

2017-12-19 14:00:16

數據庫MySQL死鎖排查

2019-04-18 10:55:00

故障演練流量

2019-12-27 10:43:48

磁盤數據庫死鎖

2020-09-16 08:26:18

圖像定位尺寸

2011-08-12 09:30:02

MongoDB

2021-12-02 07:50:30

NFS故障內存

2021-01-08 13:52:15

Consul微服務服務注冊中心

2021-06-08 08:38:36

MySQL數據庫死鎖問題

2023-04-06 10:52:18

2016-11-16 09:25:15

WindowsWindow 8Windows 10

2021-05-26 11:06:06

Kubernetes網絡故障集群節點
點贊
收藏

51CTO技術棧公眾號

主站蜘蛛池模板： 羞羞视频网站免费观看 | 国产精品国产三级国产aⅴ中文 | 欧美综合久久 | 九九热这里只有精品6 | 欧美精品片| 精品电影 | 欧美激情亚洲激情 | 国产精品爱久久久久久久 | 欧美日韩专区 | 免费视频二区 | 欧美亚洲另类丝袜综合网动图 | 日韩久久综合网 | 久久蜜桃av一区二区天堂 | 成人午夜在线视频 | 涩涩导航| 国产欧美日韩久久久 | 久久亚洲欧美日韩精品专区 | 久久久久久久久久久久亚洲 | 91精品国产综合久久久久久 | 国家aaa的一级看片 h片在线看 | 国产 91 视频| 韩国久久精品 | 亚洲啊v | 国产精品久久久久久久久污网站 | 伊人网99| 日韩一级黄色毛片 | 欧美成年网站 | 国产精品国产亚洲精品看不卡15 | 黑人成人网 | 国产一二三区精品视频 | 欧美极品视频在线观看 | 亚洲人精品 | www.色.com| 日韩一区二区在线视频 | 蜜桃臀av一区二区三区 | 成人免费在线视频 | 北条麻妃一区二区三区在线视频 | 欧美一级二级在线观看 | 色视频一区二区 | 久久久蜜桃一区二区人 | 超碰免费在线观看 |