一、锁定机制最常讨论的话题
1、什么是锁
锁是数据库系统区别于档案系统的一个关键特性。数据库系统使用锁是为了支援对共享资源进行并发访问，提供资料的完整性和一致性。例如：操作缓冲池中的 LRU 列表，删除、新增、移动 LUR 列表中的元素。
对于任何一种数据库来说都需要有相应的锁定机制，所以 MySQL 自然也不能例外。
MySQL 数据库由于其自身架构的特点，存在多种资料储存引擎，每种储存引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种储存引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各储存引擎的锁定机制也有较大区别。
MySQL 常用储存引擎(MyISAM，InnoDB)用了两种型别（级别）的锁定机制：表级锁定，行级锁定。
1）表级锁
表级别的锁定是 MySQL 各储存引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
使用表级锁定的主要是 MyISAM、MEMORY、CSV 等一些非事务性储存引擎。
2）行级锁
行级锁定最大的特点就是锁定物件的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体效能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是 InnoDB 储存引擎。
总结如下：
例如，下图只是对 myisam 表修改一行记录：
其他 insert 操作就需要等待上个 update 语句执行完成，再执行 insert 操作，这时候就会产生表锁。
2、InnoDB锁的型别
InnoDB 储存引擎实现了如下两种标准的行级锁：
如果一个事务 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 储存引擎支援意向锁设计比较简练，其意向锁即为表级别的锁，设计目的主要是为了在一个事务中揭示下一行将被请求的锁型别。其支援两种意向锁：
意向锁是有资料引擎自己维护的，使用者无法手动操作意向锁，在为资料行加共享/排他锁之前，InooDB 会先获取该资料行所在在资料表的对应意向锁。
由于 InnoDB 储存引擎支援的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。
表级意向锁与行锁的相容性：
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 语句下不生效：
5、自增长与锁
自增长在数据库中非常常见的一种属性，也是很多 DBA 或开发人员首选主键方式。在 InnoDB 储存引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。
插入操作会依据这个自增长的计数器加 1 赋予自增长列。这个实现方式称作 AUTO-INC Locking（自增锁）。 这种自增锁是采用一种特殊的表锁机制，为了提高插入的效能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql 语句后立即释放。
AUTO-INC Locking 从一定程度上提高了并发插入的效率，但还是存在一些效能上的问题。
Innodb_autoinc_lock_mode 来控制自增长的模式，改引数的预设值为1。
InnoDB 提供了一种轻量级互斥量的自增长实现机制，大大提高了自增长值插入的效能。提供引数 innodb_autoinc_lock_mode 来控制自增长锁使用的演算法，预设值为 1。他允许你在可预测的自增长值和最大化并发插入操作之间进行权衡。
插入型别的分类：
innodb_autoinc_lock_mode 在不同设定下对自增长的影响：
因此，使用这种方式，任何情况下都需要使用row-base replication，这样才能保证最大并发效能和replication的主从资料的一致。
二、行锁的几种演算法
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的前置条件：
Gap Lock 的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。
可以通过两种方式来关闭 Gap Lock：
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、避免死锁发生的方法
在事务性数据库中，死锁是个经典的问题，但只要发生的频率不高，则死锁问题不需要太过担心。死锁应该非常少发生，若经常发生，则系统是不可用。
检视死锁的方法有两种：
减少死锁发生的方法：
六、事务
事务的主要目的了：事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作是，可以确保要么所有修改都已近储存了，要么所有修改都不储存。
InnoDB 储存引擎中的事务完全符合 ACID 的特性：
1、了解事务
事务可由一条非常简单的 SQL 语句组成，也可以有一组复杂的 SQL 组成。事务是访问并更新数据库中各种资料项的一个程式执行单元，在事务中的操作，要么都做修改，要么都不做这就是事务的目的。
事务 ACID 的特性：
A (Atomicity)，原子性：
指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个 SQL 语句执行失败，已近执行成功的 SQL 语句也必须撤销。数据库状态应该退回到执行事务前的状态。
比如 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。 其中两部分组成：
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
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 储存引擎来说， 事务提交时会进行两个阶段的操作：
步骤 1 相对步骤 2 是一个较慢的过程，这是因为储存引擎需要与磁盘打交道。但当有事务进行这个过程是，其他事务可以进行步骤 1 的 操作，正在提交的事务完成提交操作，再次进行步骤 2 时，可以将多个事务的重做日志通过一次fsync 重新整理到磁盘，这样就大大减少了磁盘的压力，从而提高了数据库的整体效能。对于写入或更新较为频繁的操作，group commit 的效果尤为明显。
二段提交流程：
组提交 ：
5.6 引入了组提交，并将提交过程分成 Flush stage、Sync stage、Commit stage 三个阶段。
每个 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 操作。
使用者可以使用那些事务控制语句：
例：
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,ALTER EVENT,ALTER PROCEDURE,ALTER TABLE ,ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE TRIGGER , CREATE VIEW, DROP DATABASE ,DROP EVENT , DROP INDEX , DROP PROCEDURE , DROP TABLE , DROP TRIGGER , DROP VIEW ,RENAME TABLE , TRUNCATE TABLE .
用来隐式修改 MySQL 架构的操作：
CREATE USER,DROP USER ,GRANT , RENAME USER ,REVOKE , SET PASSWORD.
管理语句：
ANALYZE TABLE,CACHE INDEX, CHECK TABLE ,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 标准定义的四个隔离级别为:
sql server 和oracle 预设的隔离级别是 READ COMMITED。
不可能重复读和幻读的区别：
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于 update 和 delete，而幻读的重点在于 insert。
在 InnoDB 引擎中，可以使用一下命令来设定当前会话和全域性的事务的隔离级别：
mysql> help isolation;
Name: \'ISOLATION\'
Deion:
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
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
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
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 版本
作者：keme
来源：www.cnblogs.com/keme/p/10731177.html#624-group-commit
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：[email protected]
