MySql Innodb存储引擎--锁和事务

 

 

lock和latch的比较

latch 一般称为闩锁(轻量级的锁) 因为其要求锁定的时间非常短，若迟勋时间长，则应用性能非常差，在InnoDB存储引擎中，latch有可以分为mutex(互斥锁)和rwlock(读写锁)其目的用来保证并发线程操作临界资源的正确性，并且没有死锁检测的机制

 

lock的对象是事务，用来锁定的是数据库中的UI想，如表、页、行。并且一般lock对象仅在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同)，此外lock正如大多数数据库中一样，是有死锁机制的。表显示了lock与latch的不同

 

mysql> SHOW ENGINE INNODB MUTEX;
+--------+-------------------+-------------+
| Type   | Name              | Status      |
+--------+-------------------+-------------+
| InnoDB | dict0dict.cc:1057 | os_waits=2  |
| InnoDB | log0log.cc:844    | os_waits=1  |
| InnoDB | fil0fil.cc:1690   | os_waits=1  |
| InnoDB | dict0dict.cc:1066 | os_waits=3  |
| InnoDB | log0log.cc:907    | os_waits=11 |
+--------+-------------------+-------------+

在DEBUG版本下，通过SHOW ENGINE INNODB MUTEX 可以看到latch的更多信息

debug中status字段中的参数介绍

 

若将上锁的对象看成一棵树，那么对最上层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁，如上图，如果需要对页上的记录r进行上X锁，那么分别需要对数据A、表、页上意向锁IX，最后对记录r上X锁,若其中任何一部分导致等待，那么该操作需要等待粗粒度锁的完成

 

 

 

 

 

innodb锁相关的表

INNODB_LOCKS表

a)        lock_id：锁的id以及被锁住的空间id编号、页数量、行数量

b)        lock_trx_id：锁的事务id。

c)        lock_mode：锁的模式。

d)        lock_type：锁的类型，表锁还是行锁

e)        lock_table：要加锁的表。

f)          lock_index：锁的索引。

g)        lock_space：innodb存储引擎表空间的id号码

h)        lock_page：被锁住的页的数量，如果是表锁，则为null值。

i)          lock_rec：被锁住的行的数量，如果表锁，则为null值。

j)          lock_data：被锁住的行的主键值，如果表锁，则为null值。

 

innodb_lock_waits表

1)        requesting_trx_id：申请锁资源的事务id。

2)        requested_lock_id：申请的锁的id。

3)        blocking_trx_id：阻塞的事务id。

4)        blocking_lock_id：阻塞的锁的id。

 

innodb_trx表

trx_id事务ID

trx_state事务状态

trx_started事务开始时间

trx_requested_lock_idinnodb_locks.lock_id

trx_wait_started事务开始等待的时间

trx_weight

trx_mysql_thread_id事务线程ID

trx_query具体SQL语句

trx_operation_state事务当前操作状态

trx_tables_in_use事务中有多少个表被使用

trx_tables_locked事务拥有多少个锁

trx_lock_structs

trx_lock_memory_bytes事务锁住的内存大小（B）

trx_rows_locked事务锁住的行数

trx_rows_modified事务更改的行数

trx_concurrency_tickets事务并发票数

trx_isolation_level事务隔离级别

trx_unique_checks是否唯一性检查

trx_foreign_key_checks是否外键检查

trx_last_foreign_key_error最后的外键错误

trx_adaptive_hash_latched

trx_adaptive_hash_timeout

 

 

 

 

 

一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(multi versionning)的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这是读取操作不会因此等待行上锁的释放。相反的，InnoDB会去读取行的一个快照数据

 

 

一致性锁定读的SQL语法

-- 排他锁
SELECT ......  FOR UPDATE

-- 共享锁
SELECT ...... LOCK IN SHARE MODE

 

 

 

自增锁

-- 内部用下列方式实现的
SELECT max(auto_inc_col) FROM t FOR UPDATE;

MySQL 5.1.22版本开始，InnoDB提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长插入的性能。

InnoDB提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1，在继续讨论新的自增长实现方式之前，需要对自增长的插入进行分类

 

