search

git-zjx / git-zjx.github.io

blog
5 stars 0 forks source link

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？ #14

Open git-zjx opened 5 years ago

git-zjx commented 5 years ago

全局锁

对整个数据库实例加锁，MySQL 提供一个加全局读锁的命令：Flush tables with read lock（FTWRL），命令执行后，数据更新语句（增删改）、数据定义语句（建表和修改表结构等）和更新类事务都会被阻塞，经常用于数据库备份场景

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

参数 -single-transaction 需要数据库引擎支持事务，如果不支持就只能使用 FTWRL 方案

为什么不使用 set global readonly = true 方式？

  1. 有些系统中 readonly 会被用作其他逻辑，例如判断是主库还是从库
  2. 异常处理机制有差异，FTWRL 后客户端异常断开，MySQL 会自动释放全局锁，库恢复正常；设置readonly，客户端异常，则会保持 readonly，会导致长时间处于不可写状态，风险较高
  3. readonly 对 super 权限无效

表级锁

MySQL 的表级锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

lock tables … read/write 语句用于加表锁，使用 unlock tables 可以释放锁，客户端断开连接时也会释放锁 需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象 举个例子, 如果在某个线程 A 中执行lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

在 MySQL 5.5 版本中引入了 MDL，MDL 不需要显式调用，在访问一个表时自动调用，主要保证读写的正确性，防止DDL和DML并发的冲突

当表做增删改查操作时，加MDL读锁；当对表结构变更的时候，加MDL写锁

小表加字段为什么会导致整个库崩溃？ image Session A 会给表 t 加 MDL 读锁，Session B 需要的也是读锁，可以执行 Session C 需要 MDL 写锁，但 Session A 的 MDL 读锁还未释放，需要等待 Session D 需要 MDL 读锁，但是因为 MySQL 内部维护了一个队列决定执行顺序，Session C 阻塞了 Session D 这里可以知道事务中的 MDL 锁会等到整个事务结束后再释放

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

git-zjx commented 4 years ago

MySQL 5.6不是支持 online ddl 了吗？也就是对表操作增加字段等功能，实际上不会阻塞读写？

Online DDL的过程是这样的： ①拿MDL写锁 ②降级成MDL读锁 ③真正做DDL ④升级成MDL写锁 ⑤释放MDL锁； ①、②、④、⑤如果没有锁冲突，执行时间非常短。 第③步占用了 DDL 绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”

git-zjx commented 4 years ago

关于MDL写锁，有个更好的处理方法不知道是否稳妥？先基于原表创建一个临时表，完成字段更新修改；然后对原表加MDL写锁，加锁成功后把临时表更名为原表，这样加锁时间很短

最早的 DDL 内部实现就是这么做的，不过加锁时间也不短。 “先基于原表创建一个临时表，完成字段更新修改；” 这个过程，要包含“把原表的数据导入临时表”，导入的过程还是对原表加 MDL 锁的。

git-zjx commented 4 years ago

各个锁的读锁、写锁会产生什么效果？

读锁之间兼容，读写、写写之间需要互相等待

git-zjx commented 4 years ago

Online DDL 完成后，为啥要升级 MDL 写锁再释放？

需要切表名

git-zjx commented 4 years ago

Online DDL 过程中，假设线程 A 正在执行第 3 步，给表加字段，这时线程 B 查询数据，正在遍历表中的数据。这样拿到的数据会不会对不上？

在DDL执行过程中读数据，看到的是修改之前的的表结构

git-zjx commented 4 years ago

Online DDL 为什么可以降为MDL读锁？

改元数据是在第四步和第五步之间做的，这时候确实持有写锁

git-zjx commented 4 years ago

为什么要先拿写锁再降为读锁再升级为写锁，好像也不符合二阶段锁协议的锁上升和下降的约束？

为了并发

git-zjx commented 4 years ago

如果autocommit=1设置了，select之前不显示的开启一个事务，也没有相应的commit是不是就不存在DML锁了？

DML一般指的是增删改，不过若果select ... lock in share mode; select ... for update也会有行锁

git-zjx commented 4 years ago

二级索引会把索引列+主键值作为排序键来排序？

是的，有保证主键算进去，还是有序的