MySql 死锁

墨蓝 2023-10-06 18:41 2阅读 0赞

#### MySql 死锁 ####

*  一、什么是死锁
 *   *  InnoDB存储引擎定义了四种类型的行锁
     *  隔离等级对加锁的影响
     *  当前数据对加锁的影响
 *  二、为什么会形成死锁
 *   *  两阶段锁协议
     *  产生死锁的四个必要条件
 *  三、MySQL 如何处理死锁？
 *   *  杀死进程
     *  MySQL表
     *  间隙锁
     *  排他锁
     *  共享锁
     *  分析行锁定
     *  行锁优化
 *  四、如何避免发生死锁
 *   *  1.对索引加锁顺序的不一致很可能会导致死锁；
     *  2.Gap 锁往往是程序中导致死锁的真凶；
     *  3.为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大；
     *  4.我们知道 MyISAM 只支持表锁，它采用一次封锁技术来保证事务之间不会发生死锁，所以，我们也可以使用同样的思想，在事务中一次锁定所需要的所有资源，减少死锁概率；
     *  5.避免大事务；
     *  6.避免在同一时间点运行多个对同一表进行读写的脚本；
     *  7.设置锁等待超时参数：innodb\_lock\_wait\_timeout；
 *  五、死锁的影响
 *  六、如何发现死锁
 *   *  1.查询数据库进程
     *  2.查看当前的事务
     *  3.查看当前锁定的事务
     *  4.查看当前等锁的事务
 *  MySQL事务 autocommit 自动提交
 *   *  查看当前autocommit模式
     *  禁止使用当前会话的自动提交
     *  Mysql 查询正在执行的事务以及等待锁的操作方式
     *   *  查看正在执行的事务
     *  查看正在锁的事务
     *  查看等待锁的事务
     *  查询mysql数据库中存在的进程
 *  MySQL命令
 *   *  show engines;查看数据库存储引擎
     *  MYSQL 事务处理主要有两种方法：
     *  查看当前线程处理情况
     *  通过navicat中的【工具】=> 【服务器监控】进行查看结果如下
     *  记录死锁日志
     *  开启mysql慢查询和死锁日志
     *   *  显示慢查询状态及日志目录
         *  开启慢查询(说明: 1开启;0关闭;)
         *  显示慢查询阈值(单位秒)，默认执行时间超过10s才会被记录到日志
         *  设置慢查询阈值 (注意:设置后需要重新打开mysql客户端才能到最新的值)
         *  查看死锁的日志是否开启
         *  开启记录死锁
         *  InnoDB 行锁等待超时时间(默认为50秒)
         *  获取死锁日志信息
         *  日志中常见关键字样
         *  设置mysql 事务锁超时时间 innodb\_lock\_wait\_timeout
 *  共享锁、排他锁与意向锁
 *  MySQL事务查看
 *   *  1，查看数据库的隔离级别：
     *  2，去查看先当前库的线程情况：
     *  3，kill掉，线程ID;
     *  4，MySQL锁简写名称

## 一、什么是死锁 ##

官方定义如下：两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

这个就好比你有一个人质，对方有一个人质，你们俩去谈判说换人。你让对面放人，对面让你放人。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]  
共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（`即读读并行`）；

排他锁（X Lock）：允许事务删除或更新一行数据，多个事务有且只有一个事务可以拿到X锁（`即写写/写读互斥`）；

记录锁（LOCK\_REC\_NOT\_GAP）: lock\_mode X locks rec but not gap

间隙锁（LOCK\_GAP）: lock\_mode X locks gap before rec

Next-key 锁（LOCK\_ORNIDARY）: lock\_mode X  
Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

插入意向锁（LOCK\_INSERT\_INTENTION）: lock\_mode X locks gap before rec insert intention

### InnoDB存储引擎定义了四种类型的行锁 ###

*  **LOCK\_ORDINARY**：Next-Key 锁，锁一条记录及其之前的间隙，这是 RR 隔离级别用的最多的锁，从名字也能看出来；`lock_mode X`
 *  **LOCK\_GAP**：间隙锁，锁两个记录之间的 GAP，防止记录插入；`lock_mode X locks gap before rec`
 *  **LOCK\_REC\_NOT\_GAP**：记录锁 只锁记录；`lock_mode X locks rec but not gap`
 *  **LOCK\_INSERT\_INTENSION**：插入意向锁，插入意向 GAP 锁，插入记录时使用，是 LOCK\_GAP 的一种特例。`lock_mode X locks gap before rec insert intention`

### 隔离等级对加锁的影响 ###

<table> 
 <thead> 
  <tr> 
   <th> 隔离级别</th> 
   <th> 读数据一致性</th> 
   <th> 脏读</th> 
   <th> 不可重复读</th> 
   <th> 幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> 未提交读（Read uncommitted）</td> 
   <td> 最低级别，只能保证不读取物理上损坏的数据，事务可以看到其他事务没有被提交的数据（脏数据）</td> 
   <td> 是</td> 
   <td> 是</td> 
   <td> 是</td> 
  </tr> 
  <tr> 
   <td> 已提交度（Read committed）</td> 
   <td> 语句级，事务可以看到其他事务已经提交的数据</td> 
   <td> 否</td> 
   <td> 是</td> 
   <td> 是</td> 
  </tr> 
  <tr> 
   <td> 可重复读（Repeatable read）</td> 
   <td> 事务级，事务中两次查询的结果相同</td> 
   <td> 否</td> 
   <td> 否</td> 
   <td> 是</td> 
  </tr> 
  <tr> 
   <td> 可序列化（Serializable）</td> 
   <td> 串行</td> 
   <td> 否</td> 
   <td> 否</td> 
   <td> 否</td> 
  </tr> 
 </tbody> 
</table>

这里说明一下，`RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本`，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为 `快照读（Snapshot Read）`

MySQL 还提供了另一种读取方式叫`当前读（Current Read）`，它读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据语句和加锁的不同，又分成三种情况：

*  SELECT … LOCK IN SHARE MODE：加共享(S)锁
 *  SELECT … FOR UPDATE：加排他(X)锁
 *  INSERT / UPDATE / DELETE：加排他(X)锁

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：`RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题。`

### 当前数据对加锁的影响 ###

比如一条最简单的根据主键进行更新的 SQL 语句，如果主键存在，则只需要对其加记录锁，如果不存在，则需要在加间隙锁。

## 二、为什么会形成死锁 ##

看到这里，也许你会有这样的疑问，事务和谈判不一样，为什么事务不能使用完锁之后立马释放呢？

居然还要操作完了之后一直持有锁？

这就涉及到 MySQL 的并发控制了。

MySQL的并发控制有两种方式，一个是 MVCC，一个是两阶段锁协议。

那么为什么要并发控制呢？

是因为多个用户同时操作 MySQL 的时候，为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样（可串行化调度）。

### 两阶段锁协议 ###

官方定义：

两阶段锁协议是指所有事务必须分两个阶段对数据加锁和解锁，在对任何数据进行读、写操作之前，事务首先要获得对该数据的封锁；

在释放一个封锁之后，事务不再申请和获得任何其他封锁。

对应到 MySQL 上分为两个阶段：

*  扩展阶段（事务开始后，commit 之前）：获取锁
 *  收缩阶段（commit 之后）：释放锁

就是说呢，只有遵循两段锁协议，才能实现 可串行化调度。

但是两阶段锁协议不要求事务必须一次将所有需要使用的数据加锁，并且在加锁阶段没有顺序要求，所以这种并发控制方式会形成死锁。

### 产生死锁的四个必要条件 ###

（1） 互斥条件：一个资源每次只能被一个进程使用。  
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。  
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。  
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

## 三、MySQL 如何处理死锁？ ##

MySQL有两种死锁处理方式：

*  等待，直到超时（innodb\_lock\_wait\_timeout=50s）。
 *  发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb\_deadlock\_detect=on）。

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

