mysql -死锁示例,死锁分析,解决办法
Leonardyp
共 36003字，需浏览 73分钟
·
2023-08-17 11:48
mysql 服务器配置
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.24 |
+-----------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%'; //锁等待超时时间，单位秒
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 7200 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%innodb_deadlock_detect%'; //死锁检测
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+
1 row in set (0.00 sec)
死锁一定很着急，先说快速解决办法
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';
> 执行 KILL 命令将线程杀死
示例：
1. 表结构
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 {
//事务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()
//事务2, trx_id:579857
if err := db.Updates(&order).Error; err != nil {
fmt.Println(err)
}
}()
return nil
})
死锁分析
1. 通过 processlist 和 INNODB_TRX 查看锁定的线程 执行的 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） 事务等待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 对记录
mj7k36ec7prntetzmi5t67iqme加行级排他锁，579857 WAITING 等待 579856 锁释放，代码: ：579856 事务需要等待 579857 事务提交，结果记录 mj7k36ec7prntetzmi5t67iqme 死锁，
579858 更新多条
order记录，多条符合条件的记录加 X 锁 ，其中包含记录mj7k36ec7prntetzmi5t67iqme由于需要等待 579856，造成锁等待。
总结
事务嵌套事务，579856 与 579857 更新同一条记录，造成事务互相等待，谁也无法提交。
另，579858 范围更新订单 sql 包含 579856 同一条订单，导致锁的范围进一步扩大。
| 是否兼容 | IS（意向共享锁） | IX（意向排他锁） | S | X |
|---|---|---|---|---|
| IS | 是 | 是 | 是 | 否(是，则指的表级排他锁) |
| IX | 是 | 是 | 否 | 否(是，则指的表级排他锁) |
| S | 是 | 否 | 是 | 否 |
| X | 否 | 否 | 否 | 否 |
tips
加读锁
select * FROM
orderWHERE id='id' LOCK IN SHARE MODE
//必须在事务中，事务提交锁自动释放mysql 简单查询默认不加锁，通过 MVVC 快照读，实现非锁定读 X 不生效，加读锁 X 才生效
IX 不影响表的 INSERT 和 非锁定行的 DELETE
评论