547 字
3 分钟
Mysql死锁
2023-05-07
2023-06-09

目标:什么是死锁,怎么解决死锁。

什么是死锁#

MySQL中的死锁是指两个或多个事务在相互竞争数据库资源(如行锁或表锁)时发生的阻塞现象。


死锁是怎么生成的#

通过下面2个事务说明。(示例表/数据在下面)

TXA#

因为2个事务流程分别都要锁ID10和ID20,因为先后顺序,互相占用了。导致进程无法往下运行,这个时候主动检测死锁的功能将自动启动。

-- 事务1
begin;
select * from t3 where id = 10 for update; -- 第一步
select * from t3 where id = 20 for update; -- 第三步

TXB#

-- 事务2
begin;
select * from t3 where id = 20 for update; -- 第二步
select * from t3 where id = 10 for update; -- 第四步

解决死锁的一些方法#

临时设置方式#

SET [SESSION | GLOBAL] innodb_lock_wait_timeout = value;

设置事务超时时间#

innodb_lock_wait_timeout 默认值是50秒

[mysqld]
innodb_lock_wait_timeout = 50;

主动检测死锁#

innodb_deadlock_detect默认开启

回滚代价比较低的事务,让另外一个事务成功执行。

但是当项目比较大,成千上万的事务在执行,这个会是导致数据库卡的一个原因之一(可以选择关闭主动检测,并且优化业务逻辑的事务)。

[mysqld]
innodb_deadlock_detect = OFF

主动发现阻塞#

SELECT waiting_pid AS '被阻塞的线程',
waiting_query AS '被阻塞的SQL',
blocking_pid AS '阻塞线程',
blocking_query AS '阻塞SQL',
wait_age AS '阻塞时间',
sql_kill_blocking_query AS '操作建议'
FROM sys.innodb_lock_waits
-- 设置阻塞时间大于N
WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(wait_started)) > 30

业务逻辑优化建议#

  1. 控制长事务,非必要,不用开启事务
  2. 数据更新、删除、等尽量放到事务的最后部分,可以有效降低锁存在的时间

示例结构/数据#

表结构

CREATE TABLE `t3` (
`id` INT ( 11 ) NOT NULL,
`c` INT ( 11 ) DEFAULT NULL,
`d` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `c` ( `c` )
) ENGINE = INNODB;

数据

INSERT INTO t3
VALUES
( 0, 0, 0 ),
( 10, 10, 10 ),
( 20, 20, 20 ),
( 30, 30, 30 ),
( 40, 40, 40 ),
( 50, 50, 50 );