听不懂你打我，MySQL的锁机制就这么简单

小鱼儿 2022-11-30 01:40 164阅读 0赞

# MySQL的锁机制 #

我们首先要清楚，不同的存储引擎，锁机制也不同：

*  MyISAM：表级锁（table-level locking）
 *  MEMORY：表级锁（table-level locking）
 *  BDB：页面锁（page-level locking）但也支持表级锁
 *  InnoDB(当前主流)：既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

## 1、锁的分类 ##

我们可以看到上面提到的三种锁机制：  
（锁机制特性关键词：**开销、加锁速度、死锁、粒度、并发性能**）

> 锁粒度：锁粒度相当于是对上锁范围的描述，举例：张三上公共厕所蹲坑，但要保证张三上厕所不能  
> 有别人和张三一起蹲坑，所以张三需要锁门，不让别人进来，这叫上锁；而锁的粒度就是我是锁这个厕所隔间的门，还是公厕大门，又或是商场大门，锁住范围越大，粒度越大，可是明显资源浪费明显。

1.  **表级锁**：  
    开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

> 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

1.  **行级锁**：  
    开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

> 表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

1.  **页面锁**：  
    开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

> 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

三种锁各有优缺，实际运用依然要根据实际情况选择最优的方案，根据需求选择最合适的技术是开发出好项目的前提！

> 这里我们主要看看MyISAN和InnoDB的区别！

## 2、MyISAN存储引擎 ##

MyISAN仅支持表锁，也是MySQL最开始几个版本唯一支持的锁类型。

MySQL表级锁两种模式：

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

再看一下MySQL中的表级锁兼容性:  
![MySQL中的表锁兼容性][MySQL]

很直观的可以看出，在MySQL读锁模式下，不会阻塞其他用户对同一个表的读操作，但是会阻塞写操作；但写锁模式下，其他用户对着同一个表的读操作和写操作都会被阻塞。

我们来测试一下：

> 准备工作：  
> create database test default charset=utf8;  
> use test;  
> create table user\_myisam(id int not null primary key auto\_increment,name varchar(20),age tinyint,address varchar(20) )engine=myisam default charset=utf8;  
> create table user\_innodb(id int not null primary key auto\_increment,name varchar(20),age tinyint,address varchar(20) )engine=innodb default charset=utf8;

查看表结构

#### 测试MyISAM写锁 ####

现在分别两个用户同时操做，得到下面这种情况：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center]

使用lock table user\_myisam write;对user\_myisam表加写锁之后，其他用户操作user\_myisam表都会被阻塞了。

#### 测试MyISAM读锁 ####

提示：在用**LOCK TABLES**给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；  
同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。  
所以**MyISAM表不会出现死锁(Deadlock Free)** 。

![测试MyISAM读锁][MyISAM]

#### MyISAM支持并发插入 ####

MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量**concurrent\_insert**，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

*  当concurrent\_insert设置为0时，不允许并发插入。
 *  当concurrent\_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
 *  当concurrent\_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

#### MyISAM的锁调度 ####

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。  
但它认为写锁的优先级比读锁高，所以即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！ 这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。 可以通过一些设置来调节MyISAM的调度行为。

*  通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
 *  通过执行命令SET LOW\_PRIORITY\_UPDATES=1，使该连接发出的更新请求优先级降低。
 *  通过指定INSERT、UPDATE、DELETE语句的LOW\_PRIORITY属性，降低该语句的优先级。

可以看出这三种方法可以保证查询优先，或者更新优先，可以很好的解决查询操作多的应用的读锁等待严重问题。  
MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max\_write\_lock\_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。上面已经讨论了写优先调度机制带来的问题和解决办法。

**注意**：一些查询操作如果运行时间比较长，有可能导致写进程被 “饿死” ！为了避免这种情况，尽量避免产生长时间的查询操作，也就是不要想着1条sql语句完成整个操作，尽管语句写短，看起来巧妙精简，往往比较复杂，导致运行时间长，可以根据实际情况分解为几个步骤完成，保证每个步骤能在短时间完成。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜深人静时执行。

## 3、InnoDB ##

InnoDB与MyISAM最大的两个不同点

*  InnoDB支持事务
 *  InnoDB采用行级锁

#### 事务的四个原则ACID： ####

**事务概念：要么全成功，要么全不成功**

*  **A(Atomicity)原子性:** 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
 *  **C(Consistent)一致性：** 在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
 *  **I(Isolation)隔离性：** 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
 *  **D(Durable)持久性：** 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

#### 并发事务处理带来的问题 ####

