MySQL锁机制详解-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_46596508/article/details/117994283

4mysql锁机制

【有利有弊】

4.1锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢资源）。
在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。肯定要涉及***事务***

4.2锁分类

从对数据操作的粒度分：

1）表锁：操作时，会锁定整个表。【偏读】
2）行锁：操作时，会锁定当前操作行。【偏写】
从对数据操作的类型分：
1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。 2）写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

4.3三锁

相对其他数据库而言，MySQL的锁机制比较简单，其**最显著的特点是不同的存储引擎支持不同的锁机制。**下表中罗列出了各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持（默认）	不支持
MEMORY	支持	不支持	不支持
RDB	支持	不支持	支持

MySQL这3种锁的特性可大致归纳如下：

锁类型	特点
表锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高
页锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

4.3.1MyISAM表锁【偏读】

4.3.1.1特点

特点：偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。MyISAM 存储引擎只支持表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁： lock table table_name read;
加写锁： lock table table_name write；

4.3.1.2案例分析：

【加锁速度，死锁，粒度，并发性能】

【MyISAM存储引擎的写阻塞读的案例】

session1	session2
给表mylock加写锁lock table tb_book write；[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QZhxStWc-1623913877870)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616170455198.png)]	等待session1开启写锁以后，session2再连接终端。会话2可以对其他非锁定表进行操作。
在本会话对锁定表的增删改查操作都可以执行，但是不能操作其他表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Di5bEjKt-1623913877873)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616170744957.png)]	其他会话对锁定表的查询被阻塞，需要等待锁被释放[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ctnz9wSO-1623913877880)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616170950043.png)]
将写锁释放unlock tables；	会话2获得锁，查询返回[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A7yiDS0Q-1623913877884)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616171236722.png)]

4.3.1.3案例结论：

Mysql的表级锁有两种形式：

表共享读锁（Table Read Lock）

表独占写锁 (Table Write Lock)

锁类型	可否兼容	读锁	写锁
读锁	可以	是	否
写锁	可以	否	否

对MyISAM表的读操作【加读锁】，不会影响其他进程对同一表的读请求，但会阻塞对同一表的写操作，只有当读锁释放，才会执行其他进程的写操作。
对MyISAM表的写操作【加写锁】，会阻塞其他进程对同一表的读和写操作，只有当写锁释放，才会执行其他进程的读写操作。

总结：读锁会阻塞写，不会阻塞读；写锁把读和写都阻塞。

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。所以MyISAM表锁偏读，如果写的话就会加写锁，加了写锁其它线程不能做操作。

4.3.1.4表锁分析：查看锁的争用情况

1查看哪些表被锁：show open tables;

in_use : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

2分析表锁定：show status like ‘Table_locks%’;

| Variable_name | Value |

| Table_locks_immediate | 86 |

| Table_locks_waited | 1 |

Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。
Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

4.3.2InnoDB行锁【偏写】

4.3.2.1行锁介绍

行锁特点：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。【比如说一个用100行，一个用52行，并不冲突，锁冲突概率低】
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。（支持事务的原因主要也是增加了行级锁）

4.3.2.2复习事务

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

ACID属性	含义
原子性（Atomicity）	事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（Consistent）	在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。
持久性（Durable）	事务完成之后，对于数据的修改是永久的。

并发事务处理带来的问题

问题	含义
丢失更新（Lost Update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖
脏读（Dirty Reads）	当一个事务正在访问数据，并且对数据进行了修改，而这种已经修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。【不符合一致性】脏读是修改了数据。
不可重复读（Non- Repeatable Reads）	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。（update）事务A读取到了事务B已经提交的修改数据【不符合隔离性】
幻读（Phantom Reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。（insert）【不符合隔离性】幻读是新增了数据。

事务隔离级别

隔离级别	丢失更新	脏读	不可重复读	幻读
Read uncommitted未提交读【最低级】	解决	不能解决	不能解决	不能解决
Read committed已提交读【语句级】	解决	解决	不能解决	不能解决
Repeatable read可重复读【事务级】	解决	解决	解决	不能解决
Serializable可序列化【事务级】	解决	解决	解决	解决

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

Mysql 的数据库的默认隔离级别为 Repeatable read ，所以默认情况下可能会出现幻读查看方式：

show variables like ‘tx_isolation’;

4.3.2.3案例

建表

create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

行锁定基本演示

会话1	会话2
设置事务不自动提交set autocommit=0;	设置事务不自动提交set autocommit=0
会话1客户端可以正常查询数据[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RYd6OUH6-1623913877887)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616221419275.png)]	会话2客户端可以正常查询数据[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JDPzWJcf-1623913877888)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616221422831.png)]
更新但不提交，自己查的话是更新的【读己之所写】[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9IY03j7i-1623913877890)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616221521268.png)]	会话2查不到会话1更新的，还是原来的。【如果做查询操作】
	也执行更新这条数据，被阻塞，只能等待【如果做更新操作】[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j2SoKWCN-1623913877892)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210616221548763.png)]
提交更新commit；	会话2commit之后（因为设置了不能手动提交，查询也需要手动提交才有结果）才可以查到会话1更新的值【查询操作对应的】；会话1提交后，解除阻塞，会话2不需要提交，更新正常进行【更新操作对应的】，最后在提交
	commit；提交之后
更新不同行数据，会话1更新第三行	会话2更新第9行，不会发生阻塞

这个时候就能看出来行锁的作用了，当客户端一执行更新操作的时候，会自动给该行数据加上锁，在还没有执行commit提交事务的时候，如果**其他线程也要对该条数据进行操作（增删改），那么就会进入阻塞状态。**只有客户端一执行了commit之后，才会释放行锁，其他线程才能进行操作。

无索引行锁升级为表锁

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

会话1	会话2
关闭事务自动提交	关闭事务自动提交
执行更新语句，更改第四行update test set a=1000 where b=10;注意a是varchar类型	执行更新语句，更新第九行，但处于阻塞状态
提交事务	解除阻塞
	提交事务

分析：ab两个字段都是索引，但是a是varchar类型，这样更新的时候mysql底层会对索引字段自动类型转换导致索引失效。【导致不会走索引】造成了表锁，这样其他线程操作的不是同一行数据也会进入阻塞。

间隙锁危害

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

在范围条件之内的都先占了。

会话1	会话2
关闭事务自动提交	关闭事务自动提交
根据范围执行更新语句update test set a=1000 where b<10;假设b小于10的数据有缺失，比如没有2
	插入b为2的记录，处于阻塞状态
提交事务
	解除阻塞，自动执行插入
	提交事务

间隙锁的危害：当锁定一个范围键值之后，即使某些不存在的简直也会被锁定，造成在锁定的时候无法插入锁定简直范围内的任何数据，在某些场景下对性能造成很大的危害。

面试题：如何锁定一行

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE … FOR UPDATE

4.3.2.4案例结论

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；

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

共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

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

4.3.2.5行锁分析

分析系统上的行锁的争夺情况：

show status like ‘innodb_row_lock%’;

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 95062 |
| Innodb_row_lock_time_avg      | 23765 |
| Innodb_row_lock_time_max      | 51027 |
| Innodb_row_lock_waits         | 4     |
+-------------------------------+-------+

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。