Mysql死锁问题如何排查和解决

Mysql 查询是否存在锁表有多种方式，这里只介绍一种最常用的。

1、查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX
2、查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
4、查询是否锁表
SHOW OPEN TABLES where In_use > 0;
在发生死锁时，这几种方式都可以查询到和当前死锁相关的信息。
5、查看最近死锁的日志
show engine innodb status
解除死锁
如果需要解除死锁，有一种最简单粗暴的方式，那就是找到进程id之后，直接干掉。
查看当前正在进行中的进程
show processlist
// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;
这两个命令找出来的进程id 是同一个。
杀掉进程对应的进程 id

kill id
验证（kill后再看是否还有锁）

SHOW OPEN TABLES where In_use > 0;
————————————————

前言

发生死锁了，如何排查和解决呢？本文将跟你一起探讨这个问题

• 准备好数据环境
• 模拟死锁案发
• 分析死锁日志
• 分析死锁结果

环境准备

数据库隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

自动提交关闭：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

表结构:

//id是自增主键，name是非唯一索引，balance普通字段
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表中的数据：

模拟并发

开启两个终端模拟事务并发情况，执行顺序以及实验现象如下：

1）事务A执行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Wei';
Query OK, 1 row affected (0.01 sec)

2）事务B执行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Eason';
Query OK, 1 row affected (0.01 sec)

3）事务A执行插入操作，陷入阻塞~

mysql> insert into account values(null,'Jay',100);

这时候可以用select * from information_schema.innodb_locks;查看锁情况：

4）事务B执行插入操作，插入成功，同时事务A的插入由阻塞变为死锁error。

mysql> insert into account values(null,'Yan',100);
Query OK, 1 row affected (0.01 sec)

锁介绍

在分析死锁日志前，先做一下锁介绍，哈哈~

主要介绍一下兼容性以及锁模式类型的锁：

共享锁与排他锁

InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

• 共享锁（S锁）：允许持锁事务读取一行。
• 排他锁（X锁）：允许持锁事务更新或者删除一行。

如果事务 T1 持有行 r 的 s 锁，那么另一个事务 T2 请求 r 的锁时，会做如下处理：

• T2 请求 s 锁立即被允许，结果 T1 T2 都持有 r 行的 s 锁
• T2 请求 x 锁不能被立即允许

如果 T1 持有 r 的 x 锁，那么 T2 请求 r 的 x、s 锁都不能被立即允许，T2 必须等待T1释放 x 锁才可以，因为X锁与任何的锁都不兼容。

意向锁

• 意向共享锁( IS 锁)：事务想要获得一张表中某几行的共享锁
• 意向排他锁( IX 锁)： 事务想要获得一张表中某几行的排他锁

比如：事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。

InnoDB存储引擎中锁的兼容性如下表：

记录锁（Record Locks）

• 记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
• 记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB也会隐式的创建一个索引，并使用这个索引实施记录锁。
• 会阻塞其他事务对其插入、更新、删除

记录锁的事务数据（关键词：lock_mode X locks rec but not gap），记录如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁（Gap Locks）

• 间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
• 使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
• 间隙锁只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。

间隙锁的事务数据（关键词：gap before rec），记录如下：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

Next-Key Locks

• Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

插入意向锁（Insert Intention）

• 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。
• 假设有索引值4、7，几个不同的事务准备插入5、6，每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了4、7之间的间隙，但是不阻塞对方因为插入行不冲突。

事务数据类似于下面：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

锁模式兼容矩阵（横向是已持有锁，纵向是正在请求的锁）：

如何读懂死锁日志？

show engine innodb status

可以用show engine innodb status，查看最近一次死锁日志哈~，执行后，死锁日志如下：

2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account  values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

我们如何分析以上死锁日志呢？

第一部分

1）找到关键词TRANSACTION，事务38048

2）查看正在执行的SQL

insert into account values(null,'Jay',100)

3）正在等待锁释放(WAITING FOR THIS LOCK TO BE GRANTED)，插入意向排他锁（lock_mode X locks gap before rec insert intention waiting），普通索引（idx_name），物理记录(PHYSICAL RECORD)，间隙区间（未知，Wei）;

第二部分

1）找到关键词TRANSACTION，事务38049

2）查看正在执行的SQL

insert into account  values(null,'Yan',100)

3）持有锁(HOLDS THE LOCK)，间隙锁(lock_mode X locks gap before rec)，普通索引(index idx_name)，物理记录(physical record)，区间（未知，Wei）;

 

4）正在等待锁释放(waiting for this lock to be granted)，插入意向锁(lock_mode X insert intention waiting)，普通索引上(index idx_name)，物理记录(physical record)，间隙区间（未知，+∞）;

5）事务1回滚(we roll back transaction 1)；

