mysql 应该知道的知识

1、什么是锁

锁是数据库系统区别于文件系统的一个关键特性。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。例如：操作缓冲池中的 LRU 列表，删除、添加、移动 LUR 列表中的元素。

对于任何一种数据库来说都需要有相应的锁定机制，所以 MySQL 自然也不能例外。

MySQL 数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

MySQL 常用存储引擎(MyISAM，InnoDB)用了两种类型（级别）的锁定机制：表级锁定，行级锁定。

1）表级锁 

表级别的锁定是 MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

使用表级锁定的主要是 MyISAM、MEMORY、CSV 等一些非事务性存储引擎。

2）行级锁 

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是 InnoDB 存储引擎。

总结如下：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

例如，下图只是对 myisam 表修改一行记录：

其他 insert 操作就需要等待上个 update 语句执行完成，再执行 insert 操作，这时候就会产生表锁。

2、InnoDB锁的类型

InnoDB 存储引擎实现了如下两种标准的行级锁：

• 共享锁（S Lock）：允许事务读一行数据。但不能修改，增加，删除数据。

• 排他锁（X Lock）：获准排他锁的事务既能读数据，又能修改数据。

如果一个事务 t1 已近获得了行 r 的共享锁，那么另外的事务 t2 可以获得行 r 的共享锁，因为读取并没有改变行 r 的数据，称这种情况为锁兼容（Lock Compatible）。但若有其他的事务想获得行 r 的排它锁，则必须等待事务 t1，t2 释放行 r 的共享锁——这种情况称为锁不兼容(confilict)。

此外，InnoDB 存储引擎支持多粒度（granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持了一种额外的锁方式，称为意向锁（Intention Lock）。

意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

如下图，若将上锁的对象看成一颗树：

那么最下层的对象（行记录）上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。

如果需要对页上的记录 r 进行上 X 锁，那么分别需要对数据库 A、表、页上意向锁，最后对记录 r 上 X 锁，若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。

举例来说，在对记录 r 加 X 锁之前，已近有事务对表 1 进行了 S 表锁，那么表 1 上已存在 S 锁，之后事务需要对记录 r 表 1 上加 IX ， 由于不兼容，所以该事务，需要等待表锁操作的完成。

InnoDB 存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁，设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

• 意向共享锁（ intention shared lock, Is），事务有意向对表中的某些行加共享锁（S锁）

• 意向排它锁（intention exclusive lock,IX），事务有意向对表中的某些行加排他锁（X锁）

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

表级意向锁与行锁的兼容性：

• S：共享锁

• X：排它锁

• IS：意向共享锁

• IX：意向排它锁

• 排它锁（X）：与任何锁都不兼容

• 共享锁（S）：只兼容共享锁和意向共享锁

• 意向锁（IS,IX）: 互相兼容，行级别的锁只兼容共享锁

3、一致性锁定读

用户有时候需要显示地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于 select 的只读操作。InnoDB 存储引擎对于 select 语句支持两种一致性的锁定读操作：

select ... for update;

select ... lock in share mode;

select … for update 对读取的行记录加一个 X 锁，其他事务不能对已锁定的行加上任何锁。

select … lock in share mode 对读取的行记录加一个 S 锁，其他事务可以向被锁定的加 S  锁，但是如果加 X 锁，则会组赛。

此外 select ... for update , select ... lock in share mode 必须在一个事务中，当事务提交了，锁也就释放了。因此在使用上诉两句select 锁定语句时，务必加上BEGIN，START TRANSACTION 或者 SET AUTOCOMMIT=0。

4、一致性非锁定读

在默认的隔离级别下，一致读是指 InnoDB 在多版本控制中在事务的首次读时产生一个镜像，在首次读时间点之前，其他事务提交的修改可以读取到，而首次读时间点之后，其他事务提交的修改或者是未提交的修改，都读取不到。

唯一例外的情况，是在首次读时间点之前的本事务未提交的修改数据可以读取到。

在读取提交数据隔离级别下，一致读的每个读取操作都会有自己的镜像。一致读操作不会施加任何的锁，所以就不会阻止其他事务的修改动作。

比如最经典的 mysqldump --single-transaction 备份的时候就是把当前的事务隔离级别改变为可重复读并开启一个一致性事务的快照 , 就是一致性非锁定读。

一致读在某些 DDL 语句下不生效：

• 碰到 drop table 语句时，由于 InnoDB 不能使用被 drop 的表，所以无法实现一致读 。

• 碰到 alter table 语句时，也无法实现一致读 。

• 当碰到 insert into… select，update … select 和 create table … select 语句时，在默认的事务隔离级别下，语句的执行更类似于在读取提交数据的隔离级别下。

5、自增长与锁

自增长在数据库中非常常见的一种属性，也是很多 DBA 或开发人员首选主键方式。在 InnoDB 存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。

