【MySQL】全局锁、表锁和行锁

计量经济学tempdisagg的python库:年度时序变月度 阅读详情

学习于极客时间

全局锁

全局锁的典型使用场景是,做全库逻辑备份。Flush tables with read lock (FTWRL)。在备份过程中整个库完全处于只读状态。

让整库都只读:

(1)如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;

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

不加锁的话,备份系统备份的得到的库不是一个逻辑时间点,视图是逻辑不一致的。

其实是有一个方法能够拿到一致性视图的,就是在可重复读隔离级别下开启一个事务。

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

一致性读是好,但前提是引擎要支持这个隔离级别。比如,对于 MyISAM 这种不支持事务的引擎,如果备份过程中有更新,总是只能取到最新的数据,那么就破坏了备份的一致性。这时,我们就需要使用 FTWRL 命令了。

single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎,那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

为什么不使用 set global readonly=true 的方式呢?

第一,在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大,我不建议你使用。

第二,在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。

MVCC

多版本并发控制(MVCC) 是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

1. 读写之间阻塞的问题

通过 MVCC 可以让读写互相不阻塞,即读不阻塞写,写不阻塞读,这样就可以提升事务并发处理能力。提高并发的演进思路:

普通锁,只能串行执行;
读写锁,可以实现读读并发;
数据多版本并发控制,可以实现读写并发。

2. 降低了死锁的概率

因为 InnoDB 的 MVCC 采用了乐观锁的方式,读取数据时并不需要加锁,对于写操作,也只锁定必要的行。

3. 解决一致性读的问题

一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果。

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

因此,在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

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

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

如何安全地给小表加字段?

事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新 session 再请求的话,这个库的线程很快就会爆满。

首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?

这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后再通过重试命令重复这个过程。

行锁

一次性锁协议,事务开始时,即一次性申请所有的锁,之后不会再申请任何锁,如果其中某个锁不可用,则整个申请就不成功,事务就不会执行,在事务尾端,一次性释放所有的锁。一次性锁协议不会产生死锁的问题,但事务的并发度不高。

两阶段锁(two-phase locking,2PL)协议,是数据库事务处理时的并发控制方法,以保证可串行化。这种方法使用数据库锁在两个阶段:

  1. 扩张阶段:不断上锁,没有锁被释放
  2. 收缩阶段:锁被陆续释放,没有新的加锁

2PL可能会导致死锁。

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

建议:如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。

解决死锁策略:

一,直接进入等待。直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置

二,主动死锁检测。发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。

怎么解决由热点行更新导致的性能问题呢?

控制并发度。

根据上面的分析,你会发现如果并发能够控制住,比如同一行同时最多只有 10 个线程在更新,那么死锁检测的成本很低,就不会出现这个问题。一个直接的想法就是,在客户端做并发控制。但是,你会很快发现这个方法不太可行,因为客户端很多。我见过一个应用,有 600 个客户端,这样即使每个客户端控制到只有 5 个并发线程,汇总到数据库服务端以后,峰值并发数也可能要达到 3000。因此,这个并发控制要做在数据库服务端。如果你有中间件,可以考虑在中间件实现;如果你的团队有能修改 MySQL 源码的人,也可以做在 MySQL 里面。基本思路就是,对于相同行的更新,在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

SAP_SD模块路径及运输功能后台配置简介 后台路径:SPRO—后勤执行—装运—基本发运功能—定义路线—定义传输模式根据实际运输过程运输地理的类型:海运、公路运输、空运后台路径:SPRO—后勤执行—装运—基本发运功能—定义路线—定义发运类型根据实际运输过程运输方式:汽车、卡车、火车、轮船、飞机后台路径:SPRO—后勤执行—装运—基本发运功能—路径—定义路线—定义运输连接点指在运输过程中需要中转的地区可以维护中转地区的停留的时间后台路径:SPRO—后勤执行—装运—基本发运功能—路径—定义路线—定义路径和阶段。 阅读详情

相关推荐

保姆级教程:用Wokwi玩转ESP32 MicroPython仿真(含库文件配置指南)

