Mysql事务学习笔记
Mysql事务学习笔记
1、事务概述
事务是数据库的执行单元,它包含了一条或多条sql语句,进行的操作是要么全部执行,要么全部都不执行。
2、事务执行命令
语法格式:
start transaction;
//sql语句
commit;
mysql> start transaction;
Query OK, 0 rows affected (0.09 sec)
mysql> update user set money=money+100 where username = 'lxy';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money-100 where username = 'Rlxy93';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.09 sec)
在Mysql中,默认使用的是自动提交。
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.11 sec)
即如果没有直接start transaction,执行的每条sql语句都会被当成一个事务来操作。
如果关闭了autocommit,那么执行的所有sql语句都在一个事务中,
mysql> update user set money=money+100 where username = 'lxy';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money-100 where username = 'Rlxy93';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.09 sec)
如果使用了一些特殊的命令,那么Mysql会强制提交事务。比如create table/drop table/alter table/lock tables等语句。
3、ACID
ACID是事务的四个特性,其中包括原子性、一致性、隔离性、持久性。
3.1、A(原子性)
原子性是事务的基本工作单位,要么全部执行,要么全部不执行,如果执行失败则回滚,执行成功则提交。
原子性主要是靠Mysql日志中的事务日志来操作的。
事务日志分为两种:redo log和undo log。
【redo log】
Mysql对数据进行修改的时候,需要将数据从磁盘读取到buffer pool中,然后对buffer pool进行修改,这时,buffer pool的数据和磁盘上的数据不一致,如果在修改时出现故障,那么会导致修改的数据丢失,这时需要一个记录buffer pool修改记录的文件,它就是redo log。
当机器发生故障的时候,由于修改记录已经保存到这个redo log上了,就可以根据这个文件上记录的修改记录,还原发生故障前的数据。
比如,将name="lxy"的记录修改成name="rlxy93",这时,redo log保存的时候会将name="rlxy93"保存,如果机器出现故障,重启后将redo log中的name="rlxy93"恢复即可。
注意:buffer pool中的数据写入到磁盘时是一个随机IO,效率较低。而buffer pool中的修改记录写入到redo log是一个顺序IO,效率较高。
【undo log】
和redo log相反,undo log记录的是对buffer pool操作之前的数据,以保证事务回滚。
比如,将name="lxy"的记录修改成name="rlxy93",这时,redo log保存的时候会将name="rlxy93"保存,而undo log保存的是name="lxy",在进行回滚操作时,undo log就可以起到作用。
3.2、D(持久性)
持久性是指事务提交以后,对数据的改变便是永久性的。
持久性主要依靠redo log来实现。
测试:开启事务修改数据。
数据表准备:
mysql> start transaction;
Query OK, 0 rows affected (0.34 sec)
mysql> update ADMIN set FEE = FEE + 100 where id = 1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
此时,数据修改还没有持久化到数据文件。
mysql> commit;
Query OK, 0 rows affected (0.09 sec)
使用commit提交后,数据库被修改。
3.3、I(隔离性)
隔离性是指一个事务的操作和其他的事务的操作是隔离的,各个事务之间没有干扰。
【锁】
由于隔离性要求同一时间只能有一个事务对数据进行操作,InnoDB通过锁来控制。
锁:事务在修改数据之前,需要先请求得到一个锁,得到锁之后才能对数据进行操作,在执行期间,其他事务只能等它操作完成之后释放锁才能得到一个新的锁。
【行锁和表锁】
锁分为行锁、表锁和介于二者之间的页锁。
顾名思义,行锁在操作数据时只锁定需要操作的数据,表锁是在操作数据时会锁定整张表。出于性能的考虑,大部分的情况下都是使用的行锁。
【测试表锁】
A客户端加锁,并查询ADMIN表:
mysql> lock table ADMIN write;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
| 2 | userid | 2019-12-20 | 10 | 10 |
+----+-------------------------+------------+-----+------+
2 rows in set (0.13 sec)
B客户端直接查询ADMIN表:
mysql> select * from ADMIN;
B客户端这时直接卡住了,需要等待A客户端释放锁。
A客户端释放锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.89 sec)
B客户端立即查询到数据:
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
| 2 | userid | 2019-12-20 | 10 | 10 |
+----+-------------------------+------------+-----+------+
2 rows in set (114.20 sec)
【测试行锁】
A客户端开启事务,并修改id为1的数据:
mysql> start transaction;
Query OK, 0 rows affected (0.70 sec)
mysql> update ADMIN set FEE = 0 where id = 1;
Query OK, 1 row affected (0.24 sec)
Rows matched: 1 Changed: 1 Warnings: 0
B客户端直接修改id为1和2的数据:
mysql> update ADMIN set FEE = 1000 where id = 1;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> update ADMIN set FEE = 1000 where id = 2;
Query OK, 1 row affected (0.68 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这时会提示id为1的记录已经被锁。需要等事务结束,即事务提交。
而id为2的记录可以直接修改。
A客户端提交事务:
mysql> commit;
Query OK, 0 rows affected (0.71 sec)
这时,B客户端再次修改id为1的记录:
mysql> update ADMIN set FEE = 1000 where id = 1;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这时才修改成功,表示A客户端在修改时,使用的是行锁。
【行锁自动变成表锁】
对ADMIN表设置两个索引。
在A客户端中开启事务,并根据条件修改数据:
mysql> start transaction;
Query OK, 0 rows affected (0.17 sec)
mysql> update ADMIN set FEE = 0 where FEE = 1000;
Query OK, 0 rows affected (0.16 sec)
Rows matched: 0 Changed: 0 Warnings: 0
在B客户端中修改id为2的记录:
mysql> update ADMIN set FEE = 1000 where id = 2;
1205 - Lock wait timeout exceeded; try restarting transaction
这时,B客户端修改失败,根据表中的数据,A客户端中加锁的应该是id为1的记录。这是因为,使用了where来检索数据,会导致索引失效,从而让行锁自动变成表锁。
A客户端提交事务:
mysql> commit;
Query OK, 0 rows affected (0.70 sec)
B客户端重新修改id为2的记录:
mysql> update ADMIN set FEE = 1000 where id = 2;
Query OK, 1 row affected (0.63 sec)
Rows matched: 1 Changed: 1 Warnings: 0
【间隙锁】
定义:当使用范围条件来操作记录时,InnoDB会对符合条件的记录加锁,同时也会对符合条件但不存在的记录加锁,后者叫做间隙锁。
坏处:当where的范围是1-10,表中只存在1,3,5,7,9时,会对2,4,6,8,10加锁,如果插入2,4,6,8,10,会出现错误,无法插入。
测试:
ADMIN表
A客户端开启事务,并查询id为1-7之间的记录:
mysql> start transaction;
Query OK, 0 rows affected (0.64 sec)
mysql> update ADMIN set FEE = 0 where id >= 1 and id <=7;
Query OK, 3 rows affected (0.16 sec)
Rows matched: 4 Changed: 3 Warnings: 0
B客户端插入一条id为3,4,6的记录:
mysql> insert into ADMIN values(3,"1","2019-12-21",1,1);
1205 - Lock wait timeout exceeded; try restarting transaction
这时,B客户端会提示有锁,无法插入。
A客户端提交事务:
mysql> commit;
Query OK, 0 rows affected (0.61 sec)
B客户端重新插入:
mysql> insert into ADMIN values(3,"1","2019-12-21",1,1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ADMIN values(4,"1","2019-12-21",1,1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ADMIN values(6,"1","2019-12-21",1,1);
Query OK, 1 row affected (0.18 sec)
【脏读、不可重复读和幻读】
脏读:事务B读取到事务A中还未提交的数据。
不可重复读:事务A读取两次同一个数据,结果不一致。
幻读:事务A查询两次数据库,查询到的记录条数不一致。
【事务的四种隔离级别】
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable read | × | × | √ |
Serializable | × | × | × |
【数据库默认的隔离级别】
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.34 sec)
3.3.1、事务的四种隔离级别测试
准备:设置不自动提交事务。
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.06 sec)
mysql> set autocommit = off;
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.06 sec)
【Read uncommitted】
设置事务的隔离级别
mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.22 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.07 sec)
开启AB两个客户端:
A客户端操作,将FEE字段的值+100:
mysql> use DB1;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.10 sec)
mysql> update ADMIN set FEE = FEE + 100 where id = 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
B客户端查询:
mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT * FROM ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
+----+-------------------------+------------+-----+------+
1 row in set (0.10 sec)
这时,B读取到的是A还未提交的数据,如果这时A出现异常,需要回滚,B读取到的数据属于脏读。
【Read committed】
A客户端设置成read-committed
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.09 sec)
B客户端设置成read-committed
mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.09 sec)
A客户端开启事务,同时查询ADMIN表:
mysql> start transaction;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 200 | 10 |
+----+-------------------------+------------+-----+------+
1 row in set (0.14 sec)
B客户端开启事务,同时修改ADMIN表并提交:
mysql> start transaction;
Query OK, 0 rows affected (0.10 sec)
mysql> update ADMIN set FEE = FEE + 100 where id = 1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.13 sec)
A客户端再次查询ADMIN表:
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
+----+-------------------------+------------+-----+------+
1 row in set (0.34 sec)
这时,A客户端的两次查询得到的数据的值不一致,属于不可重复读。
【repeatable-read】
A客户端设置成repeatable-read
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.09 sec)
B客户端设置成repeatable-read
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.09 sec)
A客户端开启事务,并查询ADMIN表:
mysql> start transaction;
Query OK, 0 rows affected (0.30 sec)
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
+----+-------------------------+------------+-----+------+
1 row in set (0.14 sec)
B客户端开启事务,并插入一条数据提交:
mysql> start transaction;
Query OK, 0 rows affected (0.26 sec)
mysql> insert into ADMIN values(2,"userid","2019-12-20",10,10);
Query OK, 1 row affected (0.10 sec)
mysql> commit;
Query OK, 0 rows affected (0.10 sec)
这时,A客户端查询ADMIN表:
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
+----+-------------------------+------------+-----+------+
1 row in set (0.13 sec)
还是只有一条数据
A客户端插入一条一模一样的数据:
mysql> insert into ADMIN values(2,"userid","2019-12-20",10,10);
1062 - Duplicate entry '2' for key 'PRIMARY'
插入失败。A客户端插入id为2的记录失败,从select语句的查询结果来看,是能够插入的,但是在物理表上已经存在了id为2的数据,A属于幻读了id为2的记录。
【serializable】
A客户端设置成serializable
mysql> set tx_isolation='serializable';
Query OK, 0 rows affected (0.08 sec)
B客户端设置成repeatable-read
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.09 sec)
B客户端开启事务,并插入一条数据:
mysql> start transaction;
Query OK, 0 rows affected (0.25 sec)
mysql> insert into ADMIN values(3,"userid","2019-12-20",10,10);
Query OK, 1 row affected (0.08 sec)
A客户端开启事务,并查询ADMIN表:
mysql> start transaction;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from ADMIN;
Lock wait timeout exceeded; try restarting transaction
这是A会提示有锁,需要等待B客户端提交。
B客户端提交
mysql> commit;
Query OK, 0 rows affected (0.53 sec)
A客户端重新查询ADMIN表:
mysql> select * from ADMIN;
+----+-------------------------+------------+-----+------+
| ID | USER_ID | TRAVELDATE | FEE | DAYS |
+----+-------------------------+------------+-----+------+
| 1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 300 | 10 |
| 2 | userid | 2019-12-20 | 10 | 10 |
+----+-------------------------+------------+-----+------+
2 rows in set (0.13 sec)
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
3.3.2、事务加锁
使用for update上锁。
数据表准备:
开启一个查询窗口查询ID为1的记录:
start transaction;
select * from ADMIN where id = 1 for update;
----------运行结果--------
1 iz2zeaf5jdjve80rjlsjgnz 2016-01-01 100 10
开启另一个查询窗口更新ID为1的记录:
start transaction;
update ADMIN set FEE = FEE + 100 where id = 1;
commit;
------------运行结果-----------
start transaction
> OK
> 时间: 0.084s
update ADMIN set FEE = FEE + 100 where id = 1
这时,update语句还没有执行,因为前一个查询窗口对update语句操作的行加了锁。
提交select的事务:
commit;
------------运行结果-----------
commit
> OK
> 时间: 0.093s
update事务:
update ADMIN set FEE = FEE + 100 where id = 1
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 51.097s
3.4、C(一致性)
一致性是指事务提交后,数据库的完整性约束没有被破坏,事务执行的前后的数据都是合法的。
3.5、总结
原子性:事务里面的语句要么全部执行,要么全部不执行。
持久性:事务提交后,如果机器宕机,数据不会丢失。
隔离性:多个事务之间相互不受影响。
问题
Q1:redo log作用是记录buffer pool的修改记录,那为什么不直接修改对应的数据文件?
A1:buffer pool把数据写入到磁盘是随机IO,写入到redo log中是顺序IO。Mysql第一次启动时就会为redo log分配好预定的空间,以保证能够存储在一段连续的内存单元上。
Q2:发生脏读,不可重复读等该如何解决?
A2:
1、更新前加锁,使用for update语句。
2、设置更高的隔离级别。
3、程序中加锁。