Mysql中的三类锁,你知道吗?

2023-11-23 16:10
文章标签 mysql 知道 database 三类

本文主要是介绍Mysql中的三类锁,你知道吗?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

导读

正所谓有人(锁)的地方就有江湖(事务),人在江湖飘,怎能一无所知?

今天不聊江湖,来细说一下Mysql中的三类锁,分别是全局锁、表级锁、行级锁。

全局锁

全局锁简单的说就是锁住整个数据库实例,命令是Flush tables with read lock 。当你需要为整个数据库处于只读的状态的时候,可以使用这个命令。

一旦使用全局锁,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。 

全局锁的使用场景大部分都是用来数据库备份。

1. 为什么备份要加全局锁?

用户买东西,首先会从余额里扣除金额,然后在订单里添加商品。如果备份数据库,不加锁,并且备份顺序为先备份用余额,再备份订单商品,有可能备份了用户余额后,用户下订单买东西提交事务,然后再备份订单商品表, 此时订单商品已存在。最后备份出来的数据为。最后用户余额为买东西前的余额,没有减少,但是订单商品却多了。演示如下图:

这种情况可能用户会觉得赚了,但是如果备份顺序反过来,先备份商品表再备份余额表,用户就会发现我付了钱,但是商品没有加,这中结果就会更加的严重。 

因此保证备份数据的一致性很重要,必要的手段就是加锁。

2. 全局锁有什么坏处?

全局锁是个啥?介绍完了读者心里已经有数了,让这个库只读?这是多么可怕的操作,简单列举几个危险之处:

  1.  如果在主库备份,备份期间不能执行任何更新操作,会导致整个业务停摆,高并发情况下更甚。

  2.  如果你在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。

3. 全局备份比较好的解决方案

全局锁远瞅不错,近瞅吓一跳,陈某在此不推荐使用。

其实 官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。 

一致性备份是好,但前提是存储引擎支持事务,这也是MyISAM被InnoDB取代的原因之一。

表级锁

MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。 


表锁一般是在数据库引擎不支持行锁的时候才会被用到的 。

MDL会直到事务提交才释放,在做表结构变更的时候,你一定要小心不要导致锁住线上查询和更新 。

1. 如何加表锁

显式加表锁和解锁的语句很简单,如下:

lock tables tb_name read/write;unlock tables;

需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write 这个语句,则其他线程写t1、读写t2的语句都会被阻塞。同时,线程A在执行unlock tables之前,也只能执行读t1、读写t2的操作。连写t1都不允许,自然也不能访问其他表。 

2. MDL

MDL不需要显式使用,在访问一个表的时候会被自动加上。 

当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。 

读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。 

3. 查询表级锁争用

查询表级锁的争用可以通过以下参数分析获得

Table_locks_immediate能够立即获得表级锁的次数 
Table_locks_waited不能立即获取表级锁而需要等待的次数 

查询语句如下:

show status like 'table_locks_waited'

如果Table_locks_waited 的值比较大,则说明存在着较严重的表级锁争用情况。

行级锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB是支持行锁的,这也是MyISAM被InnoDB替代的重要原因之一。 

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。 

在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。

行级锁分为排它锁(写锁)、共享锁(读锁)、间隙锁。

1. 排他锁

排他锁,也称写锁,独占锁,当前写操作没有完成前,它会阻断其他写锁和读锁。 

Mysql中的更新语句(update/delete/insert)会自动加上排它锁。

 如上图,事务B中的update语句被阻塞了,直到事务A提交才能执行更新操作。

排他锁也可以手动添加,如下:

select * from user where id=1 for update;

注意以下两点:

1. 行锁是针对索引加锁的,上述例子中id是主键索引。

2. 加了排他锁并不是其他的事务不能读取这行的数据,而是不能再在这行上面加锁了。

2. 间隙锁

当我们用范围条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做"间隙(GAP)"。InnoDB也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。 

如上图,给id>5中并不存在的数据加上了间隙锁,当插入id=6的数据时被阻塞了。

这是一个:若执行的条件是范围过大,则InnoDB会将整个范围内所有的索引键值全部锁定,很容易对性能造成影响。

3. 共享锁

共享锁,也称读锁,多用于判断数据是否存在,多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作,很可能会造成死锁。如下图所示。

4. 分析行锁定

通过检查InnoDB_row_lock 状态变量分析系统上的行锁的争夺情况 。

show status like 'innodb_row_lock%'
innodb_row_lock_current_waits当前正在等待锁定的数量。
innodb_row_lock_time从系统启动到现在锁定总时间长度;非常重要的参数
innodb_row_lock_time_avg每次等待所花平均时间;非常重要的参数。
innodb_row_lock_time_max从系统启动到现在等待最常的一次所花的时间; 
innodb_row_lock_waits系统启动后到现在总共等待的次数;非常重要的参数。直接决定优化的方向和策略。 

5. 死锁解决方案

  1. 直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置,默认50秒。注意超时时间不能设置太短,如果仅仅是短暂的等待,一旦设置时间很短,很快便解锁了,会出现误伤。

  2. 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑,默认开启。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。当并发很高的时候,检测死锁将会消耗大量的资源,因此控制并发也是很重要的一种策略。

诚邀读者进群

陈某的公众号开通还没到一个月,分享了二十多篇原创文章,每一篇文章都是陈某熬夜写出来的,原创不易,且读且珍惜。

由于公众号关闭了留言功能,很多读者有疑问不能及时解惑,因此陈某创建了一个微信交流群,群内交流氛围很好,有兴趣的可以加一下。

往期推荐

Mysql性能优化:什么是索引下推?

Mysql性能优化:为什么要用覆盖索引?

一条SQL查询语句是如何执行的?

Spring解决循环依赖,你真的懂了吗?

老司机带你从源码开始撸Spring生命周期!!!

老司机带你聊聊接口限流!!!

这篇关于Mysql中的三类锁,你知道吗?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/419115

相关文章

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们