MySQL-隔离级别相关-20201020
|
session1 |
session2 |
session2是否被锁 |
|
begin; |
|
|
|
select * from t1 where id=1; |
|
|
|
|
drop table t1； |
hang |
|
|
update t1 set name='aaa' where id=1; |
hang |
|
|
begin; |
|
|
|
update t1 set name='aaa' where id=1; |
ok |
脚本如下：
1.session2未开事务，执行update操作
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where id=1;
+------+------+
| id | name |
+------+------+
| 1 | CCC |
+------+------+
1 row in set (0.47 sec)
session2
mysql> drop table t1; ---被hang住
session3
update t1 set name='AAA' where id=1; ---被hang住
2.session2新开事务，执行update
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set name='AAA' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
----成功执行
锁问题分析；
1.该sql语句对应的表的字段是否有主键
2.数据库的事务隔离级别
3.字段是否有索引
4.看执行计划
事务的隔离级别
|
read-uncommited |
读-未提交 |
|
read-commited |
读-已提交 |
|
repeatable read |
可重复读 |
|
serializable |
串读 |
查看当前的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
-
脏读模拟（隔离级别必须是 读取-未提交）
mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
mysql> select * from t1 where id=1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
+------+------+
2 rows in set (0.00 sec)
--session2修改数据
mysql> update t1 set name='AAA' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1 where id=1; --数据无故变更
+------+------+
| id | name |
+------+------+
| 1 | AAA |
+------+------+
1 row in set (0.00 sec)
-
不可重复读模拟（隔离级别必须是 读取-未提交）
会话1确认数据
mysql> select * from t1 where id=1;
+------+------+
| id | name |
+------+------+
| 1 | AAA |
+------+------+
1 row in set (0.00 sec)
会话2修改数据
mysql> update t1 set name='AABB' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
会话1确认数据已变更
mysql> select * from t1 where id=1;
+------+------+
| id | name |
+------+------+
| 1 | AABB |
+------+------+
1 row in set (0.00 sec)
会话1看的数据已由 AAA 变为 AABB
-
幻读模拟（隔离级别必须是 读取-未提交）
session1
mysql> select * from t1 where id>=1;
+------+------+
| id | name |
+------+------+
| 1 | AABB |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
session2新插入数据
mysql> insert into t1 values(3,'ccc');
Query OK, 1 row affected (0.00 sec)
session1再次确认数据
mysql> select * from t1 where id>=1;
+------+------+
| id | name |
+------+------+
| 1 | AABB |
| 2 | bbb |
| 3 | ccc |
+------+------+
3 rows in set (0.00 sec)
会话1看的数据已由 无故加了一条 3 ccc
-
可重复读模拟（隔离级别必须是 可重复读）
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
mysql> select name from t1 where id=1;
+------+
| name |
+------+
| AABB |
+------+
1 row in set (0.00 sec)
session2 修改数据
mysql> update t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session2确认数据已修改
mysql> select name from t1 where id=1;
+------+
| name |
+------+
| aaa |
+------+
1 row in set (0.00 sec)
session1再次确认结果
mysql> select name from t1 where id=1;
+------+
| name |
+------+
| AABB |
+------+
1 row in set (0.00 sec)
what happend？？？？
哈哈 牛逼的可重复读
当要update某条数据时，会先获得该行数据的排它锁 及该表的意向排它锁
当要select某条数据时，会先获得该行数据的共享锁 及该表的意向共享锁
MySQL默认隔离级别是 可重复读
