MySQL表级锁使用说明

 更新时间:2022年10月19日 18:24  点击:97 作者:万里顾一程

表级锁

该锁会锁定整张表,它是MySQL中最基本的锁策略,并不依赖于存储引擎(不管你是MySQL的什么存储引擎,对于表锁的策略都是一样的),并且表锁是开销最小的策略(因为粒度比较大)。由于表级锁一次会将整个表锁定,所以可以很好的避免死锁问题。当然,锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高,导致并发率大打折扣。

1、表级别的S锁,X锁

InnoDB存储引擎

在对某个表执行SELECT、INSERT、DELETE、UPDATE 语句时,InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。

一般情况下,不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下,比方说崩溃恢复过程中用到。

InnoDB存储引擎下,手动添加表t的S锁或X锁:

lock tables t read  -- S锁
lock tables t write  -- X锁

不过尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES这样的手动锁表语句,它们并不会提供什么额外的保护,只是会降低并发能力而已。

MyISAM存储引擎

MyISAM 的表级锁有2种模式,分别为:表共享读锁(S锁) 和 表独占写锁(X锁)。

表共享读锁(S锁):当开启事务A 获取表共享读锁, 则其他新开启事务只能读取数据,不能对操作的同张表进行更新或者插入操作,删除操作,

表独占写锁(X锁):当开启事务A 获取独占写锁,则其他新开启的事物 读取,新增,修改,删除 等操作会处于阻塞状态, 只到 事务A 主动释放锁。

MyISAM存储引擎下,手动添加表t的S锁或X锁:

lock tables t read  -- S锁
lock tables t write  -- X锁

可通过 show status like 'tables%'; 命令来 查看 mysql 内部表级锁定的情况:

2、意向锁

意向锁概述

InnoDB支持多粒度锁(multiple granularity locking),它允许行级锁与表级锁共存,而意向锁就是其中的一种表锁。

==意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。==

意向锁是一种不与行级锁冲突的表级锁,这一点非常重要。

意向锁分为两种:

  • 意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享锁(S锁)

select column from table ... lock in share mode; -- 

  • 意向排他锁(intention exclusive lock, IX):事务有意向对表中的某些行加排他锁(X锁)

select column from table ... for mode; -- 

申请意向锁的动作是数据库完成的,就是说,事务A申请一行的行锁的时候,数据库会自动先开始申请表的意向锁,不需要我们程序员使用代码来申请。

意向锁解决的问题

事务A锁住了表中的一行,让这一行只能读,不能写。之后,事务B申请整个表的写锁。

如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。

数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。于是就有了意向锁。

事务B只需检查表上的意向锁,发现表上有意向共享锁IS,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。

在数据表的场景中,如果我们给某一行数据加上了排它锁,数据库会自动给更大一级的空间,比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排它锁了,这样当其他人想要获取数据表排它锁的时候,只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

  • 如果事务想要获得数据表中某些记录的共享锁,就需要在数据表上添加意向共享锁。
  • 如果事务想要获得数据表中某些记录的排他锁,就需要在数据表上添加意向排他锁。

意向锁的并发性

开启一个事务,并给查询记录加上X锁:此时针对查询的记录还加上了一个表级别的共享排它锁(IX)

再开启一个事务,查询不同记录,并给查询记录加上X锁:表级别的 IX共享排它锁加锁成功,因为两次事务加的IX是针对不同的记录的

结论:

  • InnoDB支持多粒度锁,特定场景下,行级锁可以与表级锁共存。
  • 意向锁之间互不排斥,但除了IS与S兼容外,意向锁会与共享锁/排他锁互斥。
  • lX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突。
  • 意向锁在保证并发性的前提下,实现了行锁和表锁共存且满足事务隔离性的要求。

3、自增锁(AUTO-INC锁)

自增锁是MySQL一种特殊的锁,如果表中存在自增字段,当向表中插入数据时,MySQL便会自动维护一个表级的自增锁。

在执行插入语句时就在表级别加一个AUTO-INC锁,然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值,在该语句执行结束后,再把AUTO-INC锁释放掉。

