[MySQL]表锁，全局锁，行锁。

最新推荐文章于 2024-07-26 16:04:09 发布

pmdream

最新推荐文章于 2024-07-26 16:04:09 发布

阅读量211

点赞数

分类专栏： sql

本文链接：https://blog.youkuaiyun.com/pmdream/article/details/103440605

版权

sql 专栏收录该内容

39 篇文章

订阅专栏

1.2.4 与set global readonly=true的区别？

1.3.6 给表加字段的时候，要注意什么？

1.3.7 如何安全的给表加字段？

1.3.8 DDL（data definition language）

Q:在备份中SAVEPOINT 与 ROLLBACK的使用？

Q:主从延迟是什么？

Q: 怎么安排事务语句？

Q:如何删除表的前10000行数据？

Q：update table set xx;这种更新全表的锁是什么？

Q: 死锁检测并非每条事务都要检测？

Q：innodb的行锁？

Q：在myisam表上更新操作MDL锁和表锁？

前言：

感谢极客时间<MySQL 45讲>一点一点学，一点一点做笔记~

mysql的锁，面试好像也很少会提到。

如果要问起来，到底是怎么回事？

更多是为了面试准备~知识点干货

1. 初识

1.1 锁的种类和目的

目的：处理并发请求，解决多用户共享资源的问题。

种类：MySQL 里面的锁大致分成全局锁、表级锁和行锁三类；

(对于行锁和表锁)：

MyISAm存储引擎只支持表锁

nnodb存储引擎即支持行锁也支持表锁

FTWRL也就是全局锁这两个都支持~

1.2 全局锁

对整个数据库的实例加锁，Mysql有加全局读锁的方法，Flush tables with read lock

让整个库处于只读的状态，之后其他线程的增删改查,建表修改表和事务都会进行阻塞。

1.2.1 全局锁的使用场景

做全局逻辑备份。(防止新的数据插入)

这种备份让整个库都处于只读状态，真的好鸡肋啊。（但是其实我错了，工作场景可能会用到这种，存在及合理的。）

如果是从主库进行备份，那么备份过程都不能进行增删改查,相当于停止了业务了。

如果是从slave中进行备份，那么这个过程salve并不能执行主库同步过来的binlog，导致主从延迟。

1.2.2 优点

对于备份，因为有些数据涉及到用户的金额或者一些花钱的东西，或者一些很重要的东西，

都要保证类似于事务的一致性的。(为了有些表没有用带事务的库而生)

1.2.3 逻辑备份工具

mysqldump --喂喂：我又不是DBA记这个干啥

当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

PS:Multi-Version Concurrency Control即多版本并发控制。就是乐观锁的一种实现方式。

那么也就是用这个工具加上设置了事务，拿到一致性视图

那么有上面这个方法为什么还要FTWRL？

single-transaction 方法只适用于所有的表使用事务引擎的库。(mysql原生引擎MyISAM是不支持事务引擎的)

因为一致性读，mysql原生引擎MyISAM不支持上面那个事务。

比如有的表没有使用带事务的引擎，那么我们备份只能用FTWRL~

备份是需要一致性的，所以需要使用FTWRL命令。

1.2.4 与set global readonly=true的区别？

这个是一个全局变量，是有风险的。

1. readonly有可能用来判断是主库还是备库。修改global变量有风险。

2.FTWRL 是可以发生异常释放锁。

但是如果readonly并不能达到这个效果。可能会一直阻塞~

1.3 表级锁

表级锁：包括两个1.表锁2.元数据锁(MDL meta data lock)

1.3.1 表锁语法

lock tables … read/write

比如：lock tables t1 read, t2 write;

unlock tables 可以去显式释放

1.3.2 表锁特点

可能是原生的引擎处理并发的方式。(实在没有更好的了)

InnoDB是可以支持行的锁的引擎，所以一般不建议使用lock tables来控制并发。

锁住整个表影响很大

1.3.3 元数据锁MDL的特点

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

所以我们执行的过程中是有MDL来保证读写的正确性的，

mysql在5.5中引入了MDL那么在做增删改查的时候，加上读锁。

对表结构做更改的时候，加上MDL写锁。

1.3.4 MDL的优势

1.读锁不互相影响，可以多个线程一起对一张表CRUD~

2.读写锁质检，写锁之间互斥。所以用来保证变更表结构时候的安全性。

比如两个线程同时给一个表做逻辑上的字段的增加，那么并不能同时操作~(会等待)

1.3.5 MDL是系统默认会加上的！！

