【MySQL】锁机制、事务、并发控制、引擎、索引总结

本文链接：https://blog.csdn.net/Bitter_Li/article/details/118912584

什么是加锁？

锁是计算机协调多个进程 / 线程并发访问某一资源的机制。加锁在保证了数据的安全操作的同时，增加了额外开销，我们应尽可能不加锁以减少开销。

一、事务

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。

1、事务的四大特性

① A：原子性（atomicity）
一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

① C：一致性（consistency）
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

① I：隔离性（isolation）
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

① D：持久性（durability）/ 永久性（permanence）
一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作 / 故障不应该对其有任何影响。

2、并发产生的问题

举个例子，事务A和事务B操纵的是同一个资源，事务A有若干个子事务，事务B也有若干个子事务，事务A和事务B在高并发的情况下，会出现以下几种问题。

① 脏读
脏读，指事务A读到了事务B还没有提交的数据。

② 不可重复读
不可重复读，指在一个事务里面读取了两次某个数据，读出来的数据不一致。

③ 幻读
幻读，就是指在一个事务里面的操作中发现了未被操作的数据。

3、隔离级别

事务隔离级别，为了解决以上几种问题而诞生。事务隔离级别越高，在并发下会产生的问题就越少，但同时带来较大开销，因此应权衡并发性与性能。

① 未提交读（READ_UNCOMMITTED）
读未提交，即能够读取到没有被提交的数据，该级别隔离机制无法解决脏读、不可重复读、幻读中的任何一种，故很少使用。

② 已提交读（READ_COMMITED (RC)）
读已提交，即能够读到那些已经提交的数据，解决了脏读问题，无法解决不可重复读、幻读问题。

③ 可重复读（REPEATABLE_READ (RR)）
在数据读出来之后加锁，解决了脏读、不可重复读的问题，无法解决幻读问题。

④ 可串行化（SERLALIZABLE）
最高事务隔离级别，所有事务排队串行执行，完全解决了脏读、不可重复读和幻读的问题了。

我们较常使用的是RC和RR。

各隔离级别 无法解决的问题：

隔离级	脏读可能性	不可重复读可能性	幻读可能性	加锁读
未提交读 READ_UNCOMMITTED	√	√	√
已提交读 READ_COMMITED (RC)		√	√
可重复读 REPEATABLE_READ (RR)			√
可串行化 SERLALIZABLE				√

4、隔离级别查看、修改

查看事务隔离级别：SELECT @@TX_ISOLATION
修改当前会话事务隔离级别使用SET session TRANSACTION ISOLATION LEVEL [隔离级别];）
修改全局事务隔离级别使用SET global TRANSACTION ISOLATION LEVEL [隔离级别];

二、锁 - 常见术语

1、乐观锁、悲观锁

所谓乐观锁、悲观锁是一种概念，数据库通常不会提供乐观锁，需要我们自己实现。

乐观锁
指在操作数据时，乐观地认为操作不会导致冲突，不做任何处理，在更新后判断是否有冲突。

悲观锁
指在操作数据时，悲观地认为操作会导致冲突，每次操作都必须加锁，悲观锁所需的开销较大，较为耗时。
悲观锁在Mysql中自带的具体实现有两种：共享锁、排他锁。

2、共享锁、排他锁

① 共享锁（读锁、S锁）

共享锁指的就是对于多个不同的事务，对同一个资源共享加锁。其他事务只可读，不可写。

共享锁允许多个不同事物对同一个资源加锁，其他事务可以读取数据，检查数据是否已被修改，又称读锁。

对于普通的SELECT语句，InnoDB引擎不会加任何锁。
Mysql中使用方式：在语句的结尾加上LOCK IN SHARE MODE

② 排他锁（写锁、X锁）

排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。即其他事务不可读也不可写。

排他锁具有一对一的性质，所以一般在写数据（UPDATE、INSERT、DELETE操作）的时候使用，不得被其他事务干扰，故又称写锁。

使用InnoDB引擎时，会自动给涉及UPDATE、INSERT、DELETE操作的数据集加排他锁。
Mysql中使用方式：在语句的结尾加上FOR UPDATE

3、页锁、行锁、表锁

Mysql常见引擎对页锁、行锁、表锁的支持：

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

① 页锁
开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

② 行锁
开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

③ 表锁
开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。

三、并发控制

当程序中可能出现并发的情况时，就需要保证在并发情况下数据的准确性，以此确保当前用户和其他用户一起操作时，所得到的结果和他单独操作时的结果是一样的。这就叫做并发控制。

目的：保证一个用户的工作不会对另一个用户的工作产生不合理的影响。
任务：确保多个事务同时增删改查同一数据时，不破坏事务的隔离性、一致性和数据库的统一性。
解决问题：脏读、幻读、不可重复读。

MVCC - 多版本并发控制

多版本并发控制 (MultiVersionConcucrrencyControl, MVCC)

原理：通过数据行的多个版本管理来实现数据库的并发控制（保存历史版本），通过比较版本号决定数据是否显示。读取数据时不加锁可以保证事务的隔离效果。

解决问题：

读写互相不阻塞，提升数据并发处理能力。
降低死锁概率，（MVCC采取乐观锁方式：读取数据不加锁、写入数据只锁定必要的行）
解决一致性都问题（快照读时只能看到这个时间点前事务提交更新的结果）

如何存储记录多个版本？

Undo Log：为回滚提供依据，内容为copy事务前的数据库内容（行）undo buffer，在适合的时间把 undo buffer 中的内容刷新至磁盘。
Redo Log：将执行的SQL语句保存至指定的Log文件，l执行恢复时重新执行即可。当执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。
事务版本号：每开启一个日志，都会从数据库中获得一个自增的 事务ID（事务版本号） ，通过ID大小可判断事务的时间顺序。
行记录隐藏列：
- ROW_ID：隐藏的行ID，用以生成默认的聚簇索引。
- DB_TRX_ID：操作该事务的ID，即最后一个对数据插入 / 更新的事务ID。
- DB_POOL_PTR：回滚指针，指向这个记录的 Undo Log 信息。

四、两段锁协议

两段锁协议：每个事务的执行可以分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）。

加锁阶段：
在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

解锁阶段：
当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

实现：事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。

五、死锁问题

1、死锁成因

不同表相同记录行锁冲突
相同表记录行锁冲突
不同索引锁冲突
gap锁冲突

2、解决策略

先排序，后执行，这样就避免了交叉等待锁的情形
将大事务拆分，保证小事务正常执行
同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择（比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。）
为表添加合理的索引。

六、引擎

1、什么是引擎？

MySQL引擎，又称 插件式存储引擎：

插件式存储引擎：MySQL的最重要的特性之一。
插件式存储引擎：实现了根据存储数据的需要去调整关系数据库系统的物理存储机制。
插件式存储引擎：在MySQL Server的体系结构中是一个 软件层 ，负责隔离MySQL Server的物理数据层和逻辑层，并提供底层的 I/O 操作。
该机制允许人们创建属于自己的存储引擎。

2、常见的引擎

我们可以通过语句 SHOW ENGINES 查看MySQL默认支持的引擎（默认支持9种引擎）。

常用的引擎有3种：InnoDB、MyISAM、MEMEORY，我们对他们的特性进行总结：

功能	InnoDB	MyISAM	MEMEORY
存储限制	64TB	256TB	RAM
支持事务	√
支持全文索引		√
支持B树索引	√	√	√
支持哈希索引			√
支持集群索引	√
支持数据索引	√		√
支持数据压缩		√
空间利用率	低	N/A	高
支持外键	√