ToB企服应用市场:ToB评测及商务社交产业平台

标题: 13. MySQL 事务底子知识(详细阐明实操剖析) [打印本页]

作者: 卖不甜枣 时间: 4 天前
标题: 13. MySQL 事务底子知识(详细阐明实操剖析)
13. MySQL 事务底子知识(详细阐明实操剖析)

@
目录

1. 数据库事务概述

1.1 存储引擎支持情况

SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些，以及这些存储引擎是否支持事务。

mysql> show engines;

复制代码

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

复制代码

能看出在 MySQL 中，只有InnoDB 是支持事务的。

1.2 事务基本概念

**事务： ** 一组逻辑操纵单元，使数据从一种状态变动到另一种状态。
事务处理的原则： 保证所有事务都作为一个工作单元来执行，纵然出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操纵时，要么所有的事务都被提交( commit )，那么这些修改就永世地保存下来；要么数据库管理体系将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。

1.3 事务的 ACID 特性

原子性（atomicity）：

原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

一致性（consistency）：

（国内许多网站上对一致性的阐述有误，具体你可以参考 Wikipedia 对 Consistency 的阐述）。
根据定义，一致性是指事务执行前后，数据从一个合法性状态变动到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。
那什么是合法的数据状态呢？满足预定的约束的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！假如事务中的某个操纵失败了，体系就会自动撤销当前正在执行的事务，返回到事务操纵之前的状态。

隔离型（isolation）：

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操纵及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
假如无法保证隔离性会怎么样？假设A账户有200元，B账户0元。A账户往B账户转账两次，每次金额为50 元，分别在两个事务中执行。假如无法保证隔离性，会出现下面的情形：

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';

复制代码

持久性（durability）：

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永世性的，接下来的其他操纵和数据库故障不应该对其有任何影响。
持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据举行修改的时间，首先会将数据库的厘革信息记录到重做日志中，然后再对数据库中对应的行举行修改。这样做的利益是，纵然数据库体系瓦解，数据库重启后也能找到没有更新到数据库体系中的重做日志，重新执行，从而使事务具有持久性。
总结：
ACID是事务的四大特征，在这四个特性中，原子性是底子，隔离性是本领，一致性是约束条件，而持久性是我们的目标。
数据库事务，其实就是数据库设计者为了方便起见，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操纵称为一个事务。
1.4 事务的状态

我们现在知道事务是一个抽象的概念，它其实对应着一个或多个数据库操纵，MySQL根据这些操纵所执行的不同阶段把事务大致划分成几个状态：

活动的（active）
事务对应的数据库操纵正在执行过程中时，我们就说该事务处在活动的状态。
部分提交的（partially committed）
当事务中的最后一个操纵执行完成，但由于操纵都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。
失败的（failed）
当事务处在活动的大概部分提交的状态时，大概碰到了某些错误（数据库自身的错误、操纵体系错误大概直接断电等）而无法继续执行，大概人为的制止当前事务的执行，我们就说该事务处在失败的状态。
中断的（aborted）
假如事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操纵还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为回滚。当回滚操纵执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中断的状态。

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';

复制代码

提交的（committed）
当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。
一个基本的状态转换图如下所示：

图中可见，只有当事务处于提交的大概中断的状态时，一个事务的生命周期才算是结束了。对于已经提交的事务来说，该事务对数据库所做的修改将永世生效，对于处于中断状态的事务，该事务对数据库所做的所有修改都会被回滚到没执行该事务之前的状态。
2. 如何使用事务

使用事务有两种方式，分别为显式事务和隐式事务。
2.1 显式事务

步骤1： START TRANSACTION 大概 BEGIN ，作用是显式开启一个事务。

mysql> BEGIN;
#或者
mysql> START TRANSACTION;

复制代码

START TRANSACTION 语句相较于 BEGIN 特殊之处在于，后边能跟随几个修饰符：

READ ONLY ：标识当前事务是一个只读事务，也就是属于该事务的数据库操纵只能读取数据，而不能修改数据。

补充：只读事务中只是不允许修改那些其他事务也能访问到的表中的数据，对于临时表来说（我们使用 CREATE TMEPORARY TABLE 创建的表），由于它们只能再当前会话中可见，所有只读事务其实也是可以对临时表举行增、删、改操纵的。

READ WRITE ：标识当前事务是一个读写事务，也就是属于该事务的数据库操纵既可以读取数据，也可以修改数据。
WITH CONSISTENT SNAPSHOT ：启动一致性读。

比如：

START TRANSACTION READ ONLY; # 开启一个只读事务

复制代码

START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT # 开启只读事务和一致性读

复制代码

START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT # 开启读写事务和一致性读

复制代码

注意：

READ ONLY和READ WRITE是用来设置所谓的事务访问模式的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时即设置为只读的也设置为读写的，以是不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边。

假如我们不显式指定事务的访问模式，那么该事务的访问模式就是读写模式

