Mysql锁

导读:本篇文章讲解 Mysql锁,希望对大家有帮助,欢迎收藏,转发!站点地址:www.bmabk.com

目录

概述

锁的不同角度分类

一、表级锁

1. 表级别的S锁、X锁

2. 意向锁

3. 自增锁(AUTO-INC锁)

4. 元数据锁(MDL锁)

二、行级锁

1. 记录锁

2. 间隙锁

3. 临键锁

4. 插入意向锁

三、页锁


概述

        在数据库中,除传统的计算资源(如CPU

RAM

I/O
等)的争用以外,数据也是一种供许多用户共享的 资源。为保证数据的一致性,需要对
并发操作进行控制
,因此产生了

。同时
锁机制
也为实现
MySQL 的各个隔离级别提供了保证。
锁冲突
也是影响数据库
并发访问性能
的一个重要因素。所以锁对数据库而言显得尤其重要,也更加复杂。

锁的不同角度分类

为了尽可能提高数据库的并发度,每次锁定的数据范围越小越好,理论上每次只锁定当前操作的数据的方案会得到最大的并发度,但是管理锁是很 耗资源 的事情(涉及获取、检查、释放锁等动作)。因此数据库系统需要在高并发响应系统性能 两方面进行平衡,这样就产生了“锁粒度(Lock granularity)”的概念。

对一条记录加锁影响的也只是这条记录而已,我们就说这个锁的粒度比较细;其实一个事务也可以在 表级别 进行加锁,自然就被称之为 表级锁 或者 表锁,对一个表加锁影响整个表中的记录,我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁,页级锁行锁。

Mysql中的锁根据锁粒度划分大致可分为以下三种:

  • 表级锁
    • 表级别的S锁、X锁
    • 意向锁
    • 自增锁
    • MDL锁
  • 行级锁
    • Record Locks
    • Gap Locks
    • Next-Key Locks
    • 插入意向锁
  • 页级锁

示意图如下:

Mysql锁

一、表级锁

1.表级别的S锁、X锁

        一般情况下,不会使用InnoDB
存储引擎提供的表级别的
S


X
锁 ,
而会选择力度更小的
行级锁
。只会在一些特殊情况下,比方说
崩溃恢复
过程中用到。比如,在系统变量
autocommit=0

innodb_table_locks = 1
时,
手动
获取。InnoDB存储引擎提供的表
t

S

或者
X

可以这么写:
  • LOCK TABLES teacher READ InnoDB存储引擎会对表 teacher 加表级别的 S
  • LOCK TABLES teacher WRITE InnoDB存储引擎会对表 teacher 加表级别的 X
        不过尽量避免在使用InnoDB
存储引擎的表上使用
LOCK TABLES
这样的手动锁表语句,因为它们只会降低并发能力而不能提供额外的保护。InnoDB
的厉害之处还是实现了更细粒度的
行锁
,关于InnoDB表级别的
S


X

大家了解即可。

2.意向锁

InnoDB
支持
多粒度锁(
multiple granularity locking

,即允许多种粒度的锁共存,比如 
行级锁

表级锁
共存,而
意向

就是其中的一种
表锁
。而意向锁的存在也使得行级锁的性能更上一层楼
  1. 意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。
  2. 意向锁是一种 不与行级锁冲突表级锁,这一点非常重要。
  3. 表明“某个事务正在某些行持有了锁或该事务准备去持有锁”

① 意向锁分为两种:

  • 意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享锁S锁)
-- 事务要获取某些行的 S 锁,必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;
  • 意向排他锁(intention exclusive lock, IX):事务有意向对表中的某些行加排他锁X锁)
-- 事务要获取某些行的 X 锁,必须先获得表的 IX 锁。 
SELECT column FROM table ... FOR UPDATE;
即:意向锁是由存储引擎
自己维护的
,用户无法手动操作意向锁,在为数据行加共享
/
排他锁之前, InooDB 会先获取该数据行
所在数据表的对应意向锁

② 意向锁解决的问题:

如果我们给某一行数据加上了排他锁,数据库会自动给更大一级空间,比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排他锁了。

举例:在数据库数据量大时,想给某个表加锁时无需一行一行查找是否上锁,只需查看表上是否有意向锁。

③ 演示:

创建表teacher,并插入数据

Mysql锁

开启一个事务:

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

#下面的查询语句为id为5的行加上了X锁,同时teacher表上会加上意向锁IX
mysql> select * from teacher where id = 5 for update;
+----+--------+
| id | name   |
+----+--------+
|  5 | 钱七   |
+----+--------+
1 row in set (0.00 sec)

开启另一个事务:

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

#以下命令想在teacher表上添加S锁,在新的事务中执行发现阻塞
mysql> lock tables teacher read;

因为在表中某一行上加了X/S锁后表自动加上意向锁IX/IS,所以在表上添加S锁或者X锁都会被阻塞

