![f8777a4f42be985aa409ef281a4831ff.png](https://i-blog.csdnimg.cn/blog_migrate/d396325ed0ac233dfe22d3b7f87e70ea.jpeg)
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索引原理
![f5db65c771b1078786dc029cfc9c56ff.png](https://i-blog.csdnimg.cn/blog_migrate/e079c0a4189c0485656ba223f6217f6f.png)
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ÿ