*  更新丢失（Last update）：A和B同时对一行数据进行处理，A修改后进行保存，然后B修改后进行保存，这样A的更新被覆盖了，相当于发生丢失更新的问题。所以可以在A事务未结束前，B不能访问该记录，这样就能避免更新丢失的问题。
 *  脏读（Dirty Reads）：A事务对一条记录做了修改，但是在未提交的时候，这时B事务来读这同一条记录，读到的是修改前的旧记录，然后B对旧记录进行进一步处理。这样就产生了未提交数据的依赖关系，这种现象叫“脏读”
 *  不可重复读（Non-Repeatable Reads）：B事务在读取某些数据后的某个时间，事务内再次读取刚才在事务内也读过的数据，却发现其读出的数据已经发生了改变（被更新或者删除了，例如A事务修改了）。这种现象叫做“不可重复读”。
 *  幻读（Phantom Reads)：A事务按照相同查询条件，重新读取事务内之前检索过的内容，却因为其它事务插入或修改了其查询条件，导致查询不到事务内之前查询到的数据，甚至查询到了之前没有查询到的新数据，这种现象就叫”幻读“。
 *

#### 事务的隔离级别 ####

事务隔离实质上就是使事务在一定程度上 “串行化”进行，但与“并发”相对。

数据库的事务隔离越严格，并发问题越小，但付出的成本代价也就越高。但是，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力，这样就可以不用那么严格的事务隔离级别，从而提升效率。

**4种隔离级别比较：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center 1]

#### InnoDB的行锁模式以及加锁方法 ####

InnoDB实现了2种行锁：

*  共享锁(S)：允许一个事务去多一行，阻止其它事务获得相同数据集的排他锁。
 *  排他锁(X): 允许获得排他锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排他写锁。

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

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

InnoDB行锁模式兼容性列表:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center 2]  
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。 意向锁是InnoDB自动加的；对于UPDATE、DELETE和INSERT语句，InnoDB会自动给设计数据集加排他锁（X）；对于普通的SELECT语句，InnoDB不会加锁。

通过以下语句显示给记录集加共享锁或排他锁：

共享锁（S）：SELECT \* FROM TABLE\_NAME WHERE … LOCK IN SHARE MODE.

排他锁（X）：SELECT \* FROM TABLE\_NAME WHERE … FOR UPDATE

用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

所以在使用共享锁模式下，查询完数据后不要进行更新操作，不然又可能会造成死锁；要更新数据，应该使用排他锁模式。

#### InnoDB行锁实现方式 ####

InnoDB行锁是通过**给索引上的索引项加锁**来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！(**没加索引，行锁变表锁**）

*  在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
 *  由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
 *  当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
 *  即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。

> 可以通过explain执行计划查看是否真正使用了索引。

#### 间隙锁（Next-key锁） ####

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

举个例子：假如emp表中只有101条记录，其id的值从1~101，下面的sql： select \* from emp where id > 100 for update; 是范围条件查询，InnoDB不仅会对符合条件的id值为101的记录加锁，也会对id大于101（并不存在的值）的“间隙”加锁

结论：

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

#### 关于死锁（DeadLock) ####

上面知识点说过，MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步或得的，所以InnoDB发生死锁是可能的。

举例：

> 首先我们得关闭mysql中的autocommit属性，因为mysql默认使用自动提交模式，也就是说当我们进行一个sql操作的时候，mysql会将这个操作当做一个事务并且自动提交这个操作

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center 3]

这样**互相持有资源不释放，导致环形等待**，这就是死锁产生的原因。

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb\_lock\_wait\_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

#### 避免死锁的方法 ####

在应用中，如果不同的程序会并发存取多个表，应**尽量约定以相同的顺序来访问表**，这样可以大大降低产生死锁的机会。在下面的例子中，由于两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。

在程序以批量方式处理数据的时候，如果**事先对数据排序，保证每个线程按固定的顺序来处理记录**，也可以大大降低出现死锁的可能。

在事务中，如果**要更新记录，应该直接申请足够级别的锁**，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将**隔离级别改成READ COMMITTED**，就可避免问题。

当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。

对于这种情况，可以**直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁**

参考:  
https://juejin.im/post/5ce8eee45188253114078f2a  
https://blog.csdn.net/qq\_38238296/article/details/88362999  
······

# ps:各位观众老爷轻点打 \* \_ \* #

[MySQL]: /images/20221124/8a16ad5e5b224fe09632d33ac71bbadd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/5efdae9d345e481d84b651e0bbdb6c54.png
[MyISAM]: /images/20221124/618bb63040ea4daba0ef72a9f30480a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221124/969fe1a0c61a4b5386a63fc2ce267429.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221124/57cce5e91f9b448092aaa4363dfc5431.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1YW5nZG9uZzUw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221124/af1d01bcb30d418f8b3f6aecb5bcfe42.png