Mysql高级篇（中）——锁机制

一、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制

二、分类

1、读锁

在MySQL中，读锁（Read Lock），也叫共享锁（Shared Lock，S锁），是一种用于控制多个事务同时读取数据的一种机制。理解读锁的工作原理对优化数据库性能和确保数据一致性非常重要。以下结合一个具体的案例来讲解MySQL中的读锁及其应用场景。

1. 事务A 和 事务B 获取读锁：	事务A 执行以下SQL查询：
   
START TRANSACTION;
SELECT * FROM orders WHERE order_id = 123 FOR SHARE; --（for share 是MySQL8.0 新增语法）
---------------------------------------------------------
此时，事务A获取了订单ID为123的读锁（共享锁）。此后，事务B也可以执行相同的查询，并同样获取读锁：

START TRANSACTION;
SELECT * FROM orders WHERE order_id = 123 FOR SHARE;

由于读锁是共享的，A和B都可以并发读取订单信息，而不会互相阻塞。
---------------------------------------------------------
2. 事务C试图写入：
这时，后台服务C尝试更新订单状态，执行以下SQL语句：

START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE order_id = 123;

由于事务A和B都持有订单ID为123的读锁，写操作会被阻塞，直到A和B完成事务并释放锁。
---------------------------------------------------------
3. 释放读锁：
当事务A和B完成各自的查询操作后，分别提交事务，释放读锁：

COMMIT;

当所有读锁都被释放后，事务C的写操作才能继续进行，完成订单状态的更新。

2、写锁

MySQL中的写锁，也叫排他锁（Exclusive Lock, X锁），是一种用于控制多个事务对数据进行写入的机制。写锁与读锁不同：它不仅阻止其他事务对相同数据进行写入操作，还阻止其他事务读取数据。在高并发的场景中，合理使用写锁能够确保数据的一致性和正确性。

START TRANSACTION;

-- 获取账户A的余额
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;

-- 更新账户A的余额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 获取账户B的余额
SELECT balance FROM accounts WHERE account_id = 2 FOR UPDATE;

-- 更新账户B的余额
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

行级写锁与表级写锁

★、FOR SHARE / FOR UPDATE

• FOR SHARE 语句在 MySQL 8.0 中首次引入。它是 LOCK IN SHARE MODE 的一种替代语法，用于给查询数据加共享锁（S锁）
  
  
• MySQL 8.0 增加了 NOWAIT 和 SKIP LOCKED 选项，用于在使用 FOR UPDATE 时实现更细粒度的锁定控制。这些选项允许用户在无法立即获取锁时选择不等待或跳过被锁定的行，从而避免死锁或长时间的锁等待。

（1）NOWAIT

（2）SKIP LOCKED

（3）NOWAIT 和 SKIP LOCKED 的比较

★、 脏写

脏写（Dirty Write） 是指一个事务写入数据后，另一个事务在前一个事务未提交的情况下修改了该数据，导致修改了未提交的数据。这种情况会破坏数据库的原子性和一致性，可能导致严重的数据一致性问题。脏读（Dirty Read） 是读取到另一个事务未提交的数据。

脏写的后果比脏读更严重，因为它不仅读取了不稳定的数据，还在该基础上进行了修改，可能会导致数据污染和错误的业务逻辑。

1.事务A：用户A尝试将账户余额从1000减少到900。

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

账户余额暂时更新为900，但事务未提交
--------------------------------------------------------
2.事务B：用户B尝试将相同账户的余额从900进一步减少到800，但此时事务A尚未提交，这意味着用户B修改了未提交的余额数据。

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

账户余额现在是800，但这是基于事务A未提交的900
--------------------------------------------------------
3.如果事务A和事务B都提交，账户的最终余额变成800，而不是正确的900，因为用户B基于事务A未提交的数据进行了修改。这就是典型的脏写。

1.事务A：用户A尝试修改账户余额，MySQL会自动加上排他锁（X 锁），防止其他事务修改该行数据。

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

加了X锁，其他事务无法对该行进行修改
此时账户余额是900，但事务未提交

--------------------------------------------------------
2.事务B：在事务A未提交的情况下，用户B尝试修改相同账户余额，但由于事务A对该行数据加了排他锁，事务B会被阻塞，无法修改该数据。

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

此时事务B被阻塞，等待事务A提交或回滚
--------------------------------------------------------
3.事务A：提交修改，释放锁。

COMMIT;
事务A提交，余额更新为900，锁释放
--------------------------------------------------------
4.事务B：事务A提交后，事务B得以继续执行，它将在此基础上再次修改余额。
事务B现在可以执行，并基于余额900减去100，最终余额为800

COMMIT;

3、表级锁之 S锁 / X锁

（1）总结

MySQL 的表级锁有两种模式：（以 MyISAM 表进行操作的演示）

• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）
  

（2）InnoDB 存储引擎（行级锁）

InnoDB存储引擎中默认使用行级锁（Row-level Locking）而不是表级锁（Table-level Locking）的主要原因有以下几个方面：

（3）MyISAM 存储引擎（表级锁）

MyISAM存储引擎不支持行级锁，只能使用表级锁（Table-level Locking）。因此，在使用MyISAM存储引擎时，默认是使用表级锁。

（4）InnoDB 和 MyISAM 锁机制的对比

（5）MyISAM 存储引擎为什么会自动加锁

MyISAM 会自动加锁的原因主要是为了确保数据的一致性和操作的完整性，并简化锁机制的实现。以下是 MyISAM 自动加锁的具体原因和原理：

4、表级锁之 意向锁

（1）概述

MySQL中的意向锁（Intention Locks）是一种行级锁和表级锁共存的表锁，它由InnoDB存储引擎自动管理，不需要开发者显式加锁或管理，用于在事务操作表中的某些记录之前，对其他事务发出“意图”信号，以便对这部分记录施加更精细的行级锁。 这种机制有助于提高锁的效率和并发性能。

（2）种类

