MySQL事务隔离级别之REPEATABLE-READ
考虑这样一个业务情境.....
事务A:用户查询余额为4000元,存入2000,写入数据库,余额6000。
事务B:银行系统在月初,要进行扣费,扣费500。
建表语句:
CREATE TABLE t1 (
a int not null,
b int not null,
PRIMARY KEY (a)
);
事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 ;
+----+------+
| a | b |
+----+------+
| 51 | 2000 |
| 52 | 1234 |
| 53 | 5999 |
| 54 | 5000 |
| 55 | 6000 |
| 56 | 7000 |
| 57 | 5000 |
| 60 | 4000 |
| 61 | 2000 |
+----+------+
9 rows in set (0.00 sec)
mysql> update t1 set b = 6000 where a = 60;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1 where a = 60;
+----+------+
| a | b |
+----+------+
| 60 | 6000 |
+----+------+
1 row in set (0.00 sec)
事务A 查询到a=60的记录,然后更新a = 60 的记录的值为6000,此时事务还没有提交。。。
对应的刚才的业务场景就是月初我要在余额4000 的基础上 存入2000,最终余额为 6000。。。。
事务B
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 60;
+----+------+
| a | b |
+----+------+
| 60 | 4000 |
+----+------+
1 row in set (0.01 sec)
mysql> update t1 set b = 3500 where a = 60;
lock wait...
在事物B中,查询a = 60 的记录,值为 4000,更新a = 60 的字段值为 3500。。。
对应刚才的业务场景是月初银行要进行扣税,在余额4000的基础上扣掉500,最终余额会为3500。
事务A
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
事务A提交了,事务B的阻塞会消失,事务A对该记录的write lock解除。
事务B
Query OK, 1 row affected (25.46 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1 where a = 60;
+----+------+
| a | b |
+----+------+
| 60 | 3500 |
+----+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
事务B修改数据,并且提交事务。
此时,这个用户,查询余额,看看账户还剩多少钱。。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 60;
+----+------+
| a | b |
+----+------+
| 60 | 3500 |
+----+------+
1 row in set (0.00 sec)
mysql>
还剩3500,什么!!!!!这还了得,本来是4000+2000,居然余额成了3500了,这就是大问题了。
那么如何避免上述情况
基本思路:就是要给修改的行加排它锁,排它锁会阻塞读锁和写锁。
事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 51;
+----+------+
| a | b |
+----+------+
| 51 | 4000 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from t1 where a = 51 for update;
+----+------+
| a | b |
+----+------+
| 51 | 4000 |
+----+------+
1 row in set (0.00 sec)
mysql> update t1 set b = 6000 where a = 51;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1 where a = 51;
+----+------+
| a | b |
+----+------+
| 51 | 6000 |
+----+------+
1 row in set (0.00 sec)
完成存款4000+2000;事务没有提交。
事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 51;
+----+------+
| a | b |
+----+------+
| 51 | 4000 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from t1 where a = 51 lock in share mode;
Ctrl-C -- sending "KILL QUERY 2" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from t1 where a = 51 for update;
......................
此时在事物B中查询 a = 51 的记录,可以看到值为4000,如果在这个余额的基础上银行进行扣款500,然后set进数据库,那就会覆盖之前用户已写入的余额6000。
select * from t1 where a = 51; 这条语句那为什么能查询到数据呢?而为什么select * from t1 where a = 51 lock in share mode;又为什么会被阻塞。
在事物A中给 a = 51 的记录 加了 排它锁(写锁),这个锁是排斥其他的读锁和写锁的,lock in share mode 这是显示的给select 加了读锁,所以被排斥。
而前者 select 没有给记录加锁 ,为什么能够读到 被锁住的记录,这是因为mysql的默认的事务隔离级别是可重复读,也就是读到的是快照数据。
select * from t1 where a = 51 for update;是给那条记录加了写锁的,所以会被事务A的写锁排斥。
当过一段时间后,该锁会超时,如下
mysql> select * from t1 where a = 51 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
继续往下看。
事务A
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
事务A提交后,排它锁消失。
事务B
当事务A提交后可以通过select * from t1 where a = 51 for update;查询到结果,此时也给该记录加了写锁,也就是排它锁。
.......阻塞消失,可以查到数据........
+----+------+
| a | b |
+----+------+
| 51 | 6000 |
+----+------+
1 row in set (21.75 sec)
mysql> update t1 set b = 5500 where a = 51;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
完成扣款操作6000-500;
这时候,用户查查多少钱
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 51;
+----+------+
| a | b |
+----+------+
| 51 | 5500 |
+----+------+
1 row in set (0.00 sec)
也不对啊,不是6000吗,他又查查了账户明细,才知道是月初扣款了,对了,最终余额就是5500,就是这样。
=============END=============