10年程序员总结的MySQL行级锁、表级锁、页级锁详细介绍

2024-06-03 09:38

本文主要是介绍10年程序员总结的MySQL行级锁、表级锁、页级锁详细介绍,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

【原创】 程序猿的内心独白 2019-02-27 06:28:25

10年程序员总结的MySQL行级锁、表级锁、页级锁详细介绍

 

页级:引擎 BDB。

表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行

行级:引擎 INNODB , 单独的一行记录加锁

表级:直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许

行级:仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。

页级:表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行页级锁定,对InnoDB表进行行级锁定。

对WRITE,MySQL使用的表锁定方法原理如下:

  • 如果在表上没有锁,在它上面放一个写锁。
  • 否则,把锁定请求放在写锁定队列中。

对READ,MySQL使用的锁定方法原理如下:

如果在表上没有写锁定,把一个读锁定放在它上面

否则,把锁请求放在读锁定队列中。

InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,在SQL语句处理期间,InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得。

行级锁定的优点:

  • 当在许多线程中访问不同的行时只存在少量锁定冲突。
  • 回滚时只有少量的更改。
  • 可以长时间锁定单一的行。

行级锁定的缺点:

  • 比页级或表级锁定占用更多的内存。
  • 当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
  • 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
  • 用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

在以下情况下,表锁定优先于页级或行级锁定:

表的大部分语句用于读取。

对严格的关键字进行读取和更新,你可以更新或删除可以用单一的读取的关键字来提取的一行:

UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

DELETE FROM tbl_name WHERE unique_key_col=key_value;

SELECT 结合并行的INSERT语句,并且只有很少的UPDATE或DELETE语句。

在整个表上有许多扫描或GROUP BY操作,没有任何写操作。

/ ========================= mysql 锁表类型和解锁语句 ========================= /

如果想要在一个表上做大量的 INSERT 和 SELECT 操作,但是并行的插入却不可能时,可以将记录插入到临时表中,然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM insert_table;

mysql> TRUNCATE TABLE insert_table;

mysql> UNLOCK TABLES;

行级锁的优点有:

  • 在很多线程请求不同记录时减少冲突锁。
  • 事务回滚时减少改变数据。
  • 使长时间对单独的一行记录加锁成为可能。

行级锁的缺点有:

  • 比页级锁和表级锁消耗更多的内存。
  • 锁是计算机协调多个进程或线程并发访问某一资源的机制,不同的数据库的锁机制大同小异。由于数据库资源是一种供许多用户共享的资源,所以如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。了解锁机制不仅可以使我们更有效的开发利用数据库资源,也使我们能够更好地维护数据库,从而提高数据库的性能。
  • MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。

例如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level-locking);BDB存储引擎采用的是页面锁(page-level-locking),同时也支持表级锁;InnoDB存储引擎既支持行级锁,也支持表级锁,默认情况下是采用行级锁。

上述三种锁的特性可大致归纳如下:

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

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

3) 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

三种锁各有各的特点,若仅从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如WEB应用;行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

MySQL表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write

Lock)。什么意思呢,就是说对MyISAM表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞

对同一表的写操作;而对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作。

MyISAM表的读和写是串行的,即在进行读操作时不能进行写操作,反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行,其机制是通过控制一个系统变量(concurrent_insert)来进行的,当其值设置为0时,不允许并发插入;当其值设置为1

时,如果MyISAM表中没有空洞(即表中没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录;当其值设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

MyISAM锁调度是如何实现的呢,这也是一个很关键的问题。例如,当一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,此时MySQL将会如优先处理进程呢?通过研究表明,写进程将先获得锁(即使读请求先到锁等待队列)。但这也造成一个很大的缺陷,即大量的写操作会造成查询操作很难获得读锁,从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates,使MyISAM默认引擎给予读请求以优先的权利,设置其值为1(set

low_priority_updates=1),使优先级降低。

10年程序员总结的MySQL行级锁、表级锁、页级锁详细介绍

 

InnoDB锁与MyISAM锁的最大不同在于:一是支持事务(TRANCSACTION),二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元,其有四个属性(简称ACID属性),分别为:

  • 原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全都不执行;
  • 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态;
  • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行;
  • 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

InnoDB有两种模式的行锁:

1)共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

