MySQL-事务处理

MySQL事务

什么是事务

• 数据中的事务是指数据库执行的一些操作，这些操作最终要么全部执行成功，要么全部失败，不会存在部分成功，部分失败的情况

如果在事务的支持下，最终有两种结果：

• 操作成功：A账户减少100，B账户增加100
• 操作失败：A，B账户没有发生任何变化

事务的特征（ACID）

原子性（Atomicity）

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果上来看的，从最终结果上来看的话这个过程是不可分割的

一致性（Consistency）

一个事务必须使数据库从一个一致性转换到另一个一致性的状态

一致性：

• 从业务逻辑上来说，最终结果符合逻辑就是一致性

隔离性（Isolation）

一个事务的执行不能被其他事务干扰。事务与事务之间不能互相干扰。

持久性（Durability）

一个事务一旦提交了，对数据库中的数据来说应该是永久性的。当事务提交之后，数据会持久化到磁盘中，修改时永久性的。

MySQL中的事务操作

mysql中事务默认为隐式事务，执行insert，update，delete操作的时候，数据库会自动开启事务，提交或回滚事务

是否开始隐式事务时有变量autocommit控制的

隐式事务

隐式事务自动开启，提交或回滚，比如insert，update，delete语句，事务的开启，提交或回滚由mysql内部自动控制的

show variables like 'autocommit';

autocommit为ON表示开启自动提交

显式事务

显式事务手动开启，提交和回滚，由开发者自动控制

方式1

语法：

//设置不自动提交事务
set autocommit  = 0;
//执行事务的操作
commit；//提交事务
rollback；//回滚事务

示例1：

create table test( a int);

窗口1	窗口2
设置为不自动提交（set commit = 0）
insert into test values (10)
select * from test;(有数据10)	select * from test;(没有数据10)
commit；
select * from test;(有数据10)	select * from test;(有数据10)
insert into test values (11)
select * from test;(有数据11)	select * from test;(没有11)
rollback;(回滚)
select * from test;(没有数据11)	select * from test;(没有数据11)

autocommit：还原

-- 自动提交事务
set autocommit  = 1;

方式2

语法：

-- 开启事务
start transaction ;
-- 执行事务
-- 提交事务
commit ;
-- 回滚事务
rollback ;

案例1：

窗口1	窗口2
show variables like ‘autocommit’;（默认开启）	show variables like ‘autocommit’;（默认开启）
start transaction ;（开启事务）
insert into test values (11);
select * from test;（没有数据）	select * from test;（没有数据）
commit;
	select * from test;(有数据)

案例2：

窗口1	窗口2
show variables like ‘autocommit’;（默认开启）	show variables like ‘autocommit’;（默认开启）
start transaction ;（开启事务）
delete from test where a = 11;
select * from test;（数据不存在11）	select * from test;（数据存在11）
rollback;（回滚）
select * from test;（数据存在11）	select * from test;（数据存在11）

savepoint关键字

示例：

窗口1	窗口2
show variables like ‘autocommit’;	show variables like ‘autocommit’;
start transaction ;（开启事务）
insert into test values (4);
savepoint insert_save1;（设置一个保存点insert_save1）
delete from test where a = 10;
savepoint delete_save2;
rollback to delete_save2;
commit;
select * from test;（有10数据）	select * from test;（有10数据）

只读事务

表示在事务中执行的是一些只读操作，比如查询，但不会做insert，update，delete操作，数据库内部对只读事务可能回有一个性能上的优化

用法：

start transaction read only ;

示例：

-- 提交事务
commit ;

-- 开启只读事务
start transaction read only ;

select * from test;

-- 删除全部数据
delete from test;
-- 只读状态无法删除数据
[25006][1792] Cannot execute statement in a READ ONLY transaction.
-- 提交事务
commit ;

select * from test;

只读事务中执行delete操作会报错： 只读状态无法删除数据

脏读

# 设置脏读
set session transaction isolation level read uncommitted;

脏读是指在数据库中，一个事务读取了另一个事务未提交的数据。

如果这些未提交的数据被回滚了，那么第一个事务读取的数据就是无效的。

读已提交

set session transaction isolation level read committed ;

一个事务操作过程中可以读取到其他事务已提交的数据

事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据（当前读）

可重复读

一个事务操作中对应一个读取操作不管多少次，读到的数据结果始终都是一样的

幻读

幻读在可重复度的模式下才会出现的，其他隔离隔离级别中不会出现

例如：

在可重复读模式下，如有用户表，手机号为主键，两个数据操作：

事务1：

• 打开只读事务
• 查询电话号码为x的存在，发现不存在
• 插入电话号码为x的数据，插入报错

start transaction read only ;

select * from user1 where iphone = 120;

insert into user1 values ('zhangsan',110);

commit ;

事务2：

对于事务2操作来说，发生幻觉了一样，

start transaction ;

insert into user1 values ('zhangsan',120);