• 意向共享锁（IS，Intention Shared Lock）：表示事务准备对表中的某些记录加共享锁（Slock），而不是整个表。
• 意向他排锁（IX，Intention Exclusive Lock）：表示事务准备对表中的某些记录加排他锁（X锁），而不是整个表。

（3）作用

• 意向锁的主要作用是协调表级锁和行级锁之间的兼容性
  
  
  • 当一个事务要在某张表上锁定某个行时，它先声明自己的意图（通过意向锁），以告知其他事务“我要在这张表的某行上施加行级锁”。这样，当另一个事务要给整个表加锁时，可以快速判断当前表是否已经被锁住。
    
    通过意向锁，系统不需要扫描整个表的每一行来检查是否有锁，而是直接通过表上的意向锁来确定是否可以进行表级锁定。

（4）使用

在MySQL中，意向锁是由InnoDB存储引擎自动管理的，开发者不需要直接操作意向锁。 你只需通过操作行级锁或表级锁来间接使用意向锁，MySQL会自动为你处理好意向锁的加锁与释放。换句话说，开发者不需要显式使用或管理意向锁，MySQL会根据你在事务中的操作决定何时加意向锁。

下面解释如何通过常见的锁操作来触发意向锁：

（5）使用场景与示例

-- 事务A
START TRANSACTION;
SELECT * FROM employees WHERE id = 1 FOR UPDATE; -- 加行级排他锁（X锁）

-- 事务B
START TRANSACTION;
LOCK TABLES employees READ; -- 尝试给整个表加共享锁（S锁）


在这个例子中，事务A首先在某一行上加了排他锁（X锁），它会在表上放置一个意向排他锁（IX）。
此时，事务B试图对整个表加共享锁（S锁），但是由于意向锁的存在，它必须等待事务A完成操作并释放锁。

（6）意向锁和表级（共享/排他）锁的互斥性规则

锁的互斥性可以通过一张锁兼容性表来解释，表中的“是”表示可以兼容，即多个事务可以并发执行这些操作，而“否”表示冲突，不能并发执行。

（7）意向锁和行级（共享/排他）锁

意向锁（IS/IX锁）和 行级锁（共享锁S/排他锁X）之间不会直接相互排斥。

意向锁用于在表级别上声明事务的锁定意愿，表示事务拟对表中的某些行加共享锁或排他锁，而行级锁是对具体行进行加锁。因此，意向锁和行级锁的主要目的是不同的，它们是协同工作的，而不是相互冲突的。

意向锁的互斥性主要体现在与表级锁的互斥性

5、（了解）表级锁之 自增锁

MySQL中的自增锁（Auto-Increment Lock）是与自增字段（通常是AUTO_INCREMENT属性）相关的一个锁机制，它保证多个事务并发插入记录时，分配给自增字段的值是唯一且递增的；

MySQL为每个带有AUTO_INCREMENT属性的列维护一个计数器，这个计数器保证每次插入时分配的值是递增的，并且在表的多个会话/事务之间不会发生冲突。

自增锁的实现方式和锁在策略不同的存储引擎中有所不同，以InnoDB为例，其具体行为可以分为以下几个方面：

场景描述：

假设有一张表 orders，其中 id 列是自增列。
两个会话（Session A 和 Session B）将同时尝试往 orders 表中插入数据，我们将看到自增锁如何确保自增值的唯一性和递增性。
创建表 orders：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100)
);
--------------------------------------------------------
步骤1：Session A 开始插入数据
Session A 中插入一条新记录，并且不提交事务（保持事务未提交状态，以模拟长时间持有自增锁的情况）：

START TRANSACTION;

INSERT INTO orders (product_name) VALUES ('Product A');
-- 假设插入的自增 id 为 1

此时，Session A 获得了 orders 表的自增锁，并分配了 id 值为 1 给新插入的记录。由于事务未提交，自增锁还没有释放。
--------------------------------------------------------
步骤2：Session B 尝试插入数据
Session B 中也插入一条新记录，尝试获取自增值：

START TRANSACTION;

INSERT INTO orders (product_name) VALUES ('Product B');
-- 由于 Session A 还持有自增锁，Session B 将被阻塞

此时，Session B 会被阻塞，因为 Session A 还未提交事务，持有自增锁。Session B 必须等待 Session A 完成事务并释放自增锁，才能继续执行。
--------------------------------------------------------
步骤3：Session A 提交事务：Session A 完成事务并提交：

COMMIT;

当 Session A 提交事务后，自增锁被释放，Session B 将会立即继续执行插入操作，并为 product_name = 'Product B' 的记录分配自增值 id = 2。

-- Session B 插入成功，自增 id 为 2
COMMIT;

最终结果是两条记录插入到了 orders 表中，id 值分别为 1 和 2，尽管它们是由两个会话几乎同时插入的，但自增锁确保了它们的 AUTO_INCREMENT 值是唯一且递增的。

6、（了解）表级锁之 元数据锁

MySQL 中的元数据锁（Metadata Lock，简称 MDL）是一种用于保护表结构和元数据一致性的锁机制。元数据锁在 MySQL 5.5 中引入，目的是确保在执行某些 DDL（数据定义语言）操作时，表的元数据不会被同时修改或使用。它广泛应用于 DDL 语句（如 ALTER TABLE、DROP TABLE）、DML 语句（如 SELECT、INSERT、UPDATE）和事务处理，确保数据一致性和操作的原子性。


以下是对 MySQL 中元数据锁的详细介绍：

场景描述：

我们有两个会话（Session A 和 Session B），其中一个会话正在对表进行查询操作，另一个会话尝试修改表结构。
这种情况下就会触发元数据锁的冲突，因为 MySQL 需要保证表结构的一致性。

假设我们有一个名为 test_table 的表，结构如下：

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);
------------------------------------------------------
步骤1：Session A 执行一个长时间的查询操作
在 Session A 中执行一个 SELECT 查询，这个查询会获取元数据读锁：

START TRANSACTION;

