mysql ---- innodb-3-锁、事务

6 锁

数据库区别与文件系统的一个关键特性

6.1 什么是锁

锁机制用于管理对共享资源的并发访问。

Innodb引擎不仅仅是在行级别上使用锁，在其他方面也会使用。例如：操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素

6.2 InnoDB存储引擎中的锁

6.2.1 锁类型

两种标准的行级锁：

1. 共享锁（S lock）：允许事务读一行数据
2. 排他锁（X lock）：允许事务删除或更新一行数据

为了支持在不同粒度上进行加锁操作，Innodb 引擎支持意象锁（Intention Lock），意象锁是将锁定的对象分为多个层次，意象锁意味着事务希望在更细粒度上进行加锁。（不会阻塞除全表扫描之外的其他任何请求）

在InnoDB Plugin之前，只能通过SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS等命令来查看当前的数据库请求，然后在判断当前事务中锁的情况。

新版本中在INFORMATION_SCHEMA架构下添加了INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS三张表。通过这三张表可以简单的监控当前的事务并分析锁的问题。

Mysql8.0 将 information_schema.innodb_locks改为 performance_schema.data_locks

1. INNODB_TRX字段
   • trx_id：事务id
   • Trx_state：当前事务的状态
   • trx_started: 事务开启时间
   • trx_requested_lock_id：等待事务的锁ID
   • trx_wait_started:事务等待开始的时间
   • trx_weight:事务权重，反应了一个事务修改和锁住的行数
   • trx_mysql_thread_id： mysql中的线程ID，show processlist 显示的结果
   • trx_query: 事务运行的sql，实际中有时会显示null
2. INNODB_LOCKS的字段
   • lock_id：锁的id
   • lock_trx_id：事务的id
   • lock_mode：锁的模式
   • lock_type：锁的类型，行锁或者是表锁
   • lock_table：要枷锁的表
   • lock_space：Innodb引擎表空间的id
   • lock_page：被锁住的页的数量，若为表锁，则为null
   • lock_rec：被锁主的行的数量，若为表锁，泽伟null
   • lock_data：被锁住行的主键，欧维表锁，则为null
3. INNODB_LOCK_WAITS的字段
   • requesting_trx_id：申请锁资源的事务id
   • requesting_lock_id：申请的锁的id
   • block_trx_id：阻塞的事务id
   • block_lock_id：阻塞的锁的id

6.2.2 一致性的非锁定读操作

非锁定读大大提高了读取的并发性，是Innodb引擎中默认的读取方式，即读取不会占用和等待表上的锁。

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

上图展示了Innodb引擎的一致性非锁定读（不需要等待X锁的释放）。快照数据是指该行数据之前版本的数据，该实现是通过Undo段实现的。而Undo用来在事务回滚数据，因此快照数据本身没有额外的开销（此外读取快照数据不需要上锁）

• 注意：

  在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。

  在上图得知，快照数据并不是只有一个版本，一个行可能有不止一个快照数据（多版本技术）。由此带来的并发控制称为多版本并发控制（Multi Version Concurrency Control, MVCC）。

  • Read Committed

    在读已提交事务隔离级别下，对于快照数据，总是读取被锁定行的最新的一份快照数据。

  • Repeatable Read (innodb 引擎的默认隔离级别)

    在可重复度事务隔离级别下，对于快照数据，总是读取事务开始时行的快照数据

  上述情况的例子：

  -- 回话A：
  begin; -- 开启事务
  select * from table where id = 1; -- 此时查出id=1的数据   （没有提交事务）
  
  
  -- 会话B：（此时A没有结束事务）
  begin;
  update table set id = 3 where id = 1; -- 将id改为3  （事务同样没有提交）
  

  经过上述步骤，此时的会话B给行加了一个X锁， 如果此时再次回到会话A中读取数据，根据Innodb引擎的特性，在Read Committed和Repeatable Read事务隔离级别下使用非锁定一致性读。

  在上述两个隔离级别下，会话A中仍然能够查到id为1的数据（因为此时只有一份快照数据，因此不管哪个事务隔离级别，结果都一样）。

  然后回到会话B中，执行commit，提交事务，然后在回到会话A中，执行查询，此时两种隔离级别的查询结构就不同了，

  对于 ReadCommitted 事务隔离级别来说，他总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新的一个快照（fresh snapshot）。在上述情况下，会话 B 已经提交，所以 Read Committed事务隔离级别下，执行 sql select * from table where id = 1是查不到数据的

  对于 Repeatable Read事务隔离级别，总是读取事务开始的行数据，因此执行 sqlselect * from table where id = 1仍然可以查到 id 为1的数据。

  	-- 查询当前事务隔离级别
  	select @@tx_isolation;
  

  对于 Read Committed事务隔离级别来说，其实是违背了事务的 ACID 中的 I特性（隔离性）