1.3.6 给表加字段的时候，要注意什么？

要小心操作，因为加字段修改字段等等或者增加索引，是需要扫描全表的数据的。

所以在对数据量很大的时候，要小心操作。

CRUD都需要在表上申请MDL读锁。

因为可以这样说,一个表的CRUD会申请读MDL，但是该表结构会等待在它语句前面的读锁的完成。

本来读锁是并发执行的，如果更改表结构，就会导致在执行更改表结构后面的语句阻塞。

就会影响了后面的读操作了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

下面的更为重要：

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

1.3.7 如何安全的给表加字段？

不能有长事务和事务不提交的那些，霸占MDL锁，导致无法释放。

所以在更新表的时候，发现有长事务在执行，可以考虑等事务完成在进行DDL或者kill这个长事务。

如果表数据量不大，但是很常用，请求很多，必须给线上加个字段了！！！！

那么执行：主要是设置执行的等待时间！！

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

1.3.8 DDL（data definition language）

指的是更改表或者建立表的那些语句~

1.4 行锁

MyISAM 引擎就不支持行锁。（如果使用表锁，那么同表任何时刻只能有一个更新在执行，不能并发）

行锁就是给行加锁，对同一行进行操作，那么有先后顺序执行。(竞争锁)

1.4.1 两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，等到事务结束时才释放。这个就是两阶段锁协议。

因为这个协议，我们在使用事务的时候，要把影响并发的锁尽量往后面放。

1.4.2 实例

花钱：

1.我(客户)要去商店花50元，那么我(客户)的余额-50

2.商店余额+50.

3.记录一个操作log。

所以是两个更新操作，和一个insert一个记录。

为了保证交易的原子性，这三个操作在一个事务。

那么这三个语句也在在事务中考虑一下顺序~

商店的余额是最影响大的，根据两阶段锁的协议，我们行锁是最后事务提交的时候才被释放。

所以把2语句放在最后，那么影响是最小的，减少了事务之间的等待，提高了并发~

1.4.3 行锁的互相等待导致死锁

场景很简单，两个事务同时开始。

高并发的情况下，两个事务都没有执行完，

事务A执行更新语句 1 和更新语句 2；

事务B执行更新语句2和更新语句1；

那么可能相互等待行锁的释放。

解决：

1.设置超时时间，通过参数 innodb_lock_wait_timeout 来设置。

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

一般开启死锁检测会更有用吧

但是也会出现CPU占用率过高的问题

1.4.4 死锁检测的缺点

极端情况：如果所有的事务都更新同一行，那么可能会导致所有新来的线程都在判断自己的加入会不会导致死锁。

这是一个O(n)操作，比如1000并发都要更改同一行，那么死锁的复杂度就是1000*1000=100w的量级。

所以最终发现没有死锁，但是占用了大量的CPU资源，所以可能有的秒杀业务对同一行一直在操作，

那么可能会发现CPU利用率很高，但是每秒执行不了多少事务

1.4.5 解决热点问题

死锁检测要耗费很多的CPU资源，如果这个业务一定不会出现死锁，那么关闭死锁检测。(但是可能带来大量超时等等，因为一般思索了就回滚事务，重新重试，业务中是可以被允许的)

或者另一种思路，让并发变得少一点，控制住。每一个行最多只能让10个线程更新等等。

可以借助中间件，感觉可以借助MQ之类，让它们对同一行操作的排队，然后限制同时并发数。

2. 可以在面试中提到的东西~

Q:表锁的使用场景？

只有在数据库引擎不支持行锁的时候才会被用到。应该升级引擎。

或者升级了引擎代码中还存在：业务开发应该把 lock tables 和 unlock tables 改成 begin 和 commit

Q:MDL注意事项？

做表的变更，不能锁住线上的查询和更新！！

TIPS:备份会在备库进行备份

Q:在备份中SAVEPOINT 与 ROLLBACK的使用？

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;


START TRANSACTION WITH CONSISTENT SNAPSHOT； // WITH CONSISTENT SNAPSHOT的目的是 确保这个语句执行完就可以得到一个一致性视图

SAVEPOINT sp; //增加保存点
 
show create table `t1`; // 展示表的结构

SELECT * FROM `t1`;  //导出数据

ROLLBACK TO SAVEPOINT sp;  //释放 t1 的 MDL 锁

Q:主从延迟是什么？

https://blog.youkuaiyun.com/hao_yunfeng/article/details/82392261

1)、MySQL数据库主从同步延迟原理mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。