SELECT * FROM test_table WHERE SLEEP(10);
-- 该查询会执行 10 秒钟

此时，test_table 上有一个元数据读锁（MDL read lock），确保在查询期间，表的结构不会发生变化。
------------------------------------------------------
步骤2：Session B 尝试修改表结构
在 Session B 中执行一个 ALTER TABLE 操作，试图修改表结构。这种操作需要获取元数据写锁（MDL write lock）：

ALTER TABLE test_table ADD COLUMN age INT;

此时，Session B 会被阻塞，因为 ALTER TABLE 需要获取一个元数据写锁，但由于 Session A 正在持有读锁，MySQL 不能立即获得写锁。
------------------------------------------------------
步骤3：Session A 提交事务
在 Session A 中提交事务，释放元数据读锁：

COMMIT;

一旦 Session A 释放了元数据锁，Session B 将会继续执行 ALTER TABLE 操作，并成功添加新列。

7、InnoDB 行锁之 记录锁

在MySQL 8.0中，行锁（Record Lock）是指锁住单行数据的锁，用于确保在并发环境下数据的一致性。
行锁的一种具体形式是记录锁（Record Lock），它锁定的是索引中的某一特定记录，而不仅仅是表中的一行数据。 这在基于索引的操作中尤为重要。下面详细介绍记录锁的特点和使用场景：

假设有如下 users 表：

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);


现在有三条数据：

id | name  | age
-----------------
1  | Alice | 20
2  | Bob   | 25
3  | Carol | 30


执行以下语句：

SELECT * FROM users WHERE id = 2 FOR UPDATE;

（1）概述

（2）使用场景

BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 执行其他操作
COMMIT;

此时，id = 1 的那一行被记录锁锁定，其他事务试图更新或删除该行时将被阻塞。

BEGIN;
UPDATE users SET name = 'Alice' WHERE id = 1;
-- 执行其他操作
COMMIT;

这时，id = 1 的那一行会被锁定，其他事务必须等待当前事务提交才能继续操作该行。

（3）特性

• 基于索引： 记录锁是基于索引的。如果在查询或更新时没有使用索引，InnoDB 会回退到锁定整个表中的所有行，即出现表锁的情况。这就是为什么合理地为表创建索引可以提升性能。
  
  
• 范围锁定： 记录锁不仅会锁定查询条件匹配的记录，还会锁定与该记录相邻的索引位置（间隙锁 Gap Lock）。这种机制防止“幻读”（Phantom Read）现象，即其他事务在当前事务提交前插入新记录。

（4）示例

假设有一个用户表 users，它有一个主键 id：	

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

-----------------------------------------------
事务1：

BEGIN;
SELECT * FROM users WHERE id = 2 FOR UPDATE;
-----------------------------------------------
事务2：

BEGIN;
UPDATE users SET age = 30 WHERE id = 2; -- 事务2将被阻塞

-----------------------------------------------

在事务1没有提交之前，事务2中的 UPDATE 操作会被阻塞，因为 id = 2 的那一行已经被事务1的记录锁锁定。

通过这种锁机制，MySQL 8.0 提供了高效的并发控制，保证了数据的一致性，特别是在高并发的事务处理中。

8、InnoDB 行锁之 间隙锁

在MySQL 8.0 中，行锁（Record Lock）的一种变体是间隙锁（Gap Lock），它用于锁定索引之间的空隙，而不仅仅是锁定现有的记录。
间隙锁的主要作用是防止其他事务在某个索引范围内插入新记录，尤其是在可重复读（REPEATABLE READ）隔离级别下，帮助解决“幻读”（Phantom Read）的问题。

（1）概述

（2）使用场景

间隙锁通常在使用可重复读（REPEATABLE READ）隔离级别时会被自动启用，特别是在执行范围查询时。常见的操作如 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句会使用到间隙锁。

假设我们有一张包含用户信息的表 users，并且表中已经有以下几条记录：

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (3, 'Bob', 30), (5, 'Carol', 35);

------------------------------------------------------
当前表中有 id 为 1、3 和 5 的记录。
现在如果我们执行以下语句：

BEGIN;
SELECT * FROM users WHERE id > 1 AND id < 5 FOR UPDATE;

此查询会锁定 id 范围在 1 和 5 之间的所有索引区域。由于表中没有 id = 2 或 id = 4 的记录，因此 MySQL 会在这个范围内加上间隙锁。结果如下：
id = 1 到 id = 3 之间的间隙被锁定。
id = 3 到 id = 5 之间的间隙也被锁定。
------------------------------------------------------
其他事务
假设事务 2 试图插入一条 id = 2 的新记录：

BEGIN;
INSERT INTO users (id, name, age) VALUES (2, 'David', 28);

由于事务 1 已经在 id = 1 到 id = 3 的范围上加了间隙锁，事务 2 会被阻塞，直到事务 1 提交或回滚。

（3）特性

（4）示例

我们可以通过一个完整的示例来更好地理解间隙锁的工作机制。

假设我们有以下 users 表，并且已经插入了一些用户数据：

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (3, 'Bob', 30), (5, 'Carol', 35);
--------------------------------------------------------
场景 1：使用 SELECT ... FOR UPDATE 锁定区间
现在事务 1 执行以下查询：

BEGIN;
SELECT * FROM users WHERE id > 1 AND id < 5 FOR UPDATE;


这个查询会锁定 id = 1 到 id = 5 之间的索引区间（间隙）。具体来说：
id = 1 到 id = 3 之间的间隙被锁住，阻止插入 id = 2 的记录。
id = 3 到 id = 5 之间的间隙被锁住，阻止插入 id = 4 的记录。
此时，事务 1 没有锁定整张表，但它锁定了这两个索引之间的“空隙”，其他事务不能在这个区间内插入任何记录。
--------------------------------------------------------
场景 2：其他事务尝试插入
假设事务 2 试图在同一区间插入一条新记录：

BEGIN;
INSERT INTO users (id, name, age) VALUES (2, 'David', 28);

