事务与并发控制

痛定思痛。 2023-07-01 08:59 3阅读 0赞

# 1 什么是事务  #

>  *  事务是数据库执行操作的最小逻辑单元
>  *  事务可以由一个SQL组成也可以由多个SQL组成
>  *  组成事务的SQL要么全执行成功要么全执行失败

START TRANSACTION / BEGIN
    	SELECT ...
    	UPDATE ...
    	INSERT ...
    	DELETE ...
    COMMIT / ROLLBACK

> 事务开启后只能执行DML操作（执行DDL会执行**COMMIT**操作会把这个事务结束掉）

--------------------

# 2 事务的特性 #

<table>  事务的特性  
 <tbody> 
  <tr> 
   <td style="width:184px;"> <p>特征</p> </td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td style="width:184px;"> <p>原子性（<span style="color:#f33b45;">A&nbsp;</span><a href="https://fanyi.baidu.com/#en/zh/Atomicity" rel="nofollow">Atomicity</a>）</p> </td> 
   <td>一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。</td> 
  </tr> 
  <tr> 
   <td style="width:184px;"> <p>一致性（<span style="color:#f33b45;">C&nbsp;</span><a href="https://fanyi.baidu.com/#en/zh/Consistency" rel="nofollow">Consistency</a>）</p> </td> 
   <td>在事务开始之前和事务结束以后，数据库的完整性没有被破坏。</td> 
  </tr> 
  <tr> 
   <td style="width:184px;"> <p>隔离性（<span style="color:#f33b45;">I&nbsp;</span><a href="https://fanyi.baidu.com/#en/zh/Isolation" rel="nofollow">Isolation</a>）</p> </td> 
   <td>事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见。</td> 
  </tr> 
  <tr> 
   <td style="width:184px;"> <p>持久性（<span style="color:#f33b45;">D&nbsp;</span><a href="https://fanyi.baidu.com/#en/zh/Durability" rel="nofollow">Durability</a>）</p> </td> 
   <td>事务一旦提交了，其结果就是永久性的，就算发生了宕机等事故，数据库也能将数据恢复。</td> 
  </tr> 
 </tbody> 
</table>

--------------------

# 3 并发带来的问题 #

## 3.1 脏读 ##

> 一个事务读取了另一个事务未提交的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70][]

##  3.2 不可重复读 ##

> 一个事务前后两次读取的同一数据不一致。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1][]

## 3.3 幻读 ##

> 指一个事务两次查询的结果集记录数不一致

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 2][]

--------------------

# 4 事务的隔离性 #

> **INNODB**的隔离级别：默认可重复读（SQL标准里面不能避免幻读，但是MySQL可以）

<table> 
 <tbody> 
  <tr> 
   <td style="width:318px;"> <p>隔离级别</p> </td> 
   <td style="width:87px;">脏读</td> 
   <td style="width:91px;">不可重复读</td> 
   <td style="width:91px;">幻读</td> 
   <td style="width:123px;">隔离性</td> 
   <td style="width:138px;">并发性</td> 
  </tr> 
  <tr> 
   <td style="width:318px;"> <p>顺序读（SERIALIZABLE）</p> </td> 
   <td style="width:87px;">N</td> 
   <td style="width:91px;">N</td> 
   <td style="width:91px;">N</td> 
   <td style="width:123px;">最高</td> 
   <td style="width:138px;">最低</td> 
  </tr> 
  <tr> 
   <td style="width:318px;"> <p>可重复读（REPEATABLE READ）</p> </td> 
   <td style="width:87px;">N</td> 
   <td style="width:91px;">N</td> 
   <td style="width:91px;"><span style="color:#f33b45;"><strong>N</strong></span></td> 
   <td style="width:123px;">&nbsp;</td> 
   <td style="width:138px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:318px;"> <p>读以提交（READ COMMITTED）</p> </td> 
   <td style="width:87px;">N</td> 
   <td style="width:91px;">Y</td> 
   <td style="width:91px;">Y</td> 
   <td style="width:123px;">&nbsp;</td> 
   <td style="width:138px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:318px;">读未提交（READ UNCOMMITTED）</td> 
   <td style="width:87px;">Y</td> 
   <td style="width:91px;">Y</td> 
   <td style="width:91px;">Y</td> 
   <td style="width:123px;">最低</td> 
   <td style="width:138px;">最高</td> 
  </tr> 
 </tbody> 
