mysql事务和锁的实践

我参与11月更文挑战的第20天，活动详情查看：2021最后一次更文挑战

概念解释:

脏读 : 就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据

不可重复读 ： 是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据,并且提交了修改。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读 :  是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql 的隔离级别

隔离级别 脏读 不可重复读 幻读 未提交读(Read uncommitted) 可能 可能 可能 已提交读(Read committed) 不可能 可能 可能 可重复读(Repeatable read) 不可能 不可能 不可能 可串行化(Serializable ) 不可能 不可能 不可能

实验如下：

第一次采用RC隔离级别

A窗口开启事务不提交，更新数据会锁住数据。

mysql> select * from class_teacher;

| id | classname | teacherid | | -- | ---------- | ---------- | | 1 | aaaa | 2 | | 2 | bbb | 3 |

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update class_teacher set class_name = 'ccc' where id = 1;

Query OK, 1 row affected (0.01 sec)\ Rows matched: 1 Changed: 1 Warnings: 0

B窗口更新数据会因为A窗口的锁提示失败。

mysql> update class_teacher set class_name = 'ddd' where id = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

B窗口插入数据则成功

mysql> insert into class_teacher values (3, 'eee', 4);

Query OK, 1 row affected (0.00 sec)

RC可以避免脏读，可以避免另一个事务对已有数据进行修改。\ 但无法避免\ 可重复读：如果A窗口查询了一次数据，B窗口对该数据进行修改并且提交，A窗口再次读取数据的时候，数据已经改变。\ 幻读：B窗口仍能插入新数据，B窗口插入新数据后，A窗口再次读取数据，数据就会改变

第二次采用RR隔离级别

A窗口开启事务，读取数据。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from class_teacher;

| id | classname | teacherid | | -- | ---------- | ---------- | | 1 | ddd | 2 | | 2 | bbb | 3 | | 3 | eee | 4 | | 4 | fff | 5 |

4 rows in set (0.00 sec)

B窗口开启事务，修改和插入数据，提交。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update class_teacher set class_name = 'ggg' where id = 1;

Query OK, 1 row affected (0.00 sec)\ Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into class_teacher values(5, 'kkk', 6);

Query OK, 1 row affected (0.00 sec)

mysql> select * from class_teacher;

| id | classname | teacherid | | -- | ---------- | ---------- | | 1 | ggg | 2 | | 2 | bbb | 3 | | 3 | eee | 4 | | 4 | fff | 5 | | 5 | kkk | 6 |

5 rows in set (0.00 sec)

mysql> commit;

A窗口再次读取数据，B窗口修改的数据不会影响到A，但新插入的数据影响到了A

mysql> select * from class_teacher;

| id | classname | teacherid | | -- | ---------- | ---------- | | 1 | ddd | 2 | | 2 | bbb | 3 | | 3 | eee | 4 | | 4 | fff | 5 |

4 rows in set (0.00 sec)

RR隔离级别可以避免脏读，可重复读和幻读(注：大部分资料都写着不能解决幻读，mysql内部其实已经解决了)

GAP锁\ A窗口事务未提交

mysql> update class_teacher set class_name = 'ass' where teacher_id = 2;

Query OK, 1 row affected (0.00 sec)\ Rows matched: 1 Changed: 1 Warnings: 0

B窗口事务

mysql> insert into class_teacher values(9,'asd',2);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into class_teacher values(10, 'asd', 10000);

Query OK, 1 row affected (0.00 sec)

一条成功，一条失败，mysql内部会将teacher_id=2的数据锁住