插入操作会依据这个自增长的计数器加 1 赋予自增长列。这个实现方式称作 AUTO-INC Locking（自增锁）。 这种自增锁是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql 语句后立即释放。

AUTO-INC Locking 从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。

• 首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入完成。

• 其次，对于 insert …select 的大数据量的插入会影响插入的性能，因为另一个事务中插入会被阻塞。

Innodb_autoinc_lock_mode 来控制自增长的模式，改参数的默认值为1。

InnoDB 提供了一种轻量级互斥量的自增长实现机制，大大提高了自增长值插入的性能。提供参数 innodb_autoinc_lock_mode 来控制自增长锁使用的算法，默认值为 1。他允许你在可预测的自增长值和最大化并发插入操作之间进行权衡。

插入类型的分类：

innodb_autoinc_lock_mode 在不同设置下对自增长的影响：

• innodb_autoinc_lock_mode = 0 ：

  MySQL 5.1.22版本之前自增长的实现方式，通过表锁的 AUTO-INC Locking 方式。

• innodb_autoinc_lock_mode = 1（默认值）： 

  对于『simple inserts』，该值会用互斥量（mutex）对内存中的计数器进行累加操作。对于『bulk inserts』会用传统的 AUTO-INC Locking 方式。这种配置下，如果不考虑回滚，自增长列的增长还是连续的。需要注意的是：如果已经使用 AUTO-INC Locking 方式去产生自增长的值，而此时需要『simple inserts』操作时，还需要等待 AUTO-INC Locking 的释放。

• innodb_autoinc_lock_mode = 2 ：

  对于所有『insert-like』自增长的产生都是通过互斥量，而不是AUTO-INC Locking方式。这是性能最高的方式。但会带来一些问题：因为并发插入的存在，每次插入时，自增长的值是不连续的基于statement-base replication会出现问题。

因此，使用这种方式，任何情况下都需要使用row-base replication，这样才能保证最大并发性能和replication的主从数据的一致。

二、行锁的几种算法

• Record Lock：单个行记录上的锁 。

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。

• Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

• Insert Intention Locks：插入意向锁。

1、Record Lock

Record Lock 总是会去锁住索引记录， 如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这是 InnoDB 存储引擎会使用隐式的主键来进行锁定。

行级锁是施加在索引行数据上的锁，比如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 语句是在 t.c1=10 的索引行上增加锁，来阻止其他事务对对应索引行的insert/update/delete操作。

行锁总是在索引记录上面加锁，即使一张表没有设置任何索引，InnoDB 会创建一个隐藏的聚簇索引，然后在这个索引上加上行锁。例如:

create table t (c1 int primary key);

insert into t select 1;

insert into t select 3;

insert into t select 10;

# 会话A

start transaction;

update t set c1=12 where c1 = 10 ;

# 会话B:

mysql> update t set c1=11 where c1=10;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

会阻止该事务对索引行上的修改

当一个 InnoDB 表没有任何索引时， 则行级锁会施加在隐含创建的聚簇索引上，所以说当一条 SQL 没有走任何索引时，那么将会在每一条聚集索引后面加 X 锁，这个类似于表锁，但原理上和表锁应该是完全不同的。例：

# 删除表t的主键索引

alter table t drop primary key;

开启会话1：

start transaction;

update t set c1=11 where c1=10;

开启会话2：

start transaction;

update t set c1=8 where c1=10;

这个时候发生了锁等待，

这时候开启会话3，锁等待发生了什么：

mysql> select * from sys.innodb_lock_waits\G;

如下截图：

2、Gap Lock

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件 的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个"间隙"加锁。

间隔锁是施加在索引记录之间的间隔上的锁，锁定一个范围的记录、但不包括记录本身，比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE 语句，尽管有可能对 c1 字段来说当前表里没有=15的值，但还是会阻止=15的数据的插入操作，是因为间隔锁已经把索引查询范围内的间隔数据也都锁住了，间隔锁的使用只在部分事务隔离级(可重复读级)别才是生效的 。

间隔锁只会阻止其他事务的插入操作，就是只有 insert 操作会产生 GAP 锁，update 操作不会参数 GAP 锁。例:

# 创建keme1 测试数据， 插入模拟数据

create table keme1 (id int primary key,name varchar(10));

insert into keme1 values (1,'a'),(3,'c'), (4,'d'), (5,'e'), (6,'f');

# 开启三个session 窗口，两个窗口模拟两个事务， 另外一个窗口看 两个事务发生一些间隔锁的信息

session1：

start transaction;

mysql> update keme1 set name='bb' where id between 1 and 3;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

session2:

start transaction;

mysql> insert into keme1 values (2,'bb');

# 这时候就有锁等待了

select * from sys.innodb_lock_waits\G;

使用gap lock的前置条件：

• 事务隔离级别为 REPEATABLE-READ，innodb_locks_unsafe_for_binlog 参数为0，且 sql 走的索引为非唯一索引（无论是等值检索还是范围检索）

