Mysql锁机制解析

刺骨的言语ヽ痛彻心扉 2023-02-24 07:54 85阅读 0赞

总括：

一般加了行锁会加一个意向锁标记是否有行锁  
MyISAM 表级锁 ，非聚簇索引  
inodb  行级锁，实际执行计划不走索引会升级为表级锁（行级锁锁的是索引，不是数据），聚簇索引---维护成本高查询效率低

RR:MVCC每次一个事务读快照使用的都是第一次读生成的快照生成视图，只要这一次快照读在其他事务修改之前，都感知不到数据变化---数据不变-----视图的生成时机不同  
RC每一次快照读，都会生成新的快照并获取最新的视图，所以可以实时刷新视图的数--数据可能会变

MySql支持可插拔的存储引擎，不同存储引擎使用的锁机制不尽相同。MySql常用的存储引擎为InnoDB、MyISAM、MEMORY等一般我们在需要数据库事物支持的互联场景下主要使用 InnoDB(MySQL 5.5.5以上版本的默认存储引擎)，以下基于InnoDB的锁机制进行分析  
   
事务的隔离级别  
隔离级别定义的是并发事物之间的可⻅性和影响程度，为什么要有事物隔离级别?

在理想的情况下，事务之间是完全隔离的，这样就可以避免出现脏读，不可重复读，幻读等问题，且 事物隔离级别越⾼，在并发下会产⽣的问题就越少，但同时付出的性能消耗也将越⼤。因此很多时候必须在并发性和性能之间做⼀个权衡，针对这四种隔离级别，应该根据具体的业务来取舍，如果某个系统的业务⾥根本就不会出现重复读RR的场景，完全可以将数据库的隔离级别设置为 RC，这样可以最⼤程度的提⾼数据库的并发性

事务的ACID特性：

原子性(Atomicity)  
一致性(Consistency)  
隔离性(Isolation)  
持久性(Durability)  
事务的隔离级别：

读未提交（READ UNCOMMITTED）  
读已提交（READ COMMITTED）  
可重复读（REPEATABLE READ）  
串行化（SERIALIZABLE）  
事务并发引起的问题：

脏读（DIRTY READ）  
不可重复读（UNREPEATABLE READ）  
幻读（PHANTOM READ）  
其中：

读未提交：.可以读取未提交的记录，会出现脏读，幻读， 不可重复读，所有并发问题都可能遇到

读已提交：事务中只能看到已提交的修改，不会出现脏读，但是会出现幻读，不可重复读（⼤多数数据库的默认隔离级别都是 RC，如Oracle、Mysql非InnoDb，InnoDb 默认是 RR）

可重复读：解决了不可重复读问题，但是仍然存在幻读问题.（MySQL通过MVCC+GAP间隙锁解决了幻读）

序列化：“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时 候，后访问的事务必须等前⼀个事务执⾏完成,没有并发问题.

SQL 规范中定义的四种隔离级别，分别是为了解决事务并发时可能遇到的问题，⾄于如何解决，实现⽅式是什么，规范中并没有严格定义。锁作为最简单最显⽽易⻅的实现⽅式被应⽤在很多数据库中。除了锁，实现并发问题的⽅式还有时间戳，多版本控制等。这些也可以称为⽆锁的并发控制。

基于锁的隔离级别并发控制（Lock-Based Concurrent Control，简写 LBCC）  
通过对读写操作加不同的锁，以及对释放锁的时机进⾏不同的控制，就可以实现四种隔离级别。传统的锁有两种：读操作通常加共享锁（Share locks，S锁，⼜叫读锁），写操作加排它锁（Exclusive locks，X锁，⼜叫写锁）；加了共享锁的记录，其他事务也可以读，但不能写；加了排它锁的记录，其他事务既不能读，也不能写。另外，对于锁的粒度⼜分为⾏锁和表锁，⾏锁只锁⼀⾏或某⼏⾏记录，对其它⾏的操作不受影响，表锁会锁住整张表，所有对这个表的操作都受影响。

写锁/读锁互斥关系

![20200708153312718.png][]

