[大数据]MySQL-事务与锁

• 事务 （Transaction） 是用户定义的一个操作序列，这些操作要么全部成功要么全部失败，是一个不可分割的工作单位(构成单一逻辑工作单元的操作集合)。
• 如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。
• 如果事务遇到错误且必须取消或回滚，则所有更改均被清除

例：对于银行系统而言，转账业务是银行最基本的、且最常用的业务，要保证转账业务的正常进行就必须要用到事务处理。

1 逻辑架构和存储引擎

MySQL服务器逻辑架构从上往下可以分为三层：

1. 第一层：处理客户端连接、授权认证等。
2. 第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。
3. 第三层：存储引擎，负责MySQL中数据的存储和提取。

MySQL中服务器层不管理事务，事务是由存储引擎实现的。 MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyIsam、Memory等。

2 事务操作

2.1 基本命令

START TRANSACTION
	[transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic: {
	WITH CONSISTENT SNAPSHOT
	| READ WRITE
	| READ ONLY
}
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

命令解释说明：

• START TRANSACTION 或 BEGIN 开始新的事务。
• COMMIT 提交当前事务，使其更改永久化。
• ROLLBACK 回滚当前事务，取消其更改。
• SET autocommit 禁用或启用当前会话的默认自动提交模式。

2.2 执行流程

2.3 关闭自动提交

MySQL中默认采用的是自动提交（autocommit）模式，在自动提交模式下，每个sql语句都会被当做一个事务执行提交操作。

查看状态

SHOW VARIABLES LIKE 'autocommit';

关闭自动提交的方法有两种：

1. 显示地关闭自动提交：

SET autocommit=0;

通过将 autocommit 变量设置为零禁用自动提交模式后 ，对事务安全表（例如 InnoDB 或 的 那些NDB ）的更改 不会立即永久生效。您必须使用 COMMIT 将更改存储到磁盘或ROLLBACK 忽略更改。

该设置针对连接的，在一个连接中修改了参数，不会对其他连接产生影响

2. 隐式地关闭自动提交:

START TRANSACTION;
......
COMMIT;

使用 START TRANSACTION ，自动提交将保持禁用状态，直到您使用 COMMIT 或结束事务ROLLBACK 。自动提交模式然后恢复到其先前的状态。

start transaction;只针对当前操作，不会修改系统会话变量@@autocommit的值。

2.4 开启事务

START TRANSACTION
启动事务后把所有后继的SQL语句看作事务的组成部分，直到提交或回滚事务为止。
该语句标记一个显式事务的开始点，即事务开始

语法：

START { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ]
]

示例：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
......

2.5 提交事务

COMMIT TRANSACTION
提交事务后，事务结束，事务期间对数据库所做的任何修改将应用到数据库，事务结束。

语法：

COMMIT { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable ] ]

示例：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

在MySQL中，存在一些特殊的命令，如果在事务中执行了这些命令，会马上强制执行commit提交事务；
比如：DDL语句(create table/drop table/alter/table) ，lock tables语句等等。

2.6 回滚事务

**ROLLBACK TRANSACTION **
回滚事务后，事务结束，放弃事务期间所做的任何修改，事务结束。
该语句将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。

语法：

ROLLBACK { TRAN | TRANSACTION }
	[ transaction_name | @tran_name_variable
	| savepoint_name | @savepoint_variable ]

示例：

2.7 保存点

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

语法：

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

保存点操作：
1. 设置保存点 savepoint a
2. 取消部分事务 rollback to SAVEPOINT a
3. 取消全部事务 rollback

示例：

3 事务特性

3.1 MySQL日志

MySQL的日志有很多种，日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志(bin log) 和事务日志(包括redo log和undo log)。

InnoDB存储引擎提供了两种事务日志：

• redo logg(重做日志)：可以理解是当服务宿机时，重启后强制保持一致
• undo log(回滚日志)：可以理解是如果回滚时回滚到之前某一个状态

