MYSQL事务

介绍

事务（Transaction）是指作为单个逻辑工作单元执行的一系列操作。

特性

• 原子性（Atomicity）

        表示组成一个事物的多个数据库操作是一个不可分隔的原子单元，只有所有的操作执行成功，整个事务才提交，事务中任何一个数据库操作失败，已经执行的任何操作都必须撤销，让数据库返回到事务开始执行的初始状态。

• 一致性（Consistency）

        事务操作成功后，数据库所处的状态和它的业务规则是一致的，即数据不会被破坏。

• 隔离性（Isolation）

        在并发数据操作时，不同的事务拥有各自数据空间，它们的操作不会对对方产生干扰。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发性越弱。

• 持久性（Durabiliy）

        一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证能够通过某种机制恢复数据。

类型

• 自动提交事务

        系统默认每个TRANSACT -SQL命令都是一个事务处理，由系统自动开始并提交。

• 隐式事务

        不需要显示开始事务，需要显示提交，隐式事务是任何单独的INSERT、UPDATE或者DELETE语句构成。当有大量的DDL和DML命令执行时会自动开始，并一直保持到用户明确提交为止。

•    显示事务

        显示事务是用户自定义事务，以START TRANSACTION（事务开始）开头，以COMMIT（事务提交）或者ROLLBACK（回滚事务）语句结束。

• 分布式事务

        跨越多个服务器的事务称为分布式事务，从MySQL5.03开始支持分布式事务。

语法

• 开始事务

START {TRAN | TRANSACTION};

• 提交事务

COMMIT;

• 事务回滚

ROLLBACK;

• 事务设置

        SET AUTOCOMMIT 可以修改当前事务提交的方式，SET AUTOCOMMIT = 0，则需要以明确的命令进行提交或者回滚事务。

隔离级别

• Read Uncommitted（读未提交）

        事务可以读取其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读已提交）

        事务只能读取其他事务已经提交的执行结果。本隔离级别支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• Repeatable Read（可重复读）

        MySQL默认的事务隔离级别，会给查询的记录做快照，直到事务结束。确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

• Serializable（串行化）

        最高的隔离级别，对同一条记录读和修改的多个事务只能结束一个，才能开始下一个。
通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。在每个读的数据行上加上共享锁，可能导致大量的超时现象和锁竞争。

隔离级别的设置

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别，语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果使用GLOBAL关键字，在全局对新开始创建的所有新连接设置默认事务级别，需要SUPER权限。使用SESSION关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别，或者为下一个事务设置隔离级别。

查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

通过mySQL配置文件修改全局事务隔离级别，设置全局会话默认事务隔离级别:

[mysqld]
xxxxxxx
transaction-isolation=read-committed

重启mySQL服务，生效。

准备

创建一张表，包含ID，姓名，年龄，地址，用于后面验证事务的隔离级别：

create database tx_test;
use tx_test;

DROP TABLE IF EXISTS `fruit`;
CREATE TABLE `fruit` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(100) DEFAULT '' COMMENT '名称',
     `price` float(5,2) unsigned DEFAULT '0' COMMENT '价格',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='水果';

insert into fruit values(1, "apple", 35);
insert into fruit values(2, "banana", 35);

上述语句执行之后person表中的数据如下：

设置事务的隔离级别

1.查看会话的隔离级别

在终端中登录到mysql，记为会话1，使用以下命令查看会话1的事务隔离级别：

select @@tx_isolation;

查询结果如下:

 2.修改会话1的事务隔离级别为READ-UNCOMMITTED：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

3.再次查看会话1的事务隔离级别,发现已经被修改为了READ-UNCOMMITTED：

select @@tx_isolation;

查询结果如下：

4. 重新打开一个终端并登录到mysql，记为会话2，查看会话2下的事务隔离级别,发现在会话2下隔离级别仍然是REPEATABLE-READ：

select @@tx_isolation;

查询结果如下：

 总结：通过一下方式设置事务的隔离级别只在当前会话有效。