④ 意向锁的兼容互斥性

意向锁之间是相互兼容的,虽然意向锁和意向锁互相兼容,但是会与表级别的普通的排他/共享锁互斥。

Mysql锁

 这里的共享锁和排他锁指的是表级别的,意向锁不会与行级别的共享/排他锁互斥。Mysql锁

意向锁不会与行级的共享
/
排他锁互斥!正因为如此,意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。

3. 自增锁(AUTO-INC锁)

① 举例:

我们创建老师表的同时为id字段加上自增

CREATE TABLE `teacher` ( 
`id` int NOT NULL AUTO_INCREMENT, 
`name` varchar(255) NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

这意味着我们在插入时可以不指定id的值,插入SQL语句如下:

INSERT INTO teacher(name) VALUES('张三'),('李四');
上边的插入语句并没有为
id
列显式赋值,所以系统会自动为它赋上递增的值,结果如下所示。

Mysql锁

② 插入模式

上面我们演示的只是一种简单的插入模式,所有插入数据的方式总共分为三类,分别是


Simple inserts



Bulk inserts



Mixed

mode inserts

1. “Simple inserts” (简单插入)

        我们上面演示的就是简单插入,即可预先确定要插入的行数 (当语句被初始处理时)的语句。包括没有嵌套子查询的单行和多行 INSERT…VALUES() REPLACE 语句。

2. “Bulk inserts”
(批量插入)
         
比如
INSERT … SELECT , REPLACE … SELECT

LOAD DATA
这种 事先不知道要插入的行数

(和所需自动递增值的数量)的语句。但不包括纯INSERT

InnoDB
在每处理一行,为
AUTO_INCREMENT
列 分配一个新值。
3. “Mixed-mode inserts”
(混合模式插入)
        这些是“Simple inserts”
语句但是指定部分新行的自动递增值。例如
INSERT INTO teacher(id,name) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);
只是指定了部分
id
的值。另一种类型的

混合模式插入”

INSERT … ON DUPLICATE KEY UPDATE

③ 锁定模式

innodb_autoinc_lock_mode
有三种取值,分别对应与不同锁定模式:

1

innodb_autoinc_lock_mode = 0(“
传统

锁定模式
)
        在此锁定模式下,所有类型的insert
语句都会获得一个特殊的表级
AUTO-INC
锁,用于插入具有 AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子,即每当执行
insert
的时候,都会得到一个 表级锁(AUTO-INC

)
,使得语句中生成的
auto_increment
为顺序,且在
binlog
中重放的时候,可以保证 master与
slave
中数据的
auto_increment
是相同的。因为是表级锁,当在同一时间多个事务中执行
insert
的 时候,对于AUTO-INC
锁的争夺会
限制并发
能力。

2

innodb_autoinc_lock_mode = 1(“
连续

锁定模式
)
        在 MySQL 8.0
之前,连续锁定模式是
默认
的。
        在这个模式下,“bulk inserts”
仍然使用
AUTO-INC
表级锁,并保持到语句结束。这适用于所有
INSERT … SELECT,
REPLACE … SELECT

LOAD DATA
语句。同一时刻只有一个语句可以持有
AUTO-INC
锁。
        对于“Simple inserts”
(要插入的行数事先已知),则通过在
mutex
(轻量锁)
的控制下获得所需数量的 自动递增值来避免表级AUTO-INC
锁, 它只在分配过程的持续时间内保持,而不是直到语句完成。不使用 表级AUTO-INC
锁,除非
AUTO-INC
锁由另一个事务保持。如果另一个事务保持
AUTO-INC
锁,则
“Simple inserts”等待
AUTO-INC
锁,如同它是一个
“bulk inserts”

3

innodb_autoinc_lock_mode = 2(“
交错

锁定模式
)
        从 MySQL 8.0
开始,交错锁模式是
默认
设置。
        在此锁定模式下,自动递增值
保证
在所有并发执行的所有类型的
insert
语句中是
唯一

单调递增
的。但是,由于多个语句可以同时生成数字(即,跨语句交叉编号),
为任何给定语句插入的行生成的值可能
不是连续的。

4. 元数据锁(MDL锁)

        MySQL5.5引入了
meta data lock
,简称
MDL
锁,属于表锁范畴。
MDL
的作用是,保证读写的正确性。比如,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个
表结构做变更
,增加了一 列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。
        因此,
当对一个表做增删改查操作的时候,加
MDL
读锁;当要对表做结构变更操作的时候,加
MDL

锁。
① 举例
开启一个事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#表上会自动加上MDL读锁
mysql> select * from teacher;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
|  4 | 赵六   |
|  5 | 钱七   |
+----+--------+
5 rows in set (0.00 sec)