</table>

## 4.1 设置事务的隔离级别 ##

SET [PERSIST|GLOBAL|SESSION]
         TRANSACTION ISOLATION LEVEL
         {
          | READ UNCOMMITTED
          | READ COMMITTED
          | REPEATABLE READ
          | SERIALIZABLE
         }

## 4.2 查看当前SESSION的事务隔离级别 ##

SHOW VARIABLES LIKE '%iso%';

## 4.3 数据 ##

DROP DATABASE IF EXISTS tx;
    CREATE DATABASE tx;
    USE tx;

-- ----------------------------
    -- 账户表
    -- ----------------------------
    CREATE TABLE account (
      `id` INT (11) NOT NULL AUTO_INCREMENT COMMENT '账户表id',
      `username` VARCHAR (20) NOT NULL COMMENT '用户名',
      `deposit` DECIMAL (20, 2) DEFAULT NULL COMMENT '用户存款,单位是元,保留两位小数',
      PRIMARY KEY (`id`)
    ) ;

INSERT INTO account(`username`, `deposit`) VALUES ('蔡徐坤','1000');
    INSERT INTO account(`username`, `deposit`) VALUES ('乔碧萝','2000');
    INSERT INTO account(`username`, `deposit`) VALUES ('卢本伟','3000');
    INSERT INTO account(`username`, `deposit`) VALUES ('张三','4000');
    INSERT INTO account(`username`, `deposit`) VALUES ('李四','5000');
    INSERT INTO account(`username`, `deposit`) VALUES ('王五','6000');

SELECT * FROM account;

mysql> SELECT * FROM account;
    +----+-----------+---------+
    | id | username  | deposit |
    +----+-----------+---------+
    |  1 | 蔡徐坤    | 1000.00 |
    |  2 | 乔碧萝    | 2000.00 |
    |  3 | 卢本伟    | 3000.00 |
    |  4 | 张三      | 4000.00 |
    |  5 | 李四      | 5000.00 |
    |  6 | 王五      | 6000.00 |
    +----+-----------+---------+

UPDATE `account` SET `deposit` = 1000 WHERE `username` = '蔡徐坤' AND `id` = 1;
    UPDATE `account` SET `deposit` = 2000 WHERE `username` = '乔碧萝' AND `id` = 2;
    UPDATE `account` SET `deposit` = 3000 WHERE `username` = '卢本伟' AND `id` = 3;
    UPDATE `account` SET `deposit` = 4000 WHERE `username` = '张三' AND `id` = 4;
    UPDATE `account` SET `deposit` = 5000 WHERE `username` = '李四' AND `id` = 5;
    UPDATE `account` SET `deposit` = 6000 WHERE `username` = '王五'AND `id` = 6;

mysql -u root -p123456 tx

## 4.4 serializable 级别两个事务并发情况 ##

