MySQL-事务

简介

事务适用于保证数据的一致性，由一组相关的insert、delete、update（增删改）语句组成，该组的增删改语句要么全都成功，要么全部失败。

事务和锁

当执行事务操作时（增删改），mysql会在表上加锁，防止其他用户修改表中数据。

mysql数据库控制台事务的几个重要操作

1）start transaction        --开始一个事务

2）savepoint 保存点名         --设置保存点

3）rollback to 保存点名        --回退事务

4）rollback                --回退全部事务

5）commit                --提交事务，所有操作生效，不能回退

#事务操作流程

-- 1.创建测试表
CREATE TABLE t9(
		id INT,
		`name` VARCHAR(32));
		
-- 2.开启事务
START TRANSACTION;

-- 3.设置保存点
SAVEPOINT a;

-- 4.执行dml操作
INSERT INTO t9 VALUES (100, 'tom');
SELECT * FROM t9;

-- 5.设置保存点
SAVEPOINT b;

-- 6.执行dml操作
INSERT INTO t9 VALUES (200, 'lucy');
SELECT * FROM t9;

-- 7.回退到b
ROLLBACK TO b;
SELECT * FROM t9;

-- 8.回退到a
ROLLBACK TO a;
SELECT * FROM t9;

-- 提交事务
COMMIT;

回退事务

保存点（savepoint）是实务中的点，用于取消部分事务，当结束事务（commit）时会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

提交事务

使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务后，其他会话【其他链接（例如多个用户连接，一个用户对数据库进行操作后没有提交，另一个用户能看到那些操作的内容，和隔离级别相关）】将可以查看到事务变化后的新数据。

事务细节

1）如果不开始事务，默认情况下增删改操作是自动提交的，不能回滚；

2）如果开始一个事务，没有创建保存点，可以执行rollback语句，默认退回到事务开始的状态；

3）可以在还没有提交的事务中创建多个保存点；

4）可以在事务没有提交前，选择回退到指定的个保存点（回退到更靠前的保存点后，不能再向指定的保存点之后的保存点回退）；

5）InnoDB存储引擎支持事务，MyISAM不支持；

6）开始一个事务star transaction或者set autocommmit = off；

-- 事务细节
INSERT INTO t9 VALUES (300,'jack');	-- 执行后自动提交，不能回滚
SELECT * FROM t9;
ROLLBACK;	-- 回滚后没有改变

START TRANSACTION;		-- 回退后到这个位置
INSERT INTO t9 VALUES (400,'lucy');
INSERT INTO t9 VALUES (400,'smith');
SELECT * FROM t9;
ROLLBACK;	-- 回退后，到了事务开始的时候

COMMIT;

-- 开始事务
SET autocommit = off;

MySQL事务隔离级别

在不同的事务中，根据隔离的强度不同，每一个事务看到的表中的数据不同。

1）多个连接开启各自事务，在操作数据库中的数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性；

2.如果不考虑隔离性可能会发生如下问题：①脏读；②不可重复读；③幻读；

★脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时，产生脏读（A事务在对表进行数据操作，但是没有commit（提交）；而B事务在对表数据进行操作时，操作的数据受到A事务修改的影响，发生脏读）；

★不可重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读（A事务在对表进行数据操作，操作完成后commit（提交））；而B事务在对表数据进行操作时（B事务操作时间长度包括A事务的操作），每次进行同一查询的数据内容不同，受到A事务操作的影响，发生不可重复读）；

★幻读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

MySQL隔离级别定义了事务与事务之间的隔离程度

MySQL隔离级别（4中）	脏读	不可重复读	幻读	加锁读
读未提交（Read uncommitted)	可能出现	可能出现	可能出现	不加锁
读已提交（Read committed）	不可能出现	可能出现	可能出现	不加锁
可重复读（Repeatable read）	不可能出现	不可能出现	不可能出现	不加锁
可串行化（Serializable）	不可能出现	不可能出现	不可能出现	加锁

加锁：如果发现一个表正在被A事务操作并且没有commit（提交），那么B事务就不能够再操作这个表(查询数据时会进入等待)，当A事务commit（提交）后，B事务才能对表中数据进行操作。

-- 1.开启多个控制台【A控制台进行A事务操作和B控制台进行B事务操作】
-- 2.查看A控制台当前mysql的隔离级别
SELECT @@tx_isolation;
/*
+------------------------------+
| @@tx_isolation          |
+-----------------+
| REPEATABLE-READ |
+------------------------------+
*/

-- 3.把B控制台的隔离级别设置为Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@tx_isolation;
/*
+--------------------------------+
| @@tx_isolation            |
+------------------+
| READ-UNCOMMITTED |
+--------------------------------+
*/

-- 4.在A控制台创建表
CREATE TABLE `account`(
		id INT,
		`name` VARCHAR(32),
		money INT);

