MySQL 锁的相关知识总结

MySQL中的锁

锁是为了解决并发环境下资源竞争的手段,其中乐观并发控制,悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段(具体可见我之前的文章),而MySQL中的锁就是其中的悲观并发控制。

MySQL中的锁有很多种类,我们可以按照下面方式来进行分类。

按读写

从数据库的读写的角度来分,数据库的锁可以分为分为以下几种:

  • 独占锁:又称排它锁、X锁、写锁。X锁不能和其他锁兼容,只要有事务对数据上加了任何锁,其他事务就不能对这些数据再放置X了,同时某个事务放置了X锁之后,其他事务就不能再加其他任何锁了,只有获取排他锁的事务是可以对数据进行读取和修改。
  • 共享锁:又称读锁、S锁。S锁与S锁兼容,可以同时放置。
  • 更新锁:又称U锁。它允许再加S锁,但不允许其他事务再施加U锁或X锁,当被读取的数据要被更新时,则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务,并同时确保事务A自从上次读取数据后数据没有被更改,因此可以减少X锁和S锁的冲突,同时避免使用S锁后再升级为X锁造成的死锁现象。注意,MySQL并不支持U锁,SQLServer才支持U锁。

兼容性矩阵如下(+ 代表兼容, -代表不兼容)

右侧是已加的锁 X S U
X - - -
S - + +
U - + -

按粒度

MySQL支持不同级别的锁,其锁定的数据的范围也不同,也即我们常说的锁的粒度。MySQL有三种锁级别:行级锁、页级锁、表级锁。不同的存储引擎支持不同的锁粒度,例如MyISAM和MEMORY存储引擎采用的是表级锁,页级锁仅被BDB存储引擎支持,InnoDB存储引擎支持行级锁和表级锁,默认情况下是采用行级锁。

特点

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。数据库引擎总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁从而避免死锁。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。行锁总是逐步获得的,因此会出现死锁。
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

下面详细介绍行锁和表锁,页锁由于使用得较少就不介绍了。

行锁

按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的,Innodb一定存在聚簇索引,行锁最终都会落到聚簇索引上,通过非聚簇索引查询的时候,先锁非聚簇索引,然后再锁聚簇索引。如果一个where语句里面既有聚簇索引,又有二级索引,则会先锁聚簇索引,再锁二级索引。由于是分步加锁的,因此可能会有死锁发生。

MySQL的行锁对S、X锁上做了一些更精确的细分,使得行锁的粒度更细小,可以减少冲突,这就是被称为“precise mode”的兼容矩阵。(该矩阵没有出现在官方文档上,是有人通过Mysql lock0lock.c:lock_rec_has_to_wait源代码推测出来的。)

行锁兼容矩阵

  • 间隙锁(Gap Lock):只锁间隙,前开后开区间(a,b),对索引的间隙加锁,防止其他事务插入数据。
  • 记录锁(Record Lock):只锁记录,特定几行记录。
  • 临键锁(Next-Key Lock):同时锁住记录和间隙,前开后闭区间(a,b]。
  • 插入意图锁(Insert Intention Lock):插入时使用的锁。在代码中,插入意图锁,实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。
右侧是已加的锁(+ 代表兼容, -代表不兼容) G R N I
G + + + +
R + +
N + +
I + +

S锁和S锁是完全兼容的,因此在判别兼容性时不需要对比精确模式。精确模式的检测,用在S、X和X、X之间。从这个矩阵可以看到几个特点:

  • INSERT操作之间不会有冲突:你插入你的,我插入我的。
  • GAP,Next-Key会阻止Insert:插入的数据正好在区间内,不允许插入。
  • GAP和Record,Next-Key不会冲突
  • Record和Record、Next-Key之间相互冲突。
  • 已有的Insert锁不阻止任何准备加的锁。
  • 间隙锁(无论是S还是X)只会阻塞insert操作。

注意点

  • 对于记录锁,列必须是唯一索引列或者主键列,查询语句必须为精确匹配,如“=”,否则记录锁会退化为临键锁。
  • 间隙锁和临键锁基于非唯一索引,在唯一索引列上不存在间隙锁和临键锁。

表锁与锁表的误区

只有正确通过索引条件检索数据(没有索引失效的情况),InnoDB才会使用行级锁,否则InnoDB对表中的所有记录加锁,也就是将锁住整个表。注意,这里说的是锁住整个表,但是Innodb并不是使用表锁来锁住表的,而是使用了下面介绍的Next-Key Lock来锁住整个表。网上很多的说法都是说用表锁,然而实际上并不是,我们可以通过下面的例子来看看。