一个事务在持有AUTO-INC锁的过程中,其他事务的插入语句都要被阻塞,可以保证一个语句中分配的递增值是连续的。也正因为此,其并发性显然并不高,当我们向一个有AUTO_INCREMENT关键字的主键插入值的时候,每条语句都要对这个表锁进行竞争,这样的并发潜力其实是很低下的。

所以 innodb 引擎通过设置 innodb_autoinc_lock_mode 的值来提供不同的锁定机制,来显著提高sQL语句的可伸缩性和性能。

innodb_autoinc_lock_mode有三个取值:0,1,2

tradition(innodb_autoinc_lock_mode = 0) 模式:==传统==锁定模式

  • 它提供了一个向后兼容的能力
  • 在这一模式下,所有类型的insert语句都会在语句开始的时候得到一个表级的auto_inc锁,用于插入具有auto_inc列的表,在语句结束的时候才释放这把锁,注意,这里说的是语句级而不是事务级的,一个事务可能包涵有一个或多个语句。
  • 它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
  • 由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。因为是表级锁,当在同一时间多个事务中执行 insert 的时候,对于auto_inc锁的争夺会限制并发能力。

consecutive(innodb_autoinc_lock_mode = 1) 模式:==连续==锁定模式

  • 在MySQL8.0之前,==连续==锁定模式是默认的添加模式
  • 这一模式在simple insert (要插入的行数已知)做了优化,由于simple insert一次性插入值的个数可以立马得到确定,所以mysql可以一次生成几个连续的值,用于这个insert语句;总的来说这个对复制也是安全的 (它保证了基于语句复制的安全)
  • 这一模式也是mysql的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁

interleaved(innodb_autoinc_lock_mode = 2) 模式:==交错==锁定模式

  • 在MySQL8.0,==交错==锁定模式是默认的添加模式
  • 由于这个模式下所有insert语句都不回使用表级auto_inc锁,并且可以同时执行多个语句,这是最快和最可扩展的锁定模式,所以这个模式下的性能是最好的;但是它也有一个问题,由于多个语句可以同时生成数字,为任何给定语句插入的行生成的值可能是不连续的。

4、元数据锁(MDL锁)

在对某个表执行一些诸如ALTER TABLE、DROP TABLE 这类的 DDL 语句时,其他事务对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。

同理,某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时,在其他会话中对这个表执行DDL语句也会发生阻塞。

这个过程其实是通过在server层使用一种称之为元数据锁(英文名: Metadata Locks,简称MDL)结构来实现的。

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

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

==读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。读锁和写锁之间、写锁和写锁之间是互斥的==,用来保证变更表结构操作的安全性,解决了 DML 和 DDL 操作之间的一致性问题。MDL锁不需要显式使用,在访问一个表的时候会被自动加上。

以上就是MySQL表级锁使用说明的详细内容,更多关于MySQL 表级锁的资料请关注猪先飞其它相关文章!

原文出处:https://juejin.cn/post/7155443329985937415

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
  • Mysql命令大全(详细篇)

    一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
  • MySQL数据库备份还原方法

    MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • mongodb与mysql命令详细对比

    传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
  • Delphi远程连接Mysql的实现方法

    这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30
  • 如何解决安装MySQL5.0后出现1607异常

    最近项目比较多,时间不宽松,一直没给大家整理,今天小编抽个时间把我的解决方案分享给大家,具体内容如下所示。问题描述:我在自己的电脑上配置了Mysql5环境,同时安装了一个phpMyAdmin管理工具,安装完成后,发现在phpMyAdmin里面...2015-10-21
  • MySQL 从全库备份中恢复指定的表和库示例

    这篇文章主要介绍了MySQL 从全库备份中恢复指定的表和库示例的相关资料,这里提供了相应的方法,来实现恢复指定的表和库数据,需要的朋友可以参考下...2016-12-02
  • MySQL数据库遭到攻击篡改(使用备份和binlog进行数据恢复)

    这篇文章主要介绍了MySQL数据库遭到攻击篡改(使用备份和binlog进行数据恢复),需要的朋友可以参考下...2016-04-26
  • python操作mysql、excel、pdf的示例

    这篇文章主要介绍了python操作mysql、excel、pdf的示例,帮助大家更好的理解和学习使用python,感兴趣的朋友可以了解下...2021-03-29
  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23