mysql两个时间的时间差_MySQL：锁（InnoDB引擎）

最新推荐文章于 2022-04-15 07:52:56 发布

weixin_39687542

最新推荐文章于 2022-04-15 07:52:56 发布

阅读量149

点赞数

文章标签： mysql两个时间的时间差

本文链接：https://blog.csdn.net/weixin_39687542/article/details/111617307

版权

1、为什么写这篇文章

有个项目要上线，但是在做压测的时候频繁出现以下错误

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

然后通过如下命令查看死锁信息，发现有两个thread在彼此等待锁（最小复现模型详见第5节），于是就深入纠结了一下，才有了后续下文

 mysql>show engine innodb statusG

2、基础知识

2.1、InnoDB索引原理

2.2、唯一索引与非唯一索引

唯一索引：索引定义时带有UNIQUE关键字的索引（UNIQUE_IDX_TOTAL）

 CREATE TABLE `test` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `total` int(11) DEFAULT NULL,
   ` catalog ` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE `UNIQUE_IDX_TOTAL` (`total`)
 ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4

非唯一索引：索引定义时不带有UNIQUE关键字的索引（IDX_TOTAL）

 CREATE TABLE `test` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `total` int(11) DEFAULT NULL,
   `catalog` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `IDX_TOTAL` (`total`)
 ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4

2.3、索引方法（将在另外一篇文章介绍）

BTREE
HASH

2.4、索引类型

FULLTEXT
NORMAL
SPATIAL
UNIQUE

2.5、事务隔离级别（transaction isolation level）

Read Uncommitted：读取未提交内容

Read Committed：读取提交内容

Repeatable Read：一致性读（默认隔离级别）

示例1：T2读之前，T1已提交

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+
 | id | total |
 +----+-------+
 |  1 |    10 |
 |  2 |    15 |
 |  3 |    20 |
 +----+-------+
 3 rows in set (0.00 sec)
 
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-01
 mysql> insert into test(total) values(25);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> # step-02: T1 commit;
 mysql> commit;
 Query OK, 0 rows affected (0.09 sec)
 
 mysql> # T2 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> # step-03
 mysql> select * from test;
 +----+-------+
 | id | total |
 +----+-------+
 |  1 |    10 |
 |  2 |    15 |
 |  3 |    20 |
 | 21 |    25 |
 +----+-------+
 4 rows in set (0.00 sec)

示例2：T2第一次读，T1未提交；T2第二次读，T1已提交，在T1未提交前，T2的读取不包含T1提交的内容，T2在第二次读取（相同条件）依然不包含T1提交的内容（尽管此时T1已经提交完成），保证可重复读，实际上这个时候算是幻读了（数据并不是最新的状态）

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+
 | id | total |
 +----+-------+
 |  1 |    10 |
 |  2 |    15 |
 |  3 |    20 |
 +----+-------+
 3 rows in set (0.00 sec)
 
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-02
 mysql> insert into test(total) values(25);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> # step-03: T1 commit;
 mysql> commit;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # T2 bgin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 mysql> # step-01
 mysql> select * from test;
 +----+-------+
 | id | total |
 +----+-------+
 |  1 |    10 |
 |  2 |    15 |
 |  3 |    20 |
 +----+-------+
 3 rows in set (0.00 sec)
 mysql> # step-04 
 mysql> select * from test;
 +----+-------+
 | id | total |
 +----+-------+
 |  1 |    10 |
 |  2 |    15 |
 |  3 |    20 |
 +----+-------+
 3 rows in set (0.00 sec)

Serializable：串行化

2.6、MVCC

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间和行的删除时间，实际上存储的是事务ID（全系统递增且唯一）。然后通过很简单的算法就能实现MVCC，算法如下：

创建行时，将当前事务ID写入创建时间列
删除行时，将当前事务ID写入删除时间列
更新行时，将当前事务ID写入删除时间列，并新创建一个值一样的行（不包括这两个隐藏列），并将当前事务ID写入行

3、锁的类型

3.1、基础概念

表级锁：
行级锁：
Locking Read：
Consistent Read：

3.2、锁的类型

