mysql 事务测试

数据库表 t_student 里的数据如下

mysql> select * from t_student;
+----+-----+-------+
| id | age | sname |
+----+-----+-------+
|  1 |  10 | river |
|  2 |  20 | fank
  |
|  3 |  30 | lucy  |
+----+-----+-------+
3 rows in set

打开两个console 测试隔离性。

1 隔离性测试

A 开启事物，对第一条数据进行修改

mysql> begin ;
Query OK, 0 rows affected
mysql> update t_student set age = 11 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B 查看表数据

mysql> select * from t_student;
+----+-----+-------+
| id | age | sname |
+----+-----+-------+
|  1 |  10 | river |
|  2 |  20 | fank
  |
|  3 |  30 | lucy  |
+----+-----+-------+
3 rows in set

B 看不对 A 未提交事物的更改。

如果B 开启了事物查看到的也是相同的结果

mysql> begin;
Query OK, 0 rows affected

mysql> select * from t_student;
+----+-----+-------+
| id | age | sname |
+----+-----+-------+
|  1 |  10 | river |
|  2 |  20 | fank
  |
|  3 |  30 | lucy  |
+----+-----+-------+
3 rows in set

如果B对 第一条数据更改呢

update t_student set age = 11 where id = 1;

B 被阻塞主了。
如果A长时间不提交B就会报异常

mysql> update t_student set age = 12 where id = 1;
1205 - Lock wait timeout exceeded; try restarting transaction

但是此时B 还在自己的事务里必须提交后才能看到A的变化。
B的更新 有没有 被执行？
被执行了。mysql 自己 try restarting transaction

2 更新被锁住

A 按照条件更新（非主键）

mysql> begin;
Query OK, 0 rows affected

mysql> update t_student set sname = 'age11'  where age = 11;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B 插入数据时候 被阻塞

mysql> INSERT INTO river.t_student (age, sname) VALUES(33, 'tom');
1205 - Lock wait timeout exceeded; try restarting transaction

update 也被阻塞了

mysql> update t_student set age = 44 where id = 3;
1205 - Lock wait timeout exceeded; try restarting transaction

这里的锁 锁住了整张表。

3 间隙锁测试

表里数据

mysql> select * from t_student;
+----+-----+-------+
| id | age | sname |
+----+-----+-------+
|  1 |  11 | age11 |
|  2 |  20 | fank
  |
|  3 |  30 | lucy  |
|  6 |  33 | tom   |
|  9 |  33 | tom   |
| 10 |  33 | tom   |
+----+-----+-------+
6 rows in set

A表更新数据

mysql> begin;
Query OK, 0 rows affected

mysql> update t_student set sname = 'river' where id > 6 and id < 10
;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

B 表插入数据 id 为 8 ，刚好在（6，10）范围内

mysql> INSERT INTO t_student (id,age, sname) VALUES(7,33, 'tom');

被锁住
如果不在 （6,10）的范围内呢

mysql> INSERT INTO t_student (id,age, sname) VALUES(20,36, 'tom');
Query OK, 1 row affected

没有被锁住。