由于事务 1 已经锁定了 id = 1 和 id = 3 之间的索引区间，事务 2 将被阻塞，直到事务 1 提交或回滚。
--------------------------------------------------------

（5）实际应用

总结：
间隙锁是 MySQL 中 InnoDB 引擎用于 解决幻读 问题的重要机制。它通过锁定索引之间的空隙，确保事务在并发环境下的一致性。间隙锁不会阻止现有记录的读取或更新，但会防止其他事务在锁定范围内插入新数据。

9、InnoDB 行锁之 临键锁

在MySQL 8.0 中，临键锁（Next-Key Lock） 是一种组合了记录锁和间隙锁的锁定机制，用于防止“幻读”（Phantom Read）。

临键锁不仅会锁定索引中的某一条记录，还会锁定该记录前后的索引区间。

这种锁在可重复读（REPEATABLE READ）隔离级别下，主要用于范围查询，以确保查询结果在事务执行期间保持一致。

（1）概述

（2）使用场景

临键锁的典型使用场景是范围查询，尤其是在隔离级别为可重复读的事务中。当使用范围条件进行查询时，MySQL 会使用临键锁锁定符合条件的记录及其附近的空隙。

假设我们有一张用户表 users，并且表中已经有以下几条记录：
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (3, 'Bob', 30), (5, 'Carol', 35);
------------------------------------------------------------
当前表中有 id 为 1、3 和 5 的记录。
现在如果我们执行以下查询：

BEGIN;
SELECT * FROM users WHERE id > 1 FOR UPDATE;


此时，MySQL 将会应用临键锁，具体效果如下：
锁定 id = 3 的那一行（记录锁）。
同时锁定 id = 1 和 id = 3 之间的空隙，以及 id = 3 和 id = 5 之间的空隙（间隙锁）。
临键锁的效果是，在当前事务未提交之前，其他事务无法在这些空隙中插入新的 id 记录（如 id = 2 或 id = 4），也无法修改 id = 3 这一行。

（3）特性

（4）工作原理

我们以更详细的例子来说明临键锁的作用：

场景 1：使用范围查询的临键锁
假设我们有如下 users 表，并且已经插入了一些用户数据：

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (3, 'Bob', 30), (5, 'Carol', 35);

----------------------------------------------
事务 1 运行以下查询：

BEGIN;
SELECT * FROM users WHERE id > 1 FOR UPDATE;

在这个查询中，MySQL 将使用临键锁锁定范围 id > 1，具体表现为：

锁定 id = 3 的索引条目（记录锁）。
同时锁定 id = 1 和 id = 3 之间的空隙，防止插入 id = 2。
锁定 id = 3 和 id = 5 之间的空隙，防止插入 id = 4。
这意味着在事务 1 提交之前，其他事务无法在 id = 1 和 id = 5 之间插入新记录。

----------------------------------------------
场景 2：其他事务试图插入数据
假设事务 2 试图在同一区间内插入一条新记录：

BEGIN;
INSERT INTO users (id, name, age) VALUES (2, 'David', 28);

由于事务 1 已经通过临键锁锁定了 id = 1 和 id = 3 之间的空隙，事务 2 将被阻塞，直到事务 1 提交或回滚。

----------------------------------------------

场景 3：查询时的锁定范围
假设事务 1 执行以下查询：

BEGIN;
SELECT * FROM users WHERE id >= 3 FOR UPDATE;

这个查询会对 id >= 3 的所有记录和间隙加锁：

锁定 id = 3 和 id = 5 之间的空隙。
锁定 id = 5 的记录。
这意味着在事务 1 提交之前，其他事务无法在 id >= 3 的范围内插入任何新记录。

（5）实际应用

总结：
临键锁是 MySQL 8.0 中 InnoDB 存储引擎为防止“幻读”而设计的一种锁，它通过锁定现有记录和这些记录之间的空隙来确保查询结果的一致性。临键锁不仅限制其他事务对现有记录的修改，还防止在指定范围内插入新记录，是范围查询中保持一致性的重要机制。

10、InnoDB 行锁之 插入意向锁

在 MySQL 8.0 中，插入意向锁（Insert Intention Lock）是一种特殊类型的间隙锁，主要用于下面的插入操作场景。它是 InnoDB 存储引擎中的一种轻量级锁，允许多种事务在不同的索引间隙内插入新记录，而不会相互阻碍。

（1）概述

（2）使用场景

插入意向锁在大量插入时非常有用，尤其是在索引间存在多个间隙的情况下。 它允许多个事务同时在不同的间隙插入数据，而不必等待其他事务完成插入。

例子：
假设我们有一个用户表users，其中包含以下记录：
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (5, 'Bob', 30);

当前表有id = 1和id = 5的记录，中间有空白id = 2到id = 4。

（3）工作方式

插入意向锁在插入操作之前申请，当一个事务准备插入新记录时，不会立即锁定整个间隙，而是首先为插入位置设置插入意向锁。这种锁的目的并不是阻止其他事务，而是通知其他事项“我打算在这个空隙中插入一条记录”。

场景1：多个事务同时插入不同位置
想象事务 1 和事务 2 同时执行以下插入操作：

事务1：
BEGIN;
INSERT INTO users (id, name, age) VALUES (2, 'David', 28);

事务2：
BEGIN;
INSERT INTO users (id, name, age) VALUES (4, 'Eve', 27);


此时，事务1会在id = 1相同id = 5的索引空隙设置插入意向锁，且插入位置为id = 2。
事务2会在相同的索引空隙设置插入意向锁，但插入位置为id = 4。

因为这两个事务允许插入的记录位置不同，插入意向锁意不会相互冲突，两个事务可以执行并发插入操作。插入向锁在相同范围内的不同间隙进行并发插入。

场景2：多个尝试插入相同位置的事情
如果尝试在同一个位置插入两个记录，那么就会发生冲突。例如：