本教程详细介绍了如何使用Wokwi平台进行ESP32 MicroPython仿真开发,包括库文件配置、传感器数据模拟和REPL调试技巧。通过Wokwi的仿真调试系统,开发者可以在浏览器中快速验证代码逻辑,提高开发效率,特别适合物联网项目的快速迭代。

weixin_29195615的博客 451

MySQL之死锁

互斥:一个资源只能被一个进程(事务)使用;请求与条件保持:一个进程(事务)在等待请求的资源过程中,自身已经占有的资源不可释放;不剥夺条件:进程(事务)在运行活跃时候,已经获得的资源未使用完之前,不可强行剥夺;循环等待条件:若干个进程(事务)形成首尾相接的循环等待关系;一般来说业务基本上就是对商品进行新增、修改和查询等操作,我们在对商品进行新增操作前,先通过select … for update操作进行查询,然后再进行相应的业务逻辑处理,所以当业务量很大时,就有可能出现死锁。

peng_shakalaka的博客 2253

【吐血整理-历时两个月,长达万字】FDTD Solutions学习笔记

原教学视频来自于Ansys-Lumerical官方的FDTD100,所看视频来自B站大佬UP主 【Nestor_呐等】 的幸苦翻译,本文内容粘贴自我个人在学习FDTD100时,通过字幕和一些个人理解总结的一份word。整理不易,强烈建议收藏!...

weixin_45719141的博客 10万+

MySQL 的lock_wait_timeout 参数

作用:控制元数据锁(MDL)等待的最长时间(秒)默认值:31536000秒(1年,MySQL 5.7+),86400秒(1天,MySQL 5.6)有效范围:1到31536000秒动态性:全局和会话级别均可动态修改。

文牧之的博客 1364

数学:箭头符号→(to)都表示什么意义?

数学符号“→”在不同领域有不同含义:在逻辑中表示“蕴含”(p→q);在函数中表示映射关系(f:A→B);在极限中表示变量趋近(x→a);在推导过程中表示推理方向(A→B)。此外,它也可能用于向量表示或数列趋势。其具体含义需结合上下文判断,核心应用场景包括逻辑运算、函数定义、极限分析和数学推导等。

shunzi2016的博客 5592

Android Display Graphics #1 整体框架介绍一

手机屏上显示的内容,2D或3D ,如果是 3D 的,不管是 App 还是游戏,或者简单的图片界面,底层都是通过 GPU、 通过 OpenGL(ES)绘制出来的。是Java层的一个类,提供了一系列API,使用这些API可以实现各种的产品经理要求的效果。显示的不同阶段对这块内存的叫法不同,在上层可以是Window、ANnativeWindow、Surface、SurfaceView、layer、buffer,这些本质上没啥区别,可以广泛意义上理解为用于显示内容的那块内存,只不过各个阶段的操作接口不同。

oDwyane03的博客 1389

MySQL innodb_lock_wait_timeout 参数的作用及使用示例(定义一个事务等待行锁的最大时间长度)

参数在MySQL数据库中定义了一个事务等待行锁的最大时间长度。当一个事务尝试获取已被另一个事务持有的行锁时,如果等待时间超过了设定的值,MySQL将自动回滚这个等待中的事务,并抛出错误。这个设置主要是用来防止由于长时间的锁等待导致的系统僵死或性能问题。

学亮编程手记 2387

Benchmark系列——基本概念和分类

本文主要介绍了Benchmark的基本概念和分类情况。

Bluetangos的博客 8446

mysql 数据库锁及解锁办法

通过SELECT * FROM information_schema.innodb_trx查询未提交事务,查到一个一直没有提交的只读事务(trx_state=”LOCK WAIT”),找到对应线程,执行:kill 线程ID。如果这行和 TRX_IS_READ_ONLY 都为1的话,InnoDB会优化事务,以减少事务改变表数据的开销。INNODB_TRX表提供了当前在InnoDB内部执行的所有事务信息,包含事务是否在等待锁,事务何时开始以及事务正在执行的SQL语句(如果有的话,sql语句阻塞就可以显示)。