select * from user1 where iphone = 120;

commit ;

事务中后面的操作（插入电话号码数据）需要提交事务之后才可以支持，对于事务2来说可以添加数据，但是不能查询数据，

事务的隔离级别

隔离级别分为4种：

• 读未提交：READ-UNCOMMITED
• 读已提交: READ-COMMITTED
• 可重复读: REPEATABLE-READ
• 串行: SERIALIZABLE

当多个事务同时进行的时候，如何确保当前事务种数据的正确性，不如A，B两个事务同时进行的时候，A是否可以看到B已提交事务或者未提交事务，需要看到的话依靠隔离界别来保证，不同隔离级别可以产生的效果不一样。

事务的隔离级别主要解决了多个书屋之间数据可见性和数据正确性的问题。

查看隔离级别

show variables like 'transaction_isolation';

当前隔离级别为可重复读

隔离级别的设置

READ-UNCOMMITTED:读未提交

修改C:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件，设计隔离级别

# 设置为读未提交
transaction-isolation=READ-UNCOMMITTED

修改完成后重启mysql服务

C:\WINDOWS\system32>net stop mysql57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。

C:\WINDOWS\system32>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

修改完成后查看隔离级别

show variables like 'transaction_isolation';

按照时间顺序在2个窗口执行

时间	窗口1	窗口2
T1	start transaction ;
T2	select * from test;
T3		start transaction;
T4		insert into test values(1);
T5		select * from test;
T6	select * from test;
T7		commit;
T8	select * from test;
T9	commit;

窗口1可以读取到窗口2未提交的数据，说明出现了脏读

结论：

• 在读未提交的情况下，可以读取到其他事务还未提交的数据，多次读取，出现脏读，不可重复读

READ-COMMITED：读已提交

# 设置为读已提交

transaction-isolation=READ-COMMITTED

C:\WINDOWS\system32>net stop mysql57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。

C:\WINDOWS\system32>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

# 查看隔离级别
show variables like 'transaction_isolation';

按照时间顺序在2个窗口执行

时间	窗口1	窗口2
T1	start transaction ;
T2	select * from test;
T3		start transaction;
T4		insert into test values(1);
T5		select * from test;
T6	select * from test;
T7		commit;
T8	select * from test;
T9	commit;

窗口1开启事务，无法读取到窗口2未提交的数据，说明没有脏读

窗口2提交事务后，窗口1则可以读取到数据。

结论：

• 在读已提交的情况下，无法读取到其他窗口未提交的数据，可以读取到已提交的数据

REPEATABLE-READ：可重复读

# 设置为可重复读
transaction-isolation=READ-COMMITTED

C:\WINDOWS\system32>net stop mysql57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。

C:\WINDOWS\system32>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

# 查看隔离级别
show variables like 'transaction_isolation';

按照时间顺序在2个窗口执行

时间	窗口1	窗口2
T1	start transaction ;
T2	select * from test;
T3		start transaction;
T4		insert into test values(1);
T5		select * from test;
T6	select * from test;
T7		commit;
T8	select * from test;
T9	commit;
T10	select * from test;

窗口2未提交事务，窗口1无法查找未提交数据：未出现脏读

窗口2提交事务后，窗口1依旧无法查看数据

窗口1提交事务后，窗口1可以查看窗口2提交的事务数据

SERIALIZABLE：串行

SERIALIZABLE会让并发的事务串行执行（多个事务之间读写，写读，写写会产生互斥，这个效果就是串行执行，多个事务之间读读操作不会产生互斥）

读写互斥：事务1中先读取操作，事务2发起写入操作，事务1中的读取会导致事务2中的写入处于等待状态，直到事务1完成为止；

# 设置串行
transaction-isolation=SERIALIZABLE

C:\WINDOWS\system32>net stop mysql57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。

C:\WINDOWS\system32>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

# 查看隔离级别
show variables like 'transaction_isolation';

按照时间顺序在2个窗口执行

时间	窗口1	窗口2
T1	start transaction ;
T2	select * from test;
T3		start transaction;
T4		insert into test values(1);
T5
T6	commit;
T7		commit;

隔离级别为串行状态下，事务只能串行执行，串行情况下不存在脏读，不可重复读，幻读的问题

各个隔离级别中出现的问题

隔离级别		脏读	不可重复读	幻读
读未提交	READ-UNCOMMITED	有	有	无
读已提交	READ-COMMITTED	无	有	无
可重复读	REPEATABLE-READ	无	无	有
串行	SERIALIZABLE	无	无	无

总结

事务的特性：原子性，隔离性，一致性，持久性

是否开启自动提交事务：set autocommit = 0 ：手动，1 ：自动;

开启事务： start transcation

开启只读事务：start transcation read only；

提交事务：commit；

回滚事务：rollback；

设置保存点：savepoint (name);

回滚保存点：rollback to （name）；

查看隔离级别：show variables like ‘transaction_isolation’;