Mysql 事务隔离级别 与 读出现的问题实验

读未提交：一个事务的隔离级别为‘读未提交’，它可以读取其他事务未提交的数据

读已提交：一个事务的隔离级别为‘读已提交’，它只可以读其他事务已提交的数据

可重复读：一个事务的隔离级别为‘可重复读’，不关心其他事务的操作，整个事务内读取的数据一致。

可能会出现幻读。

事务1: 可重复读

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set, 1 warning (0.00 sec)

事务2:可重复读

mysql> select @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set, 1 warning (0.00 sec)

事务1: 开启事务，查询，

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | dd | NULL |

+----+------+------+

4 rows in set (0.00 sec)

事务2:开启事务，查询，插入，查询，提交事务

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | dd | NULL |

+----+------+------+

4 rows in set (0.00 sec)

mysql> insert into account (id, name) values(5, 'ee');

Query OK, 1 row affected (0.00 sec)

mysql> select * from account;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | dd | NULL |

| 5 | ee | NULL |

+----+------+------+

5 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

事务1:查询，插入

mysql> select * from account;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | dd | NULL |

+----+------+------+

4 rows in set (0.00 sec)

mysql> insert into account (id, name) values(5, 'ee');

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

事务1没有查询到 (5, 'ee')这条，但在插入的时候，报错：重复

可串型化：一个事务的隔离级别为‘可串型化’，对自己关心的数据加上锁，别的事务在操作这些数据的时候会等待，直到‘可串型化’事务提交。

事务3:可串型化，开启事务，查询

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | dd | NULL |

| 5 | ee | NULL |

+----+------+------+

5 rows in set (0.00 sec)

事务2:查询，插入

mysql> select * from account;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | dd | NULL |

| 5 | ee | NULL |

+----+------+------+

5 rows in set (0.00 sec)

mysql> insert into account (id, name) values(6, 'ff');

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

mysql> update account set age=10 where name='aa';

‘可串型化’事务，对自己关心的数据加锁，不允许修改

// session 1: 开启事物、查询、更新某一行、查询

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from book;

+--------+------+

| bookid | card |

+--------+------+

| 23 | 2 |

| 10 | 3 |

| 6 | 4 |

| 16 | 4 |

| 9 | 5 |

| 4 | 6 |

| 14 | 6 |

| 3 | 7 |

| 8 | 7 |

| 20 | 7 |

| 1 | 9 |

| 5 | 9 |

| 12 | 10 |

| 17 | 10 |

| 2 | 12 |

| 13 | 12 |

| 7 | 16 |

| 19 | 16 |

| 15 | 17 |

| 18 | 17 |

| 11 | 20 |

+--------+------+

21 rows in set (0.00 sec)

mysql> update book set card = 3 where bookid = 23;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from book;

+--------+------+

| bookid | card |

+--------+------+

| 10 | 3 |

| 23 | 3 |

| 6 | 4 |

| 16 | 4 |

| 9 | 5 |

| 4 | 6 |

| 14 | 6 |

| 3 | 7 |

| 8 | 7 |

| 20 | 7 |

| 1 | 9 |

| 5 | 9 |

| 12 | 10 |

| 17 | 10 |

| 2 | 12 |

| 13 | 12 |

| 7 | 16 |

| 19 | 16 |

| 15 | 17 |

| 18 | 17 |

| 11 | 20 |

+--------+------+

21 rows in set (0.00 sec)

// session 2: 查询、更新其他行、更新 session 1操作的行

mysql> select * from book;

+--------+------+

| bookid | card |

+--------+------+

| 23 | 2 |

| 10 | 3 |

| 6 | 4 |

| 16 | 4 |

| 9 | 5 |

| 4 | 6 |

| 14 | 6 |

| 3 | 7 |

| 8 | 7 |

| 20 | 7 |

| 1 | 9 |

| 5 | 9 |

| 12 | 10 |

| 17 | 10 |

| 2 | 12 |

| 13 | 12 |

| 7 | 16 |

| 19 | 16 |

| 15 | 17 |

| 18 | 17 |

| 11 | 20 |

+--------+------+

21 rows in set (0.00 sec)

mysql> update book set card = 2 where bookid =10;

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update book set card = 2 where bookid =23;

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

Innodb 默认为行锁，一个shiwu

 原文地址

https://blog.csdn.net/wjb214149306/article/details/82431042