回滚

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，`INFORMATION_SCHEMA.INNODB_TRX` 表中的 trx\_weight 字段来判断。

### 杀死进程 ###

通过以上方法一可以查询对应死锁的数据库进程,可以直接杀掉。

`kill 进程ID`

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。  
（2）避免事务中的用户交互。  
（3）保持事务简短并在一个批处理中。  
（4）使用低隔离级别。  
（5）使用绑定连接。

### MySQL表 ###

CREATE TABLE `book` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `isbn` varchar(255) DEFAULT '',
      `author` varchar(255) DEFAULT '',
      `score` decimal(11,2) DEFAULT '0.00',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('10', 'N001', 'Bob', '9.20');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('18', 'N002', 'ABob', '7.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('25', 'N003', 'CBob', '7.80');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('30', 'N004', 'DBob', '9.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('41', 'N005', 'EBob', '3.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('45', 'N006', 'F5Bob', '6.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('49', 'N006', 'willem', '6.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('60', 'N007', 'willem', '8.70');

### 间隙锁 ###

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；

对于键值在条件范围内但并不存在的记录，叫做”间隙(GAP)”。

InnoDB也会对这个”间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 1]  
`危害(坑)：若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。`

### 排他锁 ###

排他锁，也称写锁，独占锁，当前写操作没有完成前，它会阻断其他写锁和读锁。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 4]

### 共享锁 ###

共享锁，也称读锁，多用于判断数据是否存在，多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作，很可能会造成死锁。如下图所示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 5]

### 分析行锁定 ###

*  innodb\_row\_lock\_current\_waits: 当前正在等待锁定的数量
 *  innodb\_row\_lock\_time: 从系统启动到现在锁定总时间长度；非常重要的参数，
 *  innodb\_row\_lock\_time\_avg: 每次等待所花平均时间；非常重要的参数，
 *  innodb\_row\_lock\_time\_max: 从系统启动到现在等待最常的一次所花的时间；
 *  innodb\_row\_lock\_waits: 系统启动后到现在总共等待的次数；非常重要的参数。直接决定优化的方向和策略。

### 行锁优化 ###

1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。

2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。

3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。

4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

## 四、如何避免发生死锁 ##

对于 MySQL 的 InnoDb 存储引擎来说，死锁问题是避免不了的，没有哪种解决方案可以说完全解决死锁问题，但是我们可以通过一些可控的手段，降低出现死锁的概率。

收集死锁信息：

利用命令 `SHOW ENGINE INNODB STATUS`查看死锁原因。  
调试阶段开启`innodb_print_all_deadlocks`，收集所有死锁日志。

### 1.对索引加锁顺序的不一致很可能会导致死锁； ###

所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；

### 2.Gap 锁往往是程序中导致死锁的真凶； ###

由于默认情况下 MySQL 的隔离级别是 RR，所以如果能确定幻读和不可重复读对应用的影响不大，可以考虑将隔离级别改成 RC，可以避免 Gap 锁导致的死锁；

### 3.为表添加合理的索引，如果不走索引将会为表的每一行记录加锁，死锁的概率就会大大增大； ###

### 4.我们知道 MyISAM 只支持表锁，它采用一次封锁技术来保证事务之间不会发生死锁，所以，我们也可以使用同样的思想，在事务中一次锁定所需要的所有资源，减少死锁概率； ###

### 5.避免大事务； ###

尽量将大事务拆成多个小事务来处理；因为大事务占用资源多，耗时长，与其他事务冲突的概率也会变高；

### 6.避免在同一时间点运行多个对同一表进行读写的脚本； ###

特别注意加锁且操作数据量比较大的语句；我们经常会有一些定时脚本，应该避免它们在同一时间点运行；

### 7.设置锁等待超时参数：innodb\_lock\_wait\_timeout； ###

这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

## 五、死锁的影响 ##

当产生某表死锁的一开始,所有涉及这张表的操作都将受到阻塞。