Shared Lock（S），Exclusive Lock (X)，Intention Exclusive Lock (IX)，Intention Shared Lock (IS)，Record Lock，GAP Lock，Next-Key Lock，Insert Intention Lock，AUTO-Inc Lock

3.3、S锁 & X锁

S锁（Shared Lock）：共享锁，允许持有该锁的事务对row进行读取

X锁（Exclusive Lock）：排他锁，允许持有该锁的事务对row进行更新、删除

如果T1（Transaction1，下同）在R（row）上持有S锁

T2在R上的S锁请求会立即被授权
T2在R上的X锁请求无法被立刻授权

如果T1在R上持有X锁

T2在R上的S、X锁请求都无法被立刻授权，需要等待T1释放X锁

3.4、IS锁 & IX锁

Intention Shared Lock (IS)：意向共享锁，表明事务准备在表上的某些行设置S锁

Intention Exclusive Lock (IX)：意向排他锁，表明事务准备在表上的某些行设置X锁

在T(Transaction)对表上的R(Row)设置S锁之前，必须先请求一个IS或更高级别的表锁

在T对表上的R设置X锁之前，必须先请求一个IX级别的表锁

3.5、X，S，IX，IS互斥矩阵

纵坐标：事务T1持有的锁；横坐标：事务T2申请的锁

3.6、Record Lock：行锁

作用于索引上，如果没有索引，则作用于隐藏的主键上（row id）

InnoDB引擎实现的是标准的行级锁，在行上，有两种锁类型：Shared Lock、Exclusive Lock

示例1

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  1 |    11 |      11 |
 |  2 |    15 |      15 |
 |  3 |    22 |      22 |
 +----+-------+---------+
 3 rows in set (0.00 sec)
 
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-01
 mysql> select * from test where id=2 lock in share mode;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  2 |    15 |      15 |
 +----+-------+---------+
 1 row in set (0.00 sec)
 mysql> # T2 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-02
 mysql> update test set total=16 where id=2;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-03
 mysql> update test set total=12 where id=1;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> # step-04
 mysql> update test set total=23 where id=3;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

示例2

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  1 |    12 |      11 |
 |  2 |    15 |      15 |
 |  3 |    23 |      22 |
 +----+-------+---------+
 3 rows in set (0.00 sec)
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> # step-01
 mysql> select * from test where id in (1,2,3) and catalog=15 lock in share mode;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  2 |    15 |      15 |
 +----+-------+---------+
 1 row in set (0.00 sec)
 
 mysql> # T2 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-02
 mysql> update test set total=11 where id=1;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-03
 mysql> update test set total=16 where id=2;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-04
 mysql> update test set total=21 where id=3;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.7、GAP Lock：间隙锁

锁定一定范围的行（不管行是否存在），具体锁定的行根据当前索引所处的位置决定
可作用于单列索引、复合索引(联合索引)以及空间索引
性能与并发的折忠方案
唯一索引列的搜索，不会发生GAP Lock，但如果是复合唯一索引并且只检索部分column，也会发生GAP Lock
两个进程可以对某个范围同时持有S GAP和X GAP（背后的机制比较复杂我也还没完全搞明白）

3.8、Next-KEY Lock & 机制

这是一个组合锁：Record Lock + GAP Lock，其中GAP Lock作用于包含row对应索引前后区间的GAP（对于唯一索引，不会发生Next-Key Lock）

假设有个索引，覆盖了值 10, 15, 20，则在这个索引上的Next-Key Lock有可能覆盖一下几个区间 (官方文档是左开右闭区间，测试结果左闭右开区间)

(负无穷, 10)
[10, 15)
[15, 20)
[20, 正无穷)

Next-KEY Lock的机制，与BTREE有关，具体不细说了，当发生了Next-KEY Lock时，非唯一索引处于GAP锁区间的值的行记录无法被插入。

锁定最左区间：

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  1 |    10 |      11 |
 |  2 |    15 |      15 |
 |  3 |    20 |      22 |
 +----+-------+---------+
 3 rows in set (0.00 sec)
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-01
 mysql> select * from test where total=10 lock in share mode;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  1 |    10 |      11 |
 +----+-------+---------+
 1 row in set (0.00 sec)
 
 mysql> # T2 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-02
 mysql> insert into test(total, catalog) values(9, 9);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-03
 mysql> insert into test(total, catalog) values(12, 12);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-04
 mysql> insert into test(total, catalog) values(17, 17);
 Query OK, 1 row affected (0.00 sec)