事务1：
BEGIN;
INSERT INTO users (id, name, age) VALUES (3, 'David', 28);


事务2：
BEGIN;
INSERT INTO users (id, name, age) VALUES (3, 'Eve', 27);



此时，事务 1 和事务 2 都尝试在id = 3该位置插入数据，这会导致插入意向锁的冲突。事务 2 将被阻塞，直到事务 1 完成提交并。

（4）插入意向锁与间隙锁的区别

（5）示例说明插入意向锁的工作

我们可以通过更详细的场景来看插入意向锁的行为：

场景 1：插入意向锁允许并发插入

假设表 users 中有以下数据：

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (5, 'Bob', 30);

现在，两个事务分别执行插入操作：

事务 1：
BEGIN;
INSERT INTO users (id, name, age) VALUES (2, 'David', 28);

事务 2：
BEGIN;
INSERT INTO users (id, name, age) VALUES (4, 'Eve', 27);

事务 1 尝试在 id = 1 和 id = 5 之间插入 id = 2，并为该插入操作加上插入意向锁。同样，事务 2 尝试在相同区间内插入 id = 4，并为该操作设置插入意向锁。由于两个事务的插入位置不同，插入意向锁不会冲突，两个插入操作可以并发进行。

场景 2：插入意向锁冲突

假设现在有两个事务同时尝试插入相同的 id 值：
事务 1：
BEGIN;
INSERT INTO users (id, name, age) VALUES (3, 'David', 28);


事务 2：
BEGIN;
INSERT INTO users (id, name, age) VALUES (3, 'Eve', 27);


在这种情况下，事务 1 和事务 2 都试图在 id = 3 的位置插入记录。此时，插入意向锁会发生冲突，事务 2 将会被阻塞，直到事务 1 提交或回滚。

（6）插入意向锁的实际作用

总结：

插入意向锁是 MySQL 8.0 中一种轻量级的锁，用于在并发插入时提高性能。 它允许多个事务在不同的空隙位置进行插入，而不需要等待其他事务完成。插入意向锁不会像间隙锁那样阻止所有插入操作，它只会在两个事务试图插入相同位置时才会发生冲突。在高并发插入场景下，插入意向锁有助于提高数据库的并发性能。

11、（了解）页锁

在MySQL中，页锁（Page Lock）是介于行锁（Row Lock）和表锁（Table Lock）之间的一种锁定机制，它锁定的是整个数据页。数据页是InnoDB存储引擎中数据的基本存储单位，通常为16KB的大小。

12、乐观锁

（1）概述

乐观锁（Optimistic Lock）是一种用于并发控制的机制，假设在大多数情况下多个事务不会发生冲突。因此，它不会像悲观锁那样主动加锁，而是在事务提交时检测是否存在冲突，并在检测到冲突时采取措施（如重试或失败处理）。

（2）工作原理

乐观锁的 核心思想 是：每个事务在修改数据之前，都会先记录当前数据的 版本信息（通常是版本号或时间戳）。在事务提交更新时，再检查该版本信息是否发生变化：

1. 如果版本信息没有变化，说明没有其他事务修改过这条数据，当前事务可以安全地进行更新。
   
   
2. 如果版本信息发生了变化，说明数据已被其他事务修改，当前事务的更新操作将会失败，通常需要回滚或重试。

这种机制确保了数据在并发修改时的一致性，并避免了传统悲观锁带来的锁等待问题。

（3）实现方式

乐观锁通常通过 版本号 或 时间戳 来实现。

（4）适用场景

• 高并发读取、多写少的应用，如电商、社交平台等，事务冲突的概率较低。
• 需要优化并发性能的场景，避免频繁加锁导致系统吞吐量下降。
• 冲突较少的场景： 乐观锁通常用于事务冲突较少的应用场景，如用户信息修改、商品详情查看等，冲突少可以提高更新成功率。

（5）优缺点

（6）MySQL 8.0 乐观锁使用场景示例

MySQL 8.0本身并不提供直接的乐观锁语法，而是通过自定义的业务逻辑实现。
典型的实现方式是使用版本号（version）或时间戳字段。
当对数据进行更新时，系统检查版本号是否与读取时一致，如果一致，更新成功；如果不一致，表示数据已被修改，更新失败。

假设我们有一个库存管理系统，使用乐观锁来避免并发下的超卖问题。我们为每件商品的库存增加一个version字段，记录数据的版本，每次更新时版本号递增。通过这个机制，多个用户可以并发查询商品库存，但在更新时，只有版本号一致的事务才允许成功更新。

1. 创建库存表，添加 version 字段

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    version INT
);


在这张 inventory 表中，version 字段用于实现乐观锁。每次更新数据时，版本号都会加1，用于检测并发冲突。
-------------------------------------
插入一条初始记录，表示当前有10台笔记本电脑库存，版本号为1：

INSERT INTO inventory (product_id, product_name, quantity, version) 
VALUES (1, 'Laptop', 10, 1);

2. 使用乐观锁实现并发安全的库存管理
当用户购买商品时，我们通过乐观锁机制来确保并发环境下的安全库存扣减。

1. 查询当前的库存数量和版本号。
2. 检查库存是否足够。
3. 尝试更新库存和版本号。更新时会检查版本号是否与查询时一致，
如果一致，表示没有其他事务修改过该记录，更新成功；
如果不一致，表示发生了并发修改，更新失败。

以下是使用乐观锁的库存更新示例：

START TRANSACTION;

-- 1. 查询商品的库存和版本号
SELECT quantity, version FROM inventory WHERE product_id = 1;

-- 2. 检查库存是否足够
IF (SELECT quantity FROM inventory WHERE product_id = 1) > 0 THEN
    -- 3. 更新库存并检查版本号是否匹配
    UPDATE inventory 
    SET quantity = quantity - 1, version = version + 1 
    WHERE product_id = 1 AND version = 1;
    
    -- 4. 检查更新是否成功
    IF (ROW_COUNT() = 0) THEN
        -- 版本号不匹配，更新失败，返回冲突错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update conflict, please retry';
    END IF;