参数innodb_autoinc_lock_mode以及各个设置下对自增的影响，其共有3个有效值可以设定 0 1 2 

 

 

 

 

MySql锁的类型

InnoDB有三种行锁的算法：

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

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

innodb对于唯一索引，还有主键索引使用的是Record Lock，对于普通索引，联合索引才会使用next key lock算法，因为唯一索引是一个确定的值，不需要锁定一个范围的。

next key lock也可以解决幻读的问题

MySql中死锁检查是通过超时机制，还有 wait-for graph算法解决的

 

 

 

 

事务的隔离级别

=================================================================================

 隔离级别            脏读（Dirty Read）       不可重复读（NonRepeatable Read）     幻读（Phantom Read） 

=================================================================================

 

未提交读（Read uncommitted）        可能                            可能                       可能

 

已提交读（Read committed）          不可能                          可能                        可能

 

可重复读（Repeatable read）          不可能                          不可能                     可能

 

可串行化（Serializable ）                不可能                          不可能                     不可能

 

================================================================================

·未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

·提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

·可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

·串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

 

 

 

阻塞相关的参数

-- 控制等待的时间，动态参数
innodb_lock_wait_timeout

-- 用来设定是否在等待超时时对进行中的事物进行回滚操作(默认是OFF不回滚)，静态参数
innodb_rollback_on_timeout

 

 

锁升级

1.由单独的SQL在一个对象上持有的锁数量超过了阀值，之默认值是5000，如果是不同对象则不会升级

2.锁资源占用的内存超过了激活内存的40%就会发生锁升级

innodb根据每个事物访问的每个页对锁进行管理，采用的是位图的方式

innodb不存在锁升级的问题，这些是微软的SQL存在的

 

 

 

 

 

事务的ACID

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个很好的事务处理系统，必须具备这些标准特性：

原子性（atomicity）

　　一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性（consistency）

     数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性（isolation）

     通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

持久性（durability）

　　一旦事务提交，则其所做的修改不会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。持久性是个有占模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必，而且不可能有能做到100%的持久性保证的策略。）

 

 

 

事务的分类

扁平事务(Flat Transactions)

带有保存点的扁平事务(Flat Transactions with Savepoints)

链事务(Chained Transactions)

嵌套事务(Nested Transactions)

分布式事务(Distributed Transactions)

 

扁平事务 是事务类型中最简单的一种，但是在实际生产环境中，这可能是使用最频繁的事务，在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是源自的，要么都执行，要么都回滚，因此扁平事务是应用程序称为原子操作的的基本组成模块

 

带有保存点的扁平事务 对于扁平的事务来说，隐式的设置了一个保存点。然而整个事务中，只有这一个保存点，因此，回滚只能会滚到事务开始时的状态，保存点用SAVE WORK函数来建立，通知系统记录当前的处理状态。当出现问题时，保存点能用作内部的重启动点，根据应用逻辑，决定是回到最近一个保存点还是其他更早的保存点。

 

链事务 可视为保存点模式的一种变种，带有保存点的扁平事务，当发生系统崩溃是，所有的的保存点都将消失，因为其保存点是易失的，这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行

链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务，提交事务操作和开始下一个事务操作 将合并为一个原子操作，这意味着下一个事务将看到上一个事务的结果，就好像一个事务中进行的一样，如图显示了链事务的工作方式

 

嵌套事务 是一个层次结构框架，由一个顶层事务(top-level transaction)控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，其控制每一个局部的变换

采用保存点技术比嵌套查询有更大的灵活性

但是用保存点技术来模拟嵌套事务在锁的持有方面还是与嵌套查询有些区别。当通过保存点技术来模拟嵌套事务时，用户无法选择哪些锁需要被子事务集成，哪些需要被父事务保留

 

 

 

事务控制语句

start transction 显示的开启一个事务
begin	显示的开启一个事务
 commit （commit work）
    commit work与completion_type的关系，commit work是用来控制事务结束后的行为，是chain还是release的，可以通过参数completion_type来控制，默认为0

rollback，rollback work与commit，commit work的工作原理一样。
rollback（rollback work）
savepoint [identifier]	在事务中创建一个保存点，一个事务允许有多个保存点
release savepoint [identifier]	删除事务中的保存点，当时一个保存点也没有时执行这个命令，会报错抛出一个异常

 

 

 

