MySQL学习笔记事务和锁篇

Posted on 2022-11-24 Edited on 2024-05-08 In MySQL Views:

这是学习MySQL的笔记事务和锁篇，主要包含数据库的事务、锁和MVCC机制。

事务概述

事务是数据库区别于文件系统的重要特性之一，当有了事务，就会让数据库使用保持一致性，同时还能通过事务的机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

存储引擎支持事务的情况

SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)

可以看到，只有InnoDB支持事务。

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务的处理原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交（commit），那么这些修改就永久地保存下来；要么数据库管理系统将放弃所做的所有修改，整个事务回滚（rollback）到最初状态。

事务的ACID特性

原子性 Atomicity：原子性指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚，没有中间状态
一致性 Consistency：一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态，这种状态是语义上的而不是语法上的，跟具体业务有关。事务执行前后的状态，都是合法的，就是一致性。
隔离性 Isolation：隔离性是指事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
持久性 Durability：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是通过事务日志来保证的，日志包括了重做日志和回滚日志，当通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样的好处是即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

ACID是事务的四大特性，原子性是基础，隔离性是手段，一致性是约束条件，持久性是目的。

事务的状态

MySQL根据对事务的一个或多个操作阶段，把事务大致划分几个状态：

活动的 active：事务对应的数据库操作正在执行过程中时，就说该事务处在活动的状态。
部分提交的 partially committed：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，该事务处在部分提交状态。
失败的failed：当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，该事务处于失败的状态。
中止的 aborted：事务执行了一部分而变成失败的状态，那么就需要把已经修改的事务中的操作还原到初始状态，也就是需要撤销操作。这个撤销的过程称之为回滚，当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，该事务处在了中止的状态。
提交的 commit：当一个处在部分提交的状态的事务将修改后的数据都同步到磁盘上之后，该事务处在了提交的状态。

事务状态转换图：

使用事务

事务的完整过程

显式事务：

-- 显式事务 
-- 步骤1：开启事务
-- START TRANSACTION 可以限制事务操作 
-- 当前事务是只读事务，不能增删改，只是不允许修改那些其他事务也能访问到的表的数据，对于临时表(CREATE TEMPORARY TABLE)来说，是可以进行增删改的 
START TRANSACTION READ ONLY;
-- 读写，默认 
START TRANSACTION READ WRITE;
-- 开启一致性读 
START TRANSACTION READ CONSISTENT SNAPSHOT;
-- 或者  
BEGIN;
-- 步骤2：一系列的DML操作
-- 步骤3：提交或者中止状态  
-- 提交事务 
COMMIT;
-- 回滚事务  
ROLLBACK;

隐式事务：

-- 隐式事务  
-- 通过 autocommit 设置 ,默认 on，代表每一条DML都是一个独立的操作，执行之后，默认会自动提交。
SHOW VARIABLES LIKE '%autocommit%';
-- 关闭自动提，只针对DML，对DDL是无效的  
SET autocommit=FALSE; 
-- 执行操作，构成一个独立的事务   
-- 提交  
COMMIT;
-- 当 autocommit 为 true，START TRANSACTION或者BEGIN之后，都不会将DML操作作为单个事务，而是作为整体一个事务。

保存点：

-- 保存点 SAVEPOINT ： 事务操作中设置的一个状态，在事务结束之前，可以回到保存点继续操作失误。
-- 设置保存点 
SAVEPOINT save1;
-- 回滚到保存点 
ROLLBACK TO save1;
-- 删除保存点 
RELEASE SAVEPOINT save1;

隐式提交数据的情况

数据定义语言 DDL ：例如操作数据库、表、视图、存储过程等结构，CREATE、ALTER、DROP等语句
隐式使用或修改MySQL数据库中的表：使用 ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句也会隐式提交前面语句所属于的事务
事务控制或关于锁定的语句：在一个事务中没有提交或者回滚，又使用START TRANSACTION或者BEGIN开启一个新的事物，会隐式的提交上一个事务
将 autocommit 设置为true，也会隐式提交前边语句所属的事务
使用 LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。
加载数据的语句：LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前面语句所属的事务。
关于MySQL复制的一些语句：START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句，也会隐式的提交前面语句所属的事务。
其他的一些语句：使用 ANALYZE TABLE，CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前面语句所属的事务。

事务操作

使用事务

-- 使用事务 
CREATE TABLE user3(name VARCHAR(10) PRIMARY KEY);
SELECT * FROM user3;
-- 开启事务 
BEGIN;
-- 插入数据
INSERT INTO user3 VALUES('a');
-- 获取数据，可以获取到 
SELECT * FROM user3;
-- 提交 
COMMIT;
-- 获取数据，也可以获取到 
SELECT * FROM user3;

-- 开启事务 
BEGIN;
INSERT INTO user3 VALUES('c');
-- 收到主键影响，会插入失败  
INSERT INTO user3 VALUES('c');
ROLLBACK;

-- DDL 操作会自动提交数据，不受 autocommit 变量的影响 
TRUNCATE TABLE user3;
-- autocommit为默认状态   
SHOW VARIABLES LIKE '%autocommit%';
-- 默认一条DML为一个事务，可以提交成功
INSERT INTO user3 VALUES('a'); 
-- 由于主键原因，操作失败，处于 failed 状态 
INSERT INTO user3 VALUES('a');
-- 回滚，回滚到失败状态之间
ROLLBACK;
-- 只插入了1条记录  
SELECT * FROM user3;

-- completion_type的使用  
SELECT @@completion_type;
SET @@completion_type = 1;
-- autocommit为默认状态   
SHOW VARIABLES LIKE '%autocommit%';
-- 默认一条DML为一个事务，可以提交成功
INSERT INTO user3 VALUES('b'); 
-- 由于主键原因，操作失败，处于 failed 状态 
INSERT INTO user3 VALUES('b');
-- 回滚
ROLLBACK;
-- 还是只有a这1条记录，这就是由于   @@completion_type 的影响 
SELECT * FROM user3;

completion_type的使用

completion_type=0，默认情况，当执行COMMIT的时候会提交事务，执行下一个事务时，还需要用START TRANSACTION或者BEGIN来开启。
completion_type=1，提交事务之后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事物，即提交事务之后会开启一个相同隔离级别的事务。
completion_type=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当事务提交时，会自动与服务器断开连接。

InnoDB和MyISAM在事务下的区别

MyISAM不支持事务

-- 创建表  
CREATE TABLE test1(i INT) ENGINE = INNODB;
CREATE TABLE test2(i INT) ENGINE = MYISAM;
-- 针对 InnoDB 
BEGIN; 
INSERT INTO test1 VALUES (1);
ROLLBACK;
-- 回滚之后，没有数据 
SELECT * FROM test1;

-- 针对 MyISAM 
BEGIN;
INSERT INTO test2 VALUES (1);
ROLLBACK;
-- 回滚之后，数据还在，事务对MyISAM无效 
SELECT * FROM test2;

保存点 SAVEPOINT

-- 创建表 
CREATE TABLE user4(NAME VARCHAR(10),balance DECIMAL(10,2));
-- 操作事务 
BEGIN;
INSERT INTO user4(NAME,balance) VALUES('a',1000);
COMMIT;
SELECT * FROM user4;

-- 再次操作事务  
BEGIN;
UPDATE user4 SET balance=balance-100 WHERE NAME = 'a';
UPDATE user4 SET balance=balance-100 WHERE NAME = 'a';
-- 设置保存点 
SAVEPOINT s1;
UPDATE user4 SET balance=balance+1 WHERE NAME = 'a';
-- 单独回滚 +1 的操作，回滚到保存点 s1  
ROLLBACK TO s1;
SELECT * FROM user4;
-- 此时事务没有完成  
ROLLBACK;
-- 或者  
COMMIT;

事务的隔离级别

实际使用场景中，可能出现多线程并发操作，而且可能出现多个线程同时使用事务，这种情况下，需要保障事务的ACID，此时就需要通过隔离性，某个事务对某个数据进行访问时，其他事务应该进行排队，当事务提交之后，其他事务才可以就行访问这个数据。但是这样对性能影响很大，因此需要权衡。

数据准备

-- 创建表
CREATE TABLE student(
studentno INT,
name VARCHAR(20),
class VARCHAR(20),
PRIMARY KEY(studentno)
)ENGINE=INNODB CHARSET=utf8;
-- 插入数据
INSERT INTO student VALUES(1,'小谷','1班');
-- 获取数据
SELECT * FROM student;
+-----------+--------+-------+
| studentno | name   | class |
+-----------+--------+-------+
|         1 | 小谷   | 1班   |
+-----------+--------+-------+
1 row in set (0.01 sec)

数据并发问题

脏写（Dirty Write）

对于两个事务Session A、Session B，如果事务Session A修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写
脏读（Dirty Read）

对于两个事务Session A、Session B，Session A读取了已经被Session B更新但还没有被提交的字段。之后若Session B回滚，Session A读取的内容就是临时且无效的。
不可重复读（Non-Repeatable Read）

对于两个事务Session A、Session B，**Session A读取了一个字段，然后Session B更新了该字段。之后Session A再次读取同一个字段，值就不同**，就意味着发生了不可重复读。
幻读（Phantom）

对于两个事务Session A、Session B，Session A从一个表中读取了一个字段，然后Session B在该表中插入了一些新的行。之后，如果Session A再次读取同一个表，就会多出几行。那就意味着发生了幻读。

注意1：如果Session B删除了一些记录，导致Session A读取的记录变少了，这个现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

注意2：对于先前已经读到的记录，之后又读取不到，这相当于每一条记录都发生了不可重复读的现象。

SQL中的四种隔离级别

上面的问题按照严重性排序：

1	脏写 > 脏读 > 不可重复读 > 幻读

可以舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，并发问题发生的就越多。

SQL标准设立了4个隔离级别：