ELSE
    -- 库存不足，抛出库存不足错误
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock';
END IF;

-- 5. 提交事务
COMMIT;

（7）并发情况下的乐观锁

13、悲观锁

（1）概述

MySQL中的悲观锁（Pessimistic Lock）是一种通过锁定数据来防止并发修改的机制，旨在假设在多个事务并发访问同一数据时，会频繁发生冲突。悲观锁的主要策略是： 一旦一个事务获取了数据的锁，就会阻止其他事务对该数据进行修改，直到锁被释放。 它是一种防止脏读、幻读和不可重复读的手段。

（2）锁定方式

• 悲观锁通常会通过显式地 锁定数据 来实现。事务在读取数据时就会立即加锁，确保在该事务完成之前，其他事务无法修改该数据。
• 悲观锁会导致相应的 行 或 表 处于锁定状态，其他事务只能等待锁释放后才能进行操作。

（3）实现方式

悲观锁通常通过数据库的原生锁机制实现。在MySQL8.0，可以通过SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 来实现悲观锁。

• SELECT ... FOR UPDATE：在读取数据时，对读取到的行加 排他锁，使其他事务不能对这些行进行更新或删除。
• SELECT ... FOR SHARE：在读取数据时，对读取的行加 共享锁，其他事务可以读取但不能修改这些行。

（4）应用场景

• 高并发写入：当系统对同一数据的并发写入非常频繁时，悲观锁可以确保事务按顺序修改数据，避免冲突。
• 防止冲突：在某些业务逻辑中，多个事务同时可能操作同一条记录，而每个事务对该记录的修改不能同时进行。例如，银行转账系统中的余额扣减操作，可以通过悲观锁来确保数据一致性，避免在并发环境中多个事务对同一账户余额进行操作而导致数据错误。

（5）优缺点

（6）MySQL 8.0 悲观锁使用场景示例

假设你在构建一个简单的库存管理系统，用于记录商品库存。每次用户购买商品时，系统需要减少相应的库存，同时避免多个用户并发购买时导致的超卖问题。
悲观锁可以确保每个用户在更新库存时，其他用户的请求会被阻塞，直到当前事务完成。

创建 inventory 表
首先，我们创建一个库存表 inventory，记录商品ID、商品名称和库存数量。

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT
);


假设有一条商品记录，表示当前有10台笔记本电脑的库存：
INSERT INTO inventory (product_id, product_name, quantity) VALUES (1, 'Laptop', 10);

START TRANSACTION;

-- 查询库存并锁定此行，防止其他事务修改此行
SELECT quantity FROM inventory WHERE product_id = 1 FOR UPDATE;

-- 检查库存是否足够
IF (SELECT quantity FROM inventory WHERE product_id = 1) > 0 THEN
    -- 更新库存，将数量减少
    UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
ELSE
    -- 如果库存不足，返回错误
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock';
END IF;

-- 提交事务，释放锁
COMMIT;

（7）并发情况下的悲观锁

（8）悲观锁的细节与注意事项

总结
在MySQL 8.0中，悲观锁主要用于防止并发事务对同一数据的冲突。通过 SELECT ... FOR UPDATE 实现的悲观锁，可以有效防止多事务同时修改数据时造成的不一致问题。虽然悲观锁能确保数据安全性，但由于会阻塞其他事务，可能引发性能问题甚至死锁，因此在高并发场景下应慎重使用。

★、乐观锁和悲观锁的区别与联系

区别总结