查看日志结果

查看日志可得：

• 事务A正在等待的插入意向排他锁（事务A即日志的事务1，根据insert语句来对号入座的哈），正在事务B的怀里~
• 事务B持有间隙锁，正在等待插入意向排它锁

这里面，有些朋友可能有疑惑，

• 事务A持有什么锁呢？日志根本看不出来。它又想拿什么样的插入意向排他锁呢？
• 事务B拿了具体什么的间隙锁呢？它为什么也要拿插入意向锁？
• 死锁的死循环是怎么形成的？目前日志看不出死循环构成呢？

我们接下来一小节详细分析一波，一个一个问题来~

死锁分析

死锁死循环四要素

• 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
• 请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
• 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
• 环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

事务A持有什么锁呢？它又想拿什么样的插入意向排他锁呢？

为了方便记录，例子用W表示Wei，J表示Jay，E表示Eason哈~

我们先来分析事务A中update语句的加锁情况~

update  account  set balance =1000 where name ='Wei';

间隙锁：

• Update语句会在非唯一索引的name加上左区间的间隙锁，右区间的间隙锁(因为目前表中只有name='Wei'的一条记录，所以没有中间的间隙锁~)，即（E,W) 和（W，+∞）
• 为什么存在间隙锁？因为这是RR的数据库隔离级别，用来解决幻读问题用的~

记录锁

• 因为name是索引，所以该update语句肯定会加上W的记录锁

Next-Key锁

• Next-Key锁=记录锁+间隙锁，所以该update语句就有了（E，W]的 Next-Key锁

综上所述，事务A执行完update更新语句，会持有锁：

• Next-key Lock：（E，W]
• Gap Lock ：（W，+∞）

我们再来分析一波事务A中insert语句的加锁情况

insert into account values(null,'Jay',100);

间隙锁：

• 因为Jay(J在E和W之间)，所以需要请求加(E,W)的间隙锁

插入意向锁（Insert Intention）

• 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(E,W)

因此，事务A的update语句和insert语句执行完，它是持有了 （E，W]的 Next-Key锁，（W，+∞）的Gap锁，想拿到 (E,W)的插入意向排它锁，等待的锁跟死锁日志是对上的，哈哈~

事务B拥有了什么间隙锁？它为什么也要拿插入意向锁？

同理，我们再来分析一波事务B，update语句的加锁分析：

update  account  set balance =1000 where name ='Eason';

间隙锁：

• Update语句会在非唯一索引的name加上左区间的间隙锁，右区间的间隙锁(因为目前表中只有name='Eason'的一条记录，所以没有中间的间隙锁~)，即（-∞，E）和（E，W）

记录锁

• 因为name是索引，所以该update语句肯定会加上E的记录锁

Next-Key锁

• Next-Key锁=记录锁+间隙锁，所以该Update语句就有了（-∞，E]的 Next-Key锁

综上所述，事务B执行完update更新语句，会持有锁：

• Next-key Lock：（-∞，E]
• Gap Lock ：（E，W）

我们再来分析一波B中insert语句的加锁情况

insert into account  values(null,'Yan',100);

间隙锁：

• 因为Yan(Y在W之后)，所以需要请求加(W,+∞)的间隙锁

插入意向锁（Insert Intention）

• 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即事务A需要插入意向锁(W,+∞)

所以，事务B的update语句和insert语句执行完，它是持有了 （-∞，E]的 Next-Key锁，（E，W）的Gap锁，想拿到 (W,+∞)的间隙锁，即插入意向排它锁，加锁情况跟死锁日志也是对上的~

死锁真相还原

接下来呢，让我们一起还原死锁真相吧~哈哈~

• 事务A执行完Update Wei的语句，持有（E，W]的Next-key Lock，（W，+∞）的Gap Lock ，插入成功~
• 事务B执行完Update Eason语句，持有（-∞，E]的 Next-Key Lock，（E，W）的Gap Lock，插入成功~
• 事务A执行Insert Jay的语句时，因为需要（E，W）的插入意向锁，但是（E，W）在事务B怀里，所以它陷入心塞~
• 事务B执行Insert Yan的语句时，因为需要(W,+∞) 的插入意向锁，但是(W,+∞) 在事务A怀里，所以它也陷入心塞。
• 事务A持有（W，+∞）的Gap Lock，在等待（E，W）的插入意向锁，事务B持有（E，W）的Gap锁，在等待(W,+∞) 的插入意向锁，所以形成了死锁的闭环~（Gap锁与插入意向锁会冲突的，可以看回锁介绍的锁模式兼容矩阵哈~）
• 事务A,B形成了死锁闭环后，因为Innodb的底层机制，它会让其中一个事务让出资源，另外的事务执行成功，这就是为什么你最后看到事务B插入成功了，但是事务A的插入显示了Deadlock found ~

