MySQL 基础 ————事务与隔离级别总结

引言

 

在处理并发读或写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题：

共享锁（shared lock）和排它锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

读锁是共享的，也就是互相不阻塞。多个客户在同一时刻可以同时读取同一个资源，互不干扰。

写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，只有这样，才能保证在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。 

在实际数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL 会通过锁定防止其他用户读取同一数据。

一、事务的概念

事务主要针对查询以外的其他几项操作：插入、更新、删除，将多条SQL合并到一个执行单元中，要么全部执行成功，要么全部回滚。MySQL的TCL 事务控制语言，就是为事务而设计，接下来我们来总结一下。

二、事务的 ACID 属性

1、原子性

原子性是指事务是一个不可分割的工作单元，不可分割，意味着要么全部执行，要么全部回滚。

2、一致性

事务必须使数据库从一个一致状态，变换到另一个一致状态。

3、隔离性

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是不可见的。并发的多个事务之间互不干扰。

4、持久性

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

三、事务操作的演示

3.1 事务提交变量：autocommit

MySQL控制事务提交的变量叫做'autocommit'，可以通过如下语句进行查看：

SHOW VARIABLES LIKE 'autocommit';

默认情况下是开启的，即执行SQL后会自动提交事务。如果希望手动提交事务，需要将ON —> OFF。但这种方法只在当前会话中有效，只要新开一个连接或是MySQL会话，就会恢复自动提交事务：

SET autocommit = 0;

3.2 开启事务和提交事务

通过 set autocommit = 0 即表示开启显式事务（默认是隐式事务），其实开启事务的语句是下面这句，不过一般都会省略：

START TRANSACTION;

开启事务后，我们就可以执行多条SQL，一般是 insert、update、delete 语句等，且不会自动提交。

当我们执行完这些SQL，想提交的时候，可以执行下面语句：

COMMIT; -- 提交事务

ROLLBACK; -- 回滚事务

注意：事务中的语句仅支持 select、insert、update、delete，这四种，其他的如 create 等是不支持的。

3.3 案例演示

假设现在有两个会话连接到MySQL数据库，第一个连接关闭事务自动提交：

先查询 emp 表的一条记录，准备后续的更新操作：

将名称更新为'张三丰'：

切换到另一个会话，查看该条记录，可以看到记录还没有任何变化：

切换回第一个会话，提交事务：

此时再切换到另一个事务查看该条记录，已经变成了 “张三丰”：

四、并发事务与隔离级别

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。

1、脏读：对于任意两个事务：T1、T2。T1读取了已经被T2 更新但还没有提交的字段之后，若 T2 回滚， T1 读取到的内容是临时且无效的。读取了事务执行过程中的中间数据。

2、不可重复读：对于两个事务 T1、T2 。T1 读取了一个字段，然后T2 更新了该字段之后，T1 再次读取同一个字段，值就不同了。在同一个事务中，受其他事务更新影响，两次读取的数据不一致。

3、幻读：对于两个事务 T1、T2。T1 读取一张表，然后 T2 在该表中插入了一些新的记录。之后，如果 T1 再次读取同一张表，就会多出几行。在同一个事务中，受其他事务插入删除影响，两次读取的记录数量有变化。

4.1 隔离级别介绍

为了针对上述的三种并发问题，每种数据库都会有自己的一套隔离级别，它描述了一个事务与其他事务隔离的程度。数据库规定了多种事务隔离级别，不同的隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但相应的，并发性就越差。

数据库提供四种隔离级别：读未提交、读已提交、可重复读（MySQL默认）、串行化。Oracle 仅支持读已提交和串行化，默认是读已提交。MySQL则四种全部支持，默认是可重复读。

4.2 READ-UNCOMMITTED : 读未提交

该隔离级别允许读取未被其他事务提交的变更，脏读、不可重复读和幻读的问题都会出现。

4.3 READ-COMMITTED: 读已提交

只允许读取已经被其他事务提交的变更，可避免脏读，但不可重复读和幻读问题仍然会出现。

4.4 REPEATABLE-READ : 可重复读

确保事务内可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复度，但幻读问题仍然存在。

4.5 SERIALIZABLE : 串行化事务

确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除等操作，所有并发问题都可以解决，但性能非常低。

4.6 查看、设置隔离级别

通过 SELECT 子句，查看隔离级别：

SELECT @@tx_isolation;

通过 SET 子句，设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- SESSION 可省略

注意，上述方法设置的隔离级别仅适用于当前连接，如果希望设置全局的隔离级别，可以使用 GLOBAL 关键字：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

GLOBAL 关键字可以保证所有新的连接都会使用设置的隔离级别，但是旧的连接如果没有关闭，依然会采用原来的隔离级别。另外，全局隔离级别设置仅针对本次MySQL服务有效，如果MySQL服务重启，则依然是默认的隔离级别，除非在配置文件中改变MySQL默认隔离级别。

4.7 隔离级别与并发问题的关系

	脏读	不可重复读	幻读
READ-UNCOMMITTED	出现	出现	出现
READ-COMMITTED (Oracle默认)	解决	出现	出现
REPEATABLE-READ (MySQL默认)	解决	解决	出现
SERIALIZABLE	解决	解决	解决

以上就是关于隔离级别与并发问题的总结和归纳。

重点是事务的ACID 属性，三个并发问题的定义，四种隔离级别的定义，四种隔离级别与三个并发问题的关系，以及如何查看数据库的隔离级别，设置数据库的隔离级别。

4.8 事务日志

事务日志可以帮助提高事务的效率。

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加方式，因此写日志的操作是磁盘上一小块区域的顺序IO，而不是存储数据时的随机IO，所以采用事务日志相对较快。

事务日志持久以后，内存中被修改的数据可以在后台慢慢刷回到磁盘。

目前大多数存储引擎都是这样实现的，通常称之为“预写式日志（Write-Ahead Logging）”，修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没写回磁盘，如果此时系统崩溃，那么存储引擎在重启时是可以自动恢复这部分修改的数据的。但具体的恢复方式可能不尽相同。

五、锁粒度

一种提高共享资源并发性的的方式就是让锁定对象更有选择性，尽量只锁定需要修改的部分数据。更理想的方式是，只对会修改的数据片进行精确的锁定。但是锁的数量也会增大系统的开销。

所谓锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库没有提供更多的选择，一般都是在表上施加行级锁，并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能的提供更好的性能。

MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

在存储引擎的设计中，锁管理是个非常重要的决定。

5.1 表锁

表锁是MySQL最基本的锁策略，并且是开销最小的锁策略。用户在对表进行写操作（插入、删除、更新）前，需要先获得写锁，这会阻塞其他用户对该表的所有读和写操作。只有没有写锁时，其它读取的用户才能获得读锁，读锁之间是不相互阻塞的。

在特定情况下，表锁也有良好的性能。例如，READ LOCAL表锁支持某种类型的并发写操作。

写锁比读锁有更高的优先级。一个写锁请求可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面。

存储引擎可以管理自己的锁，如InnoDB的行锁，但MySQL在某些情景下会使用表锁，从而忽略存储引擎的锁机制。例如，MySQL会为ALTER TABLE之类的语句使用表锁，忽略存储引擎的锁机制。

5.2 行锁

行级锁可以最大程度地支持并发处理，但同时锁开销也是最大的。行级锁只在存储引擎层实现，如InnoDB、XtraDB等，而MySQL服务器层没有实现。