qq_33286757的博客 997

基于python的排课表系统,用python编写排课系统

实现一个具体的课程管理系统。(5) 学生可以查看自己选上的课的成绩,以及各个课程的得分、绩点,并给出其平均绩点,但不允许查看年级其他人的成绩。(2) 学生定制专属课表,可以查看整个学期本人的课程安排,包括课程名称、上课时间、上课地点、任课老师、学分等等。(13) 系主任可以查看整个系的全部学生以及他们的平均绩点,可以对每个班进行排名,或者也可以对整个系进行排名。(15) 系主任可以查看整个学期所开设的所有课程,包括这些课程的全部详细信息。(1) 学生基本信息的查询,学生可以对本人的系统登陆密码进行修改。

yang0728y的博客 2000

7.91版本unidbg调用方式

【代码】xhs最新7.91版本unidbg调用方式。

Codeooo 博客 1108

MySQL解决Waiting for table metadata lock

更改表名时发现卡住 show full processlist; 发现更改表名的进程卡住,出现表锁,之后所有对这张表的操作都会卡住 相关状态 Command 为:Waiting for table metadata lock 使用 kill ID 杀掉进程但是, 发现杀不掉, 还会以新的 ID 重新运行 检查事务: select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.inn..

RayfunC的博客 2303

线程池详解(通俗易懂超级好)

目标 【理解】线程池基本概念 【理解】线程池工作原理 【掌握】自定义线程池 【应用】java内置线程池 【应用】使用java内置线程池完成综合案例 线程池 线程池基础 线程池使用 线程池综合案例 4.学员练习 5.线程池总结 概念介绍 1:什么是线程池 2:为什么使用线程池 3:线程池有哪些优势 什么是池 什么是线程池 线程池其实就是一种多线程处理形式,处理过程中可以将任务添加到队列中,然后在创建线程后自动启动这些任务。这里的线程就是我们前面学过的线程,这里的任务就是我们前面学过的实现了Runna.

拉格朗日的学习笔记 12万+

MySQL高级(六):全局锁、表锁和行锁

表锁是作用于单个表的锁,限制其他线程对该表的读写操作。行锁是作用于表中的特定行的锁,仅限制其他线程对特定行的操作。锁类型作用范围加锁方式优缺点常用场景全局锁整个数据库实例粒度最大,影响整个实例,不适合高并发环境。一致性备份、全库维护表锁单个表粒度较粗,影响整个表的操作,简单易用,但会降低并发性能。批量操作、逻辑控制行锁表中的特定行自动加锁或显式事务控制粒度最细,并发性能高,但加锁开销大,需依赖索引实现。高并发场景、事务操作。

www_dong的博客 1026

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

MySQL中的锁(全局锁、表锁和行锁)

Mysql中的锁

m0_73922877的博客 1248

假人有限元模型

假人模型,车辆碰撞假人有限元模型,适合做乘员安全的研究者利用

MySQL 全局锁、表锁和行锁

当我们开启多个事务的时候,假设事务的内容都是一个begin+简单的select语句(加MDL读锁),当其中一个事务没有及时提交,此时如果有一个alter table的操作(导致MDL读锁升级为MDL写锁),会导致后续的select语句,都被阻塞,即使这个表的记录数很少,在事务不及时提交的情况下,也会导。这个语句,则其他线程写t1、读写t2的语句都会被阻塞。行锁的产生,可以大大降低死锁的概率(是降低,不是杜绝),但是这种热点行的频繁更新,往往会导致MySQL的性能问题(因为死锁检测会消耗大量的CPU资源)。

2301_78145678的博客 859

复分析 阿尔福斯(Ahlfors) 第三版答案 英文

答案分为十章,有部分习题没有答案,全英文复分析阿尔福斯(Ahlfors) 第三版答案

MySQL| 全局锁和表锁、行锁