假设这张表在业务逻辑上是读写频繁的，那就会使很多操作在那里排队等待，而排队等待会占用数据库连接，当该达到该数据库连接数的最大承载数之后，就会使所有数据库操作均无法再继续下去。

致使数据库各项指标异常，导致整个环境崩溃。在生产环境中出现这种问题，那是相当致命的，当发现数据库指标异常时因快速处理！

## 六、如何发现死锁 ##

### 1.查询数据库进程 ###

主要看State字段,如果出现大量 waiting for …lock 即可判定死锁：

SHOW FULL PROCESSLIST;

注意：需要拥有root组权限（supper），否则只能看到当前用户的进程，无法查询所有。

### 2.查看当前的事务 ###

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

### 3.查看当前锁定的事务 ###

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

### 4.查看当前等锁的事务 ###

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

## MySQL事务 autocommit 自动提交 ##

MySQL默认操作模式就是autocommit自动提交模式。

我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

### 查看当前autocommit模式 ###

show variables like 'autocommit';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 6]  
从查询结果中，我们发现Value的值是ON，表示autocommit开启。

当 `autocommit` 为开启状态时，即使没有手动 `start transaction` 开启事务，mysql默认也会将用户的操作当做事务即时提交。

例如，你执行了insert into test values(2)语句，mysql默认会帮你开启事务，并且在这条插入语句执行完成之后，默认帮你提交事务。

这时候可能有人会问了，那如果我手动开启了事务呢？

例如如下操作，开启事务并插入两条数据：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 7]  
由于A客户端没有提交，因此如果我们用B客户端去查询数据，会发现新插入的数据并没有被查询到：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 8]  
从上述的操作中我们可以明白，当autocommit为ON的情况下，并且又手动开启了事务，那么mysql会把start transaction 与 commit之间的语句当做一次事务来处理，默认并不会帮用户提交需要手动提交，如果用户不提交便退出了，那么事务将回滚。

### 禁止使用当前会话的自动提交 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 10]

### Mysql 查询正在执行的事务以及等待锁的操作方式 ###

使用navicat测试学习:  
首先使用 set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚)

mysql> show variables like 'autocommit';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> UPDATE `mysqldemo`.`user` SET `id`='14', `sex`='0', `age`='28', `province`='北京' WHERE (`id`='14');
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0

#### 查看正在执行的事务 ####

`SELECT * FROM information_schema.INNODB_TRX;`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 11]  
在打开一个执行update  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 12]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 13]  
根据这个事务的线程ID（trx\_mysql\_thread\_id）:

可以使用mysql命令：kill 线程id 杀掉线程

期间如果并未杀掉持有锁的线程：则第二个update语句提示等待锁超时

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 14]

### 查看正在锁的事务 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 15]

### 查看等待锁的事务 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 16]

### 查询mysql数据库中存在的进程 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 17]

## MySQL命令 ##

### show engines;查看数据库存储引擎 ###

SHOW ENGINES显示有关服务器的存储引擎的状态信息。这对于检查是否支持存储引擎或参见默认引擎特别有用。

默认InnoDB

mysql> show engines;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

### MYSQL 事务处理主要有两种方法： ###

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

1、用 BEGIN, ROLLBACK, COMMIT来实现

*  BEGIN 开始一个事务
 *  ROLLBACK 事务回滚
 *  COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

*  SET AUTOCOMMIT=0 禁止自动提交
 *  SET AUTOCOMMIT=1 开启自动提交

