Table of Contents

Mysql 幻读与加锁
#

并发事务处理带来的问题

0）更新丢失(Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
1）脏读(Dirty Reads)：事务 A 读取了事务 B 更新的数据，然后事务B 进行回滚操作，那么事务 A 读取到的数据就是脏数据。
- 指一个事务读取到了另外事务中未提交的数据。
2）不可重复读(Non-Repeatable Reads)：事务 A 多次读取同一数据，事务 B 在事务 A 读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
- 指一个事务读取到了事务中提交的 update 的数据。
3）幻读(Phantom Reads)：系统管理员 A 将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候新增了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
- 指一个事务读取到了事务中提交的 insert 的数据。

不可重复读和幻读最大的区别，一者是对已存在的行进行操作导致，一者是对不存在的行进行操作导致。

ISO 和 ANIS SQL 提供了4 种事务隔离级别的标准。

read-uncommitted
read-committed
repeatable-read
serializable

为了解决这些问题，InnoDB 存储引擎同样提供了 4 种事务隔离级别。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	对InnoDB否
串行化（serializable）	否	否	否

READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

查看当前数据库的事务隔离级别：

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

脏读
#

将事务隔离级别提升到读已提交（read-committed）即可限制，禁止其他事务访问当前事务未提交的数据，这样就不会出现脏读的情况。

不可重复读
#

将事务隔离级别提升到可重复读（repeatable-read）即可。

该事务隔离级别下，每次开启事务都会新建一个快照，在当前事务中的多次查询都是基于此快照进行的，不会查询到其他事务提交的数据，所以也不会出现不可重复读的问题。

幻读
#

这个就比较复杂了，只有 InnoDB 存储引擎下把事务隔离级别调到可重复读（repeatable-read）才能限制该问题。

幻读与不可重复读的区别：不可重复读是对当前已存在的数据进行更新，导致后续的查询与之前的查询结果不一致，而幻读是新增了满足当前查询条件的行，导致前后查询结果不一致。

解决幻读问题的方法
#

使用可重复读（REPEATABLE READ）隔离级别：
- MySQL 的默认隔离级别是可重复读（REPEATABLE READ），它通过使用间隙锁（Next-Key Locking）来防止幻读。
- 间隙锁不仅锁定现有的行，还锁定行之间的间隙，防止其他事务在这些间隙中插入新行。
使用可序列化（SERIALIZABLE）隔离级别：
- 可序列化（SERIALIZABLE）是最高的隔离级别，它通过强制事务按顺序执行来完全防止幻读。
- 在这个隔离级别下，所有的读操作都会加锁，确保没有其他事务可以插入、更新或删除数据。

假设有一个表 employees，包含以下数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

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

使用可重复读（REPEATABLE READ）隔离级别
#

-- 事务 A
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM employees WHERE age > 20;

-- 事务 B
START TRANSACTION;
INSERT INTO employees (id, name, age) VALUES (3, 'Charlie', 28);
COMMIT;

-- 事务 A
SELECT * FROM employees WHERE age > 20;
COMMIT;

在可重复读隔离级别下，事务 A 的第二次查询不会看到事务 B 插入的 Charlie，因为间隙锁阻止了插入操作。

使用可序列化（SERIALIZABLE）隔离级别
#

-- 事务 A
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM employees WHERE age > 20;

-- 事务 B
START TRANSACTION;
INSERT INTO employees (id, name, age) VALUES (3, 'Charlie', 28);
-- 事务 B 会被阻塞，直到事务 A 提交或回滚
COMMIT;

-- 事务 A
SELECT * FROM employees WHERE age > 20;
COMMIT;

在可序列化隔离级别下，事务 B 的插入操作会被阻塞，直到事务 A 提交或回滚，从而完全防止幻读。

可重复读（REPEATABLE READ）：通过间隙锁防止幻读，是 MySQL 的默认隔离级别。
可序列化（SERIALIZABLE）：通过强制事务按顺序执行完全防止幻读，但可能会导致更多的锁争用和性能下降。

选择合适的隔离级别取决于你的应用程序对一致性和性能的需求。

ACID — 事务基本要素
#

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。

A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

多版本并发控制（MVCC，Multi-Version Concurrency Control）是一种用于数据库管理系统的并发控制方法，通过维护数据的多个版本来实现高效的并发访问。MVCC 允许多个事务同时读取和写入数据，而不会相互阻塞，从而提高系统的并发性能和响应速度。

MVCC 的基本原理
#

数据版本：
- 每个数据行在数据库中都有多个版本，每个版本对应一个特定的时间点或事务。
- 数据行的每个版本包含一个创建时间戳和一个删除时间戳，用于标识该版本的有效期。
读取操作：
- 读取操作会根据事务的时间戳选择合适的数据版本。
- 事务只能看到在其开始之前已经提交的版本，避免读取到未提交的数据。
写入操作：
- 写入操作会创建一个新的数据版本，并将其时间戳设置为当前事务的时间戳。
- 旧版本的数据不会立即删除，而是保留一段时间，以便其他并发事务可以继续访问。
事务隔离级别：
- MVCC 支持不同的事务隔离级别，如读已提交（Read Committed）、可重复读（Repeatable Read）和可序列化（Serializable）。
- 不同的隔离级别会影响事务对数据版本的可见性和并发控制策略。

MVCC 在 MySQL InnoDB 中的实现
#

MySQL 的 InnoDB 存储引擎使用 MVCC 来实现高效的并发控制。以下是 InnoDB 中 MVCC 的一些关键点：

隐藏列：
- InnoDB 在每个数据行中维护两个隐藏列：trx_id 和 roll_pointer。
- trx_id：记录最后一次修改该行的事务 ID。
- roll_pointer：指向回滚段，用于存储旧版本的数据。
快照读：
- 快照读是指事务在读取数据时，读取的是数据行的快照版本，而不是当前最新版本。
- 快照读通过读取符合事务时间戳的版本来实现，避免了读取未提交的数据。
当前读：
- 当前读是指事务在读取数据时，读取的是数据行的最新版本，并且会加锁以防止其他事务修改。
- 当前读通常用于更新操作，如 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE。
间隙锁（Next-Key Locking）：
- InnoDB 使用间隙锁来防止幻读（Phantom Read）。
- 间隙锁不仅锁定现有的行，还锁定行之间的间隙，防止其他事务在这些间隙中插入新行。

示例
#

以下是一个简单的示例，展示了 MVCC 在 MySQL 中的工作原理：

-- 创建一个示例表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

-- 插入一些数据
INSERT INTO employees (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25);

-- 事务 A
START TRANSACTION;
SELECT * FROM employees WHERE age > 20;

-- 事务 B
START TRANSACTION;
UPDATE employees SET age = 35 WHERE id = 1;
COMMIT;

-- 事务 A
SELECT * FROM employees WHERE age > 20;
COMMIT;

在这个示例中：

事务 A 开始并读取数据，此时它会看到 Alice 的年龄是 30。
事务 B 开始并更新 Alice 的年龄为 35，然后提交。
事务 A 再次读取数据时，由于使用了 MVCC，它仍然会看到 Alice 的年龄是 30，因为它读取的是事务开始时的快照版本。

MVCC 通过维护数据的多个版本，实现了高效的并发控制，允许多个事务同时读取和写入数据，而不会相互阻塞。MySQL 的 InnoDB 存储引擎通过隐藏列、快照读、当前读和间隙锁等机制实现了 MVCC，从而提高了系统的并发性能和数据一致性。

MySQL锁机制

数据库的乐观锁和悲观锁？

MySQL 中有哪几种锁，列举一下？

MySQL中InnoDB引擎的行锁是怎么实现的？

MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁

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

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事物，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类
#

从对数据操作的类型分类：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
行级锁：开销大，加锁��；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

行锁表锁页锁MyISAM√BDB√√InnoDB√√Memory√

MyISAM 表锁
#

MyISAM 的表锁有两种模式：

表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

InnoDB 行锁
#

InnoDB 实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

锁模式(InnoDB有三种行锁的算法)

记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。
临键锁(Next-key Locks)： 临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)

select for update有什么含义，会锁表还是锁行还是其他

for update 仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！假设有个表单 products ，里面有id跟name二个栏位，id是主键。

明确指定主键，并且有此笔资料，row lock

SELECT * FROM products WHERE id=‘3’ FOR UPDATE;

SELECT * FROM products WHERE id=‘3’ and type=1 FOR UPDATE;

明确指定主键，若查无此笔资料，无lock

SELECT * FROM products WHERE id=’-1’ FOR UPDATE;

无主键，table lock

SELECT * FROM products WHERE name=‘Mouse’ FOR UPDATE;

主键不明确，table lock

SELECT * FROM products WHERE id<>‘3’ FOR UPDATE;

主键不明确，table lock

SELECT * FROM products WHERE id LIKE ‘3’ FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

MySQL 遇到过死锁问题吗，你是如何解决的？

死锁
#

死锁产生：

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

InnoDB避免死锁：

为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

删除大数据
#

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

-- 步骤1：删除索引
ALTER TABLE large_table DROP INDEX index_name;

-- 步骤2：删除无用数据
DELETE FROM large_table WHERE condition;

-- 步骤3：重新创建索引
ALTER TABLE large_table ADD INDEX index_name (column_name);