前提准备一个测试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多方面入手了。
————————————————