READ UNCOMMIT：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果（没有提交，但是可以看到结果，也就意味着发生了脏读，因为如果另外一个事务回滚，则出现前后不一致。）。不能避免脏读、不可重复读、幻读。
READ COMMIT：读已提交，满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变（没有提交，则读取不到，提交，则读取到），这是大多数数据库系统的默认隔离级别（Oracle默认隔离级别）。可以避免脏读，但不可重复读、幻读问题仍然存在。
REPEATABLE READ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容（先读一条数据，后续无论其他事务是否提交，都按照这条已经读到的数据处理）（这里与脏读的差别是脏读提交之后可读取到，提交之前读取不到；可重复读是读取的都是第一次读取的数据。）。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
SERIALIZABLE：可串行化，确保事务可以从一个表中读取相同的行。这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但是性能很低。能避免脏读、不可重复读和幻读。

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

脏读是最为严重的，因此这四种隔离级别，都解决了脏读的问题。

不同隔离级别有不同的锁和并发机制，隔离级别与并发性能关系如下：

MySQL支持的四种隔离级别

MySQL虽然都支持四种隔离级别，但是跟SQL标准中的定义有些出入。MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生，主要是通过MVCC实现。

-- 查看隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别  
-- 当前会话  
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 全局 
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 或者  
SET SESSION TRANSACTION_ISOLATION = 'READ-UNCOMMITTED';
SET SESSION TRANSACTION_ISOLATION = 'READ-COMMITTED';
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';

GLOBAL：当前已经存在的会话无效，只对执行完该语句之后产生的会话起作用；

SESSION：对当前会话的所有后续事务有效，如果在事务之间执行，对后续的事务有效，该语句可以在当前事务中间执行，但不会影响当前正在执行的事务

重启之后，都会恢复成默认隔离级别。

演示脏读的问题

-- 解决读未提交 
SELECT @@transaction_isolation;

-- 开启事务 
BEGIN;
UPDATE account SET balance =balance + 100 WHERE id = 1;
-- 回滚 
ROLLBACK;

-- 开启的另外一个会话  
USE dbtest2;
SELECT * FROM account;
-- 修改隔离级别 
-- 修改成读未提交  
SET SESSION TRANSACTION_ISOLATION = 'READ-UNCOMMITTED';
SELECT @@transaction_isolation;
-- 此时另外一个session的事务未提交 
-- 可以获取到数据，出现了脏读  
SELECT * FROM account;
-- 另外一个session的事务回滚之后，再次读取数据，此时数据又发生变更 
SELECT * FROM account;

演示不可重复读的问题

SELECT * FROM account;
-- 更改隔离级别，改成读已提交 
SELECT @@transaction_isolation;
SET SESSION TRANSACTION_ISOLATION = 'READ-COMMITTED';
-- 开启事务 
BEGIN;
-- 扣除1中500 
UPDATE account SET balance = balance - 500 WHERE id = 1;
-- 查询此时数据，数据正常 
SELECT * FROM account;
-- 提交 
COMMIT;

SELECT * FROM account;
-- 更改隔离级别，改成读已提交 
SELECT @@transaction_isolation;
SET SESSION TRANSACTION_ISOLATION = 'READ-COMMITTED';
-- 开启事务 
BEGIN;
-- 开启事务之后先获取一次数据，起初状态都是 1000   
SELECT * FROM account;
-- 另外一个session操作事物之后，获取到的还是 1000，此时就避免了脏读     
SELECT * FROM account;
-- 事务没有结束，另一个session提交之后，再次读取，读取的是提交之后的数据，出现了不可重复读  
SELECT * FROM account;
COMMIT;

解决不可重复度的问题，使用可重复读的隔离级别

-- 更改隔离级别，改成可重复度
SELECT @@transaction_isolation;
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
-- 同时开启事务 
BEGIN;
-- 开启之后就获取一次数据  
SELECT * FROM account;
-- 另一个回话事务修改之后，再次获取数据，与上次获取的是一样的，脏读避免了 
SELECT * FROM account;
-- 另一个回话事务提交之后，再次获取数据，与上次获取的是一样的，也解决了不可重复读的问题  
SELECT * FROM account;

-- 更改隔离级别，改成可重复度
SELECT @@transaction_isolation;
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
-- 同时开启事务 
BEGIN;
-- 开启之后就获取一次数据  
SELECT * FROM account;
-- 另一个回话事务修改之后，再次获取数据，与上次获取的是一样的，脏读避免了 
SELECT * FROM account;
-- 另一个回话事务提交之后，再次获取数据，与上次获取的是一样的，也解决了不可重复读的问题  
SELECT * FROM account;

幻读的问题：

-- 更改隔离级别，改成可重复度
SELECT @@transaction_isolation;
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
-- 插入数据 
INSERT INTO account(id,`NAME`,balance) VALUES(3,'a',100);

-- 更改隔离级别，改成可重复度
SELECT @@transaction_isolation;
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
-- 开启事务  
BEGIN;
-- 获取个数，最开始为0个 
SELECT COUNT(*) FROM account WHERE id = 3;
-- 另一个session插入之后，再次获取个数，依然是0个，此时验证了可重复读  
SELECT COUNT(*) FROM account WHERE id = 3;
-- 但是如果此时3的记录插入，会出现报错   1062 - Duplicate entry '3' for key 'account.PRIMARY' ，也就是记录3是存在的，出现了幻读  
INSERT INTO account(id,`NAME`,balance) VALUES(3,'b',200);
ROLLBACK;

通过SERIALIZABLE串行化解决幻读的问题

SERIALIZABLE隔离级别下，如果在session A的事务中插入数据，此时会加上一个隐式的行（X）锁/gap（X）锁，而session B的事务中再次插入会被阻塞，session A 的事务结束之后，session B的锁才会被释放。

MySQL在 REPEATABLE-READ隔离级别下，也是可以避免幻读的，主要也是对select操作手动加行X锁（独占锁），从而在session A的 insert操作时会阻塞。即便当前记录不存在，比如id=3不存在，当前事务也会获得一把记录锁（因为InnoDB的行锁锁定的是索引，故记录实体存在与否没关系，存在就加行X锁，不存在就加间隙锁。），从而解决幻读的问题。

MySQL事务日志之`redo`日志

事务的ACID特性实现机制：

隔离性：通过锁机制实现
原子性、一致性、持久性由事务的redo日志和undo日志来保证
- REDO LOG称为重做日志，提供再写入操作，恢复提交事务修改的页操作（如果事务写入到一半，服务器宕机，重启之后，能够保证事务写入完整，保证数据的可靠性），用来保证事务的持久性。
- UNDO LOG称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

REDO和UNDO都可以视为一种恢复操作，但是：

redo log：存储引擎InnoDB生成的日志，记录的是物理级别上的页的修改操作，比如页号xxx、偏移量yyy写入了数据zzz。主要是为了保证数据的可靠性。
undo log：存储引擎InnoDB生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句的操作，那么undo log就记录一条与之相反的DELETE操作。主要用于事务的回滚（undo log记录的是每个修改操作的逆操作）和一致性非锁定读（undo log回滚行记录到某种特定的版本–MVCC，即多版本并发控制）。

`redo`日志

InnoDB存储引擎是以页为单位来管理存储空间的。早真正访问页面之前，需要把磁盘上的页缓存到内存中的buffer pool之后才可以访问。所有变更都必须先更新缓冲池中的数据，然后把缓冲池中的脏页会以一定的频率被刷入磁盘（checkpoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，来保证整体的性能不会下降太快。

为什么需要``redo log`？

由于checkpoint机制不是每次变更的时候触发，当事务没有提交，事务中的操作只是修改了缓冲池中的数据，此时服务器宕机，缓冲池中的数据丢失，此时就无法保证事务的持久性。或者说事务执行到一半宕机，恢复之后，能够从继续从中断的部分继续开始。

事务的持久性特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。

一个简单的做法：通过在事务提交之前，把该事务所修改的所有页面都刷新到磁盘，但是这个做法有些问题：

修改量与刷新磁盘工作量严重不成比例：修改缓冲池可能只有1Byte，但是一个页修改磁盘会修改16KB，这样会大大的增加成本。
随机IO刷新较慢：一个事务可能操作很多页，每一个页都去操作可能导致大量的随机IO，成本也很高。

针对这种情况，可以将修改了的数据记录一下。

InnoDB引擎的事务采用WAL技术（Write-Ahead Log），先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机，且数据未刷到磁盘的时候，可以通过redo log恢复，保证ACID中的D。

`redo log`的好处、特点

好处：

redo log降低刷盘频率
redo log日志占用的空间非常小：保存表空间id、页号、偏移量以及需要更新的值

特定：

redo log是顺序写入的：按照产生的顺序写入磁盘
事务执行过程中，redo log不断记录

`redo`的组成

redo log可以简单分为两个部分：

重做日志的缓冲（redo log buffer）：保存在内存中，是易失的。服务器启动时，就向操作系统申请了一大片称之为 redo log buffer的连续内存空间，这片空间被划分成若干个连续的 redo log block，一个 redo log block占用512字节大小。
1
2
-- 查看 redo log buffer
SHOW VARIABLES LIKE '%innodb_log_buffer_size%';

重做日志文件（redo log file）：保存在磁盘中，是持久的。

1
2
3

-rw-r-----  1 mysql mysql 3.2M Nov 23 02:14 '#ib_redo753'
bash-4.4# pwd
/var/lib/mysql/#innodb_redo

整体流程

更新事务过程如下：

将原始数据从磁盘加到到内存中，修改内存中的数据
生成一条redo log，写入redo log buffer，记录的是数据被修改后的值
当事务commit时，将redo log buffer 中的内存刷新到 redo log file，对 redo log file 采用追加写的方式
定期将内存中修改的数据刷新到磁盘中

`redo log` 刷盘策略

redo log buffer中的数据，会以一定频率刷入到真正的redo log file 中。

redo log buffer 刷盘到redo log file过程是刷到文件系统缓存，真正的写入会交给系统自己来决定。InnoDB也可以通过innodb_flush_log_at_trx_commit参数控制commit提交事务时，如何将redo log buffer 中的日志刷新到redo log file中。

0：表示每次事务提交时不进行刷盘操作。（默认 master thread 每隔 1s 进行一次重做日志的同步）

IO效率高于1，安全性低于2，只将数据写入 redo log buffer，是一个折中方案。
1：表示每次事务提交时都将进行同步，刷盘操作（默认值）

这种情况主要事务提交，就会刷到磁盘，效率虽然差，但是安全性高。
2：表示每次事务提交都只把 redo log buffer 内容写入 page cache，不进行同步，由 os 自己决定什么时候同步到磁盘。

只要事务提交成功，redo log buffer 中的文件会写入page cache。

查看和设置配置

1 2	SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%'; SET GLOBAL innodb_flush_log_at_trx_commit=1;

InnoDB存储引擎有一个后台线程，每隔1s，就会把redo log buff内容写入page cache，然后调用刷盘操作。

也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘，因为事务执行过程 redo log记录是会写入 redo log buffer 中，这些 redo log 记录会被后台线程刷盘。

写入`redo log buffer`过程

MySQL把底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr，比如向某个索引对应的B+树中插入一条记录的过程就是一个mtr。一个所谓的mtr可以包含一组redo log，在进行崩溃时，这一组redo log日志作为一个不可分割的整体。

一个事务可以包含多个语句，每个语句其实是由若干个mtr组成，每一个mtr又可以包含若干条redo log。

向log buffer中写入redo log的过程是顺序的，也就是先往前边的block写，当该block的空闲空间用完之后，再往下一个block写。

一个mtr执行过程的redo log作为一个不可分割的组。每个mtr运行过程中产生的日志先暂存到一个地方，当该mtr结束的时候，将过程中产生的一组redo log全部复制到log buffer中。

例如两个交叉的事务日志记录

redo log block由三个部分组成，日志头、日志体、日志尾，一共是512字节。

`redo log file`

日志目录

-- 查看 redo log file 存放位置 
SHOW VARIABLES LIKE 'innodb_log_group_home_dir';
-- 查看 redo log file 个数  
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
-- 查看单个redo log file 大小，总大小就是 innodb_log_file_size * innodb_log_files_in_group，单个默认值 48M ，总最大值512G
SHOW VARIABLES LIKE 'innodb_log_file_size';

日志文件组

写入日志文件的顺序是先写0号日志，写满了再往下一个日志文件中写

循环使用的方式可能导致后写入的redo日志覆盖前面的redo日志，因此InnoDB提出checkpoint的概念。

整个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

write pos：当前记录的位置，一边写一边后移
checkpoint：当前要擦除的位置，也是往后推移

如果 write pos追上checkpoint，表示日志文件组满了，这时候就不能再写入新的redo log，MySQL需要清空一些记录，把checkpoint推进一下。

MySQL事务日志之`undo`日志

`undo`日志

redo log 是事务持久性的保证，undo log 是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个undo log 。

事务需要原子性，当事务需要回滚或者服务器突然断电，需要将数据改变到原先的样子，这个过程就是回滚。

当做改动（INSERT、DELETE、UPDATE）时（查询不会记录undo log），会将回滚时所需的东西记录下来：

插入时，会记录一个基于主键的删除操作
删除时，会记录全部内容的一个插入操作
修改时，会记录一个相反的更新操作

MySQL把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志（undo log）。此外，undo log 会产生 redo log，也就是undo log 的产生会伴随着redo log 的产生，这是因为undo log 也需要持久化。

`undo log` 的作用

作用1：回滚数据：undo是逻辑层面，而不是物理层面，当新的数据开辟新的页，undo之后，不会回收新的页。
作用2：MVCC：当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

`undo` 的存储结构

回滚段与undo页：InnoDB对undo log的管理采用段的方式，每个回滚段记录了1024个undo log segment，在每个undo log segment段中进行undo页的申请

1个undo log segment可支持1个事务，查看回滚段的个数

SHOW VARIABLES LIKE 'innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    | -- 日志存放位置
| innodb_undo_log_encrypt  | OFF   |
| innodb_undo_log_truncate | ON    |
| innodb_undo_tablespaces  | 2     | -- undo log segment都存储于共享表空间ibdata中
+--------------------------+-------+
4 rows in set (0.02 sec)

回滚段与事务

每个事务会使用一个回滚段，一个回滚段同一时刻可能会服务于多个事务
一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段
回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前盘区不沟通，事务会在段中请求下一个盘区，如果所有已分配的盘区都用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用
回滚段存于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo 表空间。
当事务提交时，InnoDB存储引擎会做以下两件事情：
1. 将undo log 放入列表中，以供之后的purge操作
2. 判断undo log所在的页是否可以重用（undo log 在commit 之后，会被放到一个链表中，然后判断undo 页的使用空间是否小于3/4，小于的话则代表当前undo 页可以重用），若可以分配给下个事务使用

回滚段中的数据分类

未提交的回滚数据：用于实现读一致性，因此数据不能被其他事务的数据覆盖
已经提交但未过期的回滚数据：关联的事务已经提交，但是仍受到 undo retention参数的保持时间的影响。（可能有其他事务需要通过undo log 来得到记录之前的版本）
事务已经提交并过期的数据：事务已经提交，并且已经超过undo retention参数的保持时间，属于已经过期的数据。当回滚段满了之后，会优先覆盖”事务已经提交并过期的数据“

`undo`的类型

insert undo log ：insert操作中产生的undo log。这个操作只对事务本身可见，对其他事务不可见（满足事务隔离性要求），因此undo log 可以在事务提交之后直接删除。
update undo log：对update和delete操作中产生的undo log 。该undo log 可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log 链表，等待purge 线程进行最后的删除。

`undo log`的生命周期

A=1和B=2，将A修改为3，B修改为4

begin;
记录 A=1 到 undo log;
update A=3;
记录 A=3 到 redo log;
记录 B=2 到 undo log;
update B=4;
记录 B=4 到 redo log;
将 redo log 刷新到磁盘;
commit;

1-8步的任意一步系统宕机，事务未提交，该事务不会对磁盘上的数据做任何影响；
如果8-9之间宕机，恢复之后，可以选择回滚，也可以选择继续完成事务提交，因此此时redo log 已经持久化；
9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log 把数据刷回磁盘。

没有redo log和undo log，只有Buffer Pool的流程：

有 redo log和 undo log之后

详细生成过程

每一条数据的列式存储时，有隐藏的三个字段：

DB_ROW_ID：如果乜有显示的主键，也没有唯一索引，那么InnoDB会自动添加一个row_id的隐藏列作为主键
DB_TRX_ID：每个事务都会有一个id，当记录发生变更时，记录这个事务的ID
DB_ROLL_PTR：回滚指针，指向undo log的指针

当执行INSERT时

1 2	BEGIN; INSERT INTO user(name) values("tom");

插入一条数据会生成一条undo log，

执行UPDATE时

1	UPDATE user SET name="Sun" WHERE id=1;

将老的记录写入新的 undo log，让回滚指针指向新的 undo log

此时更新

1	UPDATE user SET id=2 WHERE id=1;

实际上是将原先的数据的删除标记打开，然后在后面插入一条新的数据，新的数据回滚指针指向产生的 undo log 。undo log每次都是递增，按照序号依次向前推，就可以找到原始数据。

`undo log` 回滚

通过undo no=3的日志把id=2的数据删除
通过undo no=2的日志把id=1的数据的deletemark还原成0
通过undo no=1的日志把id=1的数据的name还原成Tom
通过undo no=0的日志把id=1的数据删除

`undo log`删除

针对 insert undo log：事务提交之后直接删除
针对 update undo log：提交之后可能需要提供MVCC机制，提交时放到 undo log链表，等待 purge线程进行最后的删除

purge线程的作用有两个：

清理undo页
清除page里面带有Delete_Bit标识的数据行

日志小结

锁

锁是多个线程或进程并发访问某一资源的机制。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。

并发事务访问相同记录

读-读情况

并发事务相继读取相同的记录。读取操作本身不会对记录有任何影响，并不会出现问题，所以允许这种情况。

写-写情况

并发事务相继对相同的记录做出改动。

这种情况下会发生脏写的问题。在多个未提交事务相继对一条记录做改动时，需要让他们排队执行，这个排队过程其实是通过锁来实现的。这个锁其实是一个内存中的结构。

当这个事务对这个记录做改动时，首先看内存中有没有与这条记录挂链的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。

trx信息：代表这个锁结构是哪个事务生成的
is_waiting：代表当前事务是否在等待

T1加锁成功之后，继续执行操作。另外一个事务T2获取锁失败，则等待锁结构释放。并且T2对应的锁结构的is_waiting属性为true

当事务T1提交之后，就会把该事务生成的锁结构释放，然后看看还有没有别的事务在等待锁，发现事务T2在等待获取锁，将事务T2对应的锁结构的is_wating改为false，唤醒事务T2对应的线程，此时T2就算获取到锁。

读-写或写-读情况

即一个事务进行读取操作，另一个事务进行改动操作。这种情况可能发生脏读、不可重复读、幻读的问题。MySQL在REPEATABLE READ隔离级别上已经解决了幻读问题。

并发问题解决方案

方案一：读操作利用多版本并发控制（MVCC），写操作进行加锁

MVCC，生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本由undo日志构建），查询语句只能读到在生成ReadView之前已提交事务所做的更改，生成ReadView之前为提交的事务或者之后才开启的新事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。
普通的SELECT语句在READ COMMITED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
- 在READ COMMITED隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在保证了事务不可读取到未提交的事务所做的更改，避免了脏读现象；
- 在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，避免了不可重复读和幻读的问题；
方案二：读写都加锁

有些业务不允许读取到记录的旧版本，此时就需要加锁。这种情况下，脏读、不可重复读、幻读（幻读加锁会比较麻烦，主要是无法确定新的记录如何加锁）的问题就全部都解决了，事务没有提交时，无法读取或者修改数据。

汇总：

采用MVCC的话，读-写操作并不冲突，性能更高。
采用加锁方式的话，读-写操作彼此需要排队，影响性能。

一般情况下采用MVCC，业务特殊情况下，采用加锁的方式。

从数据操作的类型划分

读锁（read lock）、写锁（write lock），也称作共享锁（Shared Lock，S Lock）和排他锁（Exclusive Lock，X Lock）

读锁：也称为共享锁、英文用S表示。表示同一份数据，多个事务的读操作可以同时进行而不会互相影响，互相不阻塞。
写锁：也称为排它锁、英文用X表示。当前写操作没有完成前，它会阻塞其他写锁和读锁，防止其他用户读取正在写入的同一资源。

对于InnoDB引擎来说，读锁和写锁可以加在表上，也可以加在行上。

锁定读

采用加锁方式解决脏读、不可重复读、幻读这些问题时，读取一条记录时需要获取该记录的S锁，其实是不严谨的，有时候需要在读取记录时就获取记录的X锁，来禁止别的事务读写该记录，为此MySQL提出了两种比较特殊的SELECT语句格式：

对读取的记录加S锁：
1
2
3
SELECT ... LOCK IN SHARE MODE;
-- 或者，在 8.0 中
SELECT ... FOR SHARE;
如果当前事务执行该SELECT，会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁（比如别的记录也使用 SELECT ... LOCK IN SHARE LOCK;）。但是不能获取这些记录的X锁，获取X锁的时候会阻塞，直到当前事务提交之后将这些记录的S锁释放掉。
对读取的记录加X锁：
1
SELECT ... FOR UPDATE;
当前事务执行该语句，那么会对读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁，也不允许获取这些记录的X锁。如果别的事务想要获取这些记录的S锁或者X锁，那么会阻塞，直到当前事务提交之后将这些记录的X锁释放掉。

session A：

1
2
3

begin;
-- 加读锁
select * from employee LOCK IN SHARE MODE;

session B：

begin;
select * from employee; -- 可获取数据 
select * from employee LOCK IN SHARE MODE; -- 可获取数据 
select * from employee FOR UPDATE; -- 报错
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

超过了最大执行时间

SELECT @@max_execution_time;
+----------------------+
| @@max_execution_time |
+----------------------+
|                 2000 |
+----------------------+
1 row in set (0.02 sec)
-- 在MySQL调优中修改过。全局修改不会对已经连接的会话生效，改成0则是关闭。
SET @@max_execution_time=60000;

session B关闭max_execution_time：

1 2	BEGIN; SELECT * FROM employee FOR UPDATE; -- 进入阻塞状态

MySQL 8.0特性：

5.7 及之前版本，SELECT ... FOR UPDATE;如果获取不到锁，会一直等待，直到 innodb_lock_wait_timeout超时。

8.0版本中，SELECT ... FOR SHARE;和 SELECT ... FOR UPDATE;可以添加 NOWAIT、SKIP LOCKED语法，跳过等待，或者跳过锁定，立即返回。

如果查询的行已经加锁：

NOWAIT会立即返回报错
SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行

BEGIN;
-- 报错： Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
-- navicate 可能将返回优化，在  SELECT * FROM employee FOR UPDATE nowait; 不会返回报错，而是返回空行
SELECT * FROM employee WHERE emp_id=1 FOR UPDATE nowait;

1
2
3

BEGIN;
SELECT * FROM employee WHERE emp_id=1 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)

写操作

写操作就直接使用X锁。

DELETE：在B+树中定位到这条记录的位置，然后获取这条记录的X锁，再执行delete mark操作。
UPDATE：分三种情况
- 情况1：未修改该记录的键值（主键值），并且被更新的列占用的存储空间在修改前后未发生变化。
  
  在B+树中定位到这条记录的位置，然后获取该记录的X锁，然后在原纪录的位置进行修改操作。
- 情况2：未修改记录的键值（主键值），并且至少有一个被更新的列占用的存储空间在修改前后发生变化。
  
  在B+树中定位到这条记录的位置，然后获取该记录的X锁，将该记录彻底删除掉（把记录移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT提供的隐式锁进行保护。
- 情况3：修改了该记录的键值（主键值），则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行。
INSERT：一般情况下，新插入一条记录的操作并不加锁，这一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交之前不会被其他事务访问。

从数据操作的粒度划分：表级锁、页级锁、行锁

为了提高数据库的并发，每次锁定的数据范围越少越好，这样就产生了锁粒度（Lokc granularity）的概念。

对一条记录加锁只影响这条记录，这个锁的粒度比较细，这个就是行锁。

一个事务在表级别进行加锁，粒度就比较粗，这个就是表锁。

介于行锁和表锁之间，针对一个页进行操作的加锁，就是页锁。

表锁

会锁住整张表，不依赖存储引擎，而且表锁是开销最小的策略，由于一次性将整个格表锁定，也可以很好的避免死锁的问题。锁的粒度大带来的负面影响就是出现锁资源争用的概率最高，导致并发率大打折扣。

表级别的`S锁`、`X锁`

InnoDB提供行锁，因此使用表级S锁、X锁时，一般不会选择InnoDB，但是InnoDB中也会有表级锁存储，例如元数据锁。在一个事务对表进行增删改查时，如果另外一个会话修改表、删除表操作，会出现阻塞，这个过程其实是通过在server层使用的元数据锁（Metadata Locks，简称MDL）。

另外，在一些特殊情况下，也会使用InnoDB的表级别锁，例如崩溃回复过程。在系统变量autocommit=0,innodb_table_locks=1时，手动获取InnoDB提供的表的S锁或者X锁可以这样写。

1 2	LOCK TABLES t READ; -- 对表t加表级别S锁 LOCK TABLES t WRITE; -- 对表t加表级别X锁

使用MyISAM使用表锁

-- 创建 MyISAM表 
CREATE TABLE mylock(
id INT PRIMARY KEY auto_increment,
`name` VARCHAR(10)
)ENGINE=myisam;
-- 插入数据 
INSERT INTO mylock(`name`) VALUES('a');
-- 查看表上的锁  
SHOW OPEN TABLES WHERE `Table` = 'mylock';
+----------+--------+--------+-------------+
| Database | Table  | In_use | Name_locked |
+----------+--------+--------+-------------+
| mitaka   | mylock |      0 |           0 |
+----------+--------+--------+-------------+
1 row in set (0.02 sec)
-- 加读锁  
LOCK TABLES mylock READ;
-- 查看锁
SHOW OPEN TABLES WHERE `Table` = 'mylock';
+----------+--------+--------+-------------+
| Database | Table  | In_use | Name_locked |
+----------+--------+--------+-------------+
| mitaka   | mylock |      1 |           0 |
+----------+--------+--------+-------------+
-- 加写锁
LOCK TABLES mylock WRITE;
-- 查看锁 
SHOW OPEN TABLES WHERE `Table` = 'mylock';
+----------+--------+--------+-------------+
| Database | Table  | In_use | Name_locked |
+----------+--------+--------+-------------+
| mitaka   | mylock |      1 |           0 |
+----------+--------+--------+-------------+
1 row in set (0.01 sec)
-- 释放当前会话持有的锁 
UNLOCK TABLES;

对表加读锁之后

-- 自己读取，可读
SELECT * FROM mylock;
-- 自己写入，报错 > 1099 - Table 'mylock' was locked with a READ lock and can't be updated
UPDATE mylock SET `name`='b' WHERE id = 1;
-- 自己操作其他表，报错  > 1100 - Table 'employee' was not locked with LOCK TABLES
SELECT * FROM employee LIMIT 1;

-- 他人可读，可读
SELECT * FROM mylock;
-- 他人可写 ,阻塞  
UPDATE mylock SET `name`='b' WHERE id = 1;

对表加写锁之后

-- 自己读取，可读
select * from mylock;
-- 自己写入，可写
UPDATE mylock SET `name`='a' WHERE id = 1;
-- 自己操作其他的表，报错 
SELECT * FROM employee LIMIT 1;
ERROR 1100 (HY000): Table 'employee' was not locked with LOCK TABLES

-- 他人可读，阻塞 
select * from mylock;
-- 他人可写，阻塞
UPDATE mylock SET `name`='b' WHERE id = 1;

总结：

MyISAM在执行查询语句（SELECT）前，会给涉及的所有表加读锁，执行增删改操作前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁或者写锁的。

MySQL的表锁有两种模式：

表共享读锁
表独占写锁

意向锁（`intention lock`）

InnoDB只支持多粗粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

意向锁的存在是为了协调行锁和表锁的的关系，支持多粗粒度（表锁与行锁）的锁并存
意向锁是一种不予行级锁冲突表级锁
表明“某个事务正在某些行吃有了锁或该事务准备去持有锁”

意向锁分为两种：

意向共享锁（intention shared lock，IS）：事务有意向对表中的某些行加共享锁（S锁）
1
SELECT column FROM table ... LOCK IN SHARE MODE;
意向排他锁（intention Exclusi lock，IX）：事务有意向对表中的某些行加排他锁（X锁）
1
SELECT column FROM table ... FOR UPDATE;

意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据进行加共享、排他锁之前，InnoDB会先获取该数据行所在数据表的意向锁。

意向锁要解决的问题：两个事务t1和t2，当t1在某一行上加一个X锁，此时会在这个表上加一个意向锁，当t2在这个表上加X锁时，就会被阻塞。简单来说，就是给更大一级别的空间示意里面是否已经上过锁了。

如果给某一行数据加上了排他锁，数据库会自动给更大一级的空间，比如数据页或者数据表加上意向锁，告诉其他线程这个数据页或者表已经有人上过排它锁了。这样其他人要获取数据表排他锁时，就无需遍历表，只需要获取这个表的意向排他锁即可。

如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁。
如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁。

-- t1 中
-- 获取 隔离级别  ，默认是  REPEATABLE-READ 
SELECT @@transaction_isolation;
-- 开启事务 
BEGIN;
-- 在这一行上加排它锁  
SELECT * FROM employee WHERE emp_id = 1 FOR UPDATE;

1
2
3

-- t2 中 
-- 加表读锁，阻塞
lock tables employee read;

意向共享锁之间是互相兼容的

但是会与普通的排他、共享锁互斥

-- t1 
-- 开启事务 
BEGIN;
-- 在这一行上加排它锁  
SELECT * FROM employee WHERE emp_id = 1 FOR UPDATE;

-- t2 
-- 开启事务 
BEGIN;
-- 在另外一行上加共享锁
SELECT * FROM employee WHERE emp_id = 2 FOR SHARE;
+--------+-------+--------+------------+----------+-----------------+---------+----------------+--------------------+
| emp_id | fname | lname  | start_date | end_date | superior_emp_id | dept_id | title          | assigned_branch_id |
+--------+-------+--------+------------+----------+-----------------+---------+----------------+--------------------+
|      2 | Susan | Barker | 2002-09-12 | NULL     |               1 |       3 | Vice President |                  1 |
+--------+-------+--------+------------+----------+-----------------+---------+----------------+--------------------+
1 row in set (0.01 sec)

但是同一行的时候，就不是意向锁兼容，而是行锁之间互斥。

自增锁（AUTO-INC锁）

给某个列添加自增属性时，在插入语句时不需要为其赋值，系统会自动为它赋上自增的值。

实际上数据插入有三种模式：

Simple inserts（简单插入）：insert ... values(),()和 replace语句，已经确定要插入的行数。
Bulk inserts（批量插入）：insert ... select,replace ... select和load data语句，不确定插入行数，每一行插入时，为AUTO_INCREMENT列分配一个新值。
Mixed-mode inserts（混合模式插入）：insert into xx(id,name) values(1,'a'),(5,'b')指定了部分id的值，另外一种是 insert ... on duplicate key update

AUTO-INC锁是当想含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，插入数据时，在表级别加一个AUTO-INC锁，然后为每条待插入记录AUTO_INCREMENT修饰的列分配递增的值，语句执行结束后释放锁。

一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。

因此，并发性不高，当向一个有AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争。因此InnoDB通过 innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来提高SQL语句的可伸缩性和性能。

1 2	-- 查看 SHOW VARIABLES LIKE '%innodb_autoinc_lock_mode%';

0：代表传统锁定模式，也就是上述的竞争方式。
1：代表连续锁定模式，MySQL 8.0之前的默认模式，在简单插入的情况下，获取锁，一次性获取所需个数，然后释放锁，再插入。但是其他两种插入情况下还是与模式0一致。性能有部分提高。
2：代表交错锁定模式，MySQL 8.0的默认模式，所有INSERT语句都不会使用表级AUTO-INC锁，并且可以同时执行多个语句。但是当使用基于语句的复制或恢复方案时，从二进制日志重播SQL语句时，这是不安全的。

这个模式下，自动递增值保证所有并发执行的insert语句是唯一且单调递增的，但是不一定连续。

元数据锁

MySQL 5.5引入meta data lock，简称MDL锁，属于锁范畴。MDL的作用是，保证读写的正确性。比如如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加一列，那么查询线程拿到的结果就会出问题。

当对一个表做增删改查操作的时候，加MDL读锁；

当对一个表做结构变更曹锁的时候，加MDL写锁；

读锁之间不互斥，因此可以多个线程多一张表同时增删改查。读写锁之间、写锁之间互斥。不需要显式使用，在访问一个表的时候会自动加上。

1
2
3

-- t1 加读锁 
begin;
select * from t;

1 2	-- t2 加写锁 alter table t add column name varchar(10); -- 阻塞

-- 获取状态 
show processlist;
+----+-----------------+------------------+--------+---------+-------+---------------------------------+-------------------------------------------+
| Id | User            | Host             | db     | Command | Time  | State                           | Info                                      |
+----+-----------------+------------------+--------+---------+-------+---------------------------------+-------------------------------------------+
|  9 | root            | 172.17.0.1:58358 | mitaka | Query   |     8 | Waiting for table metadata lock | alter table t add column name varchar(10) |
| 17 | root            | 172.17.0.1:62312 | NULL   | Query   |     0 | init                            | show processlist                          |
+----+-----------------+------------------+--------+---------+-------+---------------------------------+-------------------------------------------+
7 rows in set (0.00 sec)

1 2	-- t3 在 t2阻塞时，再增加读锁 select * from t; -- 阻塞

show processlist;
+----+-----------------+------------------+--------+---------+-------+---------------------------------+--------------------------------------------+
| Id | User            | Host             | db     | Command | Time  | State                           | Info                                       |
+----+-----------------+------------------+--------+---------+-------+---------------------------------+--------------------------------------------+
|  5 | event_scheduler | localhost        | NULL   | Daemon  | 17112 | Waiting on empty queue          | NULL                                       |
|  9 | root            | 172.17.0.1:58358 | mitaka | Query   |    36 | Waiting for table metadata lock | alter table t add column name1 varchar(10) |
| 17 | root            | 172.17.0.1:62312 | mitaka | Query   |    19 | Waiting for table metadata lock | select * from t                            |
| 18 | root            | 172.17.0.1:62814 | NULL   | Query   |     0 | init                            | show processlist                           |
+----+-----------------+------------------+--------+---------+-------+---------------------------------+--------------------------------------------+
8 rows in set (0.01 sec)

也就是说，元数据锁可能带来的问题

行锁

行锁（Row Lock）也称为记录锁，也就是锁住某一行（某条记录row）。MySQL服务器层并没有实现锁机制，行级锁只在存储引擎层实现。

优点：锁定力度小，发生锁冲突概率低，可以实现的并发度高。

缺点：对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

InnoDB与MyISAM的最大不同有两点：一是支持事务，而是采用行级锁。

INSERT INTO student(id,name,class) VALUES(1,'张三','一班'),(3,'李四','一班'),(8,'王五','二班'),(15,'赵六','二班'),(20,'钱七','三班');

SELECT * FROM student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
|  3 | 李四   | 一班   |
|  8 | 王五   | 二班   |
| 15 | 赵六   | 二班   |
| 20 | 钱七   | 三班   |
+----+--------+--------+
5 rows in set (0.00 sec)

简化聚簇索引示意图

记录锁（Record Locks）

记录锁也就是仅仅把一条记录锁上，官方的类型名称为：Lock_REC_NOT_GAP。例如把id值为8的那条记录加一个记录锁的示意图如下：

-- t1 
-- 开启事务
begin;
-- 给这列增加x锁 
update student set name='张三1' where id=1;

-- t2
-- 开启事务
begin;
-- 同时增加x锁
update student set name='张三2' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 或者增加s锁
select * from student where id=1 for share;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 在其他的行上加s锁
select * from student where id=3 for share;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  3 | 李四   | 一班   |
+----+--------+--------+
1 row in set (0.00 sec)

记录锁也有S锁和X锁，称之为S型记录锁和X型记录锁。

当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
当一个事务获取了一条记录的X型记录锁后，其他事务既不可以获取该记录的S型记录锁，也不可以继续获取X型记录锁；

间隙锁（Gap Locks）

MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，一种是MVCC，另外一种是加锁。但是加锁方案有个问题，就是事务在第一次执行读取操作时，那些幻影上尚不存在（幻读：事务中第一次读取有5条记录，第二次读取有10条），无法给这些幻影记录加上记录锁。

InnoDB提出的一种称之为Gap Locks的锁，官方类型名称LOCK_GAP，可以简称gap锁。比如把值id为8的那条记录加一个gap锁的示意图：

意味着：不允许别的事务在id值为8的记录前面的间隙插入新记录（3和8之间）。

gap锁的提出仅仅是为了防止插入幻影记录而提出的，共享gap锁和独占gap锁的作用一样的，对一条记录加了gap锁，并不会限制其他事务对这条记录加记录锁或者继续加gap锁。

-- 对于不存在的数据
-- t1 中
-- 开启事务
begin;
select * from student where id=5 for update;
Empty set (0.01 sec)

-- t2 中
-- 开启事务 
begin;
select * from student where id=10 for update;
Empty set (0.00 sec)
select * from student where id=5 for share;
Empty set (0.01 sec)

间隙锁可以重复添加，并且与记录锁兼容。

实际作用：

-- t3 阻塞
INSERT INTO student(id,name,class) VALUES(5,'张三','一班');

-- 状态
show processlist;
| 17 | root            | 172.17.0.1:62312 | dbtest2 | Query   |    14 | update                 | INSERT INTO student(id,name,class) VALUES(5,'张三','一班')     |

-- 在id=5的地方加间隙锁，实际上是范围（3,8），以下都会锁住
INSERT INTO student(id,name,class) VALUES(4,'张三','一班');
INSERT INTO student(id,name,class) VALUES(6,'张三','一班');

间隙锁范围：

-- t1 
begin;
select * from student where id=200 for share;


-- t2
INSERT INTO student(id,name,class) VALUES(199,'张三','一班');
Query OK, 1 row affected (0.02 sec)
-- 阻塞
INSERT INTO student(id,name,class) VALUES(200,'张三','一班');
ERROR 1317 (70100): Query execution was interrupted
-- 执行成功
INSERT INTO student(id,name,class) VALUES(201,'张三','一班');
Query OK, 1 row affected (0.02 sec)

查看间隙锁信息

select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:1367:281473433260976
ENGINE_TRANSACTION_ID: 562948481699032
            THREAD_ID: 48
             EVENT_ID: 374
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281473433260976
            LOCK_TYPE: TABLE               -- 表锁
            LOCK_MODE: IS                                     -- 意向共享锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:2:281473433257984
ENGINE_TRANSACTION_ID: 562948481699032
            THREAD_ID: 48
             EVENT_ID: 374
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD            -- 记录锁
            LOCK_MODE: S,REC_NOT_GAP         -- 共享锁，间隙锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 200                             -- 间隙锁的数据
2 rows in set (0.02 sec)

间隙锁可能导致的死锁，t1和t2都有间隙锁，然后插入对方事务中间隙锁的范围

-- t1 
begin;
select * from student where id=5 for share;

-- t2
begin;
select * from student where id=5 for share;

然后相互插入数据

-- t1 
INSERT INTO student(id,name,class) VALUES(9,'张三','一班'); -- 阻塞

-- t2 
INSERT INTO student(id,name,class) VALUES(4,'张三','一班');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction -- t2 检测到死锁，MySQL会选择一个成本最低的事务进行回滚
-- t2事务重启之后，t1退出阻塞

临建锁（Next-Key Locks）

有时候既想锁住某条记录，有想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB提出Next-Key Locks的锁，官方的类型名称为：LOCK_ORDINARY，简称为next-key锁（间隙锁+记录锁）。Next—Key Locks是在存储引擎InnoDB、事务级别在可重复读的情况下使用的数据库锁，InnoDB默认的锁就是Next-Key locks。比如，把id为8的那条记录加一个next-key锁的示意图如下：

next-key锁的本质就是一个记录锁和一个gap锁的合体，既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

-- t1 中 
-- 开启事务 
begin;
-- 在 3-8之间用间隙锁，id=8的记录用X锁
select * from student where id <= 8 and id > 3 for update;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  8 | 王五   | 二班   |
+----+--------+--------+
1 row in set (0.01 sec)

-- t2 中 
-- 开启事务  
begin;
select * from student where id=8 for share; --阻塞
select * from student where id=8 for update; -- 阻塞
INSERT INTO student(id,name,class) VALUES(4,'张三','一班'); -- 阻塞  

-- 锁状态
select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504989184:1367:281473433267192
ENGINE_TRANSACTION_ID: 2015556
            THREAD_ID: 49
             EVENT_ID: 283
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281473433267192
            LOCK_TYPE: TABLE
            LOCK_MODE: IX                                        -- 表级别，意向排他锁 
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504989184:1367:281473433267104
ENGINE_TRANSACTION_ID: 2015556
            THREAD_ID: 49
             EVENT_ID: 282
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281473433267104
            LOCK_TYPE: TABLE
            LOCK_MODE: IS                                        -- 表级别，意向共享锁 
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504989184:299:4:4:281473433265224
ENGINE_TRANSACTION_ID: 2015556
            THREAD_ID: 49
             EVENT_ID: 285
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433265224
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION        -- 记录级别，排它锁，间隙锁，插入意向锁
          LOCK_STATUS: WAITING
            LOCK_DATA: 8
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:1367:281473433260976
ENGINE_TRANSACTION_ID: 2015555
            THREAD_ID: 48
             EVENT_ID: 418
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281473433260976
            LOCK_TYPE: TABLE
            LOCK_MODE: IX                                            -- 表级别排它锁 
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:4:281473433257984
ENGINE_TRANSACTION_ID: 2015555
            THREAD_ID: 48
             EVENT_ID: 418
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X                                    -- 记录级别排他锁
          LOCK_STATUS: GRANTED
            LOCK_DATA: 8
5 rows in set (0.01 sec)

插入意向锁（`Insert Intention Locks`）

事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了gap锁（next-key锁也包含gap锁），如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，这个锁就是Insert Intention Locks，官方的类型名称为：LOCK_INSERT_INTENTION，称之为插入意向锁。插入意向锁时一种Gap锁，不是意向锁，在insert操作时产生。

-- 上例中的 插入操作
INSERT INTO student(id,name,class) VALUES(4,'张三','一班'); -- 阻塞  

-- 锁状态
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504989184:299:4:4:281473433265224
ENGINE_TRANSACTION_ID: 2015556
            THREAD_ID: 49
             EVENT_ID: 285
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433265224
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION        -- 记录级别，排它锁，间隙锁，插入意向锁
          LOCK_STATUS: WAITING
            LOCK_DATA: 8

插入意向锁是一种特殊的间隙锁–间隙锁可以锁定开区间内的部分记录。

插入意向锁之间互不排斥，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

-- t1 
begin;
select * from student where id <= 8 and id > 3 for update;

-- t2 
INSERT INTO student(id,name,class) VALUES(4,'张三','一班');

-- t3
INSERT INTO student(id,name,class) VALUES(4,'张三','一班');

-- t1 commit 之后，t2退出阻塞状态，t2 commit 之后，t3退出阻塞状态，说明插入意向锁是行锁，会相互冲突。

插入意向锁不是意向锁，意向锁是表锁，插入意向锁是行锁。

页锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一页中可以有多个行记录。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每一个层级的锁数量是有限的，因为锁会占用内存空间，锁空间的大小是有限的，当某个层级的锁数量超过了这个层级的阈值时，就会自动进行锁升级。

从对待锁的态度划分：乐观锁、悲观锁

需要注意的是，乐观锁和悲观锁并不是锁，而是锁的设计思想。

悲观锁（Pessimistic Locking）

悲观锁是一种思想，很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排他性。

悲观锁总是假设最坏的情况，每次拿数据的时候都认为别人会修改，所以每次拿数据的时候都会上锁，这样别人拿这个数据就会阻塞，直到它拿到锁（共享资源每次只给一个线程使用，其他线程阻塞，用完后再把资源转让给其他线程）。比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。

select ... for update是MySQL中的悲观锁。需要注意，这个语句执行过程中所有扫描的行都会被锁上，因此在MySQL中使用悲观锁必须确定使用了索引，而不是全表扫描，否则将会把整个表锁住。

悲观锁大多数情况下依靠数据库的锁机制来实现，以保证程序的并发访问性，这样对数据库性能开销影响很大，特别是长事务而言，这样的开销往往无法承受，这个时候就需要乐观锁。

乐观锁（`Optimistic Locking`）

乐观锁认为对统一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。一般采用版本号控制或者CAS机制。乐观锁适用于多度的应用类型，这样可以提高吞吐量。

乐观锁的版本号机制

在表中设计一个版本字段 version，第一次读的时候记录version的值，然后对数据更新或删除操作时，会通过version作为where条件并且将version+1，如果已经有事务对这条数据进行了修改，修改就不会成功。

乐观锁的时间戳机制

时间戳和版本号一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

注意：如果数据表时读写分离的表，当master表中写入的数据没有及时同步到slave表中时，会造成更新一致失效的问题。此时需要强制读取master表中的数据。（将select语句放到事务中即可，这时候查询的就是master主库）

两种锁的适用场景

乐观锁：适合读操作多的场景，相对来说写的操作比较少。优点在于程序实现，不存在死锁问题、
悲观锁：适合写操作多的场景，因为写的操作具有排他性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读-写和写-写的冲突

按照加锁的方式划分：显式锁、隐式锁

隐式锁

一个事务执行INSERT操作时，如果即将插入的间隙已经被其他事务加了gap锁，那么本次insert操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下insert操作是不加锁的。

如果一个事务对一条记录（此时并没有在内存生产与该记录关联的锁结构），然后另一个事务：

立即使用 select ... for share语句读取这条记录，也就是获取这条记录的S锁，或者使用 select ... for update语句读取这条记录，也就是获取这条记录的X锁。

如果允许，则可能产生脏读问题。
立即修改这条记录，也就是获取这条记录的X锁。

如果允许，则可能产生脏写问题。

这个时候，就需要使用到事务id。聚簇索引和二级索引中的记录分开来看：

情景一：使用聚簇索引，记录中有一个trx_id隐藏列，记录着最后改动该记录的事务id，在当前事务新插入一条聚簇索引记录后，该记录的trx_id隐藏列代表的就是当前事务的事务id，如果其他事务此时想对该记录添加S锁或者X锁时，首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务，如果是，则帮助当前事务创建一个X锁（也就是为当前事务创建一个锁结构，is_waiting属性是false，代表这个事务是活跃的），然后自己进入等待状态（也就是为自己也创建一个锁结构，is_waiting属性是true）
情景二：对于二级索引来说，本身并没有trx_id隐藏列，但是在二级索引页面的Page Header部分有一个PAGE_MAX_TRX_ID属性，该属性代表对该页面做改动的最大的事务id，如果PAGE_MAX_TRX_ID属性值小于当前最小的活跃事务id，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表知道它对应的聚簇索引记录，然后再重复情景一的做法。

即：一个事务对新插入的记录可以不显式的加锁（生成一个锁结构），但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会帮助当前事务生成一个锁结构，然后自己再生产一个锁结构后进入等待状态，隐式锁时一种延迟加锁的机制，从而来减少加锁的数量。

隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时，隐式锁转化为显式锁。

-- t1 
-- 开启事务
begin;
-- 有一个 写入 操作
update student set name='张三11' where id = 1;

-- t3 获取锁信息
SELECT * FROM `performance_schema`.data_lock_waits\G
Empty set (0.00 sec)

-- t2
-- 开启事务
begin;
-- 使用 S锁 
select * from student for share; -- 阻塞 

-- t3 获取锁信息，看到隐式锁
SELECT * FROM `performance_schema`.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 281473504989184:299:4:12:281473433264192
REQUESTING_ENGINE_TRANSACTION_ID: 562948481699840
            REQUESTING_THREAD_ID: 49
             REQUESTING_EVENT_ID: 296
REQUESTING_OBJECT_INSTANCE_BEGIN: 281473433264192
         BLOCKING_ENGINE_LOCK_ID: 281473504988376:299:4:12:281473433257984
  BLOCKING_ENGINE_TRANSACTION_ID: 2015566
              BLOCKING_THREAD_ID: 48
               BLOCKING_EVENT_ID: 431
  BLOCKING_OBJECT_INSTANCE_BEGIN: 281473433257984
1 row in set (0.01 sec)

隐式锁逻辑如下：

InnoDB的每条记录中都有一个隐藏的trx_id字段，存在于聚簇索引的B+Tree中
操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务（未提交或回滚）。如果是活动的事务，首先将隐式锁转换为显式锁（就是为该事务添加一个锁）
检查是否有锁冲突，如果有冲突，创建锁，设置为waiting状态。如果没有冲突，不加锁到5；
等待加锁成功，被唤醒，或者超时
写数据，并将自己的trx_id写入trx_id字段

显式锁

1
2
3

select ... lock in share mode;
select ... for share;
select ... for update

全局锁

全局锁就是对整个数据库实例加锁。当需要让整个数据库处于只读状态，可以使用这个命令，增删改语句、DML和更新类事务会阻塞，只能读取。使用场景例如全库逻辑备份

1	FLush tables with read lock;

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，并且双方都不会释放自己的锁，从而导致恶性循环。

产生死锁的必要条件

两个或者两个以上事务
每个事务都已经持有锁并且申请新的锁
锁资源同时只能被同一个事务持有或者不兼容
事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于：两个（或以上）的Session加锁的顺序不一致。

死锁的解决方案

方式1：等待，直到超时（innodb_lock_wait_timeout=50s），超时之后，自动回滚，另外一个事务继续运行。这个方式比较被动，而且影响本事务。
方式2：使用死锁检测进行死锁处理，通过wait-for graph算法来主动进行死锁检测，每当锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

这是一种较为 主动的死锁检测机制，要求数据库保存 锁的信息链表和 事务等待链表两部分信息。

基于这两个信息，可以绘制 wait-for graph 等待图

死锁检测的原理是构建一个以事物为顶点、锁为边的有向图，判断有向图是否存在换，存在即有死锁。

一旦检测到回路、有死锁，这时候InnoDB存储引擎会选择回滚 undo 量最小的事务，让其他事务继续执行。（innodb_deadlock_detect=on表示开启这个逻辑，默认开启）

缺点：每个的被阻塞的线程都要判断自己是否进入死锁，这个操作的时间复杂度是o(n)，如果100个并发线程同时更新同一行，则要检测100 * 100 = 1万次。

解决方法：

关闭死锁检测，但是可能导致大量的超时，影响业务。
控制并发访问的数量，比如中间件中实现对于相同行的更新，在进入引擎之前排队，这样InnoDB内部就不会有大量的死锁检测工作。

进一步思路：可以考虑通过将一行改成逻辑上的多行来减少锁冲突。比如一次性获取100个数，每100更新次更新数据库1次。

避免死锁

合理设计索引，使SQL尽可能通过索引定位更少的行，减少锁竞争。
调整业务逻辑SQL执行顺序，避免update、delete长时间持有锁的SQL在事务前面
避免大失误，尽量将大事务拆成多个小事务来处理，通过减少事务的时间来降低死锁概率
高并发系统中，不要显式加锁，特使是在事务里显式加锁。
降低隔离级别。如果业务允许，将隔离级别从RR调整为RC，可以避免很多因为gap锁造成的死锁。

锁的内存结构

对于锁结构中对应的记录数不是越多越好，也不是越少越好。符合下边这些条件的记录会放到一个锁结构中

在同一个事务中进行加锁操作
被加锁的记录在同一个页面中
加锁的类型是一样的
等待状态是一样的

InnoDB存储引擎中的锁结构如下：

1. 锁所在的事务信息：
不管表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个事务的信息。

次锁所在的事务信息，在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比方说事务id等。
1. 索引信息：
对于行锁来说，需要记录一下加锁的记录时属于哪个索引的，这个也是一个指针
1. 表锁、行锁信息
表锁结构和行锁结构在这个位置内容不同：
- 表锁：
  
  记录对哪个表加的锁，还有一些其他信息
- 行锁：
  
  记录三个重要信息：
  - Space ID：记录所在表空间
  - Page Number：记录所在页号
  - n_bits：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含了很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits属性代表使用了多少比特位。
1. type_mode
  
  32位的数，被分成了lock_mode、lock_type和rec_lock_type三个部分
  - 锁的模式：例如共享意向锁、独占意向锁、共享锁、独占锁、自增锁
  - 锁的类型：表级锁还是行级锁，
  - 行锁的具体类型：例如是间隙锁、临建锁或者记录锁，第9位放置is_waiting
1. 其他信息：各种哈希表和链表
1. 一堆比特位

锁监控

获取锁信息：SHOW STATUS LIKE '%innodb_row_lock%';

获取使用过的锁：SELECT * FROM performance_schema.data_locks;

获取当前等待的锁：SELECT * FROM performance_schema.data_lock_waits;

-- 获取锁信息
SHOW STATUS LIKE '%innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |    -- 当前正在等待锁的数量
| Innodb_row_lock_time          | 466658 |    -- 系统启动到现在锁等待的总时长 **
| Innodb_row_lock_time_avg      | 16091  |    -- 平均锁定时长 ***
| Innodb_row_lock_time_max      | 50114  |    -- 锁等待最多时长
| Innodb_row_lock_waits         | 29     |    -- 锁等待的总次数 ***
+-------------------------------+--------+
5 rows in set (0.01 sec)

-- 获取锁信息
SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 2015570                                    -- 事务id
                 trx_state: LOCK WAIT                                -- 锁状态等待 
               trx_started: 2022-11-24 14:56:09
     trx_requested_lock_id: 281473504989184:299:4:12:281473433264192
          trx_wait_started: 2022-11-24 14:56:09
                trx_weight: 2
       trx_mysql_thread_id: 9
                 trx_query: select * from student for update        -- 等待的SQL语句 
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1                         -- 锁定行数 
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: 1
*************************** 2. row ***************************
                    trx_id: 2015569
                 trx_state: RUNNING
               trx_started: 2022-11-24 14:56:01
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 6
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
2 rows in set (0.00 sec)

-- MySQL 5.7 之前，查看事务的锁情况，但只能看到阻塞事务的锁，如果事务没有被阻塞，就看不到 ，8.0更换 
SELECT * FROM performance_schema.INNODB_LOCK;
-- MySQL 8.0 替代的表  
SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504989184:1367:281473433267104
ENGINE_TRANSACTION_ID: 2015570
            THREAD_ID: 49
             EVENT_ID: 301
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281473433267104
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504989184:299:4:12:281473433264536
ENGINE_TRANSACTION_ID: 2015570
            THREAD_ID: 49
             EVENT_ID: 302
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433264536
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 1
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:1367:281473433260976
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281473433260976
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:1:281473433257984
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:3:281473433257984
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:4:281473433257984
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 8
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:5:281473433257984
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15
*************************** 8. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:6:281473433257984
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20
*************************** 9. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281473504988376:299:4:12:281473433257984
ENGINE_TRANSACTION_ID: 2015569
            THREAD_ID: 48
             EVENT_ID: 435
        OBJECT_SCHEMA: dbtest2
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 281473433257984
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
9 rows in set (0.00 sec)

SELECT * FROM performance_schema.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 281473504989184:299:4:12:281473433264880
REQUESTING_ENGINE_TRANSACTION_ID: 2015570
            REQUESTING_THREAD_ID: 49
             REQUESTING_EVENT_ID: 303
REQUESTING_OBJECT_INSTANCE_BEGIN: 281473433264880
         BLOCKING_ENGINE_LOCK_ID: 281473504988376:299:4:12:281473433257984
  BLOCKING_ENGINE_TRANSACTION_ID: 2015569
              BLOCKING_THREAD_ID: 48
               BLOCKING_EVENT_ID: 435
  BLOCKING_OBJECT_INSTANCE_BEGIN: 281473433257984
1 row in set (0.00 sec)

MVCC 多版本并发控制

为了解决脏读、不可重复读、幻读的问题，除了加锁，还可以通过MVCC的方案，而且并发性比加锁更好。

MVCC时通过数据行的多个版本管理来实现数据库的并发控制。使得InnoDB（MySQL中只有InnoDB引擎支持）的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写 冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，这个读就是快照读。当前读实际上是一种加锁的控制，是悲观锁的实现，MVCC的本质是基于采用乐观锁思想的一种方式。

快照读

不加锁的简单的 SELECT 都属于快照读

1	SELECT * FROM s1 WHERE ...

MVCC在很多情况下，避免加锁操作，降低了开销。

快照读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

当前读

当前读取的是记录的最新版本（最新版本，而不是历史版本的数据），读取时还需要保证其他并发事务不能修改当前记录，会对读取的记录加锁。加锁的 SELECT ，或者对数据行增删改都会进行当前读。比如

-- 共享锁 
SELECT * FROM student LOCK IN SHARE MODE;
SELECT * FROM student FOR SHARE;
-- 排他锁
SELECT * FROM student FOR UPDATE;
-- 排他锁
INSERT INTO student VALUES() ...
-- 排他锁
DELETE FROM student WHERE ...
-- 排他锁
UPDATE student SET ...

隔离级别和隐藏字段、Undo Log版本链

SQL中的隔离级别和解决的问题

MySQL中默认隔离级别是可重复读，可以解决脏读和不可重复读的问题。而解决幻读需要通过串行化的方式，但是串行化会大幅度降低并发性。

MVCC可以不采用锁机制，而是通过乐观锁的方式来解决不可重复读和幻读问题。

对于使用InnoDB引擎的表，聚簇索引记录中包含两个必要的隐藏列。

trx_id：每次一个事务对某条聚簇索引记录进行更改时，都会把该事务的事务id赋值给trx_id
roll_point：每次对某条聚簇索引记录进行改动时，就会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，指向修改前的信息，用于回滚。

例如插入id为1的事务id为8

此时事务10、20分别对这条记录UPDATE，如果多个事务同时修改，此时会通过X锁控制并发修改的问题。例如事务10先操作，则事务20更新时会处于阻塞状态。

此时undo日志操作链：

ReadView

MVCC的实现依赖于：隐藏字段、undo log链，ReadView

MVCC机制中，多个事务对同一个行记录进行更新，会产生多个历史快照，这些历史快照保存在Undo log里（多版本基于这个方式实现）。如果一个事务要查询这个行记录，需要读取的具体版本的记录，就需要用到ReadView。

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照。InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID（活跃指的就是，启动了但还没提交。）

设计思路

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。

使用 READ COMMITED和REPEATABLE READ隔离级别的事务，都必须保证度到了已经提交了的事务修改过的记录，加入另一个事务修改了但是尚未提交，只不能读取最新版本的记录的，核心问题就是需要判断版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

ReadView中有4个比较重要的部分：

creator_trx_id：创建这个ReadView的事务id，（只有在对表中的记录做改动时，INSERT、DELETE、UPDATE这些语句时，才会为事务分配事务id，只读的事务中的事务id都默认为0）
trx_ids：表示生成ReadView时当前系统中活跃的读写事务的事务id列表
up_limit_id：活跃的事务中最小的事务id
low_limit_id：表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id是系统最大事务id值（需要注意是系统最大事务id，要区别于正在活跃的事务id）

注意：low_limit_id并不是trx_ids中的最大值，事务id时递增分配的，比如，现在又id为1,2,3这三个事务，id为3的事务提交了。一个新的读事务在生成ReadView时，trx_ids就是1,2，此时low_limit_id的值就是4.

ReadView的规则

有了ReadView，这样在访问某条记录时，只需要按照下面的步骤判断记录的某个版本是否可见。

如果被访问版本的trx_id属性值与ReadView中的**creator_trx_id值相同，表明当前事务在访问它自己修改过的记录，所以该版本可以**被当前事务访问
如果被访问版本的trx_id属性值小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下**trx_id属性值是不是在trx_ids列表中**
- 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问
- 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问（一般使用读已提交的隔离级别会发生）

MVCC整体操作流程

首先获取事务ID
获取ReadView
查询得到的数据，然后与ReadView中的事务版本号进行对比；
如果不符合ReadView规则，就需要从 undo log中获取历史快照
最后返回符合规则的数据

如果某个版本的数据对当前事务不可见，就顺着版本链找到下一个版本的数据，继续按照上面的步骤判断可见性，直到最后一个版本。如果最后一个版本也不可见，代表这条记录对事务完全不可见，查询结果就不包含该记录。（解决幻读）

InnoDB中，MVCC是通过 undo log + Read View 进行数据读取，Undo Log 保存了历史快照，Read View规则判断当前版本的数据是否可见

ReadView的生成节点

在隔离级别为READ COMMIT时，一个事务中的每一次SELECT查询都会重新获取一次ReadView。

此时，同样的查询语句都会重新获取一次ReadView，这时如果ReadView不同，就可能产生不可重复读或者幻读的情况。
在隔离级别为REPEATABLE READ时，一个事务只在第一次SELECT的时候会获取一次ReadView，后面所有的SELECT都会复用这个ReadView

使用MVCC

MVCC只在READ COMMIT和REPEATABLE READ这两个隔离级别下工作。

例如在student表中只有一条由事务id为8的事务插入的一条记录

SELECT * FROM student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
+----+--------+--------+
1 row in set (0.00 sec)

`READ COMMITTED`隔离级别下

READ COMMITTED级别下，每次读取数据前都生成一个ReadView。

现在有两个事务，id分别是10、20，在执行：

-- t10 
begin;
update student set name='李四' where id = 1;
update student set name='王五' where id = 1;

-- t20 
begin;
insert into test2 values(3); -- 由于只有第一次修改记录时才分配一个独立的事务id，因此要随便修改一下其他的表

此时，student中id为1的记录得到的版本链表如下

一个使用READ COMMITTED隔离级别的事务开始操作

set transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

show variables like '%transaction_isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.02 sec)

begin;

select * from student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
+----+--------+--------+
1 row in set (0.00 sec)

得到的结果是张三，执行 select 时，生成一个ReadView：

步骤1：

trx_ids列表的内容 [10,20]（操作的事务没有INSERT操作，事务id为0），
up_limit_id为10
low_limit_id为21
creator_trx_id为0

步骤2：从版本链中可见的记录，最新版本是王五，trx_id是10，在trx_ids列表内，符合不可见要求，根据roll_pointer跳到下一个版本

步骤3：同步骤2，不可见，跳到下一个版本

步骤4：name列是 张三，trx_id为8，小于ReadView中的up_limit_id值10，符合要求，最后返回的版本就是这条列的记录。

此时提交事务10，然后将事务20的记录改一下

-- t10 
commit；

-- t20 
update student set name='钱七' where id = 1;
update student set name='宋八' where id = 1;

此时，记录为1的版本链就长这样：

此时，再在READ COMMITTED隔离级别的事务中继续查找这个id为1的记录

select * from student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 王五   | 一班   |
+----+--------+--------+
1 row in set (0.00 sec)

得到的结果是王五，这个select的执行过程如下：

步骤1，重新生成一个单独的ReadView：

trx_ids列表的内容 [20]（事务10已经提交），
up_limit_id为20
low_limit_id为21
creator_trx_id为0

步骤2：从版本链中可见的记录，最新版本是宋八，trx_id是20，在trx_ids列表内，符合不可见要求，根据roll_pointer跳到下一个版本

步骤3：同步骤2，不可见，跳到下一个版本

步骤4：name列是 王五，trx_id为10，小于ReadView中的up_limit_id值20，符合要求，最后返回的版本就是这条列的记录。

`REPEATABLE READ`隔离级别下

使用REPEATABLE READ隔离级别，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成。

现在有两个事务，id分别是10、20，在执行：

-- t10 
begin;
update student set name='李四' where id = 1;
update student set name='王五' where id = 1;

-- t20 
begin;
insert into test2 values(3); -- 由于只有第一次修改记录时才分配一个独立的事务id，因此要随便修改一下其他的表

此时，student中id为1的记录得到的版本链表如下

一个使用REPEATABLE READ隔离级别的事务开始操作

set transaction_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

-- 开启事务
begin;
select * from student where id=1;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   | 
+----+--------+--------+
1 row in set (0.01 sec)

这个过程与使用READ COMMITTED隔离级别一致，得到的结果是张三，执行 select 时，生成一个ReadView：

步骤1：

trx_ids列表的内容 [10,20]（操作的事务没有INSERT操作，事务id为0），
up_limit_id为10
low_limit_id为21
creator_trx_id为0

步骤2：从版本链中可见的记录，最新版本是王五，trx_id是10，在trx_ids列表内，符合不可见要求，根据roll_pointer跳到下一个版本

步骤3：同步骤2，不可见，跳到下一个版本

步骤4：name列是 张三，trx_id为8，小于ReadView中的up_limit_id值10，符合要求，最后返回的版本就是这条列的记录。

此时提交事务10，然后将事务20的记录改一下

-- t10 
commit；

-- t20 
update student set name='钱七' where id = 1;
update student set name='宋八' where id = 1;

此时，记录为1的版本链就长这样：

此时，再在READ COMMITTED隔离级别的事务中继续查找这个id为1的记录

select * from student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
+----+--------+--------+
1 row in set (0.00 sec)

得到的结果是张三，这个select的执行过程跟第一次执行过程一样，只是不会再次生成一个ReadView。

MVCC解决幻读

解决幻读的前提是在REPEATABLE READ隔离级别。

假设student表只有一条数据，数据内容中，主键id=1，隐藏的trx_id=10，undo log如下：

现在有事务A和事务B并发执行，事务A的事务id为20，事务B的事务id为30.

步骤1：事务A开始第一次查询数据，SQL语句如下

1	select * from student where id >=1;

在开始查询之前，MySQL会为事务A生成一个ReadView，内容如下：

creator_trx_id = 20
trx_ids = [20,30]
up_limit_id = 20
low_limit_id = 31

此时符合 id >= 1条件的记录只有1条，可以查出id=1的记录。

步骤2：在事务B中，往表student中插入两条数据，并提交事务

1 2	insert into student(id,name) values(2,'李四'); insert into student(id,name) values(3,'王五');

此时，student表中有三条数据，对应的undo log如下

新的数据trx_id为30，在trx_ids之间，不可读取到。即使事务B提交，也在trx_ids内，也不可读取到，因此解决幻读的问题。

总结

MVCC在READ COMMITTED和REPEATABLE READ隔离级别这两种隔离级别的事务，在执行读快照操作时访问版本链，这样使不同事务的读-写、写-读操作并发执行，从而提高性能。

MVCC在READ COMMITTED和REPEATABLE READ隔离级别生成ReadView的时机不同：

READ COMMITTED每次读取时都生成ReadView
REPEATABLE READ在第一次读取时生成ReadView，以后复用

delete语句或者更新主键的update语句，并不会立即把对应的记录从页面删除，而是执行一个delete mark操作，相当于打上一个删除标志，主要就是为MVCC服务

通过MVCC解决的问题：

读写之间阻塞的问题：让读写互不阻塞，提升并发处理能力
降低死锁的概率：通过乐观锁的方式
解决快照读的问题

事务概述

事务的ACID特性

事务的状态

使用事务

隐式提交数据的情况

事务操作

使用事务

InnoDB和MyISAM在事务下的区别

保存点 SAVEPOINT

事务的隔离级别

数据并发问题

SQL中的四种隔离级别

MySQL支持的四种隔离级别

MySQL事务日志之redo日志

redo日志

redo log的好处、特点

redo的组成

整体流程

redo log 刷盘策略

写入redo log buffer过程

redo log file

日志文件组

MySQL事务日志之undo日志

undo日志

undo log 的作用

undo 的存储结构

回滚段与事务

回滚段中的数据分类

undo的类型

undo log的生命周期

详细生成过程

undo log 回滚

undo log删除

日志小结

锁

并发事务访问相同记录

读-读情况

写-写情况

读-写或写-读情况

并发问题解决方案

从数据操作的类型划分

锁定读

写操作

从数据操作的粒度划分：表级锁、页级锁、行锁

表锁

表级别的S锁、X锁

意向锁（intention lock）

自增锁（AUTO-INC锁）

元数据锁

行锁

记录锁（Record Locks）

间隙锁（Gap Locks）

临建锁（Next-Key Locks）

插入意向锁（Insert Intention Locks）

页锁

从对待锁的态度划分：乐观锁、悲观锁

悲观锁（Pessimistic Locking）

乐观锁（Optimistic Locking）

乐观锁的版本号机制

乐观锁的时间戳机制

两种锁的适用场景

按照加锁的方式划分：显式锁、隐式锁

隐式锁

显式锁

全局锁

死锁

产生死锁的必要条件

死锁的解决方案

避免死锁

锁的内存结构

锁监控

MVCC 多版本并发控制

快照读与当前读

快照读

当前读

隔离级别和隐藏字段、Undo Log版本链

ReadView

设计思路

ReadView的规则

MVCC整体操作流程

MySQL事务日志之`redo`日志

`redo`日志

`redo log`的好处、特点

`redo`的组成

`redo log` 刷盘策略

写入`redo log buffer`过程

`redo log file`

MySQL事务日志之`undo`日志

`undo`日志

`undo log` 的作用

`undo` 的存储结构

`undo`的类型

`undo log`的生命周期

`undo log` 回滚

`undo log`删除

表级别的`S锁`、`X锁`

意向锁（`intention lock`）

插入意向锁（`Insert Intention Locks`）

乐观锁（`Optimistic Locking`）

`READ COMMITTED`隔离级别下

`REPEATABLE READ`隔离级别下