• 事务隔离级别为 REPEATABLE-READ，innodb_locks_unsafe_for_binlog 参数为0，且 sql 是一个范围的当前读操作，这时即使不是非唯一索引也会加 gap lock

Gap Lock 的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。

可以通过两种方式来关闭 Gap Lock：

• 将事务的隔离级别设置为 READ COMMITTED

• 将参数 innodb_locks_unsafe_for_binlog 设置为 1

3、Next-Key Lock

在默认情况下，MySQL 的事务隔离级别是可重复读，并且 innodb_locks_unsafe_for_binlog 参数为 0，这时默认采用 next-key locks。

所谓 Next-Key Locks，就是记录锁和间隔锁的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

当扫描表的索引时，InnoDB 以这种形式实现行级的锁：遇到匹配的的索引记录，在上面加上对应的 S 锁或 X 锁。

因此，行级锁实际上是索引记录锁。如果一个事务拥有索引上记录 r 的一个 S 锁或 X 锁，另外的事务无法立即在 r 记录索引顺序之前的间隙上插入一条新的记录。

假设有一个索引包含值：10，11，13和20。下列的间隔上都可能加上一个 Next-Key 锁（左开右闭）。

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

在最后一个区间中，Next-Key 锁锁定了索引中的最大值到正无穷。

默认情况下，InnoDB 启用 RR 事务隔离级别。此时，InnoDB 在查找和扫描索引时会使用 Next-Key 锁，其设计的目的是为了解决『幻读』的出现。　　

当查询的索引含有唯一(主键索引和唯一索引)属性是，InnoDB 存储引擎会对 Next-Key Lock 进行优化，将其降级为 Record Lock ，即仅锁住索引本身，而不是范围。

4、Insert Intention Lock

插入意向锁是一种在数据行插入前设置的 gap 锁。这种锁用于在多事务插入同一索引间隙时，如果这些事务不是往这段 gap 的同一位置插入数据，那么就不用互相等待。

create table keme2 (a int primary key);

insert into keme2 values (10),(11),(13),(20);

开启三个会话窗口

session1：

start transaction;

mysql> select * from keme2 where a > 18 for update;

+----+

| a  |

+----+

| 20 |

+----+

1 row in set (0.00 sec)

session2;

start transaction;

mysql> insert into keme2 select 19;

客户端 A 创建了一个 keme2 表，包含 10,11,13,20 四条索引记录，然后去设置一个互斥锁在大于 18 的所有索引记录上。这个互斥锁包含了在 20 记录前的 gap 锁。

三、锁问题

通过锁机制可以实现事务的隔离性要求，使得事务可以并发地工作。锁提高了并发，但是也有有潜在的问题。不过好在因为事务隔离性的要求，锁只会带来三种问题，如果可以防止这三种情况的发生，哪将不会产生并发异常。

1、脏读

先了解脏数据、脏页、脏读。

脏页：指的是在缓冲池中已近被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。

脏数据：是指事务对缓冲池中行记录的修改，并且还没有被提交。

对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的，这并不影响数据的一致性（或者说两者最终会达到一致性，即当脏页都刷到磁盘）。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。

脏数据：是指未提交的数据，如果读到脏数据，即一个事务可以读到另外一个事务中未提交的数据，则显然违反了数据库的隔离性。

脏读：指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

脏读示例：

create table t (a int primary key);

insert into t values (1);

会话 A 并没有主动提交 2 这条插入事务，但是在会话 B 读取到了，这就是脏读。

2、不可重复读

不可重读是在一个事务内读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问同一数据集合，并做了一些 DML 操作。因此在第一个事务中的两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。

不可重复读和脏读的区别是：脏读示读到未提交的数据，而不可重复读读到确实已近提交的数据。

3、丢失更新

虽然数据库能阻止更新问题的产生，但是在生产应用还有另一个逻辑意义丢失更新问题，而导致该问题的并不是因为数据库本身的问题。实际上，在所有多用户计算机系统环境下都有可能产生这个问题。比如下面的情况：

比如一个用户账号中有 10000 元，他用两个网上银行的客户端分别进行转账操作，第一次转账 9000 人民币，因为网络和数据的关系，这时需要等待。

但是这时用户操作另一个网上银行客户端，转账 1 元，如果最终两笔操作都成功了，用户账号的余款是 9999 元，第一次转的 9000 人民币并没有得到更新，但是在转账的另一个账号却会收到这 9000 元，这导致了结果就是钱变多，而账不平。

但是银行了也很聪明啊，个人网银绑定 usb key 的，不会发生这种情况的。是的，通过 usb key 登录也许可以解决这个问题。但是更重要的是在数据库层解决这个问题，避免任何可能发生丢失更新的情况。

要避免丢失更新发生 ，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。