通过对锁的类型（读锁还是写锁），锁的粒度（⾏锁还是表锁），持有锁的时间（临时锁：语句执⾏完后就释放锁和持续锁：事物结束才释放锁）合理的进⾏组合，就可以实现四种不同的隔离级别。（理论模型，认识不同的锁和互斥关系，可以实现不同的事物隔离级别)

读未提交：通过对写操作加 “持续X锁”，对读操作不加锁实现；(写操作加锁是为了防⽌出现回滚覆盖，也叫做第⼀类更新丢失，数据库任何隔离级别下都不允许出现），事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读；  
读已提交：通过对写操作加 “持续X锁”，对读操作加 “临时S锁” 实现； 不会出现脏读；事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写；  
可重复读：通过对写操作加 “持续X锁”，对读操作加 “持续S锁” 实现；事务读会阻塞其他事务写但不阻塞读，事务写会阻塞其他事务读和写；  
序列化：为了解决幻读问题，⾏级锁做不到，需使⽤表级锁。  
其中：

第一类更新丢失（回滚丢失，Lost update）：如A事务撤销时，把已经提交的B事务的更新数据覆盖了。这种错误可能造成很严重的问题，通过下面的账户取款转账就可以看出来：

第二类丢失更新(覆盖丢失/两次更新问题，Second lost update) ：A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失

基于MVCC的隔离级别并发控制(Multi-Version Concurrent Control,MVCC)  
LBCC最⼤的问题是它只实现了并发的读读，对于并发的读写还是冲突的，写时不能读，读时不能写。当读写操作都很频繁时，数据库的并发性将⼤⼤降低，针对这种场景，MVCC 技术应运而生，MVCC 的全称叫做 Multi-Version Concurrent Control（多版本并发控制），InnoDb  会为每⼀⾏记录增加⼏个隐含的“辅助字段”（ROWID、事物ID、回滚指针）。事务在更新⼀条记录时会将其拷⻉⼀份⽣成这条记录的⼀个原始拷⻉，写操作同样还是会对原记录加锁，但是读操作会读取未加锁的新记录，即通过维持一个数据的多个版本，使得读写操作没有冲突，保证了读写并⾏。要注意的是，生成的新版本其实就是 undo log，它也是实现事务回滚的关键技术。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU5ODY4Mg_size_16_color_FFFFFF_t_70][]

MVCC的实现原理  
在数据库中的实现，为每行记录添加 3个隐式字段，字段名：DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID

DB\_TRX\_ID：6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID  
DB\_ROLL\_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）  
DB\_ROW\_ID：6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB\_ROW\_ID产生一个聚簇索引  
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了  
从上图中可以看出，每次修改都会新增一个undo log，各个undo log之间通过指针连接，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录

快照读和当前读

mysql通过MVCC来实现RR和RC隔离级别下的读写并⾏，它们读取的都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）。除了快照读 ， MySQL 还提供了另⼀种读取⽅式：当前读（Current Read），有时候⼜叫做 加锁读（Locking Read） 或者 阻塞读（Blocking Read），这种读操作读的不再是数据的快照版本，⽽是数据的最新版本，并会对数据加锁（当前读在 RR 和 RC 两种隔离级别下的实现也是不⼀样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题）

Read View(读视图)  
Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)，所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB\_TRX\_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB\_TRX\_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB\_ROLL\_PTR回滚指针去取出Undo Log中的DB\_TRX\_ID再比较，即遍历链表的DB\_TRX\_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB\_TRX\_ID, 那么这个DB\_TRX\_ID所在的旧记录就是当前事务能看见的最新老版本

根据上图可以看出，存在一个数值列表read\_view\_list，用来维护Read View生成时刻系统正活跃的事务ID，其中：tmin是记录read\_view\_list列表中事务ID最小的ID，tmax记录的是read\_view\_list列表中事务ID最大的ID，可见性算法主要如下：

首先比较DB\_TRX\_ID < tmin， 如果小于，则当前事务能看到DB\_TRX\_ID 所在的记录，  
如果大于等于进入下一个判断，接下来判断 DB\_TRX\_ID 大于 tmax , 如果大于则代表DB\_TRX\_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见  
如果小于则进入下一个判断，判断DB\_TRX\_ID 是否在活跃事务之中，trx\_list.contains(DB\_TRX\_ID)，如果在，则代表Read View生成时刻，当前这个事务还在活跃，还没有Commit，当前事务修改的数据是看不见的；如果不在，则说明，当前这个事务在Read View生成之前就已经Commit了，当前事务修改的数据是是能看见的  
undo日志  
undo log主要分为两种：