6.2.3 SELECT .. FOR UPDATE & SELECT ... LOCK IN SHARE MODE

在上边说过，默认情况下，innodb 引擎的 select 操作使用一致性非锁定读。 但在某些情况下，需要对读进行加锁操作。

读加锁操作的两种方式：

1. SELECT ... FOR UPDATE

   对读的行记录加一个 X 锁（排它锁），其他事务想在这些行上加任何锁都会被阻塞

   对于一致性非锁定读，此时让然可以进行读取

2. SELECT ... LOCK IN SHARE MODE

   对读取操作加一个 S 锁（共享锁），其他事务可以向被锁定的行加 S 锁，但是 对于X 锁，会被阻塞

另外，上述两种方式，必须在一个事务中，当事务提交了，锁也就失效了（因为在使用上边两个语句时，是要加上 BEGIN、START TRANSACTION、SET AUTOCOMMIT=0）

6.2.4 自增长和锁

对于含有自增长值的表都有一个自增长计数器（auto-increment counter）。当插入时，这个计数器会被初始化，执行以下 sql 来获取值：

select MAX(auto_inc_col) from t for update;

插入操作会根据这个值加1给自增长列。即 AUTO-INC Locking，这种锁定机制其实是一种表锁机制，为了提高性能，锁不是在一个事务完成之后才释放，而是在完成对自增长值的插入后立即释放。（这种方式对于大批量数据会影响效率）

在 mysql5.1.22版本开始，提供了一种轻量级互斥量的自增长实现机制，参数 innodb_autoinc_lock_mode默认值为1。（不同的参数对于不同的插入有不同的影响）

插入方式分类：

1. INSERT-like：指所有的插入语句，例如 INSERT、REPLACT、INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA等
2. Simple inserts：指在插入之前就能确定插入的行数，如：INSERT、REPLACE，不包括INSERT ...ON DUPLICATE KEY REPLACE
3. Bulk inserts：指插入之前不能确定行数，如INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA
4. Mixed-mode inserts：指插入数据有一部分指是自增长的，有一部分指是确定的，如：INSERT INTO t1(c1,c2)VALUES (1,'A'),(NULL,'B'),(4,'C'),(NULL,'D');也可以指INSERT ...ON DUPLICATE KEY REPLACE这类 sql

innodb_autoinc_lock_mode有3个可选指：

1. 0，mysql5.1.22版本之前的自增长实现方式

2. 1，默认值，对于 Simple inserts 会使用互斥量产生自增长值，对于 Bulk Inserts 仍然使用 AUTO-INC Locking 的方式

3. 2，对于所有的 INSERT-like 自增长值的产生都是通过互斥量，而不是 AUTO-INC Locking （性能最高的方式）

   innodb_autoinc_lock_mode=2，会有两个问题：一是并发插入，会导致自增长值不是连续的，二是，对于基于 Statement-Base Replication 的复制会出现问题（Row-Base Replication 没问题）

6.2.5 外键和锁

6.3 锁的算法

Innodb 引擎有3中行锁：

1. Record Lock： 单个行记录上的锁（总是会去锁定索引的，如果没有索引，Innodb 引擎会使用隐式的主键来进行锁定）
2. Gap Lock： 间隙锁，锁定一个范围，但是不包括记录本身
3. Next-Key Lock：Gap Lock + Record Lock， 锁定一个范围，包括记录（Innodb 引擎对于行的查询都是基于这种算法，对于不同 sql 查询语句，可能设置共享Next-Key Lock 和排他的 Next-Key Lock） -- 解决了幻读问题

例子：

begin;
select * from t where id < 5 lock in share mode;
-- 在上述 情况下执行
insert into t (id) values(3); -- 此时插入语句会阻塞
-- 因为在 Next-Key Locking 算法下，锁定的是 id < 6的所有值，但是如果插入 id=9，是可以的