文章目录MySQL| 全局锁和表锁、行锁全局锁表级锁行锁两阶段锁死锁和死锁检测小结 MySQL| 全局锁和表锁、行锁 根据加锁的范围,MySQL里面的锁大致可以分成全局锁,表级锁,行锁。 全局锁 对整个数据库实例加锁,MySQL提供了一个加全局读锁的方法,命令是Flush tables with read lock。当需要整个库处于只读状态时可以使用。 数据更新语句、数据定义语句和更新类事务会被阻塞。 使用场景:做全库逻辑备份。就是把整个表的select 出来存成文本。 如果再主库上做备份,那

weixin_36723038的博客 303

mysql45讲之全局锁和表锁和行锁

根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。**全局锁的典型使用场景是,做全库逻辑备份。**也就是把整库每个表都select出来存成文本。官方自带的逻辑备份工具是mysqldump。当my

w1lgy的博客 332

【MySQL】006 -- MySQL的锁:全局锁、表锁和行锁

MySQL的锁:全局锁、表锁和行锁。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

汤姆lazy的优快云博客 1088

mysql(八): 全局锁、行锁、表锁和死锁

1.全局锁 全局锁就是对整个数据库实例加锁,MySQL 提供了 flush tables with read lock (FTWRL) 的方式去加全局锁。当你需要让整个库处于只读状态的时候,就可以使用这个命令了,之后所有线程的更改操作都会被阻塞。该锁的主要是用在MyISAM这一类引擎对mysql数据备份上的使用。 逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-...

流的博客 932

mysql行锁设计_MySQL学习之全局锁和表锁

数据库锁设计的初衷是为了处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源访问的规则,锁就是用来实现这些访问规则的重要数据结构。在MySQL里锁分为三类,全局锁,表级锁,行锁。全局锁全局锁就是对整个数据库实例加锁,MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL),使用了全局锁,会让整个库处于只读状态...

weixin_28759725的博客 210

MySQL深入浅出之全局锁和表锁和行锁

数据库锁的设计初衷是处理并发问题. 作为多用户共享的资源, 当出现并发访问的时候, 数据库需要合理地控制资源的访问规则. 而锁就是用来实现这些访问规则的重要数据结构.

406156440的博客 2449

mysql全局锁,表锁和行锁

其实,在innodb中可重复读的事务级别就已经可以代替这个全局锁了,在备份的时候开启事务,再整个事务中,拿到的数据都是一致的,也就是视图一致性。

wangminlalala的博客 940

MySQL:全局锁,表锁和行锁

数据库为多用户共享的,当出现并发访问的时候,需要使用锁来控制资源的访问。根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。 1、全局锁 全局锁即给整个数据库实例加锁。主要的使用场景:做全库逻辑备份的时候,为了保证备份期间的库在同一个逻辑时间点,即保证一致性,类似于前面我们提到的可重复读的效果。 MySQL提供了一个加全局读...

sayoko06的博客 1750

MySQL 的全局锁、表锁和行锁

在前一篇文章我讲了下 MySQL 的全局锁、表记锁和行级别锁,其中行级锁只提了概念,并没有具体说。因为行级锁加锁规则比较复杂,不同的场景,加锁的形式还不同,所以这次就来好好介绍下行级锁。对记录加锁时,,它是由记录锁和间隙锁组合而成的,。但是,next-key lock 在一些场景下会退化成记录锁或间隙锁。那到底是什么场景呢?今天,我们就以下面这个表来进行实验说明。其中,id 是主键索引(唯一索引),b 是普通索引(非唯一索引),a 是普通的列。注意,。

u010830473的博客 1504

MYSQL的全局锁、表锁、行锁——全局锁和表锁:给表加个字段怎么有这么多阻碍?

参考文章:http://gk.link/a/101o8 目录 全局锁 1、如何加全局锁和全局锁的使用场景 2、备份为什么要加锁? 3、官方自带mysqldump(–single-transaction )确保拿到一致性视图 4、为什么还需要FTWRL? 5、为什么不使用readonly使全局库只读? 表级锁 1、表锁 2、元数据锁MDL 3、如何安全地给小表加字段? 根据加...

qq_38151401的博客 1086
上一篇: 【MySQL】索引
下一篇: 离职前的最后三周
keeper42
博客等级 码龄10年 84粉丝 481原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值