您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL进阶之MySQL中的锁

时间:2020-07-08 09:48:13  来源:  作者:

概述

锁是计算机协调多个进程或纯线程并发访问某一资源的机制,这些资源包括CPU、内存、I/O等,而在数据库中,数据也是一种供许多用户(进程/线程)共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题,数据库的锁便是解决这个问题的重要工具。数据库的事务具有4个属性,即ACID,其中C即是一致性,所以在数据库中,锁和事务总是息息相关的。

MySQL中的锁

在MySQL中,不同的存储引擎,可能支持的锁会有所不同。下面的内容主要围绕使用比较多的InnoDB和MyISAM进行描述。

MySQL中的锁,从粒度即范围划分为3类:全局锁、表级锁、行级锁。实际上,其它的如BDB存储引擎还有页级锁,这里不做介绍。

  • 全局锁

锁住整个数据库实例,使得整个库处于只读状态,会阻塞DML和DDL语句,由MySQL的SQL层实现。可以使用以下命令为数据库加全局锁(简称FTWRL)

FLUSH TABLES WITH READ LOCK;

释放全局锁命令如下

UNLOCK TABLES;

此外,在客户端异常断开后,全局锁会自动释放。全局锁的典型使用场景是,做全库逻辑备份(mysqldump)。

  • 表级锁

针对当前操作的整张表加锁,实现简单,消耗资源较少,InnoDB和MyISAM均支持表级锁。表级锁分为表锁和MySQL5.5版本引入的元数据锁(MDL)两种。MDL不需要显式使用,在访问或操作一个表的时候会被自动加上。当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。 MDL可以避免读写数据时突然变更了表结构等元数据而导致出现问题。

相关表级锁的一些命令

-- 手动增加表锁(READ为共享读锁,WRITE为独占写锁)
LOCK TABLE 表名 READ | WRITE;
-- 查看表锁情况
SHOW OPEN TABLES;
-- 删除表锁
UNLOCK TABLES;

表级锁从资源管理角度又分为表共享读锁(共享锁)和表独占写锁(排他锁)两种形式。如果对数据表加了共享读锁,当前会话只能读取加锁的表数据,同时不能增删改(报错),也不能读取其它表数据;其它的会话则可以读取加锁的表数据,增删改将被阻塞,但可以操作其它表数据。

表共享读锁的演示

打开一个客户端会话1,对tdep表加共享读锁,会话1可以读取tdep表的数据,但不能进行增删改,同时也不能操作其它表。

MySQL进阶之MySQL中的锁

会话1对tdep表加共享读锁的表现

此时打开另外一个客户端会话2,可以读取tdep表数据,对tdep表进行增删改将被阻塞,但可以操作其它数据表。

MySQL进阶之MySQL中的锁

其它会话表现

表独占写锁演示

打开一个客户端会话1,对tdep表加独占写锁,会话1可以对tdep表进行增删改查,但依旧不可以操作其它表。

MySQL进阶之MySQL中的锁

会话1对tdep表加独占写锁的表现

打开另外一个客户端会话2,会话可以操作其它数据表,但对tdep任何的操作包括select查询都会被阻塞。

MySQL进阶之MySQL中的锁

其它会话的表现

查看表级锁定的争用状态

mysql> SHOW STATUS LIKE  'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 99    |
| Table_locks_waited    | 0     |
+-----------------------+-------+

其中table_locks_immediate表示产生表级锁定的次数;table_locks_waited表示出现表级锁定争用而发生等待的次数。

  • 行级锁

针对某行数据加锁(也可能锁定行之间的间隙),是一种排他锁,防止其它事务修改此行。部分存储引擎支持,比如InnoDB,下面主要讲的就是InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的,因此InnoDB这种行锁实现特点意味着:只有通过
索引条件检索的数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁(自动使用表锁,不需要人员干预)!

InnoDB的行级锁,按照锁定范围来说,分为三种:

记录锁(Record Locks),锁定索引中一条记录;间隙锁(Gap Locks),要么锁住索引记录中间的值,要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值,其用于防止插入或更新间隙内的数据;Next-Key锁,是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

按照功能来说,分为两种:

共享锁(S),允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁;排他锁(X)允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于INSERT、UPDATE、DELETE语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,事务可以通过以下语句显示给记录集加共享锁或排他锁。

-- 使用LOCK IN SHARE MODE手动添加共享锁(S)
SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;

-- 使用FOR UPDATE手动添加排他锁(x)
SELECT * FROM table_name WHERE condition FOR UPDATE;

使用COMMIT或ROLLBACK语句释放锁。

查看行级锁争用状态

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

其中第2、3、5项是优化或分析问题的比较常用指标