步骤2： 一系列事务中的操纵（主要是DML，不含DDL）
步骤3： 提交事务或中断事务（即回滚事务）

# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;

复制代码

# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;
# 将事务回滚到某个保存点。
mysql> ROLLBACK TO [SAVEPOINT]

复制代码

此中关于SAVEPOINT相关操纵有：

# 在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
SAVEPOINT 保存点名称;

复制代码

# 删除某个保存点
RELEASE SAVEPOINT 保存点名称;

复制代码

2.2 隐式事务

MySQL中有一个体系变量 autocommit ：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)

复制代码

当然，假如我们想关闭这种自动提交的功能，可以使用下边两种方法之一：

显式的的使用 START TRANSACTION 大概 BEGIN 语句开启一个事务。这样在本次事务提交大概回滚前会暂时关闭掉自动提交的功能。
把体系变量 autocommit 的值设置为 OFF ，就像这样：

SET autocommit = OFF;
#或
SET autocommit = 0;

复制代码

UPDATE accout SET balance = blacnce - 10 WHERE id = 1; # 此时这条 DML 操作是一个独立的事务
UPDATE accout SET balance = blacnce + 10 WHERE id = 1; # 此时这条 DML 操作是一个独立的事务

复制代码

2.3 隐式提交数据的情况

数据定义语言（Data definition language，缩写为：DDL）
数据库对象，指的就是数据库、表、视图、存储过程等结构。当我们CREATE、ALTER、DROP等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事务。即：

BEGIN;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其他语句
CREATE TABLE ... # 此语句会隐式的提交前边语句所属于的事务

复制代码

隐式使用或修改mysql数据库中的表
当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

事务控制或关于锁定的语句

当我们在一个事务还没提交大概回滚时就又使用 START TRANSACTION 大概 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务。即：

BEGIN;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其他语句
BEGIN; # 此语句会隐式的提交前边语句所属于的事务

复制代码

注意：当上一个 BEGIN 事务，没有做提交的时间，又再执行了一个 BEGIN 事务，则上一个，在这个 BEGIN 上一个开启的事务，就会被隐式的自动提交到事务了 。

BEGIN;
SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其他语句
BEGIN; # 此语句会隐式的提交前边语句所属于的事务

复制代码

当前的 autocommit 体系变量的值为 OFF ，我们手动把它调为 ON 时，也会隐式的提交前边语句所属的事务。

使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。

加载数据的语句

使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

关于MySQL复制的一些语句

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句会隐式的提交前边语句所属的事务

其他的一些语句

使用ANALYZE TABLE、CACHE INDEX、CAECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。
2.4 使用举例1：提交与回滚

我们看下在 MySQL 的默认状态下，下面这个事务最后的处理结果是什么。
情况1：

CREATE TABLE user(name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;
BEGIN;
INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;
SELECT * FROM user;

复制代码

运行结果（1 行数据）：

mysql> commit;
Query OK, 0 rows affected (0.00 秒)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 秒)
mysql> INSERT INTO user SELECT '李四';
Query OK, 1 rows affected (0.00 秒)
mysql> INSERT INTO user SELECT '李四';
Duplicate entry '李四' for key 'user.PRIMARY'
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 秒)
mysql> select * from user;
+--------+
| name |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

复制代码

情况2：

CREATE TABLE user (name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;
INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;

复制代码

运行结果（2 行数据）：

mysql> SELECT * FROM user;
+--------+
| name |
+--------+
| 张三 |
| 李四 |
+--------+
2 行于数据集 (0.01 秒)

复制代码

情况3：

CREATE TABLE user(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;
INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;
SELECT * FROM user;

复制代码

运行结果（1 行数据）：

mysql> SELECT * FROM user;
+--------+
| name |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

复制代码

当我们设置autocommit=0时，不论是否采用 START TRANSACTION 大概 BEGIN 的方式来开启事务，都需要用 COMMIT 举行提交，让事务生效，使用 ROLLBACK 对事务举行回滚。

当我们设置 autocommit=1 时，每条 SQL 语句都会自动举行提交。不过这时，假如你采用 START TRANSACTION 大概 BEGIN 的方式来显式地开启事务，那么这个事务只有在 COMMIT 时才会生效，在 ROLLBACK 时才会回滚。

2.5 使用举例2：测试不支持事务的engine

myisam 存储引擎，不支持事务，InnoDB 存储引擎，支持事务。

CREATE TABLE test1(i INT) ENGINE=InnoDB;
CREATE TABLE test2(i INT) ENGINE=MYISAM;

复制代码

针对于InnoDB表

BEGIN;
INSERT INTO test1 VALUES(1);
ROLLBACK;
SELECT * FROM test1;

复制代码

结果：没有数据，回滚乐成。
针对于MYISAM表：

BEGIN;
INSERT INTO test1 VALUES(1);
ROLLBACK;
SELECT * FROM test2;

复制代码

结果：有一条数据
2.6 使用举例3：SAVEPOINT (保存回滚点)

创建表并添加数据：

CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15),
balance DECIMAL(10,2)
);
INSERT INTO account(NAME,balance)
VALUES
('张三',1000),
('李四',1000);