insert undo log：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃；

update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除  
purge线程：为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted\_bit，并不真正将过时的记录删除。为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted\_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（read view相当于系统中最老活跃事务的read view）；如果某个记录的deleted\_bit为true，并且DB\_TRX\_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。从这里的回滚日志也侧⾯说明长事物的性能影响，长事物不仅会占用更多的锁资源，也会产⽣⼤量的回滚日志。

在 read uncommit 隔离级别下，每次都是读取最新版本的数据⾏，所以不能⽤ MVCC 的多版本，⽽ serializable 隔离级别每次读取操作都会为记录加上读锁，也和 MVCC 不兼容，所以只有 RC 和 RR 这两个隔离级别才有 MVCC，那么RC,RR级别下的InnoDB快照读有什么不同？  
正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同，在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；  
即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见  
而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因  
总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

常见锁类型  
锁的基础分类：锁的类型（读锁还是写锁），锁的粒度（⾏锁还是表锁），持有锁的时间（临时锁还是持续锁）。在 MySQL 中锁的种类有很多，但是最基本的还是表锁和行锁：

表锁指的是对⼀整张表加锁，⼀般是 DDL 处理时使⽤，也可以⾃⼰在 SQL 中指定。

表锁由 MySQL 服务器实现，行锁由存储引擎实现 。InnoDB支持行锁，而 MyISAM 存储引擎只能使用表锁

表锁：开销小，加锁快，不会出现死锁，锁的粒度大，发生锁冲突的概率高，并发度低

行锁指的是锁定某⼀行数据或某几行，或行和行之间的间隙。行锁的加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，通常都是用行锁来处理并发事务。

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

表锁

关于表锁，我们要了解它的加锁和解锁原则。要注意的是它使⽤的是⼀次封锁技术，也就是说，我们会在会话开始的地方使用 lock 命令将后面所有要用到的表加上锁，在锁释放之前，我们只能访问这些加锁的表，不能访问其他的表，最后通过unlock tables 释放所有表锁。

mysql root @localhost :study> lock table vote\_record read,t\_item write; Query OK, 0 rows aﬀected  
Time: 0.006s  
mysql root @localhost :study> select id,user\_id,vote\_id from vote\_record limit 1;  
\+----+----------------------+---------+  
| id | user\_id    | vot e\_id |  
\+----+----------------------+---------+  
| 1   | a56351fd5b013bd0bf 3f | 985    |  
\+----+----------------------+---------+  
1 row in set Time: 0.015s  
mysql root @localhost :study> update vote\_record set vote\_id=905 where id=1; (1099, "Table 'vote\_record' was locked with a READ lock and can't be updated") mysql root @localhost :study> unlock tables;  
MySQL 表锁的加锁规则如下：

InnoDB如何加表锁：

在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。UNLOCK TABLES会隐含地提交事务

SET AUTOCOMMIT=0;  
LOCK TABLES t1 WRITE, t2 READ, ...;  
\[do something with tables t1 and t2 here\];  
COMMIT;  
UNLOCK TABLES;  
 对于读锁：lock tableName read

持有读锁的会话可以读表，但不能写表；  
允许多个会话同时持有读锁；  
 其他会话就算没有给表加读锁，也是可以读表的，但是不能写表；  
其他会话申请该表写锁时会阻塞，直到锁释放。  
对于写锁：lock tableName write

持有写锁的会话既可以读表，也可以写表；  
只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，直到锁释放；  
其他会话⽆论申请该表的读锁或写锁，都会阻塞，直到锁释放。  
表锁的释放规则如下：

使⽤ UNLOCK TABLES 语句可以显示释放表锁  
如果会话在持有表锁的情况下执⾏ LOCK TABLES 语句，将会释放该会话之前持有的锁  
如果会话在持有表锁的情况下执⾏ START TRANSACTION 或 BEGIN 开启⼀个事务，将会释放该会话之前持有的锁  
如果会话连接断开，将会释放该会话所有的锁  
MySQL 行锁的加锁规则如下：

