mysql -死鎖示例,死鎖分析,解決辦法
mysql 服務(wù)器配置
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.24 |
+-----------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%'; //鎖等待超時(shí)時(shí)間，單位秒
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 7200 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%innodb_deadlock_detect%'; //死鎖檢測(cè)
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+
1 row in set (0.00 sec)
死鎖一定很著急，先說(shuō)快速解決辦法
SELECT concat('KILL ',id,';')
FROM information_schema.processlist p
INNER JOIN information_schema.INNODB_TRX x
ON p.id=x.trx_mysql_thread_id
WHERE db='demo';
> 執(zhí)行 KILL 命令將線程殺死
示例：
1. 表結(jié)構(gòu)
CREATE TABLE order (
id varchar(26) PRIMARY KEY,
flag tinyint(4),
order_no varchar(32),
UNIQUE idx_order_no (order_no)
)
2. 造成死鎖代碼
err := db.Transaction(func(tx *gorm.DB) error {
//事務(wù)1 trx_id:579856
err := tx.Exec("update order set flag=1 where order_no=?", "1").Error
if err != nil {
fmt.Println(err)
}
func() {
order :=Order{}
if err :=db.Where("order_no = ?", "1").Take(&order).Error; err != nil {
fmt.Println(err)
}
order.UpdatedAt = time.Now()
//事務(wù)2, trx_id:579857
if err := db.Updates(&order).Error; err != nil {
fmt.Println(err)
}
}()
return nil
})
死鎖分析
1. 通過(guò) processlist 和 INNODB_TRX 查看鎖定的線程 執(zhí)行的 sql
mysql> select trx_id,trx_state,trx_started,trx_requested_lock_id,INFO FROM information_schema.processlist p INNER JOIN information_schema.INNODB_TRX x ON p.id=x.trx_mysql_thread_id WHERE db='demo' order by trx_id asc limit 3;
+--------+-----------+---------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | INFO |
+--------+-----------+---------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 579856 | RUNNING | 2021-09-15 13:58:41 | NULL | NULL |
| 579857 | LOCK WAIT | 2021-09-15 13:58:41 | 140676703063368:3606:25:66:140677199640096 | UPDATE `order` SET `order_no`='1',`updated_at`='2021-09-17 19:58:41.814' WHERE `id` = 'mj7k36ec7prntetzmi5t67iqme' |
| 579858 | LOCK WAIT | 2021-09-15 13:59:00 | 140676703065048:3606:25:66:140677199647888 | UPDATE `order` SET flag = 2 WHERE flag IN(1,6) AND updated_at <= NOW()
2. 查看鎖等待
mysql> SELECT REQUESTING_THREAD_ID,REQUESTING_ENGINE_TRANSACTION_ID, BLOCKING_THREAD_ID,BLOCKING_ENGINE_TRANSACTION_ID FROM `performance_schema`.data_lock_waits;
+----------------------+----------------------------------+--------------------+--------------------------------+
| REQUESTING_THREAD_ID | REQUESTING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_ENGINE_TRANSACTION_ID |
+----------------------+----------------------------------+--------------------+--------------------------------+
| 9213 | 579865 | 9201 | 579864 |
| 9213 | 579865 | 9191 | 579863 |
| 9213 | 579865 | 9184 | 579862 |
| 9213 | 579865 | 9170 | 579861 |
| 9213 | 579865 | 9161 | 579860 |
| 9213 | 579865 | 9150 | 579859 |
| 9213 | 579865 | 9139 | 579858 |
| 9201 | 579864 | 9191 | 579863 |
| 9201 | 579864 | 9184 | 579862 |
| 9201 | 579864 | 9170 | 579861 |
| 9201 | 579864 | 9161 | 579860 |
| 9201 | 579864 | 9150 | 579859 |
| 9201 | 579864 | 9139 | 579858 |
| 9191 | 579863 | 9184 | 579862 |
| 9191 | 579863 | 9170 | 579861 |
| 9191 | 579863 | 9161 | 579860 |
| 9191 | 579863 | 9150 | 579859 |
| 9191 | 579863 | 9139 | 579858 |
| 9184 | 579862 | 9170 | 579861 |
| 9184 | 579862 | 9161 | 579860 |
| 9184 | 579862 | 9150 | 579859 |
| 9184 | 579862 | 9139 | 579858 |
| 9170 | 579861 | 9161 | 579860 |
| 9170 | 579861 | 9150 | 579859 |
| 9170 | 579861 | 9139 | 579858 |
| 9161 | 579860 | 9150 | 579859 |
| 9161 | 579860 | 9139 | 579858 |
| 9150 | 579859 | 9139 | 579858 |
| 9139 | 579858 | 9138 | 579857 |
| 9139 | 579858 | 9141 | 579856 |
| 9138 | 579857 | 9141 | 579856 |
+----------------------+----------------------------------+--------------------+--------------------------------+
31 rows in set (0.00 sec)
從上面看出
579857（t2） 事務(wù)等待579856（t1）579858（t3） 等待579856（t1）
3. 查看具體加鎖情況
mj7k36ec7prntetzmi5t67iqme 是 id
bk5jhtgd5fy9pnyzw51zoocgir 是 id
mysql> select engine_transaction_id,thread_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from `performance_schema`.data_locks order by engine_transaction_id asc limit 6;
+-----------------------+-----------+-------------+--------------+-----------+---------------+-------------+--------------------------------------------------+
| engine_transaction_id | thread_id | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+-----------------------+-----------+-------------+--------------+-----------+---------------+-------------+--------------------------------------------------+
| 579856 | 9141 | order | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 'mj7k36ec7prntetzmi5t67iqme' |
| 579856 | 9141 | order | idx_order_no | RECORD | X,REC_NOT_GAP | GRANTED | '1', 'mj7k36ec7prntetzmi5t67iqme' |
| 579856 | 9141 | order | NULL | TABLE | IX | GRANTED | NULL |
| 579857 | 9138 | order | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 'mj7k36ec7prntetzmi5t67iqme' |
| 579857 | 9138 | order | NULL | TABLE | IX | GRANTED | NULL |
| 579858 | 9139 | order | PRIMARY | RECORD | X | GRANTED | 'bk5jhtgd5fy9pnyzw51zoocgir' |
+-----------------------+-----------+-------------+--------------+-----------+---------------+-------------+--------------------------------------------------+
6 rows in set (0.00 sec)
579856 對(duì)記錄
mj7k36ec7prntetzmi5t67iqme加行級(jí)排他鎖，579857 WAITING 等待 579856 鎖釋放，代碼: ：579856 事務(wù)需要等待 579857 事務(wù)提交，結(jié)果記錄 mj7k36ec7prntetzmi5t67iqme 死鎖，
579858 更新多條
order記錄，多條符合條件的記錄加 X 鎖 ，其中包含記錄mj7k36ec7prntetzmi5t67iqme由于需要等待 579856，造成鎖等待。
總結(jié)
事務(wù)嵌套事務(wù)，579856 與 579857 更新同一條記錄，造成事務(wù)互相等待，誰(shuí)也無(wú)法提交。
另，579858 范圍更新訂單 sql 包含 579856 同一條訂單，導(dǎo)致鎖的范圍進(jìn)一步擴(kuò)大。
| 是否兼容 | IS（意向共享鎖） | IX（意向排他鎖） | S | X |
|---|---|---|---|---|
| IS | 是 | 是 | 是 | 否(是，則指的表級(jí)排他鎖) |
| IX | 是 | 是 | 否 | 否(是，則指的表級(jí)排他鎖) |
| S | 是 | 否 | 是 | 否 |
| X | 否 | 否 | 否 | 否 |
tips
加讀鎖
select * FROM
orderWHERE id='id' LOCK IN SHARE MODE
//必須在事務(wù)中，事務(wù)提交鎖自動(dòng)釋放mysql 簡(jiǎn)單查詢默認(rèn)不加鎖，通過(guò) MVVC 快照讀，實(shí)現(xiàn)非鎖定讀 X 不生效，加讀鎖 X 才生效
IX 不影響表的 INSERT 和 非鎖定行的 DELETE
評(píng)論
圖片
表情