对比项	悲观锁	乐观锁
假设前提	假设冲突频繁，因此每次操作都需加锁	假设冲突很少，因此不在读取时加锁
加锁时机	读取数据时就加锁，防止其他事务修改数据	提交更新时检查版本号是否冲突
性能影响	频繁加锁，可能导致锁等待，影响并发性能	不加锁，并发性能较好
适用场景	写多读少，冲突频繁的场景，如库存扣减、资金交易	读多写少，冲突较少的场景，如用户资料修改
死锁风险	容易产生死锁，需要数据库检测并处理	`不存在死锁问题
冲突处理	加锁防止冲突，冲突少见	允许冲突发生，发生冲突时回滚或重试
实现难度	简单，依赖数据库的锁机制	较复杂，需要在应用层实现版本号或时间戳的校验

14、隐式锁 和 显式锁

（1）简述

• 在MySQL中，锁机制用于保证数据库的并发控制，防止多个事务同时修改相同的数据而导致数据不一致或冲突。

• 锁可以分为隐式锁（也叫自动锁）和显式锁（手动锁）这两种锁的 主要区别 在于，隐式锁是由MySQL自动管理的，而显式锁则需要用户显式地通过SQL语句来加锁或解锁。

• 区分隐式锁和显式锁，主要可以从 加锁方式、用户是否需要干预、使用场景 等角度进行分析。

（2）示例

我们通过一个简单的例子来对比 隐式锁 和 显式锁 的使用

场景：
假设你管理一个电商系统中的订单表 orders，表结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    status VARCHAR(20)
);

每个订单有一个唯一的 order_id，并且包含订单的状态 status（例如“已下单”、“已支付”、“已发货”等）。
----------------------------------------------------
1. 隐式锁的例子：
假设你要更新某个订单的状态，从“已下单”变为“已支付”。此时，你可以直接使用 UPDATE 语句，MySQL 会自动加锁。

START TRANSACTION;

UPDATE orders
SET status = '已支付'
WHERE order_id = 123;

COMMIT;

在这个例子中，隐式锁由MySQL自动完成：

当执行 UPDATE 语句时，MySQL会对 order_id = 123 这一行数据加 排他锁（X锁）。
其他事务在你 COMMIT 之前，无法对这个订单进行修改或读取（如果读取需要加锁）。
当 COMMIT 后，排他锁会自动释放。
这个过程完全是由MySQL自动管理的，用户不需要手动控制。


----------------------------------------------------
2. 显式锁的例子：
现在假设你不只是想更新订单状态，还想手动控制这个订单的锁定时间，确保在你检查某个条件后再决定是否更新它。此时你可以使用显式锁，通过 SELECT ... FOR UPDATE 锁定数据行。

START TRANSACTION;

-- 显式锁定 order_id 为 123 的订单行，防止其他事务修改它
SELECT * FROM orders
WHERE order_id = 123
FOR UPDATE;

-- 执行一些检查或其他逻辑
-- 例如：检查是否满足某个条件
IF (/* 某些条件 */) THEN
    -- 如果条件满足，更新订单状态
    UPDATE orders
    SET status = '已支付'
    WHERE order_id = 123;
END IF;

COMMIT;



在这个例子中，显式锁的使用体现为：

SELECT ... FOR UPDATE 语句显式地对 order_id = 123 这一行数据加上排他锁，确保其他事务无法修改它，直到当前事务结束。
你可以先查询并根据业务逻辑做判断，在决定是否进行更新操作后再修改数据。
这种方式给了你更细粒度的控制，你可以选择在某个时机解锁或更新数据，避免事务冲突。

总结对比：

• 隐式锁： MySQL在UPDATE操作时自动加锁，用户不需要手动管理锁定和解锁过程，适合大多数简单场景。
  
• 显式锁： 通过 SELECT ... FOR UPDATE 明确地对数据加锁，用户可以手动控制锁的时机，适合复杂的业务逻辑或者需要长时间保持锁定的场景。
  
  
  在简单事务操作中，隐式锁已经足够，但在更复杂的事务中，显式锁能提供更多的灵活性和控制力。

15、全局锁和死锁

在MySQL中，全局锁和死锁都是数据库并发控制中需要重点关注的问题，但它们的性质和影响不同。我们可以结合一些常见场景来理解它们的区别。

（1）全局锁

全局锁是对整个数据库的一种锁定机制。MySQL 提供的一个典型全局锁命令是 FLUSH TABLES WITH READ LOCK (FTWRL)。当使用这个命令时，MySQL会阻止对数据库进行写操作，确保在备份等场景下数据的一致性。所有的表都会进入只读状态，直到释放该锁。

示例场景：

假设你在生产环境中需要对某个大数据库进行备份，
你可以使用全局锁来确保备份过程中的一致性：

FLUSH TABLES WITH READ LOCK;

此时，所有对数据库的写操作都会被阻塞，直到备份完成并手动执行 UNLOCK TABLES 命令解除锁定。虽然这确保了数据的一致性，但也会导致系统的写操作完全暂停，因此它通常不适合在高并发的生产环境中长时间使用。

（2）死锁

① 概述

死锁是指多个事务互相等待对方持有的资源，导致这些事务无法继续执行。MySQL使用InnoDB存储引擎时，会经常遇到死锁问题。

② 示例场景

考虑两个事务 T1 和 T2，它们在操作同一组表时会导致死锁：
T1 锁定了表 A 中的一行，准备更新表 B 中的数据。
T2 锁定了表 B 中的一行，准备更新表 A 中的数据。
------------------------------------------
-- T1
BEGIN;
UPDATE table_A SET value = 1 WHERE id = 1;

-- T2
BEGIN;
UPDATE table_B SET value = 1 WHERE id = 2;

-- T1 试图更新 table_B
UPDATE table_B SET value = 2 WHERE id = 2;

-- T2 试图更新 table_A
UPDATE table_A SET value = 2 WHERE id = 1;

-------------------------------------
在这种情况下，
T1 等待 T2 释放 table_B 的锁，
T2 也在等待 T1 释放 table_A 的锁，
导致双方互相等待，形成死锁。

MySQL 的应对策略： MySQL 会自动检测死锁，一旦发现，它会选择回滚其中一个事务（通常是持锁时间较短的事务），从而使另一个事务继续执行。被回滚的事务会收到错误信息，提示存在死锁。

③ 死锁的产生条件

死锁的产生需要满足四个必要条件，称为“ 死锁的四个必要条件 ”：这 四个条件同时存在 时，系统便可能进入死锁状态。

• 互斥条件： 资源只能被一个事务占用，其他事务只能等待该资源释放。
  
  
• 保持与等待条件： 一个事务已经持有至少一个资源，但同时又在等待被其他事务占有的资源。
  
  
• 不可剥夺条件： 已获得的资源不能被强制剥夺，必须由持有该资源的事务主动释放。
  
  
• 循环等待条件： 存在一个事务环，事务 A 等待事务 B 持有的资源，而事务 B 又等待事务 C 的资源，以此类推，直到某个事务等待事务 A 的资源，形成一个循环。
  
  

④ 避免死锁的策略

要避免死锁，最常用的策略有以下几种：

1. 避免长事务： 长事务占用资源的时间较长，更容易发生死锁。尽量将事务拆分为多个短事务可以减少死锁的概率。

• 示例： 如果需要对大量数据进行更新，考虑将更新任务拆分成批次，而不是一次性更新所有数据。

2. 获取锁的顺序一致： 如果多个事务需要访问相同的资源，确保它们以相同的顺序获取锁，可以有效避免循环等待。

示例：
假设有两张表 A 和 B，
所有事务都应该先锁表 A，再锁表 B，
而不是随机锁表。
--------------------------------------
-- T1: 锁定表 A 然后锁定表 B
BEGIN;
LOCK TABLES A WRITE, B WRITE;
-- T2: 同样先锁定表 A 再锁定表 B
BEGIN;
LOCK TABLES A WRITE, B WRITE;

3. 合理设置超时机制： 设置 锁等待超时参数（如 InnoDB 的 innodb_lock_wait_timeout）可以避免事务无限期等待锁，超时后自动回滚避免死锁。

示例：
SET innodb_lock_wait_timeout = 10; -- 设置锁等待超时为10秒

4. 使用更高的隔离级别： 如果业务允许，可以选择较低的隔离级别（如 READ COMMITTED），减少锁的持有时间。

⑤ 处理死锁的步骤

即使采取了上述避免措施，死锁仍可能发生。此时，MySQL 一般会自动处理死锁，但我们也需要在应用层进行处理。

1. 自动死锁检测与回滚： MySQL InnoDB 存储引擎默认会检测死锁，一旦检测到死锁，MySQL 会自动回滚其中一个事务，通常是回滚持锁时间较短的事务。被回滚的事务会返回错误代码 1213: Deadlock found when trying to get lock; try restarting transaction。

• 示例： 假设两个事务 T1 和 T2 因锁定表顺序不同导致死锁，MySQL 检测到死锁后，会回滚其中一个事务，并提示如下错误：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2. 应用层重试机制： 为了处理死锁，应用层可以捕获 1213 错误，并实现重试机制。通过捕获死锁错误并重新执行事务，确保业务操作可以继续。

示例代码（伪代码）：

# python 代码
-------------------------
while True:
    try:
        begin_transaction()
        # 执行数据库操作
        commit_transaction()
        break
    except DeadlockError:
        print("检测到死锁，重试事务...")
        rollback_transaction()
        continue
-------------------------
在应用层中，检测到死锁后自动重试事务，可以有效减少死锁对业务的影响。

3. 优化索引和查询： 确保查询使用合适的索引，以减少扫描的行数。扫描的行越少，锁的范围越小，死锁的概率就越低。

• 示例： 在查询中使用索引，避免对没有索引的列进行 UPDATE 或 DELETE 操作，导致全表扫描加锁。

-- 使用索引列来限制更新范围，减少锁定的行数
UPDATE my_table SET value = 1 WHERE indexed_column = 'value';

⑥ 总结

（3）全局锁和死锁总结

全局锁 是一种对整个数据库的锁定，通常用于备份，但会导致系统短暂的“停顿”。

死锁 是事务互相等待资源的结果，MySQL可以自动检测并解决死锁，但它会对并发性能产生影响。



使用这些锁机制时，关键在于理解它们的适用场景与对并发性能的影响，合理规划事务和锁的使用以减少系统瓶颈和死锁的发生。

三、锁的内存结构与监控策略

在MySQL的InnoDB存储引擎中，事务锁的基础信息模块负责管理锁的元数据，以确保数据库在多事务并发时能正确地处理数据锁定、协调事务间的冲突，并维持系统的一致性和数据完整性。 事务锁的基础信息模块包含了一些关键字段和内存结构，用于存储有关锁的信息。以下是该模块的详细分析：

1、InnoDB存储引擎事务锁的基础信息模块

2、锁的监控策略

在MySQL中，锁的监控是保障数据库性能、避免死锁和优化并发事务的关键部分。下面是一些常见的锁监控策略：

1. 使用性能模式 (Performance Schema)

MySQL通过 performance_schema 提供了详细的锁监控数据，以下几个表对锁的监控非常有帮助：

• performance_schema.data_locks：显示当前的锁状态信息，包括持有锁的事务、锁类型和锁等待状态。
• performance_schema.data_lock_waits：显示锁等待信息，可以帮助分析哪些事务在等待锁释放，识别潜在的死锁。
• performance_schema.events_waits_summary_global_by_event_name：汇总系统中所有的等待事件，包括锁等待的事件，帮助识别是否存在锁等待时间过长的情况。

建议： 定期查询这些表，检查长时间的锁等待，评估系统中的锁争用情况。

2. 启用 InnoDB 死锁监控
InnoDB 会自动监控并检测死锁。你可以通过以下命令查看最近的死锁信息：

SHOW ENGINE INNODB STATUS;

在输出中，LATEST DETECTED DEADLOCK 部分会显示死锁相关信息。定期查看这个命令的输出，特别是高并发的系统中，帮助识别和分析死锁发生的原因。

3. 监控 InnoDB 行级锁
InnoDB 使用行级锁，你可以使用以下命令查看行级锁的等待状态：

SHOW PROCESSLIST;

在结果中，State 列为 Waiting for lock 表示该线程正在等待锁，可以分析等待中的SQL语句以及锁定资源的事务，找到瓶颈。

4. 查询事务锁定等待时间
MySQL 提供了 innodb_lock_wait_timeout 参数，控制事务等待锁的最长时间。如果某个事务超过了这个时间，会被自动回滚。通过适当调整这个参数，可以减少长时间的锁等待，特别是在高并发的应用中。

建议： 根据业务需要合理设置 innodb_lock_wait_timeout，避免事务长时间阻塞，造成其他事务的积压。

5. 使用 SHOW ENGINE INNODB STATUS 查看锁冲突
InnoDB 锁的详细信息可以通过 SHOW ENGINE INNODB STATUS 查看。这份报告包含：

• 当前事务的锁状态
• 事务的锁请求等待情况
• 是否发生死锁以及死锁的详细信息
  

建议： 定期检查这个命令的输出，确保锁冲突不频繁，优化锁使用策略。

6. 监控等待锁的查询
在 MySQL 中，某些查询由于锁等待可能变得很慢。通过以下 SQL 语句可以找到当前被锁住或等待锁的查询：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

这两张表分别显示了当前持有的锁和等待的锁信息，帮助识别长时间的锁等待和潜在的性能瓶颈。

7. 查询慢日志 (Slow Query Log)
锁导致的性能问题往往伴随着慢查询。开启 慢查询日志 可以帮助发现由于锁等待而变慢的查询。你可以通过以下方式启用慢查询日志：

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;  -- 设定慢查询的阈值为2秒

建议： 结合慢查询日志和锁等待信息，找出由于锁争用而导致的查询性能问题。

总结：
监控 MySQL 中的锁需要结合 performance_schema、InnoDB 的状态查询以及定期的锁等待信息分析。在监控过程中，重点是关注长时间的锁等待、死锁情况和查询性能下降的锁争用问题，并适当调整锁等待超时参数，优化并发事务。