0%

MySQL事务

MySQL事务

事务:一个或一组SQL语句组成的一个执行单元,要么全部成功,要么全部失败。

注:mysql中只有innodb支持事务

事务的ACID特性

  • 原子性(Atomicity) 指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
  • 一致性(Consistency) 指事务必须使数据库从一个一致性状态变成另一个一致性状态
  • 隔离性(Isolation) 指一个事务的执行不能被另一个事务干扰
  • 持久性(Durability) 指事务一旦被提交,对数据库中数据的改变是永久性的

事务的创建

默认情况下mysql的事务是自动提交的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
show variables like '%autocommit%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

#需要手动的禁用一下事务自动提交
set autocommit = 0;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+

事务的隔离级别

查看数据库的隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
#mysql8中查看隔离级别
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+

#mysql8之前查看隔离级别
select @@tx_isolation;

修改数据库的隔离级别

1
2
3
4
#修改全局隔离级别
set global transaction isolation level read committed;
#修改会话隔离级别
set session transaction isolation level read committed;

事务并发出现的问题

  • 脏读 对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段之后,若T2回滚,T1读取到的内容就是临时无效的(读取到了别人没有提交的数据)
  • 不可重复读 对于两个事务T1,T2,T1读取了一个字段,T2更新了该字段,之后T1再次读取同一个字段,值就不同了(描述的是修改操作) 读取的数据可以被其他事务修改
  • 幻读 对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,T1再次读取同一个表,就会多出几行(描述的是插入或者删除操作)同样的条件下两次读出的记录数不一样,这种情况一般都不可避免,除非使用表锁

脏读和幻读相似,脏读属于修改操作,幻读属于插入操作

隔离级别

  • READ UNCOMMITED(读未提交的数据) 允许事务读取未被其他事务提交的变更,脏读、不可重复读、幻读问题都存在,只对更新语句的行记录加锁,不会对事务加锁
  • READ COMMITED(读已提交的数据) 只允许事务读取已被其他事务提交的数据,可以避免脏读,但是不可重复读和幻读仍存在,会对事务加锁,但是每次执行SQL语句都会生成一个新的View,每次读取的都是不同的
  • REPEATABLE READ(可重复读) 确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新,可以避免脏读和不可重复读,但幻读仍存在(Mysql默认的事务隔离级别),会对事务加锁,且对于一个事务来说使用的是同一个View,Mysql对于可重复读做了优化,使用MVCC机制+间隙锁可以保证不会出现幻读
  • SERIALIZABLE(串行化) 确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入、更新和删除操作,所有并发问题都可以避免,但是性能低下

测试各个隔离级别

开启两个mysql连接,为mysql1和mysql2

原始数据为

1
2
3
4
5
6
7
8
9
select * from users;
+----+---------+
| id | u_name |
+----+---------+
| 2 | 张三 |
| 3 | 张三0 |
| 4 | 张三1 |
| 5 | 张三2 |
+----+---------+
测试READ UNCOMMITTED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
 #首先设置mysql1和mysql2隔离级别为read uncommited
set session transaction isolation level read uncommitted;
#禁用mysql1 mysql2的自动提交
set autocommit = 0;

update users set u_name='李四' where id = 3;#① mysql1修改数据但未提交
select * from users where id =3;#② mysql1 查询数据 为李四
select * from users where id =3;#③ mysql2 查询数据 也为李四 查询到了mysql1中未提交的数据 出现了脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交
select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据
select * from users where u_name = '刘亦菲';#⑥mysql2 查询到该数据 出现了幻读

rollback;#⑦mysql1 回滚之前的操作 此时mysql2在查询发现之前查询到的数据都变了 出现了不可重复读
测试read commited
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#首先设置mysql1和mysql2隔离级别为read commited
set session transaction isolation level read committed;
#禁用mysql1 mysql2的自动提交
set autocommit = 0;

update users set u_name='李四' where id = 3;#① mysql1修改数据但未提交
select * from users where id =3;#② mysql1 查询数据 为李四
select * from users where id =3;#③ mysql2 查询数据 为张三0 没有出现脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交
select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据
select * from users where u_name = '刘亦菲';#⑥mysql2 没有查询到该数据

commit;#⑦mysql1 提交之前的操作 此时mysql2在查询发现之前查询到的数据都变了 出现了不可重复读
测试read commited
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#首先设置mysql1和mysql2隔离级别为read commited
set session transaction isolation level read committed;
#禁用mysql1 mysql2的自动提交
set autocommit = 0;

update users set u_name='李四' where id = 3;#① mysql1修改数据但未提交
select * from users where id =3;#② mysql1 查询数据 为李四
select * from users where id =3;#③ mysql2 查询数据 为张三0 没有出现脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交
select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据
select * from users where u_name = '刘亦菲';#⑥mysql2 没有查询到该数据

commit;#⑦mysql1 提交之前的操作 此时mysql2在查询发现之前查询到的数据都变了 出现了不可重复读
测试repeatable read
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#首先设置mysql1和mysql2隔离级别为repeatable read
set session transaction isolation level repeatable read;
#禁用mysql1 mysql2的自动提交
set autocommit = 0;

update users set u_name='李四' where id = 3;#① mysql1修改数据但未提交
select * from users where id =3;#② mysql1 查询数据 为李四
select * from users where id =3;#③ mysql2 查询数据 为张三0 没有出现脏读

insert into users (u_name) values ('刘亦菲');#④mysql1 插入数据但未提交
select * from users where u_name = '刘亦菲';#⑤mysql1 查询到该数据
select * from users where u_name = '刘亦菲';#⑥mysql2 没有查询到该数据

commit;#⑦mysql1 提交之前的操作 此时mysql2还是没有数据

保存点savepoint

在一个事务中可以设置保存点,和rollback搭配使用

测试保存点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select * from users;

+------+-----------+
| id | u_name |
+------+-----------+
| 2 | 张三 |
| 3 | 李四 |
| 4 | 张三1 |
| 5 | 张三2 |
| 1006 | 刘亦菲 |
+------+-----------+

delete from users where id = 4;
savepoint a;#设置保存点
delete from users where id = 1006;
rollback to a;#回滚到保存点的位置


select * from users;

+------+-----------+
| id | u_name |
+------+-----------+
| 2 | 张三 |
| 3 | 李四 |
| 5 | 张三2 |
| 1006 | 刘亦菲 |
+------+-----------+