假设我们有以下的数据(MySQL8):

mysql> select * from users;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | a  | 1  |
| 2 | a  | 1  |
| 3 | a  | 1  |
| 4 | a  | 1  |
| 5 | a  | 1  |
+----+------+-----+

方法一:

我们使用表锁锁表,并查看引擎的状态

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables users write;
Query OK, 0 rows affected (0.00 sec)

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4863
Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479760456232, not started
mysql tables in use 1, locked 1  ###############注意这里
0 lock struct(s), heap size 1136, 0 row lock(s)
...

然后我们再通过非索引的字段查询来加锁,并查看引擎的状态

## 先解锁上次的表锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4864
Purge done for trx's n:o < 4862 undo n:o < 0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4863, ACTIVE 37 sec
2 lock struct(s), heap size 1136, 6 row lock(s)  ###############注意这里
...

然后我们再删除id为2,3,4的数据,然后在通过非索引的字段查询来加锁,并查看引擎的状态

mysql> delete from users where id in (2,3,4);
Query OK, 3 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4870
Purge done for trx's n:o < 4869 undo n:o < 0 state: running but idle
History list length 914
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4869, ACTIVE 9 sec
2 lock struct(s), heap size 1136, 3 row lock(s)  ###############注意这里
...

可以看到这里使用了表锁和因为没法用索引锁定特定行而转而锁住整个表是不一样的。从第二次和第三次的操作来看,lock住的row也是不同的,这是因为两者间隙的个数不同,所以可以看到使用的并不是表锁,而是Next-Key Lock。第一次锁住了(-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞],第二次锁住了(-∞,1],(1,5],(5,∞]。

方法二:

也可以通过以下语句来查看锁的信息,也可以知道用的是行锁,且是锁住了区间(插入不了数据)和记录,所以是Next-Key Lock。

mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (你的事务id);
+-----------------------+-----------+-----------+
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
+-----------------------+-----------+-----------+
|         4889 | TABLE   | IX    |
|         4889 | RECORD  | X     |
|         4889 | RECORD  | X     |
|         4889 | RECORD  | X     |
+-----------------------+-----------+-----------+
10 rows in set (0.00 sec)

LOCK_TYPE:对于InnoDB,可选值为 RECORD(行锁), TABLE(表锁)

LOCK_MODE:对于InnoDB,可选值为S[,GAP], X[,GAP], IS[,GAP],IX[,GAP], AUTO_INC和UNKNOWN。除了AUTO_INC和UNKNOWN,其他锁定模式都包含了GAP锁(如果存在)。

具体可见 MySQL文档:https://dev.mysql.com/doc/ref...

表级锁

直接对整个表加锁,影响表中所有记录,表读锁和表写锁的兼容性见上面的分析。

MySQL中除了表读锁和表写锁之外,还存在一种特殊的表锁:意向锁,这是为了解决不同粒度的锁的兼容性判断而存在的。

意向锁

因为锁的粒度不同,表锁的范围覆盖了行锁的范围,所以表锁和行锁会产生冲突,例如事务A对表中某一行数据加了行锁,然后事务B想加表锁,正常来说是应该要冲突的。如果只有行锁的话,要判断是否冲突就得遍历每一行数据了,这样的效率实在不高,因此我们就有了意向表锁。

意向锁的主要目的是为了使得 行锁 和 表锁 共存,事务在申请行锁前,必须先申请表的意向锁,成功后再申请行锁。注意:申请意向锁的动作是数据库完成的,不需要开发者来申请。

意向锁是表级锁,但是却表示事务正在读或写某一行记录,而不是整个表, 所以意向锁之间不会产生冲突,真正的冲突在加行锁时检查。

意向锁分为意向读锁(IS)和意向写锁(IX)。

表锁的兼容性矩阵

右侧是已加的锁(+ 代表兼容, -代表不兼容) IS IX S X
IS + + +
IX + +
S + +
X

以上就是MySQL 锁的相关知识总结的详细内容,更多关于MySQL 锁的资料请关注我们其它相关文章!

(0)

