Mysql死锁

发布于 2023-05-07  128 次阅读


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

什么是死锁

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

间歇性凌云壮志,持续性混吃等死