mysql 表锁 自动关闭_MySQL表死锁问题的产生和解决

MySQL表死锁问题的产生和解决

一、死锁的产生

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B

访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A

有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项

目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操

作，很容易就出现这种死锁的情况。

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情

况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

二、模拟死锁的产生

1.新建MySQL数据库表

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',

`login_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',

`pwd` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',

`nick_name` varchar(90) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户昵称',

`usable` int(11) NOT NULL DEFAULT 1 COMMENT '是否可用 0：不可用；1：可用',

`create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`last_login_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '最后登录时间',

PRIMARY KEY (`id`) USING BTREE,

UNIQUE INDEX `login_name`(`login_name`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

2.插入模拟数据

INSERT INTO `user` VALUES (1, 'aa', 'aa', 'aa', 1, '2021-01-19 11:57:42', '2021-01-19 11:57:44');

INSERT INTO `user` VALUES (2, 'bb', 'bb', 'bb', 1, '2021-01-19 11:57:57', '2021-01-19 11:57:59');

3.模拟死锁

步骤一：

新建查询，分别执行

set autocommit=0;

SELECT * from user t where t.id='1' FOR UPDATE;

set autocommit=0 是为了关闭自动提交；

SELECT * from t_user t where t.id=‘1’ FOR UPDATE 是为了给id为1的数据行添加行锁；

步骤二

重复步骤一：新建查询，分别执行如下代码，给id为2的数据行添加行锁；

set autocommit=0;

SELECT * from user t where t.id='2' FOR UPDATE;

步骤三

回到第二个新建查询对话框，出现执行超时情况，执行如下代码；

SELECT * from user t where t.id='2' FOR UPDATE;

步骤四

回到第三个新建查询对话框，出现执行超时情况，执行如下代码；

SELECT * from user t where t.id='1' FOR UPDATE;

此时， 这两条数据均被加锁，产生数据死锁；

三、检测并解决数据死锁

步骤一： 检测死锁；

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

步骤二：杀死死锁进程；

步骤三：检测现在的死锁状态；

步骤四：重新执行需要正确执行的代码，运行成功，成功解决死锁；