锁定中间区间：

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+--------+
 | id | total | catalog |
 +----+-------+--------+
 |  1 |    10 |      5 |
 |  2 |    15 |     21 |
 |  3 |    20 |     50 |
 +----+-------+--------+
 3 rows in set (0.00 sec)
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-01 
 mysql> select * from test where total=15 lock in share mode;
 +----+-------+--------+
 | id | total | catalog |
 +----+-------+--------+
 |  2 |    15 |     21 |
 +----+-------+--------+
 1 row in set (0.00 sec)
 mysql> begin ; # T2 begin
 Query OK, 0 rows affected (0.00 sec)
 mysql> # step-02
 mysql> insert into test(total, total) values(9, 9);
 Query OK, 1 row affected (0.00 sec)
 mysql> # step-03
 mysql> insert into test(total, total) values(10, 10);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-04 
 mysql> insert into test(total, total) values(12, 12);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-05
 mysql> insert into test(total, total) values(17, 17);
 mysql> # step-06
 mysql> insert into test(total, total) values(20, 20);
 Query OK, 1 row affected (0.00 sec)

锁定最右区间：

 mysql> # before T1 T2
 mysql> select * from test;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  1 |    10 |      11 |
 |  2 |    15 |      15 |
 |  3 |    20 |      22 |
 +----+-------+---------+
 3 rows in set (0.00 sec)
 mysql> # T1 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-01
 mysql> select * from test where total=20 lock in share mode;
 +----+-------+---------+
 | id | total | catalog |
 +----+-------+---------+
 |  3 |    20 |      22 |
 +----+-------+---------+
 1 row in set (0.00 sec)
 mysql> # T2 begin;
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> # step-02
 mysql> insert into test(total, catalog) values(15, 15);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-03
 mysql> insert into test(total, catalog) values(17, 17);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 mysql> # step-04
 mysql> insert into test(total, catalog) values(25, 25);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.9、Insert Intention Lock：插入意向锁

事务在INSERT操作之前会申请该锁，是一种GAP Lock，INSERT执行完毕之后锁即释放

如果不同事务插入的数据不在同一个位置时，他们无需等待彼此即可完成插入操作

 --事务1--
 mysql> select * from test; # before T1 T2
 +----+-------+--------+
 | id | total | number |
 +----+-------+--------+
 |  3 |    20 |     50 |
 | 25 |    25 |     25 |
 +----+-------+--------+
 2 rows in set (0.00 sec)
 mysql> begin; # T1 begin;
 Query OK, 0 rows affected (0.00 sec)
 mysql> select * from test where id > 3 for update;
 +----+-------+--------+
 | id | total | number |
 +----+-------+--------+
 | 25 |    25 |     25 |
 +----+-------+--------+
 1 row in set (0.00 sec)
 
 --事务2--
 mysql> # T2 begin;
 mysql> insert into test(id,total,number) values(5,5,5);
 
 --另一个终端查看  show engine innodb status--
 ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 25545 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 4626920 lock_mode X locks gap before rec insert intention waiting
 Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  0: len 4; hex 00000019; asc     ;;
  1: len 6; hex 0000004688f3; asc    F  ;;
  2: len 7; hex ae0000015f0110; asc     _  ;;
  3: len 4; hex 80000019; asc     ;;
  4: len 4; hex 80000019; asc     ;;

3.10、AUTO-Inc Lock：自增锁（表级锁）

如果表中包含AUTO_INCREMENT列，则事务T在这个表上的数据插入，可能需要申请AUTO-Inc Lock。有三种数据插入模式：