四、锁阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其实为了确保事务可以并发正常地运行。

在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来控制等待的时间(默认是50秒)， innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是off，不回滚）。参数 innodb_lock_wait_timeout 可以在 MySQL 数据库运行时进行调整：

在默认情况下 InnoDB 存储引擎不会回滚超时引发的错误异常。其实 InnoDB 存储引擎在大部分情况下都不会对异常进行回滚。

查看锁阻塞的信息：

select * from information_schema.innodb_trx\G; # 查看当前的事务信息

select * from information_schema.innodb_locks\G; # 查看当前的锁信息

select * from information_schema.innodb_lock_waits\G; # 查看当前的锁等待信息

可以联表查，查找自己想要的结果。

select * from sys.innodb_lock_waits\G; # 查看当前的锁等待信息

show engine innodb status\G;

还可以通过当前执行了执行了什么语句

select * from  performance_schema.events_statements_current\G;

show full processlist;

五、死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

1、数据库层面解决死锁的两种方式

①解决死锁的问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。

这种没有死锁问题的产生。在线上环境中，可能导致并发性能的下降，甚至任何一个事务都不能进行。而这锁带来的问题远比死锁问题更为严重，而这锁带来的问题原题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

②解决死锁的问题最简单的一种方法时超时，即当两个事务互相等待是，当一个等待时超过设置的某一阈值是，其中一个事务进行回滚，另一个等待的事务就能继续进行。用 innodb_lock_wait_timeout 用来设置超时的时间。

超时机制虽然简单，仅通过超时后对事务进行回滚的方式来处理，或者说其根据 FIFO 的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新很多行(比如某程序猿用死循环来执行一些事务)，占用了较多的 undo log，这是采用 FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会更多。

在 mysql 5.7.x 和 mysql 5.6.x 对死锁采用的方式：

mysql 5.6.x 是用锁等待（超时）的方式来解决， 没有自动解决死锁的问题。

mysql 5.7.x 默认开启了死锁保护机制：

2、死锁演示

如果程序是串行的，那么不可能发生死锁。死锁只存在于并发的情况，而数据库本身就是一个并发运行的程序，因此可能会发生死锁。

死锁示例：

a :创建表

create table temp(id int primary key ,name varchar(10));

insert into temp values(1,'a'),(2,'b'),(3,'c');

此时表里只有3条数据

执行步骤根据数据顺序来：

1. 事务1:

start transaction;

update temp set name='aa' where id=1;

2. 事务2:

start transaction;

update temp set name='bb' where id=2;

3. 事务1:update temp set name='aaa' where id=2;

这时候3的步骤会有锁等待， 立马执行4，就会马上产生死锁

4. 事务2: update temp set name='bbb' where id=1;

3、避免死锁发生的方法

在事务性数据库中，死锁是个经典的问题，但只要发生的频率不高，则死锁问题不需要太过担心。死锁应该非常少发生，若经常发生，则系统是不可用。

查看死锁的方法有两种：

• 通过 show engine innodb status 命令可以查看最后一个死锁的情况。

• 通过 innodb_print_all_deadlocks 参数配置可以将所有死锁的信息都打印到 MySQL 的错误日志中。

减少死锁发生的方法：

• 尽可能的保持事务小型化，减少事务执行的时间可以减少发生影响的概率。

• 及时执行 commit 或者 rollback，来尽快的释放锁。

• 当要访问多个表数据或者要访问相同表的不同行集合时，尽可能的保证每次访问的顺序是相同的。比如可以将多个语句封装在存储过程中，通过调用同一个存储过程的方法可以减少死锁的发生。

• 增加合适的索引以便语句执行所扫描的数据范围足够小。

• 尽可能的少使用锁，比如如果可以承担幻读的情况，则直接使用 select 语句，而不要使用 select…for update 语句。

• 如果没有其他更好的选择，则可以通过施加表级锁将事务执行串行化，最大限度的限制死锁发生。

六、事务

事务的主要目的了：事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作是，可以确保要么所有修改都已近保存了，要么所有修改都不保存。

InnoDB 存储引擎中的事务完全符合 ACID 的特性：

• 原子性 （atomicity）

• 一致性（consistency）

• 隔离性（isolation）

• 持久性（durability）

1、了解事务

事务可由一条非常简单的 SQL 语句组成，也可以有一组复杂的 SQL 组成。事务是访问并更新数据库中各种数据项的一个程序执行单元，在事务中的操作，要么都做修改，要么都不做这就是事务的目的。

事务 ACID 的特性：

A (Atomicity)，原子性：

指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个 SQL 语句执行失败，已近执行成功的 SQL 语句也必须撤销。数据库状态应该退回到执行事务前的状态。

比如 ATM 取款流程：

• 登录 ATM 机平台，验证密码。

• 从远程银行数据库中，取得账户的信息。

• 用户在 ATM 输入提取的金额。