SET SESSION TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

验证READ-UNCOMMITTED隔离级别

打开一个终端并登录到mysql，记为会话1，设置事务的隔离级别为REPEATABLE-READ：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

查看会话1的事务隔离级别：

select @@tx_isolation;

查询结果如下：

 打开一个终端并登录到mysql，记为会话2，查看事务的隔离级别：

select @@tx_isolation;

查询结果如下：

在会话2中开始一个事务，将fruit表中ID为1的记录的price字段更新为5.6：

use tx_test;
start TRANSACTION;
update fruit set price=5.6 where id=1;

在会话1中使用一下语句查看fruit表中ID为1的记录

select * from fruit where id=1;

price已经被修改为了5.6：

 在会话2中将事务回滚：

ROLLBACK;

在会话1中查看fruit表ID为1的记录：

select * from fruit where id=1;

在会话2中查看fruit表ID为1的记录：

select * from fruit where id=1;

 总结：在READ-UNCOMMITTED隔离级别下，事务1中能读到事务2中未提交的数据。

验证READ COMMITTED隔离级别

打开一个终端并登录到mysql，记为会话1，设置事务的隔离级别为READ COMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

执行结果如下：

 查询会话1的事务隔离级别：

select @@tx_isolation;

查询结果如下：

 打开一个终端并登录到mysql，记为会话2,开始一个事务，更新fruit表中ID为1的记录的price字段为5.8:

use tx_test;
start TRANSACTION;
update fruit set price=5.8 where id=1;

执行结果如下：

 在会话1中查看fruit表中ID为1的记录：

select * from fruit where id=1;

查询结果如下：

 在会话2中提交事务：

COMMIT;

执行结果如下：

 在会话1中查看fruit表中ID为1的记录：

select * from fruit where id=1;

查询结果如下：

总结：在 READ COMMITTED隔离级别下，在事务1中无法读到事务2中尚未提交的数据，一旦事务2被提交，则事务1可以读取到相关的数据。

验证REPEATABLE READ隔离级别

打开一个终端并登录到mysql，记为会话1，设置事务的隔离级别为REPEATABLE READ：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

执行结果如下：

在会话1中查看fruit表中ID为1的记录：

start TRANSACTION;
select * from fruit where id=1;

执行结果如下：

打开一个终端并登录到mysql，记为会话2，将fruit表中ID为1的记录的price字段更新为9.8：

update fruit set price=9.8 where id=1;

执行结果如下：

在会话2中查看fruit表中id为1的记录：

select * from fruit where id=1;

执行结果如下：

在会话1中再次查看fruit表中ID为1的记录：

select * from fruit where id=1;

查询结果如下：

 

 在会话1中提交事务：

COMMIT;

执行结果如下：

 在会话1中再次查看fruit表中ID为1的记录：

select * from fruit where id=1;

查询结果如下：

 总结：在REPEATABLE READ隔离级别隔离级别下，事务1对数据可以重复读，并且读到的数据是一样的，即使事务2对其中的数据做了更新并且提交了事务，在事务1中忍让能读到相同的数据。

验证SERIALIZABLE隔离级别

打开一个终端并登录到mysql，记为会话1，设置事务的隔离级别为SERIALIZABLE：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

执行结果如下：

 打开一个终端并登录到mysql，记为会话2，开始一个事务，将fruit表中ID为1的记录的price字段更新为7.3：

start TRANSACTION;
update fruit set price=7.3 where id=1;

执行结果如下：

在会话1中开始一个事务,查看fruit表中ID为1的记录：

start TRANSACTION;
select * from fruit where id=1;

 执行结果如下，会话1一直处于等待状态：

 在会话2中提交事务后：

COMMIT;

执行结果如下：

 再次在会话一中查看fruit表中ID为1的记录：

select * from fruit where id=1;

查询结果如下：

总结：在SERIALIZABLE隔离级别下，事务是串行执行的。