<table>  serializable 级别两个事务并发的情况  
 <thead> 
  <tr> 
   <th style="width:465px;">事务1</th> 
   <th style="width:433px;">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:465px;">SET SESSION TRANSACTION ISOLATION LEVEL <span style="color:#f33b45;">SERIALIZABLE </span>;</td> 
   <td style="width:433px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:465px;">BEGIN;</td> 
   <td style="width:433px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:465px;">SELECT * FROM account;</td> 
   <td style="width:433px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:465px;">&nbsp;</td> 
   <td style="width:433px;"># <span style="color:#f33b45;">修改前一个事务里面查询出来的数据</span></td> 
  </tr> 
  <tr> 
   <td style="width:465px;">&nbsp;</td> 
   <td style="width:433px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:465px;">&nbsp;</td> 
   <td style="width:433px;"> <p>UPDATE `account` SET `deposit` = `deposit` - 100 WHERE <strong>id = 1</strong>;</p> <p># 阻塞</p> </td> 
  </tr> 
  <tr> 
   <td style="width:465px;"> <p>ROLLBACK;</p> <p># 事务1执行了<strong><span style="color:#f33b45;">rollback;</span></strong>或者<span style="color:#f33b45;"><strong>commit;</strong></span>后 事务2才能继续执行</p> </td> 
   <td style="width:433px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:465px;">&nbsp;</td> 
   <td style="width:433px;">ROLLBACK;</td> 
  </tr> 
 </tbody> 
</table>

## 4.5 repeatable read 级别两个事务并发情况 ##

<table>  repeatable read 级别两个事务并发的情况  
 <thead> 
  <tr> 
   <th style="width:487px;">事务1</th> 
   <th style="width:411px;">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:487px;">SET SESSION TRANSACTION ISOLATION LEVEL <span style="color:#f33b45;">REPEATABLE READ</span> ;</td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">BEGIN;</td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 两条记录</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;"># 修改一条记录 让其满足<span style="color:#f33b45;">事务1</span>执行的SQL的条件</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;">UPDATE `account` SET `deposit` = 100 WHERE `id` = 3;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;">COMMIT ;&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p>#&nbsp; 无法读取到第<span style="color:#f33b45;">事务2</span>对数据的修改 还是两条记录</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>ROLLBACK ;</p> <p>#&nbsp; 结束当前事务</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p>#&nbsp; 能够查找到<span style="color:#f33b45;">事务2</span>修改的数据了 三条记录</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

## 4.5 read committed 级别两个事务并发情况 ##

<table>  read committed 级别两个事务并发的情况  
 <thead> 
  <tr> 
   <th style="width:487px;">事务1</th> 
   <th style="width:411px;">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:487px;">SET SESSION TRANSACTION ISOLATION LEVEL <span style="color:#f33b45;">READ COMMITTED;</span></td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">BEGIN;</td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 两条记录</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;"># 修改一条记录 让其满足<span style="color:#f33b45;">事务1</span>执行的SQL的条件</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;">UPDATE `account` SET `deposit` = 100 WHERE `id` = 3;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 两条记录</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">&nbsp;</td> 
   <td style="width:411px;">COMMIT ;&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 查询到了<span style="color:#f33b45;">事务1</span>修改的数据 三条记录</p> </td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:487px;">ROLLBACK ;</td> 
   <td style="width:411px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

## 4.6 read uncommitted 级别两个事务并发情况 ##

<table>  read uncommitted 级别两个事务并发的情况  
 <thead> 
  <tr> 
   <th style="width:507px;">事务1</th> 
   <th style="width:391px;">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:507px;">SET SESSION TRANSACTION ISOLATION LEVEL <span style="color:#f33b45;">READ UNCOMMITTED</span> ;</td> 
   <td style="width:391px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;">BEGIN;</td> 
   <td style="width:391px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 两条记录 蔡徐坤 乔碧萝</p> </td> 
   <td style="width:391px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;">&nbsp;</td> 
   <td style="width:391px;"># 修改一条记录 让其满足<span style="color:#f33b45;">事务1</span>执行的SQL的条件</td> 
  </tr> 
  <tr> 
   <td style="width:507px;">&nbsp;</td> 
   <td style="width:391px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;">&nbsp;</td> 
   <td style="width:391px;">UPDATE `account` SET `deposit` = 100 WHERE `id` = 3;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 三条记录</p> </td> 
   <td style="width:391px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;">&nbsp;</td> 
   <td style="width:391px;">ROLLBACK;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;"> <p>SELECT * FROM `account` WHERE&nbsp; `deposit` &lt;= 2000;</p> <p># 二条记录</p> </td> 
   <td style="width:391px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:507px;">ROLLBACK;</td> 
   <td style="width:391px;">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