Simple Inserts：能在插入之前就提前确定需要产生AUTO_INCREMENT值的记录数量，包括如下语句
- INSERT INTO t VALUES (); //values未指定AUTO_INCREMENT列的值
- INSERT INTO t VALUES (), (), (); //values未指定AUTO_INCREMENT列的值
- REPLACE ….
Bulk Inserts：不能在插入之前就确定需要产生AUTO_INCREMENT值的记录数量，包括如下语句
- INSERT ... SELECT;
- REPLACE ... SELECT;
- LOAD DATA;
Mixed-Mode Inserts： Simple Inserts的特殊场景，部分有指定AUTO_INCREMENT值，部分未指定AUTO_INCREMENT值，包括如下语句
- INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d’);
- INSERT … ON DUPLICATE KEY UPDATE…;

Innodb_autoinc_lock_mode参数

0 (“traditional” lock mode)
1 (“consecutive” lock mode) [默认]
- Simple Inserts：使用Mutex来生成对应的自增ID
- Bulk Inserts：使用AUTO-Inc表级锁生成自增ID，语句执行完毕即释放，同一时间同一张表，只允许有一个事务持有AUTO-Inc锁
- Mixed-Mode Inserts：不使用Mutex来生成对应的自增ID(??)
- 特殊情况：如果已经有事务持有AUTO-Inc锁，则即使当前事务要执行的是Simple Inserts，也得等待AUTO-Inc被释放
2 (“interleaved” lock mode)

4、锁的使用场景

4.1、Consistent Read

覆盖语句：

SELECT ... FROM ...

结果：

Serializable 事务隔离级别，会对所有命中的结果设置S Next-Key Lock
非Serializable 事务隔离级别，不设置任何锁

4.2、Locking Read

覆盖语句：

SELECT ... FROM ... FOR UPDATE
SELECT ... FROM ... LOCK IN SHARE MODE
UPDATE ...
DELETE ...

结果：

唯一索引，只锁定命中的索引记录，不会发生GAP Lock
非唯一索引，对索引的扫描范围进行锁定，使用GAP Lock或Next-Key Lock

4.3、Update ... WHERE ...

覆盖语句：

UPDATE ... WHERE ...

结果：

唯一索引，对所有命中的行设置Record Lock
非唯一索引，对所有检索的行设置X Next-Key Lock

4.4、Insert ...

覆盖语句：

INSERT ...

结果：

针对插入的行，设置Index Record X Lock
插入之前会获取一个Insert Intention GAP Lock
如果发生duplicate key，则会设置一个Index Record S Lock

4.5、Insert ... ON Duplicate KEY Update

覆盖语句：

INSERT ... ON DUPLICATE KEY UPDATE

结果：

如果是主键发生duplicate key，则会设置一个Index Record X Lock
如果是唯一索引发生duplicate key，则会设置一个Next-Key X Lock

4.6、Replace ...

覆盖语句：

REPLACE ...

结果：

如果唯一索引没有冲突，同INSERT
如果唯一索引有冲突，则会设置一个Next-Key X Lock

4.7、Insert into T ... Select ... FROM S

覆盖语句：

INSERT INTO T SELECT ... FROM S WHERE ...

结果：

对于每一个插入T的记录，设置Index Record Lock
对于S表
- READ COMMITTED事务隔离级别，不加锁
- Innodb_locks_unsafe_for_binlog=1，不加锁
- 其他情况：S Next-Key Lock对于检索的行
特别的：UPDATE T … WHERE COL IN (SELECT … FROM S…)，对S中检索的记录使用S Next-Key Lock

4.8、Lock Tables

设置一个表锁，是在MySQL服务层的，不在InnoDB引擎级别，如果设置了参数innodb_table_locks=1 and autocommit=0，InnoDB引擎可感知表锁，并且MySQL服务层可感知行锁

5、背景的问题分析

5.1、死锁如何产生的（如下按照时间线顺序）

T2：begin;

T1：begin;

T2：insert into demo(abc_id, xyz_id, another_id) values(1000, 500, 10),(1000, 500, 11);

 T2插入的两行ID为34，35
 持有demo IX锁
 持有id=34 X锁
 持有id=35 X锁

T1：insert into demo(abc_id, xyz_id, another_id) values(2000, 501, 10),(2000, 501, 11);

 T1插入的两行ID为36，37
 持有demo IX锁
 持有id=36 X锁
 持有id=37 X锁

