mysql for update 死锁问题排查

for update

使用场景

 高并发并且对于数据的准确性很有要求的场景

使用姿势

加锁方式

排他锁：   select * from table where ..... for update

InnoDB 默认是行级别的锁。当有明确指定的主键\索引时，是行级别，否则表级别

明确指定主键/索引，并且有此记录，行级锁

未指定主键/索引，并且有此记录，表级锁

如果查不到数据for update是否会加锁呢?

明确指定主键/索引，若查无此记录，加间隙锁

当我们使用 范围条件 而 不是 相等条件 检索数据并 请求共享锁或排他锁 时，InnoDB 会给条件 已有数据记录 的 索引项 加锁，对于键值在条件范围 但不存在的 记录 加间隙锁

SELECT * FROM user WHERE id > 6 FOR UPDATE;

由此可见当根据主键/索引查询不到数据时仍然会加锁，这时候加的是间隙锁加锁区间是[4,6)

SELECT * FROM user WHERE id = 4 FOR UPDATE;

无主键/索引，表级锁

主键/索引不明确，表级锁

SELECT * FROM user WHERE id<>’3’ FOR UPDATE;
SELECT * FROM user WHERE id LIKE ‘3’ FOR UPDATE;

for update 的注意点

for update 仅适用于 InnoDB，并且必须 开启事务，在 begin 与 commit 之间生效

要测试for update的锁表情况，可以利用 MySQL 的 Command Mode，开启二个视窗来做测试 

for update的疑问点:

当开启 一个事务 进行 for update 的时候，另一个事务 也有 for update 的时候会一直等着，直到第一个事务结束吗？

答：会的。除非 第一个事务 commit 或者 rollback 或者 断开连接 ，第二个事务会 立马拿到锁 进行后面操作
	不过也可以 设置 锁等待超时参数 innodb_lock_wait_timeout 来解决 

如果没查到记录会加锁吗？
答：会的。有 主键/索引 产生 间隙锁，无 主键/索引 产生 表级锁 

for update 和 for update nowait 区别（前者阻塞其他事务，后者拒绝其他事务）

• for update 锁住表或者锁住行，只允许当前事务进行操作（读写），其他事务被阻塞，直到当前事务 提交或者回滚，被阻塞的事务自动执行
• for update nowait 锁住表或者锁住行，只允许当前事务进行操作（读写），其他事务被拒绝，事务占据的statement连接也会被断开

问题排查

排查 sql

排查的几张表

information_schema		#保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限
information_schema.innodb_trx			# 当前运行的所有事务
information_schema.innodb_locks			 # 当前出现的锁
information_schema.innodb_lock_waits 	# 锁等待的对应关系

desc information_schema.innodb_locks

desc information_schema.innodb_lock_waits

desc information_schema.innodb_trx

操作

查看当前运行的事务

select * from information_schema.innodb_trx

杀掉 lock_wait 的事务

kill {trx_mysql_thread_id} 

其他的记录不需要关注，因为其他的记录状态为“RUNNING” 即正在执行的事务，并没有锁

设置表日志

#设置是否开启日志
-- set global general_log=on;
#设置日志记录方式 table、file
-- set global log_output='table'
 
SELECT *,CONVERT (argument USING utf8) as `sql` from mysql.general_log where thread_id = 17 ORDER BY event_time DESC;

通过日志也可以 查询到问题线程执行的sql语句。反过来去找代码中调用的地

查询数据库中哪些线程正在执行

MySQL中 show processlist命令详解

show processlist
show full processlist

大量 block ，lock wait 或者 一条 sql running 很久

首选分析 mysql 表有没有索引
sql 有没有用到索引
事务的隔离级别（一般是 rr隔离级别） 和 事务的传播行为
是不是手动事务开启 未commit 或者 rollback
嵌套事务 传播行为尝试改成 REQUIRES_NEW

相关问题

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

某个事务一直 running

其他

想要查看一个数据库的变量状态

show VARIABLES like "general_log";

关于 autocommit 参数

提交指的是事务自动提交，顾名思义，当开启自动提交之后，你的每一次 sql 执行都会立马作为一个事务提交。如果关闭自动提交，执行的sql都不会生效，除非手动执行 commit

Mysql autocommit参数

set autocommit=0;   // 关闭 autocommit 

mysql死锁分析工具show engine innodb status

mysql死锁分析工具show engine innodb status

长事务

顾名思义就是运行时间比较长，长时间未提交的事务，也可以称之为大事务
这类事务往往会造成大量的阻塞和锁超时，容易造成主从延迟，要尽量避免使用长事务。

SpringBoot - 优雅的处理【长事务】