-- 5.A控制台和B控制台先后启动事务
START TRANSACTION;

-- 6.A控制台依次进行添加数据、修改数据、删除数据的操作
-- B控制台多次查询表中数据，在A控制台进行操作的过程中每次查询都不相同
-- B控制台启动的B事务对表进行数据处理时受到A控制台A事务操作的影响；

-- 7.两个控制台提交事务后，依次改变B控制台的隔离级别到read committed、
-- repeatable read、serializable，进行重复实验，B控制台的事务操作逐渐不受
-- A控制台事务操作的影响。
-- 隔离级别read committed的控制台所进行的事务操作会发生不可重复读和幻读；
-- repeatable read 和 serializable不会发生脏读、不可重复读、幻读，前者不加锁，后者加锁

隔离级别设定的相关指令

1）查看当前会话隔离级别

select @@tx_isolation;

2）查看系统当前隔离级别

select @@global.tx_isolation;

3）设置当前会话隔离级别

set session transaction isolation level [read uncommitted [,read committed...]]

-- 隔离级别分为4种：
-- ①读未提交 read uncommitted
-- ②读已提交 read committed
-- ③可重复读 repeatable read
-- ④串行化 serializable

4）设置系统当前隔离级别

set global transaction isolation level [read uncommitted [,read committed...]]

-- 隔离级别分为4种：
-- ①读未提交 read uncommitted
-- ②读已提交 read committed
-- ③可重复读 repeatable read
-- ④串行化 serializable

5）mysql默认的事务隔离级别是repeatable read，一般情况下没有特殊要求，没有必要修改（该级别可以满足大部分项目需求）；

6）隔离级别的全局修改，可以修改默认的隔离级别，修改my.ini配置文件，在[mysqld]栏目下编写：transaction-isolation = REPEATABLE-READ

      可选隔离参数有：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

事务的ACID特性

1）原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生；

2）一致性（Consistency）

事务必须使用数据库从一个一致性状态变换到另外一个一致性状态（事务一旦提交之后，会进行一个整体性改变，开启一个新的事务）；

3）隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离；

4）持久性（Durability）

持久性是指一个事务一旦被提交，他对数据库中的数据改变是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

表类型和存储引擎

基本介绍

1）MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。

2）MySQL数据表主要支持六种类型，分别是：

①CSV：CSV存储引擎；

②Memory：基于散列，存储在内存中，对临时表有用；

③ARCHIVE：归档存储引擎；

④MRG_MYISAM：相同MyISAM表的集合；

⑤MYISAM：MyISAM存储引擎；

⑥InnoDB：支持事务、行级锁定和外键。

3）这六种又分为两类，一种是“事务安全型”（transaction-safe），比如：InnoDB；其余都属于第二类，称为“非事务安全型”（non-transaction-safe）[myisam 和 memory]。

主要的存储引擎/表类型特点

特点	MyISAM	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

1）MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求；

2）InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引；

3）MEMORY存储引擎使用存在内存中的内容来创建表，每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务器关闭，表中的数据就会丢失掉，表的结构还在。

#表类型和存储引擎

-- 查看所有的存储引擎
SHOW ENGINES;

-- InnoDB存储引擎
-- 1.支持事务；2.支持外键；3.支持行级锁；

-- myisam存储引擎
CREATE TABLE t16(
		id INT,
		`name` VARCHAR(32)) ENGINE MYISAM;
-- 1. 添加速度快；2.不支持外键；3.支持表级锁。
START TRANSACTION;
SAVEPOINT a;
INSERT INTO t16 VALUES(1,'tom');
SELECT * FROM t16;
ROLLBACK TO a;		-- 回滚操作成功，但是出现警告，数据没有回滚回去（不支持事务）
SELECT * FROM t16;
COMMIT;

-- memory存储引擎
-- 1.数据存储在内存中(关闭MySQL服务，数据丢失，但是表结构还在)；
-- 2.执行速度很快（没有IO读写，所以执行速度快）；3.默认支持索引（HASH表）。
CREATE TABLE t17(
		id INT,
		`name` VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t17 VALUES(1,'tom'), (2,'jack'),(3,'lucy');
SELECT * FROM t17;
-- 重启mysql后再次查询表，表结构还在数据消失，例如适用于用户状态
DESC t17;
SELECT * FROM t17;

-- 指令修改存储引擎
ALTER TABLE t17 ENGINE = INNODB;

如何选择表的存储引擎

1）如果应用不需要事务，处理的只是基本的CRUD操作，那么就选择MyISAM存储引擎，速度快；

2）如果需要事务，选择InnoDB；

3）Memory存储引擎是将数据存储在内存中，由于没有磁盘IO的等待，速度极快。但由于内存存储引擎，所做的任何修改在服务器重启后消失（例如：用户的在线状态）。

修改存储引擎

ALTER TABLE table_name ENGINE = StorageEngine_name;