## 4.7 repeatable read 级别下的阻塞 ##

<table>  repeatable read 级别下的阻塞  
 <thead> 
  <tr> 
   <th style="width:450px;">事务1</th> 
   <th style="width:454px;">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:450px;">SET SESSION TRANSACTION ISOLATION LEVEL<span style="color:#f33b45;"> REPEATABLE READ</span>;</td> 
   <td style="width:454px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:450px;">BEGIN;</td> 
   <td style="width:454px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:450px;">UPDATE `account` SET<span style="color:#f33b45;"> `deposit` = `deposit` + 100</span> WHERE `id` &lt;= 3;</td> 
   <td style="width:454px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:450px;">&nbsp;</td> 
   <td style="width:454px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:450px;">&nbsp;</td> 
   <td style="width:454px;"> <p>UPDATE `account` SET<span style="color:#f33b45;"> `deposit` = `deposit` - 100 </span>WHERE `id` &lt;= 3;</p> <p># 阻塞</p> </td> 
  </tr> 
  <tr> 
   <td style="width:450px;">ROLLBACK;</td> 
   <td style="width:454px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:450px;">&nbsp;</td> 
   <td style="width:454px;">ROLLBACK;</td> 
  </tr> 
 </tbody> 
</table>

> INNODB中的锁（一个事务不能对另一个事务**正在读取或修改的数据**进行修改）
> 
>  *  查询需要对资源加共享锁（S）（被加锁的对象只能被持有锁的事务读取但不能修改，其他事务无法对该对象进行修改，但也可以对该对象加共享锁进行读取）
>  *  数据修改需要对资源加排它锁（ⅹ）（被加了排他锁的对象只能被持有锁的事务读取和修改，其他事务无法读取修改被加了排他锁的对象）

--------------------

# 5 阻塞和死锁 #

## 5.1 演示阻塞现象 ##

> 什么是阻塞？
> 
>  *  由于不同锁之间的兼容关系，造成的一个事务需要等待另一个事务释放其所占用的资源后才能继续向下执行的现象

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 3][]

<table> 
 <thead> 
  <tr> 
   <th style="width:445px;">事务1</th> 
   <th style="width:453px;">事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:445px;">SET SESSION TRANSACTION ISOLATION LEVEL <span style="color:#f33b45;">REPEATABLE READ</span>;</td> 
   <td style="width:453px;">SET innodb_lock_wait_timeout = 3600 ;</td> 
  </tr> 
  <tr> 
   <td style="width:445px;"> <p>SELECT CONNECTION_ID();</p> <p>#当前连接的<span style="color:#f33b45;">连接id</span> 41</p> </td> 
   <td style="width:453px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:445px;">BEGIN;</td> 
   <td style="width:453px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:445px;">UPDATE `account` SET <span style="color:#f33b45;">`deposit` = `deposit` + 100 </span>WHERE `id` &lt;= 3;</td> 
   <td style="width:453px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:445px;">&nbsp;</td> 
   <td style="width:453px;"> <p>SELECT CONNECTION_ID();</p> <p>#当前连接的<span style="color:#f33b45;">连接id</span> 39</p> </td> 
  </tr> 
  <tr> 
   <td style="width:445px;">&nbsp;</td> 
   <td style="width:453px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:445px;">&nbsp;</td> 
   <td style="width:453px;"> <p>UPDATE `account` SET <span style="color:#f33b45;">`deposit` = `deposit` - 100 </span>WHERE `id` &lt;= 3;</p> <p>// 阻塞</p> </td> 
  </tr> 
  <tr> 
   <td style="width:445px;">KILL 39;</td> 
   <td style="width:453px;">// <span style="color:#f33b45;">ERROR 2013 (HY000): Lost connection to MySQL server during query</span></td> 
  </tr> 
  <tr> 
   <td style="width:445px;">ROLLBACK;</td> 
   <td style="width:453px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:445px;">&nbsp;</td> 
   <td style="width:453px;">ROLLBACK;</td> 
  </tr> 
 </tbody> 
