mysql 死锁测试

前提准备一个测试mysql (mariadb-10.2.12)

CREATE TABLE test (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
age int(11) unsigned DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY age (age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test (age) values (1);

insert into test (age) values (2);

select * from test;

首先执行sql1：

start transaction;
select * from test where age=1;
–delete from test where age=1;

–commit;

后两行是注释掉的，这里还没有执行

再执行sql2：

start transaction;
delete from test where age=1;

–commit;

后一行是注释掉的，这里还没有执行

然后继续执行sql1里面的第三行

–start transaction;
–select * from test where age=1;
delete from test where age=1;

–commit;

一三四行注释掉了
这个时候就出现了死锁了，通过以下语句可以查看到：

SELECT * FROM information_schema.INNODB_TRX;

![在这里插入图片描述](https://img-blog.csdnimg.cn/20191127100559807.png
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

show full processlist;

SHOW ENGINE INNODB STATUS;

【3】解决办法
① 查看并修改变量值

show GLOBAL VARIABLES like ‘%innodb_lock_wait_timeout%’;

set GLOBAL innodb_lock_wait_timeout=100;##设置大小值看系统情况

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败。参数的时间单位是秒，默认值50S。

② 找到一直未提交事务导致后来进程死锁等待的进程，并杀掉

根据锁等待表中的拥有锁的事务id(blocking_trx_id)，从innodb_trx表中找到trx_mysql_thread_id值，kill掉。

如 这里杀掉 进程235：

select trx_mysql_thread_id from information_schema.innodb_trx it
JOIN information_schema.INNODB_LOCK_WAITS ilw
on ilw.blocking_trx_id = it.trx_id;

##trx_mysql_thread_id: 235

kill 235

③ 优化SQL，优化数据库，优化项目。第一个update未执行完，第二个update就来了，超过等待时间就会报锁等待超时异常。在数据并发项目遇到这种情况概率比较大，这时候就要从项目、数据库、执行SQL多方面入手了。

————————————————