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