Innodb_row_lock_current_waits:当前正在等待锁定的数量;

Innodb_row_lock_time:从系统启动到现在锁定总时间长度;

Innodb_row_lock_time_avg:每次等待所花平均时间;

Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;

Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

行锁基本演示

使用2个会话分别连接MySQL服务器,首先使用会话1更新tdep表中id为3的记录,其中id为主键,有索引,所以会自动为该记录加上排他锁(X),示例中关闭了自动提交,以验证行锁的效果。

MySQL进阶之MySQL中的锁

会话1的表现

此时会话2可以更新其它记录,也可以读取id为3的记录,但更新id为3的记录将被阻塞

MySQL进阶之MySQL中的锁

其它会话的表现

行读锁(共享锁)演示

使用会话1为tdep表中id为3的记录加上共享锁,基本上可以做任何的操作

MySQL进阶之MySQL中的锁

会话1加行读锁的表现

会话2可以访问其它表的数据以及未锁定的其它行,id为3的行可以读取但不能更新

MySQL进阶之MySQL中的锁

其它会话的表现

行写锁(排他锁)演示

会话1对tdep表中id为3的记录加了排他锁,基本上可以做任何的操作

MySQL进阶之MySQL中的锁

会话1加行写锁的表现

会话2可以访问tdep表,但不能该记录进行更新或删除,同时也不能对该记录进行加共享锁或排他锁。

MySQL进阶之MySQL中的锁

其它会话的表现

  • 表级锁和行级锁的比较

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;

总结

关于InnoDB的行级锁,这里主要介绍了记录锁。InnoDB行级锁是通过给索引实现的,只有通过索引条件检索的数据,InnoDB才使用行级锁,否则,InnoDB将自动使用表锁。

对于INSERT、UPDATE、DELETE语句,InnoDB会自动给涉及数据记录加排他锁(X),这意味着其它会话不能再对这些记录进行INSERT、UPDATE、DELETE操作以及使用LOCK IN SHARE MODE或FOR UPDATE对涉及的数据记录进行加读锁或写锁,非要操作将被阻塞;

对于普通SELECT语句,InnoDB不会加任何锁,但可以通过LOCK IN SHARE MODE或FOR UPDATE语句显示给记录集加共享锁或排他锁。

一条记录被加了排他锁之后,其它会话不能再对该记录加锁,包括共享锁和排他锁。但如果一条记录加的是共享锁,则其它会话可以对该记录加共享锁,但不能加排他锁。即一条记录可以同时被多个会话加共享锁,但只能被一个会话加排他锁。

不同于表的独占写锁,行的排他锁可以允许其它会话读取行记录,而表的独占写锁则不允许其它会话读取表中的记录。即行级锁作用是限制了其它会话对加锁的记录进行增删改,但不限制读取。



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
前言读锁写锁意向锁,表锁行锁页面锁。在学习Java并发编程的时候,肯定少不了学习锁。最常见的就是synchronized,锁的概念不是很好理解,有的地方说是锁住了一段代码,有的地方说是锁...【详细内容】
2020-09-04  Tags: MySQL   点击:(103)  评论:(0)  加入收藏
1.1 锁概述锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。在数据库中,除传统的计算资源(如 CPU、RAM、I/O 等)的争用以外,数据也是一种供许多用户共享的资源。...【详细内容】
2020-07-24  Tags: MySQL   点击:(60)  评论:(0)  加入收藏
概述锁是计算机协调多个进程或纯线程并发访问某一资源的机制,这些资源包括CPU、内存、I/O等,而在数据库中,数据也是一种供许多用户(进程/线程)共享的资源。如何保证数据并发访问...【详细内容】
2020-07-08  Tags: MySQL   点击:(48)  评论:(0)  加入收藏
疫情期间在家工作时,同事使用了 insert into on duplicate key update 语句进行插入去重,但是在测试过程中发生了死锁现象:ERROR 1213 (40001): Deadlock found when trying t...【详细内容】
2020-06-17  Tags: MySQL   点击:(82)  评论:(0)  加入收藏
概述很多时候在mysql处理死锁问题时,由于show engine innodb status输出来的死锁日志无任务事务上下文,并不能很好地诊断相关事务所持有的所有锁信息,包括:锁个数、锁类型等。下...【详细内容】
2019-09-27  Tags: MySQL   点击:(318)  评论:(0)  加入收藏
什么是数据库锁数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定...【详细内容】
2019-09-16  Tags: MySQL   点击:(143)  评论:(0)  加入收藏
锁:计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用外,数据也是一种供许多用户共享的资源。如何保证数据并发的一致性...【详细内容】
2019-09-09  Tags: MySQL   点击:(155)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条