事务操作的统计

因为InnoDB存储引擎是支持事务的，因此对于InnoDB存储引擎的应用，在考虑每秒请求数（Question Per Second，QPS）的同时，也许更应该关注每秒事务处理的能力（Transaction Per Second，TPS）。

计算TPS的方法是（com_commit+com_rollback）/time。但是用这种方法计算的前提是：所有的事务必须都是显式提交的，如果存在隐式的提交和回滚（默认autocommit=1），不会计算到com_commit和com_rollback变量中。

show global status like'com_commit'; 
show global status like'com_rollback'; 

 

 

 

分布式事务

XA 事务的基础是两阶段提交协议。需要有一个事务协调者来保证所有的事务参与者都完成了准备工作(第一阶段)。如果协调者收到所有参与者都准备好的消息，就会通知所有的事务都可以提交了（第二阶段）。MySQL 在这个XA事务中扮演的是参与者的角色，而不是协调者(事务管理器)。

 

Mysql 的XA事务分为内部XA和外部XA。 外部XA可以参与到外部的分布式事务中，需要应用层介入作为协调者；内部XA事务用于同一实例下跨多引擎事务，由Binlog作为协调者，比如在一个存储引擎提交时，需要将提交信息写入二进制日志，这就是一个分布式内部XA事务，只不过二进制日志的参与者是MySQL本身。 Mysql 在XA事务中扮演的是一个参与者的角色，而不是协调者。

 

基本语法

XA {START|BEGIN} xid [JOIN|RESUME]     启动一个XA事务 (xid 必须是一个唯一值; [JOIN|RESUME]  字句不被支持)    
XA END xid [SUSPEND [FOR MIGRATE]]   结束一个XA事务 ( [SUSPEND [FOR MIGRATE]] 字句不被支持)
XA PREPARE xid    准备
XA COMMIT xid [ONE PHASE]    提交XA事务
XA ROLLBACK xid  回滚XA事务
XA RECOVER   查看处于PREPARE 阶段的所有XA事务

操作演示

mysql> XA START 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (i) VALUES(10);
Query OK, 1 row affected (0.04 sec)

mysql> XA END 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA COMMIT 'xatest';
Query OK, 0 rows affected (0.00 sec)

 

 

 

重做日志

重做日志保证了持久性和原子性，锁用来保持隔离性，undo log则保持了一致性

重做日志包含重做日志缓冲(redo log buffer)和重做日志文件(redo log file)

 

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

参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略

1 ，表示事务提交时必须调用一次fsync操作，

0表示事务提交时不进行写入重做日志操作，这个操作仅在master thread中完成，也就是每秒刷新一次，但可能会出现瞬间宕机数据丢失的可能性

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

重做日志缓冲区和重做日志组之间的关系

 

在InnoDB存储引擎中，重做日志都是以512字节进行存储的，这意味着重做日志缓存、重做日志文件块都是以块block的方式进行保存的，称为重做日志块(redo log block)每块的大小512字节

 

 

日志块由三部分组成，依次为日志快头(log block header)、日志内容(log body)、日志块尾(log block tailer)

 

LOG_BLOCK_HDR_NO用来标记这个数组中的位置，尤其是递增并且循环使用的。占用4个字节。但是由于第一位用来判断是否是flush bit，所以最大值为2G

LOG_BLOCK_HDR_DATA_LEN占用2个字节，表示log block所占用的大小，当log block被写满时，该值为0x200,表示使用全部的log block空间，即占用512字节

LOG_BLOCK_FIRST_REC_GROUP 占用2个字节，表示log block中第一个日志所在的偏移量。如果该值的大小和LOG_BLOCK_HDR_DATA_LEN相同，则表示当前log block不包含新的日志

LOG_BLOCK_CHECKPOINT_NO占用4字节，表示该log block最后被写入时的检查点第4字节的值

LOG_BLOCK_TAILER 只由1个部分组成，且值和LOG_BLOCK_HDR_NO相同，并在函数log_block_init中被初始化 LOG_BLOCK_TRL_NO 大小为4字节

 

 