相关推荐

  • mysql查看死锁与去除死锁示例详解

    1.查询进程 show processlist 2. 查询到相对应的进程,然后 kill id 验证(kill后再看是否还有锁) 2.查询是否锁表 show OPEN TABLES where In_use > 0; 示例: 新建一个会话执行如下的显示锁示例 LOCK TABLES account_data.account READ; SELECT SLEEP(160); UNLOCK TABLES account_data.account; 另开启一个会话检查锁表情况: mysql> sho

  • MySQL死锁检查处理的正常方法

    正常情况下,死锁发生时,权重最小的连接将被kill并回滚.但是为了找出语句来优化,启用可启用死锁将死锁信息记录下来. #step 1:窗口一 mysql> start transaction; mysql> update aa set name='aaa' where id = 1; #step 2:窗口二 mysql> start transaction; mysql> update bb set name='bbb' where id = 1; #step 3:窗口一 mysq

  • MySQL中的悲观锁与乐观锁

    在关系型数据库中,悲观锁与乐观锁是解决资源并发场景的解决方案,接下来将详细讲解

  • MySQL 8.0.19支持输入3次错误密码锁定账户功能(例子)

    MySQL 8.0.19支持输入3次错误密码,锁定账户功能 例子: >CREATE USER 'hechunyang'@'localhost' IDENTIFIED BY '123456' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3; FAILED_LOGIN_ATTEMPTS:代表尝试失败的次数 PASSWORD_LOCK_TIME:代表锁定的时间,单位天,这里为锁定3天. 如果设置UNBOUNDED,代表锁定永久,直到手工解锁. 故意输错3次以上

  • mysql 悲观锁与乐观锁的理解及应用分析

    本文实例讲述了mysql 悲观锁与乐观锁.分享给大家供大家参考,具体如下: 悲观锁与乐观锁是人们定义出来的概念,你可以理解为一种思想,是处理并发资源的常用手段. 不要把他们与mysql中提供的锁机制(表锁,行锁,排他锁,共享锁)混为一谈. 一.悲观锁 顾名思义,就是对于数据的处理持悲观态度,总认为会发生并发冲突,获取和修改数据时,别人会修改数据.所以在整个数据处理过程中,需要将数据锁定. 悲观锁的实现,通常依靠数据库提供的锁机制实现,比如mysql的排他锁,select .... for upd

  • MySQL数据库锁机制原理解析

    在并发访问情况下,很有可能出现不可重复读等等读现象.为了更好的应对高并发,封锁.时间戳.乐观并发控制(乐观锁).悲观并发控制(悲观锁)都是并发控制采用的主要技术方式. 锁分类 ①.按操作划分:DML锁,DDL锁 ②.按锁的粒度划分:表级锁.行级锁.页级锁 ③.按锁级别划分:共享锁.排他锁 ④.按加锁方式划分:自动锁.显示锁 ⑤.按使用方式划分:乐观锁.悲观锁 乐观锁和悲观锁 乐观并发控制和悲观并发控制是并发控制采用的主要方法.乐观锁和悲观锁不仅在关系数据库里应用,在Hibernate.Memca

  • MySQL 行锁和表锁的含义及区别详解

    一.前言 对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答. MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎.MyISAM不支持行锁,而InnoDB支持行锁和表锁. 相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制. MySQL大致可归纳为以下3种锁: 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生锁冲突的概率

  • 通过实例判断mysql update是否会锁表

    两种情况: 1.带索引 2.不带索引 前提介绍: 方式:采用命令行的方式来模拟 1.mysq由于默认是开启自动提交事务,所以首先得查看自己当前的数据库是否开启了自动提交事务. 命令:select @@autocommit; 结果如下: +----–+ | @@autocommit | +----–+ | 0 | +----–+ 如果是1,那么运行命令:set autocommit = 0;设置为不开启自动提交 2.当前的数据库表格式如下 tb_user | CREATE TABLE tb_use

  • mysql查询表是否被锁的方法

    具体方法: (推荐教程:mysql数据库学习教程) 查看表被锁状态 # 查询哪些表锁了 show OPEN TABLES where In_use > 0; 查看造成死锁的sql语句 # 查询innodb引擎的运行时信息 show engine innodb status; 查询进程 # 查询所有进程 show processlist; 解锁(删除进程) # 删除进程 kill id; 查看正在锁的事物 # 查看正在锁的事物 SELECT * FROM INFORMATION_SCHEMA.IN

  • Mysql事务中Update是否会锁表?

    两种情况: 1.带索引 2.不带索引 前提介绍: 方式:采用命令行的方式来模拟 1.mysq由于默认是开启自动提交事务,所以首先得查看自己当前的数据库是否开启了自动提交事务. 命令:select @@autocommit; 结果如下: +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 如果是1,那么运行命令:set autocommit = 0;设置为不开启自动提交 2.当前的数据库表格式如下 tb

随机推荐