-- 如果是查询指定 id 的值，是不会影响其他数据的插入的，因为 innodb 引擎会选择一个最小的算法模型

6.4 锁问题

丢失更新

脏读

不可重复读

6.4.1 丢失更新

两个查询，分别显示到两个页面，两个分别更新，第二次更新会直接覆盖第一次更新，导致了第一次更新丢失

解决：数据上加版本号，更新时对比版本号

6.4.2 脏读

是指读到了未提交的数据（这种情况只会发生在事务隔离级别设置为 Read Uncommitted的情况下）

6.4.3 不可重复读

多次读到的数据不同，发生在事务隔离级别为 Read Committed的情况下，违反了隔离性

不可重复读的问题是可以接受的，因此像 oracle、microsoft sql sever 的隔离级别都是读已提交

mysql 通过 Next-key Lock算法来避免不可重复读

6.5 阻塞

Innodb 引擎中，通过参数 innodb_lock_wait_timeout 来控制等待的时间（默认50秒），innodb_rollback_on_timeout用来设定是否在等待超时时对事物进行回滚操作（默认为 OFF，代表不回滚）

6.6 死锁

Innodb 存储引擎并不会回滚大部分的异常，但是死锁除外，当发生死锁时，Innodb 会马上回滚一个事务

6.7 锁升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。例如：数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。

Innodb 存储引擎中不存在锁升级的问题，1个锁和10000个锁时一样的，没有开销

7 事务

用来保证数据的完整性

分类：

1. 扁平事务（Flat Transaction）

   是事务类型中最简单的一种，但是实际生产环境中使用最频繁，即其间操作都是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序称为原子操作的基本组成模块。

   扁平事务的主要限制是不能提交或回滚事务的某一部分，或分成几个步骤提交。

2. 带有保存点的扁平事务（Flat Transaction with savepoints）

   除了支持扁平事务支持的操作外，允许在事务执行的过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求。

   保存点（savepoint）用来同值系统应该记住事务当前的状态，以便事务发生错误时，事务能回到保存点当时的状态。

   

3. 链式事务（Chained Transactions）

   可视为保存点模式的一个变种。其区别是，带有保存点的扁平事务能回滚到任意正确的保存点。而链式事务中回滚仅限于道歉事务。

   

4. 嵌套事务（Nested Transactions）

   是一种层次结构框架。由一个顶层事务控制着各个层次的事务。顶层事务之下嵌套的事务被称为渍食物，其控制每一个局部的变换。

   

5. 分布式事务（Distributed Transactions）

innodb引擎支持扁平事务，带有保存点的扁平事务。链式事务。分布式事务。

7.1 概述

事务的特性：ACID

1. 原子性（atomicity）

   指整个数据库事务是不可分割的单位。只有所有的数据库操作都执行成功才算成功。

2. 一致性（consistency）

   指事务将数据库从一种状态转变为下一种一致性的状态，在事务开始和结束以后，数据库的完整性约束没有破坏

3. 隔离性（isolation）

   一个事务的影响在该事务提交前对其他事务不可见--通过锁实现的

4. 持久性（durability）

   事务一旦提交，其结果就是永久性的

另事务的隔离级别：

1. 读未提交
2. 读已提交
3. 可重复读
4. 序列化读

7.2 事务的实现

隔离性通过锁来实现

原子性、一致性、持久性通过数据库的 redo 和 undo 来完成

原子性、持久性 ---> redo

持久性 ----> undo

7.2.1 redo

重做日志用来实现事务的持久性，即事务ACID中的D。由两部分组成，一是内存中的重做日志缓冲（redo log buffer），其实易失的。二是重做日志文件（redo log file），其实持久的。

当开始一个事务时，会记录该事务的一个 LSN（Log Sequence Number，日志序列号），当事务执行时，会往 Innodb 引擎的日志缓冲里插入事务日志；当事务提交时，必须将 InnoDB 引擎的日志缓冲写入磁盘。也就是在写数据前，需要先写日志（预写日志方式 Write-Ahead Logging, WAL）

在innodb引擎中，重做日志都是以512字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块（block）的方式保存的，也就是重做日志块（redo log block），每块为512字节。