总结

最后，遇到死锁问题，我们应该怎么分析呢？

• 模拟死锁场景
• show engine innodb status;查看死锁日志
• 找出死锁SQL
• SQL加锁分析，这个可以去官网看哈
• 分析死锁日志（持有什么锁，等待什么锁）
• 熟悉锁模式兼容矩阵，InnoDB存储引擎中锁的兼容性矩阵

MySQL添加新用户-ERROR 1045 (28000)解决办法

按照正常思维，创建用户和设置密码什么的，应该是一个动作完成的。然而事实并非如此。

我每次都是通过在网上找代码来创建MySQL的用户。如果无效，就再搜一段。
现在我心累了，必须写个自己的教程。

1，先要连接到MySQL，工具就是在终端上输入mysql。
一般来说可以设置几个参数，重要的是-u代表用户，比如root，或者guest，-p代表密码，-h代表地址，如果是localhost，可以省略。
例子 mysql --user user --host localhost --port 3306 --password
回车之后输入密码即可。
偶尔会遇到直接使用sudo mysql可以登录的情况。

2，选择mysql数据库。
use mysql;

3，创建用户，比如用户名是demo，则使用
create user demo;

4，给用户权限。
grant all on *.* to 'demo'@'localhost' identified by 'password' ;
这里是把操作数据库的所有权限给了demo，并且设置密码是password
如果希望该用户可以创建新的用户，并授权，就使用添加with grant option
grant all on *.* to 'demo'@'localhost' identified by 'password' with grant option;

5，刷新下。flush privileges;

 

问题：ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)
很久没用这台电脑的mysql了，今天需要连接数据库，启动数据库报错：
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

解决方法：
查阅资料后知道了，应该使用已知的用户名和密码登陆

mysql -u root -p

然而密码忘了，试了好几遍终于试出来了：

然后修改成通用的密码，不乱折腾了：

发现很多教程都过时了，我的版本是Server version: 8.0.19 MySQL ，最新的方法：

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
mysql> flush privileges;

退出验证下看看有没有成功：

可以看到修改密码成功了。

然后呢，我又假装密码忘了，把忘记密码的修改密码方法演示一下：
第一步：关闭Mysql服务
首先先停止mysql服务。可通过net stop mysql或者任务管理器中关闭。

以管理员权限操作：（这是第一个窗口）

 

 

第二步：跳过Mysql密码验证
进入命令提示符（管理员登陆）操作，进入mysql目录中bin文件夹下，mysql8.0与其他版本不同的地方在于无法直接使用mysqld --skip-grant-tables来跳过密码登录。在这我们使用mysqld -console --skip-grant-tables --shared-memory来跳过权限验证。

输入执行后没有反馈，新开一个管理员窗口重新执行。（这是第二个窗口）

进入目录后，确保自己已经关闭了Mysql的服务：net stop mysql

关闭Mysql服务之后，继续在D:\mysql-8.0.19-winx64\bin目录下进行操作：
输入

mysqld --console --skip-grant-tables --shared-memory
1
在输入这行代码之后，如下显示，我们就已经成功跳过Mysql的密码登录了：

第三步：无密码方式进入Mysql
在上述步骤之后，再打开一个管理员模式运行的cmd.exe （这是第三个窗口）

进入mysql下的bin目录后，直接登录mysql

不需要通过net start mysql打开mysql服务

在命令行中输入以下代码

d:
cd D:\mysql-8.0.19-winx64\bin(此处输入自己电脑上的安装目录)
mysql -u root -p

如图：

此时会显示让你输入密码，直接回车，就可以成功连接Mysql。

第四步：将登陆密码设置为空
输入代码，将密码设置为空（此时还不能直接修改密码，必须先设置为空，否则会报错）
输入：

use mysql; (使用mysql数据表)
update user set authentication_string='' where user='root';（将密码置为空）
quit; (然后退出Mysql)

操作如图：

第五步：更改自己的登陆密码
这里分为两个部分

1.关闭前两个cmd窗口(一定要关闭！)；
2.在第三个窗口中输入代码；
net stop mysql(关闭mysql服务,虽然会显示没有开启服务，但是以防万一)
net start mysql(再打开mysql服务)
1
2
(这里不要嫌麻烦，如果上一个mysql服务没关闭,我们依旧是无密码登陆)

操作如图：

接着输入：

cd D:\mysql-8.0.19-winx64\bin (此处输入自己电脑上的安装目录)
mysql -u root -p
(此处会显示输入密码，直接回车就好了，第四步我们已经将他置为空了)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';（更改密码）

如图：

最后一步：验证密码是否修改成功
输入：

quit（退出mysql）
mysql -u root -p
(输入新密码，再次登录)

图示：