复制代码

BEGIN;
UPDATE account SET balance = balance - 100 WHERE NAME = '张三';
UPDATE account SET balance = balance - 100 WHERE NAME = '张三';
SAVEPOINT s1; # 设置保存点
UPDATE account SET balance = balance + 1 WHERE NAME = '张三';
ROLLBACK TO s1; # 回滚到保存点

复制代码

结果：张三：800.00

ROLLBACK;

复制代码

结果：张三：1000.00（回到了 BEGIN 设置事务的地方）
3. 事务隔离级别

注意： 是事务的隔离级别，以是是对配置了事务上才有结果，作用的。
MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称为一个会话（ Session ）。每个客户端都可以在自己的会话中向服务器发出哀求语句，一个哀求语句大概是某个事务的一部分，也就是对于服务器来说大概同时处理多个事务。事务有隔离性的特性，理论上在某个事务对某个数据举行访问时，其他事务应该举行列队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样对性能影响太大，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，那就看二者如何衡量取舍了。
3.1 数据准备

CREATE TABLE student (
studentno INT,
name VARCHAR(20),
class varchar(20),
PRIMARY KEY (studentno)
) Engine=InnoDB CHARSET=utf8;

复制代码

然后向这个表里插入一条数据：

INSERT INTO student VALUES(1, '小谷', '1班');

复制代码

现在表里的数据就是这样的

mysql> select * from student;
+-----------+--------+-------+
| studentno | name | class |
+-----------+--------+-------+
| 1 | 小谷 | 1班 |
+-----------+--------+-------+
1 row in set (0.00 sec)

复制代码

3.2 数据并发问题

针对事务的隔离性和并发性，我们怎么做取舍呢？先看一下访问相同数据的事务在不保证串行执行（也就是执行完一个再执行另一个）的情况下大概会出现哪些问题：

脏写（ Dirty Write ）

对于两个事务 Session A、Session B，假如事务Session A 修改了另一个未提交事务Session B 修改过的数据，那就意味着发生了脏写，示意图如下：

Session A 和 Session B 各开启了一个事务，Sesssion B 中的事务先将studentno列为1的记录的name列更新为'李四'，然后Session A中的事务接着又把这条studentno列为1的记录的name列更新为'张三'。假如之后Session B中的事务举行了回滚，那么Session A中的更新也将不复存在，这种征象称之为脏写。这时Session A中的事务就没有结果了，明明把数据更新了，最后也提交事务了，最后看到的数据什么厘革也没有。这里大家对事务的隔离性比较相识的话，会发现默认隔离级别下，上面Session A中的更新语句会处于等候状态，这里只是跟大家阐明一下会出现这样的征象。

脏读（ Dirty Read ）

对于两个事务 Session A、Session B，Session A 读取了已经被 Session B 更新但还没有被提交的字段。之后若 Session B 回滚，Session A 读取的内容就是临时且无效的。

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，假如读到列name的值为'张三'，而 Session B中的事务稍后举行了回滚，那么Session A中的事务相称于读到了一个不存在的数据，这种征象就称之为脏读。

不可重复读（ Non-Repeatable Read ）

对于两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。之后 Session A 再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

我们在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了studentno列为1的记录的列name的值，每次事务提交之后，假如Session A中的事务都可以查看到最新的值，这种征象也被称之为不可重复读。
不可重复读：简单的说就是，你读取的数据，在一个事务当中(当前事务中)读取的值，居然不是重复的(不是一样的)，而是被其他事务修改的。（读不到重复的信息，读不到当前事务的同一个信息，每次读的还不一样）

幻读（ Phantom ）

对于两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插入了一些新的行。之后, 假如 Session A 再次读取同一个表, 就会多出几行。那就意味着发生了幻读。

Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为'张三'的记录；之后Session B中提交了一个隐式事务，该事务向表student中插入了一条新记录；之后Session A中的事务再根据相同的条件 studentno > 0查询表student，得到的结果集中包罗Session B中的事务新插入的那条记录，这种征象也被称之为幻读。我们把新插入的那些记录称之为幻影记录。

幻读：强调的是一个事务按照某个相同条件多次读取 记录时，后读取时读取了之前没有读到的记录 。

3.3 SQL中的四种隔离级别

上面介绍了几种并发事务执行过程中大概碰到的一些问题，这些问题有轻重缓急之分，我们给这些问题按照严峻性来排一下序：

脏写 > 脏读 > 不可重复读 > 幻读

复制代码

我们愿意舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，并发问题发生的就越多。 SQL标准中设立了4个隔离级别：

READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库体系的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍旧存在。
REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据举行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操纵。所有的并发问题都可以避免，但性能非常低下。能避免脏读、不可重复读和幻读。

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严峻程度的问题，具体情况如下：