• 从远程银行的数据库中，更新账户信息。

• ATM 机出款。

• 用户取钱。

整个过程都视为原子操作，某一个步骤失败了，都不能进行下一步。

C （consistency），一致性：

一致性定义基本可以理解为是事务对数据完整性约束的遵循。这些约束可能包括主键约束、外键约束或是一些用户自定义约束。事务执行的前后都是合法的数据状态，不会违背任何的数据完整性，这就是“一致”的意思。事务是一致性的单位，如果事务中某个动作失败了，系统就可以自动撤销事务——返回事务初始化的状态。

I (isolation)，隔离性：

隔离性还有其他的称呼，如并发控制、可串行化、锁等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见。

D（durability），持久性：

事务一旦提交，其结果就是永久性的(写入了磁盘)，即使发生宕机等故障，数据库也能将数据恢复。需要注意的是，只能从事务本身的角度来保证结果的永久性。

例如：在事务提交后，所有的变化都是永久的，即使当数据库因为崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。

但若不是数据库本身发生故障，而是一些外部的原因，如 RAID 卡损坏，自然灾害等原因导致数据库发生问题，那么所有提交的数据可能都会丢失。

因此持久性保证事务系统的高可靠性，而不是高可用性。对于高可用性的实现，事务本身并不能保证，需要一些系统来共同配合来完成。

2、事务的实现

事务的隔离性由锁来实现。原子性，一致性，持久性通过数据库的 redo log 和 undo log 来完成，redo log 成为重做日志，用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。

redo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定版本。因此两者记录的内容不同，redo 通常是物理日志，记录的是页的物理修改操作，undo 是逻辑日志，根据每行记录进行记录。

1）redo

重做日志(redo log)用来实现事务的持久性，即事务 ACID 中的 D。 其中两部分组成：

• 一是内存中的重做日志缓冲（redo log buffer），其实容易丢失的；

• 二是重做日志文件（redo log file），其是持久的。

InnoDB 是事务的存储引擎，其通过 Force Log at Commit 机制实现事务的持久性，即当事务提交(commit)时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的 commit 操作完成才算完成。

这里的日志是指重做日志，在 InnoDB 存储引擎中，由两部分组成，即 redo log 和 undo log。

redo log 用来保证事务的持久性，undo log 用来帮助事务回滚及多版本控制（mvcc）的功能，redo log 基本上都是顺序写的，在数据库运行不需要对 redo log 的文件进行读取操作。而 undo log 是需要进行随机读写的。

为了确保每次日志都写入重做日志文件，在每次都将重做日志缓冲写入重做日志文件后，InnoDB 存储引擎都需要调用一次 fsync 操作。

由于重做日志文件打开并没有使用 O_DIRECT 选项，因此重做日志缓冲先写入文件系统缓冲。为了确保重做日志写入磁盘，必须进行一次 fsync 操作。由于 fsync 的效率取决于磁盘的性能，因此磁盘的性能决定了事务的提交的性能，也就是数据库的性能。

InnoDB 存储引擎允许用户手工非持久性的情况发生，以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行 fsync 操作。

用参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略。该参数默认值为1。

改参数可以设置值为 0、1、2

• 0：表示事务提交时不进行写入重做日志操作，这个操作仅在 master thread 中完成，而在 master thread 中每 1 秒会进行一次重做日志的 fsync 操作。

• 1：表示每个事务提交时进行写入到重做日志。

• 2：表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。在这个设置下，当 MySQL 数据库发生宕机(就是数据库服务意外停止)而操作系统不发生宕机是，不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。

2）undo

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作，但是事务有时还需要进行回滚操作，这时就需要 undo。 因此在对数据库进行修改时，InnoDB 存储引擎不但会产生 redo，还会产生一定量的 undo。这样如果用户执行的事务或语句由于原因失败了，又或者用户用一条 rollback 语句请求回滚，就可以利用这些 undo 信息将数据回滚到修改之前的样子。

redo 存放在重做日志文件中，与 redo 不同，undo 存放在数据库内部的一个特殊段（segment）中，这个段称为 undo 段 。undo 段位于共享表空间内。

undo 是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十，数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

undo 除了回滚操作，undo 的另一个作用是mvcc，即在InnoDB 存储引擎中mvcc 的实现是通过undo 来完成。当用户读取一行记录时，若该记录已近被其他事务占用，当前事务可以通过undo 读取之前的行版本信息，以此实现 非锁定读取。

最重要的一点是，undo log 会产生redo log ，也就是undo log 的产生会伴随着redo log 的产生，这是因为undo log 也需要持久性的保护。

undo 存储管理

InnoDB 存储引擎有 rollback segment ，每个回滚段中记录了 1024 个undo log segment , 而在每个 undo log segment 段中进行 undo 页的申请。