若一个页中产生的重做日志数量大于512字节，那么需要分割为多个重做日志块及逆行存储。此外，由于重做日志块的大小和扇区大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要doublewrite技术。

重做日志文件中存储的就是log buffer中保存的log block，因此其也是根据块的方式进行物理存储的管理，每个块的大小与log block一样，为512字节。在innodb引擎运行过程中，log buffer根据一定的规则将内存中的log block刷新到磁盘：1. 事务提交时 2. 当log buffer中有一般的内存空间已经被使用时 3. log checkpoint 时

对于log block的写入追加在redo log file的最后部分，当一个redo log file写满时，会接着写入下一个redo log file。

虽然log block的写入追加在redo log file的最后部分，但是log buffer刷新到磁盘是并不是顺序地，因为在刷新是，会刷新redo log file前2KB的信息。

LSN是log sequence Number的缩写，代表日志序列号。在Innodb引擎中占用8字节。含义有：

1. 重做日志写入的总量
2. checkpoint的位置
3. 页的版本

LSN不仅记录在重做日志中，还存在与每个页中。在每个页的头部，有一个值FIL_PAGE_LSN，记录了该页的LSN。

7.2.2 undo

重做日志记录了事务的行为，可以很好地通过其进行重做，但是事务还需要撤销（undo）。

undo 和 redo 正好相反，对数据库进行修改时，不但会产生 redo，还会产生一定的 undo。

和 redo不同，redo 存放在重做日志文件中，undo 存放在数据库内部的一个特殊段（segment）中，也就是 undo 段，该段位于共享表空间内。（从innodb1.2版本开始可以指定独立的表空间） （innodb_undo_directiry/ innodb_undo_logs / innodb_undo_tablespaces）

逻辑的将数据恢复到之前的样子，例如：对于每个 INSERT，存储引擎回滚时会执行 DELETE，对于 DELETE，回执行 INSERT，对于 UPDATE，会执行相反的 UPDATE。

除了回滚操作外，undo另一个作用是MVCC。

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

7.3 事务控制语句

默认事务都是自动提交的

手动开始事务的3中方式：

1. BEGIN

2. START TRANSACTION

3. SET AUTOCOMMIT=0

   BEGIN、START TRANSACTION ，当执行这俩个时，mysql 会自动执行 SET AUTOCOMMIT=0（在默认配置下），当执行 COMMIT或者 ROLLBACK 时，会自动执行 SET AUTOCOMMIT=1

事务控制语句：

1. START TRANSACTION | BEGIN：显示开启一个事务
2. COMMIT：提交事务
3. ROLLBACK：回滚
4. SAVEPOINT identifier：在事务中创建一个保存点（可以有多个）
5. RELEASE SAVEPOINT identifier：删除一个事务的保存点（当没有保存点时，会跑出异常）
6. ROLLBACK TO [SAVEPOINT] identifier：和 SAVEPOINT 命令一起使用，可以把事务回滚到标记点，而标记点之前的事务不回滚
7. SET TRANSACTION：设置事务的隔离级别（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）

7.4 隐式提交的 sql

当执行完这些sql 后，会有一个隐式的 COMMIT 操作（即执行这些操作后，不能回滚）：

• DDL 语句（包括 alter、create、drop、rename、truncate） （注意delete 不是 DDL，因此可以回滚）
• 用来隐式的修改 mysql 架构的操作：CREATE USER 、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD
• 管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE

7.5 对于事务的统计操作

每秒请求数（Question Per Second，QPS）、每秒事务处理能力（Transaction Per Second，TPS）

TPS=（com_commit + com_rollback） / time

7.7 分布式事务

Innodb 存储引擎支持 XA 事务，通过 XA 事务可以实现分布式事务。

分布式事务是指允许多个独立的事务资源（transactional resources）参与一个全局事务。

在使用分布式事务时，Innodb 存储引擎的事务隔离级别必须设置为 SERIALIABLE

分布式事务由一个或者多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

分布式事务才有两阶段提交（tow-phase commit）的方式，在第一阶段，所有参数全局事务的节点都开始准备（PREPARE），告诉事务管理器他们准备好了，第二阶段，事务管理器告诉资源管理器执行 ROLLBACK 还是 COMMIT，如果任何一个节点显示不能提交，则所有节点都要回滚。