T2：insert into demo_copy(abc_id) select abc_id from demo where xyz_id=500;

 T2在xyz_id=500产生Next-Key S Lock，GAP区间为[499,500)+[500,501)

T1：insert into demo_copy(abc_id) select abc_id from demo where xyz_id = 501;

 T1在xyz_id=501产生Next-Key S Lock，GAP区间为[500,501)+[501,正无穷)

T2：insert into demo(abc_id, xyz_id, another_id) select abc_id, 502, another_id from demo where id in (34, 35) and another_id=10;

 T2：
 1、SELECT扫描两行,并对行设置Next-Key S LOCK
 2、SELECT返回第一个符合条件的行(where)
 3、INSERT 获取 AUTO-INC LOCK
 4、INSERT 获取 X Insert Intention锁，这里的xyz_id=502,事务T1持有 [501, 正无穷) 的GAP锁，所以只能等待T1释放
 此时, T1对表demo：
 持有IX锁
 持有AUTO-INC锁
 等待[501, 正无穷) 的GAP锁释放(T1持有)
    
 T2持有AUTO-INC LOCK等待T1[501, 正无穷)的GAP锁释放

T1：insert into demo(abc_id, xyz_id, another_id) select abc_id, 503, another_id from demo where id in (36, 37) and another_id = 11

 T1：
 1、SELECT扫描两行，并对行设置Next-Key S LOCK
 2、SELECT返回第一个符合条件的行(where)
 3、INSERT 获取AUTO-INC LOCK，同一时刻只能有一个事务持有AUTO-INC LOCK，所以此时T1只能等待T2释放AUTO-INC锁
 此时, T2对表demo：
 持有IX锁
 持有[501, 正无穷) 的GAP锁
 等待AUTO-INC锁的释放(T2持有)
    
 T1持有[501,正无穷)的GAP锁等待T2释放AUTO-INC LOCK

此时查看show engine innodb status，可以看到如下信息

 ------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2020-04-18 07:17:11 7fdfa7126700
 *** (1) TRANSACTION:
 TRANSACTION 47942519, ACTIVE 102 sec inserting
 mysql tables in use 2, locked 2
 LOCK WAIT 8 lock struct(s), heap size 1184, 8 row lock(s), undo log entries 5
 MySQL thread id 28, OS thread handle 0x7fdfa71a8700, query id 1416 172.24.0.1 root Sending data
 insert into demo(abc_id, xyz_id, another_id) select abc_id, 502, another_id from demo where id in (5, 6) and another_id=10
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 132638 page no 5 n bits 72 index `IDX_XYZ_ID` of table `test`.`demo` trx id 47942519 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;;
 
 *** (2) TRANSACTION:
 TRANSACTION 47942524, ACTIVE 94 sec setting auto-inc lock
 mysql tables in use 2, locked 2
 6 lock struct(s), heap size 1184, 7 row lock(s), undo log entries 4
 MySQL thread id 25, OS thread handle 0x7fdfa7126700, query id 1417 172.24.0.1 root Sending data
 insert into demo(abc_id, xyz_id, another_id) select abc_id, 503, another_id from demo where id in (7, 8) and another_id = 11
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 132638 page no 5 n bits 72 index `IDX_XYZ_ID` of table `test`.`demo` trx id 47942524 lock mode S
 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  0: len 8; hex 73757072656d756d; asc supremum;;
 
 Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  0: len 4; hex 800001f5; asc     ;;
  1: len 4; hex 80000007; asc     ;;
 
 Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  0: len 4; hex 800001f5; asc     ;;
  1: len 4; hex 80000008; asc     ;;
 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 TABLE LOCK table `test`.`demo` trx id 47942524 lock mode AUTO-INC waiting
 *** WE ROLL BACK TRANSACTION (2)

5.2、解决方案

分两步执行：先select，后insert

6、参考资料

MySQL官方手册

weixin_39687542

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql两个时间的时间差_MySQL：锁（InnoDB引擎）

1、为什么写这篇文章有个项目要上线，但是在做压测的时候频繁出现以下错误SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction然后通过如下命令查看死锁信息，发现有两个thread在彼此等待锁（最小复现模型详见第5节），于是就深入纠结了...
复制链接

扫一扫