InnoDB 支持最大128 个(回滚段)rollback segment ，故其支持同时在线的事务 128 * 1024，但是这些 rollback segment 都存储于共享表空间中。可以通过参数对 rollback segment 做进一步的设置。这些参数包括：

innodb_undo_directory

innodb_undo_logs

innodb_undo_tablespaces

innodb_undo_directory 用于设置 rollback segment 文件所在的路径。这意味着 rollback segment 可以放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为”.”，表示当前 InnoDB 存储引擎的目录。

innodb_undo_logs 用来设置 rollback segment 的个数，默认值为 128。

innodb_undo_tablespaces 用来设置构成 rollback segment 文件的数量，这样 rollback segment 可以较为平均地分布在多个文件。设置改参数后，会在路劲innodb_undo_directory 看到 undo 为前缀的文件，该文件就代表 rollback segment 文件。

数据库初始化后，innodb_undo_tablespaces 就再也不能被改动了；默认值为0，表示不独立设置undo的tablespace，默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为4，那么就会创建命名为undo001~undo004的undo tablespace文件，每个文件的默认大小为10M。修改该值会导致Innodb无法完成初始化，数据库无法启动，但是另两个参数可以修改。

3）purge

delete 和 update 操作可能并不直接删除原有的数据。

例如执行：

delete from z where a=1;

表z 上列a 有聚集索引，列表上有辅助索引，对于上述的delete 操作，在undo log 将主键列等于1 的记录delete flag 设置为1 ，记录并没有立即删除，记录还是存在B+树种，其次，对辅助索引上a 等于1 ，b等于1 的记录同样没有做任何处理，甚至没有产生undo log 。 而真正删除这行记录的删除操作其实被“延时”了，最终在purge 操作中完成。　

purge 用于最终完成delete 和 update 操作。 因为InnoDB 存储引擎支持MVCC，所以记录不能再事务提交时立即进行处理。这时其他事务可能正在引用这行，故InnoDB 存储引擎需要保持记录之前的版本。而是否可以删除该条记录通过purge 来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete 操作。可见，purge 操作是清理之前的delete 和 update 操作， 将上述操作 “最终” 完成。 而实际执行的操作为delete 操作，清理之前行记录的版本。

4）group commit

5.6 版本之前的两次提交 ：

若事务为非只读事务，则每次事务提交时需要进行一次fsync 操作，以此保证重做日志都已近写入磁盘。当数据库发生宕机时，可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能，然后磁盘的rsync 性能是有限的。为了提高磁盘fsync 的效率，数据库提供了group commit 的功能，即一次fsync 可以刷新确保多个事务日志被写入文件。 　

对于InnoDB 存储引擎来说， 事务提交时会进行两个阶段的操作：

• 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。

• 调用fsync 将确保日志都从重做日志缓冲写入磁盘。

步骤 1 相对步骤 2 是一个较慢的过程，这是因为存储引擎需要与磁盘打交道。但当有事务进行这个过程是，其他事务可以进行步骤 1 的 操作，正在提交的事务完成提交操作，再次进行步骤 2 时，可以将多个事务的重做日志通过一次fsync 刷新到磁盘，这样就大大减少了磁盘的压力，从而提高了数据库的整体性能。对于写入或更新较为频繁的操作，group commit 的效果尤为明显。

二段提交流程：

• InnoDB 的事务 Prepare 阶段，即 SQL 已经成功执行并生成 redo 和 undo 的内存日志；

• binlog 提交，通过 write() 将 binlog 内存日志数据写入文件系统缓存；

• fsync() 将 binlog 文件系统缓存日志数据永久写入磁盘；

• InnoDB 内部提交，commit 阶段在存储引擎内提交，通过 innodb_flush_log_at_trx_commit 参数控制，使 undo 和 redo 永久写入磁盘。

组提交 ：

5.6 引入了组提交，并将提交过程分成 Flush stage、Sync stage、Commit stage 三个阶段。

• InnoDB, Prepare ： SQL 已经成功执行并生成了相应的 redo 和 undo 内存日志；

• Binlog, Flush Stage ：所有已经注册线程都将写入 binlog 缓存；

• Binlog, Sync Stage ：binlog 缓存将 sync 到磁盘，sync_binlog=1 时该队列中所有事务的 binlog 将永久写入磁盘；

• InnoDB, Commit stage： leader 根据顺序调用存储引擎提交事务；

每个 Stage 阶段都有各自的队列，从而使每个会话的事务进行排队，提高并发性能。

如果当一个线程注册到一个空队列时，该线程就做为该队列的 leader，后注册到该队列的线程均为 follower，后续的操作，都由 leader 控制队列中 follower 行为。

参考网址：https://www.linuxidc.com/Linux/2018-01/150187.htm

参数 binlog_max_flush_queue_time 用来控制 flush 阶段中等待的时间，即使之前的一组事务完成提交，当前一组的事务也不马上进去 sync 阶段，而是至少需要等待一段时间。