在另一个事务中改变表的结构,出现阻塞,原因是表上已经有了MDL读锁,此时要加上MDL写锁需要等待MDL读锁的释放:
Mysql锁

 验证:

Mysql锁

② MDL锁的并发性

Mysql锁

 

二、行级锁

行锁(RowLock)也称为记录锁,顾名思义,就是锁住某一行(某条记录row)。需要的注意的是,MySQL服务器层并没有实现行锁机制,行级锁只在存储引擎层实现。

  • 优点:锁定力度小,发生 锁冲突概率低,可以实现的并发度高。
  • 缺点:对于锁的开销比较大,加锁会比较慢,容易出现 死锁 情况。

InnoDB与MvISAM的最大不同有两点:一是支持事务(TRANSACTION);一是采用了行级锁。

1. 记录锁

        记录锁也就是仅仅把一条记录锁上,官方的类型名称为:
LOCK_REC_NOT_GAP
。比如我们把
id
值为
8
的 那条记录加一个记录锁的示意图如图所示。仅仅是锁住了id
值为
8
的记录,对周围的数据没有影响。
Mysql锁
记录锁是有
S
锁和
X
锁之分的,称之为
S
型记录锁

X
型记录锁
  • 当一个事务获取了一条记录的S型记录锁后,其他事务也可以继续获取该记录的S型记录锁,但不可以继续获取X型记录锁;
  • 当一个事务获取了一条记录的X型记录锁后,其他事务既不可以继续获取该记录的S型记录锁,也不可以继续获取X型记录锁。

2.间隙锁

        MySQL

REPEATABLE READ
隔离级别下是可以解决幻读问题的,解决方案有两种,可以使用
MVCC
方案解决,也可以采用
加锁
方案解决。但是在加锁的情况下,对于尚未存在的记录也就谈不上加锁了。

        InnoDB提出了一种称之为
Gap Locks
的锁,官方的类型名称为:
LOCK_GAP
,我们可以简称为
gap

Mysql锁
图中
id
值为
8
的记录加了
gap
锁,意味着
不允许别的事务在
id
值为
8
的记录前边的间隙插入新记录
,其实就是 id列的值
(3, 8)
这个区间的新记录是不允许立即插入的。比如,有另外一个事务再想插入一条
id
值为
4
的新 记录,它定位到该条新记录的下一条记录的id
值为
8
,而这条记录上又有一个
gap
锁,所以就会阻塞插入 操作,直到拥有这个gap
锁的事务提交了之后,
id
列的值在区间
(3, 8)
中的新记录才可以被插入。
gap
锁的提出仅仅是为了防止插入幻影记录而提出的

3.临键锁

        有时候我们既想
锁住某条记录
,又想
阻止
其他事务在该记录前边的
间隙插入新记录
,所以
InnoDB
就提 出了一种称之为
Next

Key Locks
的锁,官方的类型名称为:
LOCK_ORDINARY
,我们也可以简称为
next-
key
锁 ,
相当于记录所与间隙锁的结合

Next-Key Locks
是在存储引擎
innodb
、事务级别在
可重复读
的情况下使用的数据库锁, innodb默认的锁就是
Next-Key locks
        临键锁本质上是记录锁和间隙锁的合体,他能保护该条记录,并防止其他事务将新纪录插入被保护记录前边的间隙。
begin; 
select * from student where id <=8 and id > 3 for update;

4. 插入意向锁

        我们说一个事务在
插入
一条记录时需要判断一下插入位置是不是被别的事务加了
gap


next

key

也包含
gap

),如果有的话,插入操作需要等待,直到拥有
gap

的那个事务提交。但是
InnoDB
定事务在等待的时候也需要在内存中生成一个锁结构
,表明有事务想在某个
间隙

插入
新记录,但是 现在在等待。InnoDB
就把这种类型的锁命名为
Insert Intention Locks
,官方的类型名称为:
LOCK_INSERT_INTENTION
,我们称为
插入意向锁
。插入意向锁是一种
Gap

,不是意向锁,在
insert 操作时产生。
        插入意向锁是在插入一条记录行前,由
INSERT
操作产生的一种间隙锁
        事实上
插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

三、页锁

        页锁就是在
页的粒度
上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录。当我 们使用页锁的时候,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的数据行。
页锁的开销
介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。
        每个层级的锁数量是有限制的,因为锁会占用内存空间,
锁空间的大小是有限的
。当某个层级的锁数量 超过了这个层级的阈值时,就会进行
锁升级
。锁升级就是用更大粒度的锁替代多个更小粒度的锁,比如 InnoDB 中行锁升级为表锁,这样做的好处是占用的锁空间降低了,但同时数据的并发度也下降了。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由半码博客整理,本文链接:https://www.bmabk.com/index.php/post/118512.html

(0)

相关推荐

发表回复

登录后才能评论
半码博客——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!