( Select * from table_name where ......lock in share mode)

2)排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where…for update)

为了允许行锁和表锁共存,实现多粒度锁机制;同时还有两种内部使用的意向锁(都是表锁),分别为意向共享锁和意向排他锁。

InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!

另外:插入,更新性能优化的几个重要参数

bulk_insert_buffer_size 批量插入缓存大小,

这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时,

提高效率.默认值是8M.可以针对数据量的大小,翻倍增加. concurrent_insert 并发插入, 当表没有空洞(删除过记录),

在某进程获取读锁的情况下,其他进程可以在表尾部进行插入. 值可以设0不允许并发插入, 1当表没有空洞时, 执行并发插入,

2不管是否有空洞都执行并发插入. 默认是1 针对表的删除频率来设置. delay_key_write

针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘.

值为 0不开启, 1开启. 默认开启. delayed_insert_limit, delayed_insert_timeout,

delayed_queue_size 延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持,

目前来看, 常用的InnoDB不支持, MyISAM支持. 根据实际情况调大, 一般默认够用了

/ ==================== MySQL InnoDB 锁表与锁行 ======================== /

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单products ,里面有id跟name二个栏位,id是主键。

例1: (明确指定主键,并且有此笔资料,row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;

SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键,若查无此笔资料,无lock)`sql

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键,table lock)

sql

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确,table lock)

sql

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确,table lock)

sql

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2: 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。

在MySql 5.0中测试确实是这样的

另外:MyAsim 只支持表级锁,InnerDB支持行级锁

添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定,也不被其它事务修改(修改、删除)

是表级锁时,不管是否查询到记录,都会锁定表

此外,如果A与B都对表id进行查询但查询不到记录,则A与B在查询上不会进行row锁,但A与B都会获取排它锁,此时A再插入一条记录的话则会因为B已经有锁而处于等待中,此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁,此时A就获得了锁而插入成功

10年程序员总结的MySQL行级锁、表级锁、页级锁详细介绍

 

这篇关于10年程序员总结的MySQL行级锁、表级锁、页级锁详细介绍的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



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

相关文章

HarmonyOS学习(七)——UI(五)常用布局总结

自适应布局 1.1、线性布局(LinearLayout) 通过线性容器Row和Column实现线性布局。Column容器内的子组件按照垂直方向排列,Row组件中的子组件按照水平方向排列。 属性说明space通过space参数设置主轴上子组件的间距,达到各子组件在排列上的等间距效果alignItems设置子组件在交叉轴上的对齐方式,且在各类尺寸屏幕上表现一致,其中交叉轴为垂直时,取值为Vert

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能测试介绍

性能测试是一种测试方法,旨在评估系统、应用程序或组件在现实场景中的性能表现和可靠性。它通常用于衡量系统在不同负载条件下的响应时间、吞吐量、资源利用率、稳定性和可扩展性等关键指标。 为什么要进行性能测试 通过性能测试,可以确定系统是否能够满足预期的性能要求,找出性能瓶颈和潜在的问题,并进行优化和调整。 发现性能瓶颈:性能测试可以帮助发现系统的性能瓶颈,即系统在高负载或高并发情况下可能出现的问题

水位雨量在线监测系统概述及应用介绍

在当今社会,随着科技的飞速发展,各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中,水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术,其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成,形成了一个完整的闭环系统。 数据采集单元:这是系统的“眼睛”,

Hadoop数据压缩使用介绍

一、压缩原则 (1)运算密集型的Job,少用压缩 (2)IO密集型的Job,多用压缩 二、压缩算法比较 三、压缩位置选择 四、压缩参数配置 1)为了支持多种压缩/解压缩算法,Hadoop引入了编码/解码器 2)要在Hadoop中启用压缩,可以配置如下参数

学习hash总结

2014/1/29/   最近刚开始学hash,名字很陌生,但是hash的思想却很熟悉,以前早就做过此类的题,但是不知道这就是hash思想而已,说白了hash就是一个映射,往往灵活利用数组的下标来实现算法,hash的作用:1、判重;2、统计次数;

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间