重做日志格式

通用的头部格式由一下3部分组成

redo_log_type 重做日志类型

space: 表空间ID

page_no 页的偏移量

之后是redo log body  

 

body体的内容

 

SHOW ENGINE INNODB STATUS查看LSN的情况

---
LOG
---
Log sequence number 18766833801	-- 表示当前的LSN
Log flushed up to   18766832201 -- 表示刷新到重做日志文件的LSN
Pages flushed up to 18766816420 
Last checkpoint at  18766816420 -- 表示刷新到磁盘的LSN

由于checkpoint表示已经刷新到磁盘页上的LSN，因此在恢复过程中仅需恢复checkpoint开始的日志部分

 

再放一张完整的log block分析图

 

 

 

undo log

为了满足事务的原子性，在操作任何数据之前，首先将数据备份到Undo Log，然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。与redo log不同的是，磁盘上不存在单独的undo log文件，它存放在数据库内部的一个特殊段(segment)中，这称为undo段(undo segment)，undo段位于共享表空间内。

此外undo log还用来做事务的MMVC功能，多版本并发

undo log不是物理操作，是逻辑的反向操作，比如插入一个记录，在undo log中就会有一个删除记录的操作，更新一个记录，undo就做反向更新，同理删除操作undo log就记录一个插入操作。

插入1W条记录后反向操作，可能会使得页变得更大。

 

相关参数

 

innodb_undo_directory	用来设置rollback segment文件所在的路径
innodb_undo_logs	用来设置rollback segment的个数，默认为128
innodb_undo_tablespaces	用来设置构成rollback segment文件的数量

事务提交后并不会马上删除undo log，这是因为还有其他事务需要undo log来得到之前的版本，能否删除undo log由purge线程判断

 

 

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式，每个回滚段又有多个undo log slot。

 

 

 

 

undo log的insert格式

 

undo log的update和delete格式

 

INNODB_TRX_ROLLBACK_SEGMENT  这个数据字典表用来查看rollback segment（回滚段）。

可以查看某个记录插入哪个表，哪个页，插入的偏移量和长度，这样可以获取页中的undo log内容

INNODB_TRX_UNDO  用来记录事务对应的undo log，方便DBA和开发人员详细了解每个事务产生的undo量 

 

对于delete操作，数据并不是马上删除，而是增加一个标志位，最后由purge线程异步删除这些数据

所有的undo log会放到一个列表中，然后purge会依次扫描这个列表判断哪些可以删除

下面操作会首先检查trx1，发现trx1引用到了trx5，而trx5被占用于是不能删除，再检查trx2发现可以删除因为没有人引用了

 

 

组提交(group commit)是MYSQL处理日志的一种优化方式，主要为了解决写日志时频繁刷磁盘的问题。组提交伴随着MYSQL的发展不断优化，从最初只支持redo log 组提交，到目前5.6官方版本同时支持redo log 和binlog组提交。组提交的实现大大提高了mysql的事务处理性能，下文将以innodb 存储引擎为例，详细介绍组提交在各个阶段的实现原理。

组提交思想是，将多个事务redo log的刷盘动作合并，减少磁盘顺序写。Innodb的日志系统里面，每条redo log都有一个LSN(Log Sequence Number)，LSN是单调递增的。每个事务执行更新操作都会包含一条或多条redo log，各个事务将日志拷贝到log_sys_buffer时(log_sys_buffer 通过log_mutex保护)，都会获取当前最大的LSN，因此可以保证不同事务的LSN不会重复。

 

 

 

参考

通过例子理解事务的4种隔离级别

MySQL事务隔离级别详解

Innodb锁机制：Next-Key Lock 浅谈

MySQL中一致性非锁定读

MySql死锁问题分析

MySQL · 引擎特性 · InnoDB redo log漫游

MySQL redo log及recover过程浅析

MySQL中redo日志

MySQL · 引擎特性 · InnoDB undo log 漫游

InnoDB undo log解析（一）

MySQL 研究innodb_max_purge_lag分享

MYSQL-GroupCommit

XA/JTA/MYSQL两阶段提交事务

针对SSD的MySQL IO优化