隔离级别之最佳实践-亲试ok-20201020
以下共测试4个case，分别为 读未提交、读已提交、可重复读、散列读
#会话1修改数据
#会话2确认数据
结论：会话2看到了已修改但未提交的数据，符合预期
此case即 脏读
#REPEATABLE-READ 不可重复读
#确认目前的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
会话1确认数据
会话2更新数据
会话1再次确认数据
结论：会话1看到了已修改但未提交的数据，符合预期
此case即 不可重复读
#幻读
会话1确认数据
会话2修改数据
会话1再次确认数据
看到了未提交的新增的数据，即 幻读
会话3
查看此时数据库中被锁住的事务
mysql> SELECT r.trx_id waiting_trx_id,
-> r.trx_mysql_thread_id waiting_thread,
-> r.trx_query waiting_query,
-> b.trx_id blocking_trx_id,
-> b.trx_mysql_thread_id blocking_thread,
-> b.trx_query blocking_query
-> FROM information_schema.innodb_lock_waits w
-> INNER JOIN information_schema.innodb_trx b ON
-> b.trx_id = w.blocking_trx_id
-> INNER JOIN information_schema.innodb_trx r ON
-> r.trx_id = w.requesting_trx_id;
+----------------+----------------+---------------------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+---------------------------------------------------+-----------------+-----------------+----------------+
| 1798 | 2 | select * from tab02 where id=5 lock in share mode | 1797 | 1 | NULL |
+----------------+----------------+---------------------------------------------------+-----------------+-----------------+----------------+
在普通字段上添加的锁 最终都要附加到 主键字段上，哈哈
so 第二个操作被阻塞
case2
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab02 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.01 sec)
#哈哈 我把 name='C'这条数据加了一个排他锁
会话2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tab02 values(6,'eee');
#why？ 我要插入id=6 name='eee'这条数据，为啥也是被锁呢？
case3
|
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab02 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.01 sec)
会话2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tab02 values(2,'eee');
hang住
|
|
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab02 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tab02 values(8,'F');
hang住
|
|
会话1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab02 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.01 sec)
会话2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tab02 values(6,'G');
hang住
|
创建时间：2021-12-22 10:36
넶浏览量：0