mysql> use RUNOOB;
    Database changed
    mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
    Query OK, 0 rows affected (0.04 sec)
     
    mysql> select * from runoob_transaction_test;
    Empty set (0.01 sec)
     
    mysql> begin;  # 开始事务
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> insert into runoob_transaction_test value(5);
    Query OK, 1 rows affected (0.01 sec)
     
    mysql> insert into runoob_transaction_test value(6);
    Query OK, 1 rows affected (0.00 sec)
     
    mysql> commit; # 提交事务
    Query OK, 0 rows affected (0.01 sec)
    ---------------------------------------------------------------------------------
    mysql>  select * from runoob_transaction_test;
    +------+
    | id   |
    +------+
    | 5    |
    | 6    |
    +------+
    2 rows in set (0.01 sec)
     
    mysql> begin;    # 开始事务
    Query OK, 0 rows affected (0.00 sec)
     
    mysql>  insert into runoob_transaction_test values(7);
    Query OK, 1 rows affected (0.00 sec)
     
    mysql> rollback;   # 回滚
    Query OK, 0 rows affected (0.00 sec)
     
    mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
    +------+
    | id   |
    +------+
    | 5    |
    | 6    |
    +------+
    2 rows in set (0.01 sec)
     
    mysql>

### 查看当前线程处理情况 ###

`SHOW FULL PROCESSLIST;`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 18]  
show full processlist 返回的结果是实时变化的，是对mysql链接执行的现场快照，所以用来处理突发事件非常有用。

一般用到 show processlist 或 show full processlist 都是为了查看当前 mysql 是否有压力，都在跑什么语句，当前语句耗时多久了，有没有什么慢 SQL 正在执行之类的。

### 通过navicat中的【工具】=> 【服务器监控】进行查看结果如下 ###

下面针对每列做下介绍：

Id：链接mysql 服务器线程的唯一标识，可以通过kill来终止此线程的链接。

User：当前线程链接数据库的用户

Host：显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户

db: 线程链接的数据库，如果没有则为null

Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）

Time: 线程处在当前状态的时间，单位是秒

State：显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成

Info: 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 19]

### 记录死锁日志 ###

MySQL 提供了一个系统参数 `innodb_print_all_deadlocks` 专门用于记录死锁日志，当发生死锁时，死锁日志会记录到 MySQL 的错误日志文件中。

set GLOBAL innodb_print_all_deadlocks=ON;

### 开启mysql慢查询和死锁日志 ###

#### 显示慢查询状态及日志目录 ####

show variables like '%slow_query_log%';

![在这里插入图片描述][8aef5995d7db458e83a4e849ba43b5fb.png]

#### 开启慢查询(说明: 1开启;0关闭;) ####

set global slow_query_log = 1;

#### 显示慢查询阈值(单位秒)，默认执行时间超过10s才会被记录到日志 ####

show variables like '%long_query%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 20]

#### 设置慢查询阈值 (注意:设置后需要重新打开mysql客户端才能到最新的值) ####

set global long_query_time = 0.8;

#### 查看死锁的日志是否开启 ####

show variables like "%innodb_print_all_deadlocks%";

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 21]

#### 开启记录死锁 ####

set global innodb_print_all_deadlocks=1

#### InnoDB 行锁等待超时时间(默认为50秒) ####

show variables like 'innodb_lock_wait_timeout'; -- 查看当前会话
    show global variables like 'innodb_lock_wait_timeout'; -- 查看全局设置

#### 获取死锁日志信息 ####

show engine innodb status

该命令可以用来获取死锁信息，但是它有个限制，只能拿到最近一次的死锁日志。如果想获得周期性的日志可以使用MySQL 提供的一套 InnoDb 监控机制。

死锁日志中列出了死锁发生的时间，以及导致死锁的事务信息（只显示两个事务，如果由多个事务导致的死锁也只显示两个），并显示出每个事务正在执行的 SQL 语句、等待的锁以及持有的锁信息等。

#### 日志中常见关键字样 ####

LOCK WAIT 表示事务正在等待锁
    n lock struct(s) 表示该事务的锁链表的长度为 n
    n row lock(s) 表示当前事务持有的行锁个数
    RECORD LOCKS 表示记录锁
    lock_mode X 表示该记录锁为排他锁
    insert intention waiting 表示要加的锁为插入意向锁，并处于锁等待状态

#### 设置mysql 事务锁超时时间 innodb\_lock\_wait\_timeout ####

Mysql数据库采用InnoDB模式，默认参数:innodb\_lock\_wait\_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错。

