1.行锁的三种算法

innoDB有三种行锁的算法：

①：Record lock:

  1.         单个行上的锁<br />    当事务的隔离级别为read committed的时，仅采用Record lock来加锁。

②：Gap lock:

间隙锁，所定义一个范围，但不包含记录本身

③：Next-key-lock :

         锁定一个范围，包含记录本身<br />当事务的隔离级别为 **repeatable-read **时存储引擎会自动采用Next-key-lock来加锁，这个时候，会帮助我们避免Phantom Problem(幻读)<br />  **Phantom Problem(幻读)：**<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/12862216/1623420058214-0464cf7f-60fe-4012-b7fd-d03038021cac.png#clientId=u89bf8d0c-d7dd-4&from=paste&height=53&id=uea03d3e4&margin=%5Bobject%20Object%5D&name=image.png&originHeight=105&originWidth=1113&originalType=binary&ratio=2&size=57336&status=done&style=none&taskId=ue25c1c32-faa4-43d3-883b-a513ec46592&width=556.5)<br />session A

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

mysql> select * from t where a>2;/*第一次读取*/
+------+
| a    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> select * from t where a>2;/*session Bcommit后读取*/
+------+
| a    |
+------+
|    5 |
|    4 |
+------+
2 rows in set (0.00 sec)

mysql>

session B 

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

mysql> insert into t select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql>


由于事务的隔离级别为 read committed时，会采用record lock 算法所以会造成幻读的现象，但是当事务的隔离级别为 repeatable-read 会采勇next-key-lock来加锁，所以就会避免幻读。
tips:

当列是唯一索引时，next-key-lock会自动降级为record key

2脏读

脏读：读一个事务中还没有提交的数据，只有在事务的隔离级别为read-uncommitted时才会出现
示列:
session A

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

mysql> use learn1;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where a>4;
+------+
| a    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> select * from t where a>4;/*读取到session中未提交的数据*/
+------+
| a    |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.00 sec)

mysql>

session B

mysql> use learn1;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t select 6;/*session B 中insert的数据 未提交*/
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>

3.不可重复读（幻读）

读已经提交的数据
示列
session A 

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

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
|    5 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
|    5 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t;/*当事务Session B提交后就会读取到session B插入的数据*/
+------+
| a    |
+------+
|    1 |
|    2 |
|    5 |
|    4 |
|    8 |
+------+
5 rows in set (0.00 sec)

mysql>

session B

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

mysql> insert into t select 8;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql>