表级锁
表锁
先来说说表锁。
如果我们想对学生表（t_student）加表锁，可以使用下面的命令：
//表级别的共享锁，也就是读锁；
lock tables t_student read;
//表级别的独占锁，也就是写锁；
lock tables t_stuent write;
需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。
也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。
要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：
unlock tables
另外，当会话退出后，也会释放所有表锁。
为什么需要意向锁？
在一个多用户并发环境中，对数据库表进行修改（插入、更新、删除等）前，理论上需要检查没有其他事务在同一个表的任何其他记录上持有不兼容的锁。在没有意向锁的情况下，需要扫描整个表来查找这些锁，这是非常低效的。
如何工作？ 意向共享锁（Intention Shared Locks）: 在打算给某一行或多行加共享锁之前，首先给整个表加一个意向共享锁。
意向独占锁（Intention Exclusive Locks）: 在打算给某一行或多行加独占锁之前，首先给整个表加一个意向独占锁。
这样做的优点是，当一个事务尝试在整个表上获取一个锁时（比如，一个独占表锁），它可以快速地通过检查表级的意向锁来确定是否有冲突，而不需要扫描整个表的所有行锁,意向锁的目的是为了快速判断表里是否有记录被加锁。
意向锁的目的是为了快速判断表里是否有记录被加锁。
//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;
//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;
实际应用： 例如，在 SQL 语句中： SELECT … LOCK IN SHARE MODE 将首先添加一个意向共享锁，然后为选定的记录添加共享锁。 SELECT … FOR UPDATE 将首先添加一个意向独占锁，然后为选定的记录添加独占锁。
兼容性： 意向锁之间是兼容的（可以有多个意向共享锁或意向独占锁）。 行锁和对应的意向锁是兼容的（比如，持有意向共享锁的事务可以在表中的特定行上加共享锁）。
AUTO-INC 锁和轻量级锁是 MySQL 中用于管理具有 AUTO_INCREMENT 属性的列的两种不同机制。这两种机制的主要目的是确保自增字段的值是唯一和连续的，但它们在实现和性能方面有所不同。
AUTO-INC 锁
当使用标准的 AUTO-INC 锁时，整个插入操作（从获取自增 ID 到插入完成）都是在锁的保护下完成的。也就是说，在一个事务获取了 AUTO-INC 锁并进行插入操作之后，其他所有尝试插入记录到同一个表的事务都会被阻塞，直到第一个事务完成插入操作并释放锁。这确保了自增 ID 的连续性，但会降低并发插入操作的性能。
轻量级锁 轻量级锁则更加灵活。这种锁只在分配自增 ID 的瞬间锁定，一旦 ID 分配完成，锁就会立即释放，即使插入操作还没有完成。这允许其他事务并发地进行插入操作，大大提高了性能。然而，这种方法有一个潜在的风险，就是在使用 binlog_format=STATEMENT 的主从复制环境中可能会导致数据不一致。
innodb_autoinc_lock_mode innodb_autoinc_lock_mode 参数允许在这两种机制之间进行选择：
innodb_autoinc_lock_mode = 0: 使用 AUTO-INC 锁 innodb_autoinc_lock_mode = 1: 混合模式 innodb_autoinc_lock_mode = 2: 使用轻量级锁 混合模式 (innodb_autoinc_lock_mode = 1) 是两者的折中，针对不同类型的插入操作使用不同类型的锁。
数据不一致问题 当使用轻量级锁并设置 binlog_format=STATEMENT 时，由于主从库不是完全按照相同的顺序执行插入操作，可能会导致数据不一致。解决这个问题的方法是设置 binlog_format=ROW，这样在复制过程中，从库会使用与主库相同的自增 ID。
简而言之，AUTO-INC 锁和轻量级锁都有各自的优点和缺点。AUTO-INC 锁提供了更严格的 ID 生成顺序，但降低了性能。轻量级锁
在使用轻量级锁和设置binlog_format=STATEMENT的情况下，主从不一致的原因主要是因为两个事务在主库上可能是并发执行的，但在从库上是顺序执行的。
主库上 假设有两个并发事务A和B：
事务A请求自增ID，得到ID=1。 事务B请求自增ID，得到ID=2。 事务B完成插入，提交。 事务A完成插入，提交。 在这种情况下，即使事务A先开始，事务B可能先结束，因为轻量级锁允许并发插入。
从库上 由于从库是顺序执行binlog的，因此它会按照A和B在主库上的提交顺序来执行。这意味着从库上事务A和B是顺序而不是并发执行的。
如果binlog_format=STATEMENT，从库只知道要插入一个新的记录，但不知道这个记录的ID应该是多少。因此，从库会为每个插入操作分配一个新的自增ID，这可能与主库上分配的ID不同，从而导致数据不一致。
解决方案 使用binlog_format=ROW，这样binlog中会包含每个记录的实际数据，包括自增ID。这确保了从库上插入的记录将使用与主库上相同的自增ID，从而避免了数据不一致的问题。
元数据锁（Metadata Lock，MDL）
什么是 MDL 锁，以及它在数据库中起到什么作用？ MDL（元数据锁）是一种用于同步对数据库表结构和其他数据库对象的访问的锁机制。其主要目的是确保在进行数据修改或结构变更时数据的一致性。
在哪些具体的数据库操作中，MDL 锁会自动触发？ MDL 锁主要在进行 CRUD（创建、读取、更新、删除）操作以及对表结构进行变更（比如 ALTER TABLE）时自动触发。
为什么 MDL 锁是需要的？它解决了什么样的并发问题？ MDL 锁需要用来解决并发问题。当多个线程同时读取和修改表结构时，没有适当的锁机制可能会导致数据不一致或者操作冲突。
当一个长事务持有 MDL 读锁时，会出现什么情况？写锁的优先级是否高于读锁？ 当一个长事务持有 MDL 读锁，其他试图进行结构性变更的操作（需要写锁）会被阻塞。写锁在获取优先级上通常高于读锁。当一个读锁和一个写锁同时请求时，通常会优先给写锁。但如果读锁已经存在，并且是由一个长事务持有的，那么即使写锁的优先级更高，也不能强制释放当前活跃的读锁。
如果一个长事务一直持有 MDL 读锁，造成其他请求被阻塞，你会如何处理？ 发现长事务持有 MDL 读锁导致阻塞时，一种解决方案是等待该事务完成或者手动结束这个长事务。
MDL 锁与行锁、表锁有何不同？它们是否可以同时存在？ 行锁和表锁主要用于控制对数据记录的访问，而 MDL 锁是用于同步表结构的访问。它们可以同时存在。MDL（元数据锁）主要用于保护数据库的元数据，即用于描述表结构、列类型等信息的数据。当你对一张表进行 CRUD（创建、读取、更新、删除）操作时，你实际上是在访问表的数据内容，这通常涉及到行锁或表锁。
然而，当你尝试更改表的结构，比如通过 ALTER TABLE 语句添加或删除列，这时你正在更改表的元数据。MDL 锁在这里确保在表结构变更期间，其他线程不能对该表进行不一致或可能导致错误的操作。
简而言之，行锁和表锁用于同步对表内数据的访问，而 MDL 锁用于同步对表结构（元数据）的访问。这两种锁机制可以同时存在，以确保数据库操作的完整性和一致性。
MDL 锁是在什么时候获得的，又是在什么时候释放的？ MDL 锁通常在事务开始时获得，并在事务提交或回滚时释放。
MDL 锁有哪些优点和局限性？ MDL 锁的优点包括高并发性能和防止不一致的数据修改。局限性主要在于长事务可能导致阻塞。
行级锁
基础概念：InnoDB引擎支持行级锁，主要有记录锁（Record Lock）、间隙锁（Gap Lock）和Next-Key锁。记录锁用于锁定单条记录，间隙锁用于锁定一个范围但不包括实际记录，而Next-Key锁是两者的组合。
Record Lock
Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：
当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）; 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。
mysql > begin;
mysql > select * from t_test where id = 1 for update;
就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。
Gap Lock
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。
Next-Key Lock
Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。
所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。
next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。
比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。
虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的
总结
隔离级别：在可重复读（REPEATABLE READ）隔离级别下，使用间隙锁和Next-Key锁可以有效防止幻读。在读已提交（READ COMMITTED）级别下，通常只使用记录锁。
操作语句：SELECT … FOR UPDATE触发X锁，而SELECT … LOCK IN SHARE MODE触发S锁。
锁升级与死锁：锁升级通常发生在并发高或资源争抢的情况下，可能导致死锁。避免方法包括按照固定的顺序获取锁或使用超时机制。
性能影响：行级锁允许更高的并发，但也可能导致锁竞争，特别是在高并发环境下。
特殊类型的锁：插入意向锁是一种特殊的间隙锁，用于标明一个事务希望在某个范围内插入记录，通常用于避免插入时的冲突。
实际案例：在一个电商系统中，为了避免库存超卖，我们使用了SELECT … FOR UPDATE来锁定某个商品的库存记录。
与其他锁的关系：表锁和MDL锁更为粗粒度，用于整张表。行级锁与它们可以共存，但粒度和用途有区别。
数据库特定知识：InnoDB支持行级锁，而MyISAM只支持表级锁，这使得InnoDB更适用于需要高并发读写的场景。
InnoDB 引擎是怎么加行级锁？
普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。
如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读。
//对读取的记录加共享锁(S型锁)
select ... lock in share mode;
//对读取的记录加独占锁(X型锁)
select ... for update;
上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。
除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。
//对操作的记录加独占锁(X型锁)
update table .... where id = 1;
//对操作的记录加独占锁(X型锁)
delete from table where id = 1;
共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。
InnoDB 加锁规则
唯一索引等值查询 存在的记录：由于唯一索引确保每个值都是唯一的，因此当一个存在的记录被查询时，只需锁定该特定记录。这里的Next-Key锁会退化为记录锁。
不存在的记录：在这种情况下，锁会锁定查询值应该插入的位置，即”间隙”。这样可以确保在当前事务提交前，其他事务不能在该间隙内插入新的记录。
非唯一索引等值查询 存在的记录：因为非唯一索引允许多个记录具有相同的索引值，所以查询可能会返回多个结果。因此，数据库需要扫描所有具有相同索引值的记录，并给它们加上Next-Key锁。同时，在扫描过程中找到的第一个不符合条件的记录会获得一个退化为间隙锁的Next-Key锁。
不存在的记录：如果没有符合查询条件的记录，仅需要锁定不符合条件的第一个记录的“间隙”。
范围查询的不同 唯一索引：在范围查询中，Next-Key锁也可能会退化为间隙锁或记录锁，这取决于查询的特定范围和结果集。
非唯一索引：由于范围查询可能涉及多个具有相同索引值的记录，因此Next-Key锁不会退化为间隙锁或记录锁。
唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。 非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。
日志
undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。 redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复； binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；
Undo Log 触发时机
每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如： 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了； 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了； 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了
Undo Log 日志
作用 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：
通过 trx_id 可以知道该记录是被哪个事务修改的； 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；
主从复制怎么实现？
MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。
这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。
MySQL 主从复制过程
MySQL 集群的主从复制过程梳理成 3 个阶段：
写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。 回放 Binlog：回放 binlog，并更新存储引擎中的数据。
具体详细过程如下： MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
MYSQL 中的二阶段提交
从图中可看出，事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：
prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；
两阶段提交有什么问题？
两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两个方面的影响：
磁盘 I/O 次数高：对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。 锁竞争激烈：两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。
具体更新一条记录 UPDATE t_user SET name = ‘xiaolin’ WHERE id = 1; 的流程?
执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录： 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新； 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样： 如果一样的话就不进行后续更新流程； 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作； 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。 InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。 至此，一条记录更新完了。 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）： prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘； commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）； 至此，一条更新语句执行完成。