其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础

3.2 原子性（Atomic）

原子性 是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。

实现原理：undo log(回滚日志)
在Mysql里数据每次修改前，都首先会把修改之前的数据作为历史保存一份到undo log里面的，数据里面会记录操作该数据的事务ID。当事务执行失败或调用rollback，导致事务需要回滚，便可以利用undolog中的信息将数据回滚到修改之前的状态。

undo log 主要分为两种：
1. insert undo log
代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
2. update undo log
事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；
所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

3.3 持久性 (Durability)

持久性 也称永久性（Permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。

刷脏

InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）

刷脏引发的问题

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时BufferPool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。解决这个问题通过redo log来解决。

实现原理：redo log
重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间纠正不完整事务写入的数据redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志。

3.4 一致性（Consistency）

事务操作成功后，数据库所处的状态和它的业务规则是一致的，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）

一致性是事务追求的最终目标：原子性、持久性和隔离性，都是为了保证数据库状态的一致性。

3.5 隔离性（Isolation）

隔离性 是指事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。

隔离性主要考虑最简单的 读操作 和 写操作

隔离性主要可以分为两个方面：

• (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性
• (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性

3.5.1 事务的并发

并发情况下，读操作可能存在的三类问题:

• 脏读
• 不可重复读
• 幻读

3.5.2 脏读

当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。

3.5.3 不可重复读

在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读

3.5.4 幻读

在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。

3.6 事务隔离级别

SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差

• 读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。

• 可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。

• 在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）。

• InnoDB默认的隔离级别是RR

查看当前事物级别：

select @@transaction_isolation;

4 MVCC

MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议，MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突。

4.1 相关概念

当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

MVCC 是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

4.2 MVCC实现原理

MVCC实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现.

4.2.1隐式字段

InnoDB存储引擎在每行数据的后面添加了三个隐藏字段

• DB_TRX_ID(6字节)：表示最近一次对本记录行作修改（insert | update）的事务ID。
• DB_ROLL_PTR(7字节)：回滚指针，指向当前记录行的undo log信息
• DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID。

DB_ROW_ID:当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。

4.2.2 记录行修改的具体流程

事务A（事务ID为2）对该记录做出了修改，将Honor列内容改为"fmvp"

1. 事务A先对该行加排它锁
2. 然后把该行数据拷贝到undo log中，作为旧版本
3. 拷贝完毕后，修改该行的Honor为"fmvp"，并且修改DB_TRX_ID为2（事务A的ID）, 回滚指针指向拷贝到undo log的旧版本。（然后还会将修改后的最新数据写入redo log）
4. 事务提交，释放排他锁

5. 接着事务B（事务ID为3）修改同一个记录行，将Name修改为"iguodala"

版本链：每行数据的隐藏列中的undo log也会指向更早版本的undo log，从而形成一条版本链。

不同事务或者相同事务的对同一记录行的修改，会使该记录行的undo log成为一条链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录.

4.2.3 Read View

概念： Read View 是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID 。

作用： Read View 主要是用来做可见性判断的，把生成的读视图 (Read View)当作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护）。

当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大

changes_visible() 判断可见性

把 Read View 简单的理解成有三个全局属性

• m_ids：一个数值列表,用于维护 Read View 生成时刻系统 正活跃的事务 ID 列表
• up_limit_id：lower water remark,是 trx_list 列表中事务 ID 最小的 ID
• low_limit_id： hight water mark,ReadView 生成时刻系统尚未分配的下一个事务 ID ,也就是目前已出现过的事务 ID 的最大值 + 1

为什么是 low_limit ? 因为它也是系统此刻可分配的事务 ID 的最小值

假设当前列表里的事务id为[80,100]。
1.如果你要访问的记录版本的事务id为50，比当前列表最小的id80小，那说明这个事务在之前就提交了，所以对当前活动的事务来说是可访问的
2.如果你要访问的记录版本的事务id为90,发现此事务在列表id最大值和最小值之间，那就再判断一下是否在列表内，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，所以版本可以被访问
3.如果你要访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在ReadView生成之后才发生的，所以不能被访问

5 锁（Lock）

锁是计算机在执行多线程或线程时用于并发访问同一共享资源时的同步机制，MySQL中的锁是在服务器层或者存储引擎层实现的，保证了数据访问的一致性与有效性。

5.1 锁机制的基本原理

• 事务在修改数据之前，需要先获得相应的锁；
• 获得锁之后，事务便可以修改数据；
• 该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁

5.2 锁机制的必要性

并发用户访问同一数据，锁机制可以避免数据不一致问题的发生。

5.3 MySQL锁分类

5.4 共享锁

又称之为读锁，S锁。当事务A对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能加写锁。只有当事务A上的读锁被释放后，其他事务才能对其添加写锁。

应用场景
共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免"不可重复读"的问题出现。

实现方式

select * from dept WHERE dept_id=50 LOCK IN SHARE MODE;

测试时可以在客户端打开两个查询端口，第一个端口执行上面命令，第二个端口这时可以查询，但是执行修改操作时会被阻塞，除非第一个端口执行相关操作，如commit,rollback等

查看锁的状态

SELECT * FROM PERFORMANCE_SCHEMA.data_locks;

示例：

5.5 排它锁

又称之为写锁、独占锁，排它锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读锁，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。

MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

应用场景
写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。

实现方式

select * from dept WHERE dept_id=50 FOR UPDATE;

注意：使用排他锁之前必须关闭MySQL的自动提交，显示地关闭自动提交：set autocommit=0。或者隐式地关闭自动提交：开启一个事务START TRANSACTION

5.6 锁的粒度分类

• 表级锁:开销小，加锁快，不会出现死锁，锁定力度大，发生冲突所的概率高，并发度低。
• 行级锁:开销大，加锁慢，会出现死锁，锁定力度最小，发生锁冲突的概率最低，并发度高。
• 页面锁:开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定力度介于表和行行级锁之间，并发度一般。

MyISAM和MEMORY存储引擎采用表级锁
InnoDB支持行级锁、表级锁，默认情况采用行级锁

5.7 乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

应用场景
适用于读多写少，因为如果出现大量的写操作，写冲突的可能性就会增大，业务层需要不断重试，大大降低系统性能。

实现方式
一般使用数据版本（Version）记录机制实现，在数据库表中增加一个数字类型的 “version” 字段来实现

5.8 悲观锁

具有强烈的独占和排他特性，每次去拿数据的时候都认为别人会修改，对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。

应用场景：适用于并发量不大、写入操作比较频繁、数据一致性比较高的场景。
实现方式：select…for update是MySQL提供的实现悲观锁的方式，属于排它锁。

在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

5.9 死锁

当某组资源的两个或多个线程之间有循环相关性时，将发生死锁。
死锁是一种可能发生在任何多线程系统中的状态，而不仅仅发生在关系数据库管理系统中。

死锁：事务1的解锁条件需要用到事务2的内容，事务2的解锁条件需要事务1的内容，形成无法解开的死锁

给MyISAM表施加表级锁不会导致死锁问题的发生，这是由于MyISAM总是一次性地获得SQL语句的全部锁。给InnoDB表施加行级锁可能导致死锁问题的发生，这是由于执行SQL语句期间，可以继续施加行级锁。

为尽可能避免死锁的发生，用户应该遵循以下原则：

• 在所有的事务中都按同一顺序来访问各个表。尽可能利用存储过程来完成一个事务，以便能保证对各表的访问次序都是一致的。
• 事务应该尽量小且应尽快提交。
• 尽量避免人工输入操作出现在事务中。
• 尽量避免同时执行诸如【INSERT】、【UPDATE】和【DELETE】等数据修改语句。