设置事务锁超时时间

mysql> SET GLOBAL innodb_lock_wait_timeout=7820;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_lock_wait_timeout | 7820  |
    +--------------------------+-------+
    1 row in set (0.00 sec)

## 共享锁、排他锁与意向锁 ##

共享锁与独占锁均 用于事务当中，随事务的结束而解除。

**共享锁（share lock）**

又称读锁，读取操作创建的锁。

一旦上锁，任何事务（包括当前事务）无法对其修改，其他事务可以并发读取数据，也可在对此数据再加共享锁。

语法：SELECT … LOCK IN SHARE MODE;

**排他锁（exclusive lock）**

又称写锁，如果事务对数据A加上排他锁后，则其他事务不可并发读取数据，也不能再对A加任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。

语法：SELECT … FOR UPDATE

**意向锁**

**InnoDB所用的表级锁**，其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。

InnoDB中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

**意向锁是InnoDB自动加的，不需要用户干预。**

InnoDB和Myisam不一样，可以同时支持行锁和表锁，

`select * from user where name = "libis" for update;` 其中name字段不是user表的索引  
这些情况下InnoDB都会上表锁。

## MySQL事务查看 ##

### 1，查看数据库的隔离级别： ###

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

### 2，去查看先当前库的线程情况： ###

mysql> show processlist;

查看innodb的事务表INNODB\_TRX，看下里面是否有正在锁定的事务线程，  
看看ID是否在show full processlist里面的sleep线程中，  
如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX;

### 3，kill掉，线程ID; ###

mysql> kill 9930577;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>

表数据量也不大，按照普通的情况来说，简单的update应该不会造成阻塞的，mysql都是autocommit，不会出现update卡住的情况，去查看下autocommit的值。

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

看到亮闪闪的0，这个设置导致原来的update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，当等待时间过长的时候，就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。

所以赶紧commit刚才执行的update语句，之后 set global autocommit=1;

### 4，MySQL锁简写名称 ###

SELECT ... LOCK IN SHARE MODE：加共享(S)锁
    SELECT ... FOR UPDATE：加排他(X)锁
    INSERT / UPDATE / DELETE：加排他(X)锁
    
    共享锁（S Lock）
    排他锁（X Lock）
    
    意向共享锁（IS）
    意向排他锁（IX）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/0faf43b545c14b758c6575b8d2a79be2.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/aa41eaaf52424a02a7bf58c29b4c4638.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/8f1ca9edb9d8446b873142fbf359fcc6.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/43fc73012e1b4c7897214cd195b39459.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/c5b23464a1164b3598c5c705c75e1f11.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/1d03cd47425e4470aacb8e8a450d7ebd.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/c42dbd18111e42d5b03df6fb5d9b7aa9.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/b276bf58956b48d180351a8f5cba1f37.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/d2b3c36dd499464abe395c1361ddb680.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/546b46f318674d4fb7eceee004ec0c47.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/b588170d78fb4240959f47ba3f5df820.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/ebc0ee38dd954f50abe2253d1ba1110f.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/485bf7baae5b4be287ba1decea1ec557.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/f7b6a5f7127340db95d47f6101837863.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/563deab4110944b78f5846e65890a063.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/48ba7145ba1d4421b2a3ca78e71a8038.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/3c6af9d540134d3fafc47edff1bfc1e2.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/07abc3f67b6547adb8e7dc814b0ad48b.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/686d0aaba29646248c6383d000a4a6b3.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 19]: https://img-blog.csdnimg.cn/f9d57c3ea0d04cbb8cb7f2cc26aa64df.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[8aef5995d7db458e83a4e849ba43b5fb.png]: https://img-blog.csdnimg.cn/8aef5995d7db458e83a4e849ba43b5fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 20]: https://img-blog.csdnimg.cn/3439f172e4db4a9c8fad69cae70d654f.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70 21]: https://img-blog.csdnimg.cn/4ea8bf7cade84e2cb517735524927215.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70