分布式事务 Mysql本地事务与事务隔离级别 (一)

目录

目录

目录

数据库事务

Mysql操作事务

开始事务

提交事务

回滚事务

Mysql事务操作测试

1.准备测试表

2.测试

事务特性 ACID

A - 原子性 Atomic

C - 一致性 Consistency

 I - 隔离性 Isolation

D - 持久性 Durancy

Mysql 的四种事务隔离级别

读未提交(read uncommitted)：

读已提交(read committed)：

可重复读(Repeatable read)：

可串行化(Serializable)：

Mysql 设置隔离级别

Mysql 隔离级别测试

测试一

测试二

测试三

测试四

---

数据库事务

数据库事务由一组sql语句组成。

所有sql语句执行成功则事务整体成功；任一条sql语句失败则事务整体失败，数据恢复到事务之前的状态。


下面以转账为例进一步说明。

A 账户向 B 账户转账，需要更新两个账户的记录：

- A 账户减金额
update user set money=money-100 where id='A'

- B 账户加金额
update user set money=money+100 where id='B'

• 两条sql语句都成功则转账成功。
• 任意一条sql语句失败，恢复以前的状态。

数据操作的最小单元是事务而不是一条sql语句

Mysql操作事务

开始事务

start transaction;

- 或
begin;

事务开始后，对数据的增删改查操作不直接修改表，而是被记录再日志文件中。

提交事务

commit；

将日志中记录的操作，永久保存到数据表，并清空日志文件

回滚事务

rollback；

直接清空日志文件

Mysql事务操作测试

1.准备测试表

CREATE TABLE USER (
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(128),
	`password` CHAR(32)
) CHARSET=utf8;

# 插入一条数据
INSERT INTO `user`(username,`password`) VALUES('aaa', 'aaa');

2.测试

打开两个终端，分别登录数据库

# 在终端登录 mysql
mysql -uroot -p

# 切换到你的数据库
mysql> use 数据库名;

测试一

步骤	终端A	终端B
1	begin；	begin；
2	insert into user(username) values("A");
3	update user set username = "x" where id = '1';
4		select * from user; B查询不到A提交的数据
5	select * from user; A提交的数据对自己是可见的
6	commit；
7		select * from user； 即使A已经提交，B也查询不到A提交的数据
8		commit；
9		select * from user； B的事务结束后才能查询到A提交的数据

测试二

步骤	终端A	终端B
1	rollback； 为避免未结束的事务影响，先结束前面的事务	rollback； 为避免未结束的事务影响，先结束前面的事务
2	begin；	begin
3	insert into user(username) values('B')
4	update user set username = "Y" where id = '1';
5	select * from user;
6	commit;
7		selcet * from user;
8		insert into user (username) values ('C');
9		update user set username='Z' where id=1;
10		select * from user;
11		rollback; 回滚事务，B的数据修改全部丢弃，恢复到以前状态。 自增主键不会回滚
12		select * fromuser；

事务特性 ACID

A - 原子性 Atomic

一个事物是一个不可分割的工作单元，事务中包括的操作要么都做，要么都做。

数据操作的最小单元是事务，而不是SQL语句。

C - 一致性 Consistency

事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

例如：

• 转账前 a+b = 100
• 转账后 a+b = 100

 I - 隔离性 Isolation

一个事物的执行不能被其他事务干扰。

即一个事物内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

D - 持久性 Durancy

一个事物一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

Mysql 的四种事务隔离级别

事务之间为了避免互相干扰，执行时要进行隔离。也就是A执行时B要等待。但严格的隔离会造成性能的下降。

数据库为了兼顾数据安全和性能，可以在一定程度上允许多个事务并行执行。

Mysql 提供了四种隔离级别从低到高：

读未提交(read uncommitted)：

事务在读数据的时候并未对数据加锁。

事务在修改数据的时候只对数据增加行级共享锁。

读已提交(read committed)：

事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；

事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

可重复读(Repeatable read)：

事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；

事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

可串行化(Serializable)：

事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；
事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题

脏读：读到的不是最新的数据
不可重复读：读到是是最新的数据，但是一个事务中如果多次读取，可能每次读到的数据不一致
幻读：无法保证每次读取的行数量一致，因为其它事务插入无法阻止，可能造成第一次读是15行，第二次是16行

 oracle默认的隔离级别为 读已提交。mysql的默认隔离级别为 可重复读。

隔离级别越高，越能保证数据的完整性和统一性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。MySQL支持四种事务隔离级别，其中REPEATABLE READ为默认事务隔离级别。

可能引发的问题	读未提交（READ-UNCOMMITTED）	读已提交（READ-COMMITTED）	可重复读（REPEATABLE-READ）	可串行话（SERIALIZABLE）
幻读	√	√	√	×
不可重复读	√	√	×	×
脏读	√	×	×

Mysql 设置隔离级别

set tx_isolation='read-uncommitted';

set tx_isolation='read-committed';

# repeatable-read 是Mysql默认的隔离级别
set tx_isolation='repeatable-read';

set tx_isolation='serializable';

oracle mysql 8 使用 transaction_isolation 系统变量：

set transaction_isolation='read-uncommitted';

set transaction_isolation='read-committed';

# repeatable-read 是Mysql默认的隔离级别
set transaction_isolation='repeatable-read';

set transaction_isolation='serializable';

注意：set设置的变量只对当前会话有效。需要进行全局设置使用 set global

Mysql 隔离级别测试

打开两个终端，分别登录数据库。

# 在终端登录 mysql
mysql -uroot -p

# 切换到你的数据库
mysql> use 数据库名;

测试一

步骤	终端A	终端B
1	set tx_isolation = 'read-uncommitted';	set tx_isolation = 'read-uncommitted';
2	rollback;	rollback;
3	begin;	begin;
4	insert into user(username) values ('D');
5	update user set username = 'R' where id=1;
6		select * from user; 可以读A未提交的数据，这些数据在真实端1数据表中不存在
7	rollback；
8		select * from user； A回滚后，B不能在重复读这些数据

测试二

步骤	终端B	终端B
1	set tx_isolation='read-committed';	set tx_isolation='read-committed';
2	rollback;	rollback;
3	begin;	begin;
4		select * from user;
5	insert into user(username) values('E');
6	update user set username='S' where id=1;
7		select * from user; 不能读取A未提交的数据
8	commit;
9		select * from user; A提交后，可以立即读取

测试三

步骤	终端A	终端B
1	set tx_isolation='repeatable-read';	set tx_isolation='repeatable-read';
2	rollback;	rollback;
3	begin;	begin;
4		select * from user;
5	insert into user(username) values('F');
6	update user set username='T' where id=1;
7	commit;
8		select * from user; 即使A已经提交，这里也不能读取A已提交的数据。这里读取的结果要和前一次的结果一致
9		update user set password='111'; 可以修改A已提交的数据
10		select * from user; 修改后这些数据又变成可见的

测试四

步骤	终端A	终端B
1	rollback;	rollback;
2	begin;	begin;
3		select * from user;
4	delete from user where id<5;
5	commit;
6		select * from user; 仍然可以查询到已被A删除的数据
7		update user set password='222'; 但是不可能去修改这些已删除的数据
8		select * from user; 现在被删掉的数据还是可见的
9		commit;
10		select * from user; 事务结束后这些数据不再可见