</table>

>  mysql -u root -p123456 tx

-- 查询当前会话等待事务锁超时时间 默认50s
    SHOW VARIABLES LIKE 'innodb_lock_wait_timeout' ;
    -- 修改当前会话等待事务锁超时时间：
    SET innodb_lock_wait_timeout = 3600 ;

> ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

SELECT
      waiting_pid AS 'blocked pid',
      waiting_query AS 'blocked SQL',
      blocking_pid AS 'running pid',
      blocking_query AS 'running SQL',
      wait_age AS 'blocked time',
      sql_kill_blocking_query AS 'info'
    FROM
      sys.`innodb_lock_waits`
    WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(wait_started)) > 30 \G

*************************** 1. row ***************************                 
     blocked pid: 39                                                               
     blocked SQL: UPDATE `account` SET `deposit` = `deposit` - 100 WHERE `id` <= 3 
     running pid: 41                                                               
     running SQL: NULL                                                             
    blocked time: 00:01:11                                                         
            info: KILL QUERY 41                                                    
    1 row in set (0.00 sec)

> 如何处理阻塞？
> 
>  *  终止占用资源的事务 **KILL;**
>  *  优化占用资源事务的SQL，使其尽快释放资源

## 5.2 死锁 ##

### 5.2.1 什么是死锁 ###

> 并行执行的多个事务相互之间占有了对方所需要的资源

### 5.2.2 演示死锁 ###

> **set global innodb\_print\_all\_deadlocks = on ;**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 4][]

<table>  演示死锁  
 <thead> 
  <tr> 
   <th style="width:426px;">窗口1</th> 
   <th style="width:473px;">窗口2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:426px;">SET GLOBAL innodb_print_all_deadlocks = ON ;</td> 
   <td style="width:473px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;"># <span style="color:#f33b45;">退出在登陆</span>，使设置生效 mysql -u root -p123456 tx</td> 
   <td style="width:473px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;">SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';</td> 
   <td style="width:473px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;">BEGIN;</td> 
   <td style="width:473px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;">UPDATE `account` SET `deposit` = 100000000 WHERE <span style="color:#f33b45;">id = 1</span>;</td> 
   <td style="width:473px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;">&nbsp;</td> 
   <td style="width:473px;">BEGIN;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;">&nbsp;</td> 
   <td style="width:473px;">UPDATE `account` SET `deposit` = `deposit` + 10 WHERE<span style="color:#3399ea;"><strong> id = 3</strong></span>;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;"> <p>UPDATE `account` SET `deposit` = `deposit` + 10 WHERE<strong> <span style="color:#3399ea;">id = 3</span></strong>;</p> <p># 产生柱塞</p> </td> 
   <td style="width:473px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:426px;">&nbsp;</td> 
   <td style="width:473px;"> <p>UPDATE `account` SET `deposit` = 10 WHERE <span style="color:#f33b45;">id = 1</span>;</p> <p># <span style="color:#f33b45;">ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction</span></p> </td> 
  </tr> 
 </tbody> 
</table>

### 5.2.3 查看日志 ###

-- log_error就是错误日志的位置
    -- SHOW VARIABLES LIKE '%error%';
    SHOW VARIABLES LIKE 'log_error';

### 5.2.4 如何处理死锁 ###

>  *  数据库自行回滚占用资源少的事务
>  *  并发事务按相同顺序占有资源 （1->2->3->4 改为1->4->2->3）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200324232122431.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200324232340644.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200324232712201.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200325131015369.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200325150502943.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70