这样做的好处是 group commit 的数量更多，然而这也可能会导致事务的相应时间变慢。该参数的默认值为 0，且推荐设置依然为 0。除非用户的 MySQL 数据库系统中有着大量的连接，并且不断地在进行事务的写入或更新操作。

注：任何参数都不要随意设置，看到别人设置参数能解决，为什么我的环境设置就报错了，看官方的改参数注意事项，各种版本的注意事项，在去相应测试环境实验一下。

3、事务控制语句

在 MySQLl 命令行的默认设置下，事务都是自动提交（auto commit）的，即执行 SQL 语句就会马上执行 commit 操作。

用户可以使用那些事务控制语句：

• start transaction | begin ：显示地开启一个事务（推荐start transaction）

• commit：会提交事务，并使得已对数据库做的所有修改成为永久性的

• rollback：回滚用户当前锁执行的事务，并撤销正在进行的所有未提交的修改。

• savepoint identifer：savepoint 允许在事务中创建一个保存点，一个事务中可以有多个savepoint。

• release savepoint identifier：删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常。

• rollback to[savepoint] identifer：这个语句与savepoint 命令一起使用。可以把事务回滚到标记点，而不会滚在此标记点之前的任何工作。

• set transaction：这个语句用来设置事务的隔离级别。InnoDB 存储引擎的事务隔离级别有：READ UNCOMMITED、READ COMMITED、REPEATABLE READ、SERIALIZABLE。

例：

mysql> create table u (a int primary key);