SELECT ... LOCK IN SHARE MODE：加 S 锁  
SELECT ... FOR UPDATE：加 X 锁  
INSERT / UPDATE / DELETE：加 X 锁  
常⻅的增删改（INSERT、DELETE、UPDATE）语句会⾃动对操作的数据⾏加Ｘ锁，  
对于普通SELECT语句，InnoDB不会加任何锁  
查询的时候也可以明确指定锁的类型，其中：SELECT ... LOCK IN SHARE MODE 语句加的是读锁，SELECT ... FOR UPDATE 语句加的是写锁。在 MySQL 中，行锁是加在索引上的，MySQL 有两种索引类型：主键索引（Primary Index）和⾮主键索引（Secondary Index，⼜称为⼆级索引、辅助索引，细分⼜可以分为唯⼀索引、普通索引）

InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。注意事项如下：

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

Table    | student  
Create Table | CREATE TABLE \`student\` (  
\`id\` int(11) NOT NULL AUTO\_INCREMENT,  
\`stu\_no\` varchar(20) COLLATE utf8\_bin NOT NULL COMMENT '学号',  
\`name\` varchar(20) COLLATE utf8\_bin NOT NULL COMMENT '姓名',  
\`age\` int(11) NOT NULL COMMENT '年龄',  
\`score\` int(11) NOT NULL DEFAULT '0' COMMENT '学分',  
PRIMARY KEY (\`id\`),  
UNIQUE KEY \`student\_stu\_no\_uindex\` (\`stu\_no\`),  
KEY \`student\_name\_index\` (\`name\`),  
KEY \`student\_age\_index\` (\`age\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8\_bin  
   
 mysql root@localhost:study> select \* from student;  
\+-----+--------+------+-----+-----+-----+  
| id | stu\_no   | name  | age   | score |  
\+-----+--------+------+-----+-----+-----+  
| 1     | S0001    | Bob    | 25    | 34    |  
| 3     | S0002    | Tom    | 23    | 50    |  
| 5     | S0003    | Eric  | 26    | 27    |  
| 10 | S0004    | Rain  | 21    | 48    |  
| 20 | S0005    | Tom    | 23    | 8        |  
| 30 | S0006    | Kobe  | 19    | 28    |  
| 40 | S0007    | Rose  | 25    | 52    |  
| 41 | S0008    | Jim    | 26    | 19    |  
| 42 | S0009    | Zoom  | 22    | 90    |  
| 50 | S0010    | Tom    | 24    | 81    |  
\+----+--------+------+-----+------+-----+  
1、主键加锁

update student set score=35 where id=1  
InnoDb存储引擎会在id=1这个主键索引上加一把X锁

2、普通索引加锁

update student set score =35 where name ='Rose'  
InnoDb存储引擎会在name='Rose'这个索引上加一把X锁，同时会通过name='Rose'这个二级索引定位到id=40这个主键索引，并在id=40这个主键索引上加一把X锁

3、多条记录加锁

update student set score=28 where name ='Tom'  
当UPDATE语句被发给Mysql之后，Mysql Server会根据Where条件读取第一条满足条件的记录，然后InnoDB引擎将第一条记录返回并加锁（Current read），待Mysql Server收到这条加锁的记录之后们会在发起一个UPDATE请求，更新这条记录，一条操作记录完成在读取下一条记录，直到读完所有满足条件的记录，Mysql在操作多条记录时InnoDB与Mysql Sever的交互式一条一条进行的，加锁也是一条一条进行的，先对一条满足条件的记录加锁，返回给Mysql，做DML操作后，继续下一条，直至读取完毕

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU5ODY4Mg_size_16_color_FFFFFF_t_70 1][]

可以根据多个事务的读取进行验证

行锁的种类

行锁根据场景的不同又可以进一步细分，Mysql定义了四种类型的行锁，如下：

LOCK\_ORDINARY：也称为Next-key Lock，锁一条记录及其之前的间隙，这是RR隔离级别用的最多的锁（RR下才有）  
LOCK\_GAP：间隙锁，锁两个记录之间的GAP，防止记录插入（RR下才有）  
LOCK\_REC\_NOT\_GAP：只锁记录  
LOCK\_INSERT\_INTENSION：插入意向GAP锁，插入记录时使用，是LOCK\_GAP的一种特例  
锁模式分类

LOCK\_IS：读意向锁  
LOCK\_IX：写意向锁  
LOCK\_S：读锁  
LOCK\_X：写锁  
将锁分为读锁和写锁主要是为了提高锁的并发，如果不区分读写，那么数据库将没办法并发读，并发性大大降低，而IS（读意向锁）、IX(写意向锁)只会应用到表锁上，方便表锁和行锁之间的冲突检查

读写锁

读锁，又称共享锁（Shared locks，简称S锁），加了读锁的记录所有事物都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁，写锁，又称排他锁（Exclusive locks，简称X锁），或者独占锁，对记录加了排他锁后，只有拥有该锁的事务可以读取和修改，其他事物不允许读取或者修改，并且同一时间只能有一个事务加写锁（这里说的读都是当前读，快照读无需加锁，记录上无论有没有锁，都可以快照读）

读写意向锁

表锁锁定了整张表，而行锁是锁定表中的某条记录，他们锁定的范围存在交集，因此表锁和行锁是存在冲突的，如某个表中有10000行记录，其中有一题条记录加了X锁，如果这个时候需要对表添加表锁，为了判断是否可以加这个表锁，系统需要便利表中的10000行记录，看看是否有某些记录被加了行锁，如果有则不允许加表锁，显然这种方式的效率极低，所以引入意向锁

意向锁是表级锁，也可分为读意向锁和写意向锁，当事务试图读或者写一条记录时，会现在表上加上意向锁，然后再要操作的记录上添加读锁或者写锁，这样判断表是否有记录行锁就非常简单，只需要看表中是否存在意向锁即可，意向锁之间是不会产生冲突的，他只会阻塞表级读锁或者表级写锁，另外意向锁也不会和行锁冲突，行锁只会与行锁产生冲突

查看行级锁的争用情况

执行SQL:mysql> show status like 'InnoDB\_row\_lock%';

设置监视器：mysql> create table InnoDB\_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB\_monitor;

间隙锁（Next-Key锁）

间隙锁定义：

nnodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select \* from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

间隙锁的缺点：

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害  
当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；  
当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；  
当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定  
间隙锁的作用：

防止幻读，以满足相关隔离级别的要求。  
为了数据恢复和复制的需要。  
注意

在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。  
InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

回滚较小的那个事务  
在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。  
判断事务大小：事务各自插入、更新或者删除的数据量

注意：

当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB\_lock\_wait\_timeout来解决。

优化行级锁定

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；   
合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；   
尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；   
尽量控制事务的大小，减少锁定的资源量和锁定时间长度；   
在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。  
（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；   
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；   
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

六、表级锁  
表级锁的类型：

表级锁的两种类型：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

表级锁模式的兼容性：

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

表级锁的加锁：

在执行查询语句（select）前，会自动给涉及的所有表加读锁  
在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁。这个过程并不需要用户干预，因此不需要直接用lock table命令给MyISAM表显式加锁。  
当然可以显示的加锁，如下：

显示加写锁：

// 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。  
// 其他线程的读、写操作都会等待，直到锁被释放为止。  
// test表将会被锁住，另一个线程执行select \* from test where id = 3;将会一直等待，直到test表解锁  
LOCK TABLE test WRITE;   
显示加读锁

// test表将会被锁住，另一个线程执行select \* from test where id = 3;不会等待  
// 执行UPDATE test set name='peter' WHERE id = 4;将会一直等侍，直到test表解锁  
LOCK table test READ;  
显示释放锁：

UNLOCK TABLES;  
需要注意的是，在同一个SQL session里，如果已经获取了一个表的锁定，则对没有锁的表不能进行任何操作，否则会报错。

// 锁定test表  
LOCK table test WRITE;  
   
// 操作锁定表没问题  
SELECT \* from test where id = 4;  
   
// 操作没有锁的表会报错  
SELECT \* from bas\_farm where id =1356  
报错：\[Err\] 1100 - Table 'bas\_farm' was not locked with LOCK TABLES。这是因为MyISAM希望一次获得sql语句所需要的全部锁。这也正是myisam表不会出现死锁的原因。

当然，你也不必担心，MyISAM引擎的默认方式是会给同一个session里的所有表都加上锁的，不会麻烦你自己显示操作的。

查看表级锁争用情况

执行SQL:mysql> show status like ‘table%’;

mysql> show status like 'table%';  
\+----------------------------+-----------+  
| Variable\_name              | Value     |  
\+----------------------------+-----------+  
| Table\_locks\_immediate      | 20708     |  
| Table\_locks\_waited         | 0         |  
\+----------------------------+-----------+  
Table\_locks\_immediate：产生表级锁定的次数；   
Table\_locks\_waited：出现表级锁定争用而发生等待的次数；   
如果Table\_locks\_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

优化表级锁定

优化表级锁时的最大问题是：提高并发度

1. 通过减少查询时间缩短锁定时间

缩短锁定时间的总体原则是：让Query执行时间尽可能的短。  
尽量减少大的、复杂的Query，将复杂Query分拆成几个小的Query分步执行；  
尽可能的建立足够高效的索引，让数据检索更迅速；  
尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；  
利用合适的机会优化MyISAM表数据文件。  
2. 设置可并发插入：concurrent\_insert=2

MyISAM的表锁虽是读写互相阻塞的，但依然能够实现并行操作。MyISAM存储引擎有一个控制是否打开Concurrent Insert（并发插入）功能的参数选项：concurrent\_insert，取值范围为0，1，2。

concurrent\_insert=0，不允许并发插入。  
concurrent\_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这是MySQL的默认设置；  
concurrent\_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；  
所以，我们可通过设置concurrent\_insert=2，同时定期在系统空闲时段执行optimize table tableName语句来整理空间碎片，收回因删除记录而没有真正释放的空间，从而提高并发。optimize参考：mysql中OPTIMIZE TABLE的作用及使用

3. 合理设置读写优先级

MyISAM存储引擎默认是写优先级大于读优先级。即使是写请求后到，写锁也会插到读锁请求之前。

但是，有时像修改文章点击数 操作是不那么重要的，我们希望的是读更快，此时我们可以这样：

UPDATE  LOW\_PRIORITY  article SET click\_num=134 WHERE id = 823  
LOW\_PRIORITY使得系统认为update操作优化级比读操作低，如果同时出现读操作和上面的更新操作，则优先执行读操作。

MySQL提供了几个语句调节符，允许你修改它的调度策略：

LOW\_PRIORITY关键字应用于：DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。  
HIGH\_PRIORITY关键字应用于：SELECT、INSERT语句。  
DELAYED(延迟)关键字应用于：INSERT、REPLACE语句。  
如果你希望所有支持LOW\_PRIORITY选项的语句都默认地按照低优先级来处理，那么可能使用low-priority-updates选项来启动服务器。然后可通过使用insert HIGH\_PRIORITY table.....来把个别我们希望的INSERT语句提高到正常的写入优先级。

七、意向锁  
为什么没有意向锁的话，表锁和行锁不能共存？

举个粟子（此时假设行锁和表锁能共存）： 事务A锁住表中的一行（写锁）。事务B锁住整个表（写锁）。

但你就会发现一个很明显的问题，事务A既然锁住了某一行，其他事务就不可能修改这一行。这与”事务B锁住整个表就能修改表中的任意一行“形成了冲突。所以，没有意向锁的时候，行锁与表锁共存就会存在问题！

意向锁是如何让表锁和行锁共存的？

有了意向锁之后，前面例子中的事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。

所以，意向锁的作用就是：

当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。

注：意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

意向锁是表锁还是行锁？

首先可以肯定的是，意向锁是表级别锁。意向锁是表锁是有原因的。

当我们需要给一个加表锁的时候，我们需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则我们直接判断一次就知道表中是否有数据行被锁定了。

[20200708153312718.png]: /images/20230209/2b2f96bdc77c4700820d724b2b9976cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU5ODY4Mg_size_16_color_FFFFFF_t_70]: /images/20230209/3136f1b5479c46df9cab77d3f17338ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU5ODY4Mg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200322210448910.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU5ODY4Mg==,size_16,color_FFFFFF,t_70