Query OK, 0 rows affected (0.01 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into u select 1;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> savepoint u1;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into u select 2;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> savepoint u2;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from u\G;

****** 1. row ******

a: 1

****** 2. row ******

a: 2

2 rows in set (0.00 sec)

mysql> release savepoint u1;

Query OK, 0 rows affected (0.00 sec)

# 回到了第一次插入数据的时候

mysql> insert into u select 2;

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

mysql> rollback to savepoint u2;

ERROR 1305 (42000): SAVEPOINT u2 does not exist

mysql> select * from u;

+---+

| a |

+---+

| 1 |

| 2 |

+---+

2 rows in set (0.00 sec)

# 这时候发现了，rollback to savepoint u1了，

后面的u2 的 事务已近不存在了， 但是两条记录的数据还在。

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from u;

Empty set (0.00 sec)

在上面的列子中，虽然在发生重复错误后用户通过 rollback to save point u1 命令回滚到了保存点 u1，但是事务此时没有结束。在运行命令 rollback 后，事务才会完整地回滚。　　

InnoDB 存储引擎中的事务都是原子的，这说明下两种情况：

构成事务的每天语句都会提交（成为永久），或者所有语句都回滚。这种保护还延伸到单个的语句。一条语句要么完全成功。要么完全回滚（注意，这里说的是语句回滚）。

因此一条语句失败并抛出异常时，并不会导致先前已近执行的语句自动回滚。所有的执行都会得到保留，必须由用户自己来决定是否对其进行提交或回滚的操作。

rollback to savepoint 命令并不真正地结束事务。

commit 和 rollback 才是真正的结束一个事务

4、隐式提交的SQL语句

以下这些 SQL 语句会产品一个隐式的提交操作即执行完这些语句后，会有一个隐式的 commit 操作：

DDL 语句：

ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME,<br data-filtered="filtered">ALTER EVENT,ALTER PROCEDURE,ALTER TABLE ,ALTER VIEW, <br data-filtered="filtered">CREATE DATABASE, CREATE EVENT, CREATE TRIGGER , CREATE VIEW, <br data-filtered="filtered">DROP DATABASE ,DROP EVENT , DROP INDEX , DROP PROCEDURE , DROP TABLE , DROP TRIGGER , DROP VIEW ,<br data-filtered="filtered">RENAME TABLE , TRUNCATE TABLE .

用来隐式修改 MySQL 架构的操作：　

CREATE USER,DROP USER ,GRANT , RENAME USER ,REVOKE , SET PASSWORD.

管理语句：

ANALYZE TABLE,CACHE INDEX, CHECK TABLE ,<br data-filtered="filtered">LOAD INDEX INTO CACHE,OPTIMEIZE TABLE ,REPAIR TABLE

注: 我发现 sql server 的数据库有些 ddl 也是可以回滚的。这和 InnoDB 存储引擎，oracle 这些数据库完全不同。

truncate table 演示：

mysql> insert into u select 1;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into u select 2;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from u;

+---+

| a |

+---+

| 1 |

| 2 |

+---+

2 rows in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> truncate table u;

Query OK, 0 rows affected (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from u;

Empty set (0.00 sec)

5、对于事务的操作的统计

由于 InnoDB 存储引擎是支持事务的，因此 InnoDB 存储引擎的应用需要在考虑每秒请求数（transaction per second ，TPS）

计算 TPS 的方法时( com_commit + com_rollback）/time 。但是利用这种方法进行计算的前提是：

所有的事务必须都是显示提交的，如果存在隐式提交和回滚（默认autocommit =1 ），不会计算到com_commit 和 com_rollback 变量中。 如：

MySQL 数据库中另外还有两个参数 handler_commit 和 handler_rollback 用于事务的统计操作。可以很好的用来统计 InnoDB 存储引擎显式和隐式的事务提交操作。

在 InnoDB Plugin 中这两个参数的表现有些“怪异”，如果用户的程序都是显示控制事务的提交和回滚，那么可以通过com_commit 和 com_rollback 进行统计。

6、事务的隔离级别

SQL 标准定义的四个隔离级别为:

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

sql server 和oracle 默认的隔离级别是 READ COMMITED。

• 脏读：又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。

• 不可重复读：是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

• 幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。

不可能重复读和幻读的区别：

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于 update 和 delete，而幻读的重点在于 insert。

在 InnoDB 引擎中，可以使用一下命令来设置当前会话和全局的事务的隔离级别：

mysql> help isolation;

Name: 'ISOLATION'

Description:

Syntax:

SET [GLOBAL | SESSION] TRANSACTION

transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {

ISOLATION LEVEL level

| READ WRITE

| READ ONLY

}

level: {

REPEATABLE READ

| READ COMMITTED

| READ UNCOMMITTED

| SERIALIZABLE

}

如果想在 MySQL 数据启动时就设置事务的默认隔离级别，那就需要修改 MySQL 的配置文件 my.cnf 在 [mysqld] 中添加如下行：

[mysqld]

transaction-isolation = REPEATABLE-READ

查看当前会话的事务隔离级别，可以使用：

mysql> select @@tx_isolation\G;

********** 1. row **********

@@tx_isolation: REPEATABLE-READ

1 row in set, 1 warning (0.00 sec)

查看全局的事务隔离级别，可以使用：

mysql> select @@global.tx_isolation\G;

******** 1. row ********

@@global.tx_isolation: REPEATABLE-READ

1 row in set, 1 warning (0.00 sec)

7、不好的事务的习惯

在循环中提交

用存储过程模拟一下：

create table t1 (a int ,b char(100));

创建load1

delimiter //

create procedure load1 (count INT UNSIGNED)

begin

declare s int unsigned default 1;

declare c char(80) default repeat('a',80);

while s <= count do

insert into t1 select null,c;

commit;

set s = s+1;

end while;

end //

delimiter ;

创建load2

delimiter //

create procedure load2 (count int unsigned)

begin

declare s int unsigned default 1;

declare c char(80) default repeat('a',80);

while s <= count do

insert into t1 select null,c;

set s = s+1;

end while;

end //

delimiter ;

创建load3

delimiter //

create procedure load3(count int unsigned)

begin

declare s int unsigned default 1;

declare c char(80) default repeat('a',80);

start transaction;

while s <= count do

insert into t1 select null,c;

set s = s+1;

end while;

commit;

end //

delimiter ;

比较这三个存储过程执行时间：

mysql> call load1(20000);

Query OK, 0 rows affected (16.12 sec)

mysql> truncate table t1;

Query OK, 0 rows affected (0.01 sec)

mysql> call load2(20000);

Query OK, 1 row affected (16.06 sec)

mysql> truncate table t1;

Query OK, 0 rows affected (0.01 sec)

mysql> call load3(20000);

Query OK, 0 rows affected (0.51 sec)

注：mysql 默认是自动提交的，load1 和 load2 没执行一次都会自动提交。

显然，load3 方法要快的多，这是因为每一次提交都要写一次重做日志，存储过程 load1 和 load2 实际写了 20000 次重做日志文件，而对于存储过程 load3 来说，实际只写了一次。

8、长事务

长事务就是执行时间较长的事务。比如对于银行系统的数据库，没过一个阶段可能需要更新对应账户的利息。如果对应账号的数量非常大，例如对有 1 亿用户的表 account ，需要执行以下列语句;

update accout set account_total= accoutn_total + （1+inerset_rate）

这是这个事务可能需要非常长的时间来完成。可能需要 1 个小时，也可能 4、5 个小时，这取决于数据库的硬件配置。DBA 和开发人员本身能做的事情非常少。

然而，由于事务 ACID 的特性，这个操作被封装在一个事务中完成。这就产生了一个问题，在执行过程中，当数据库或操作系统，硬件等发生问题是，重新开始事务的代价变得不可接受。

数据库需要回滚所有已近发生的变化，而这个过程可能比产生这些变化的时间还要长。因此，对于长事务的问题，有时可以通过转化为小批量的事务来进行处理。当事务发生错误是，只需要回滚一部分数据，然后接着上次已完成的事务继续进行。

注：以上所有操作全是在 MySQL 5.7.24 版本