MySQL - 实战(5) - 全局锁和表锁

MySQL - 实战(5) - 全局锁和表锁

目录

锁的分类 - 根据加锁范围

  • 全局锁
  • 表锁
  • 行锁

1 全局锁

1.1 定义

对整个数据库的实例加锁

1.2 实现方式

MySQL加全局锁的方式：

Flush tables with read lock (FTWRL)

解锁方式：

unlock tables

flush tables with read lock命令行窗口退出后，则数据库会恢复为执行该命令之前的状态

1.3 作用

加全局锁以后，整个数据库将处于只读状态，其他的线程的以下语句将会被阻塞：

  • 数据更新语句（数据的增删改）
  • 数据定义语句（包括建表、修改表结构等）
  • 更新类事务的提交语句

1.4 使用场景

全库逻辑备份：

  • 把整库每个表都 select 出来存成文本
  • FTWRL 确保没有其他线程对数据库更新，然后对整个库做备份

1.5 整库只读的问题

  • 主库备份，备份期间不能执更新，业务停摆
  • 从库备份，备份期间从库不能执行主库同步过来的 binlog，导致主从延迟

1.6 不加锁的问题

1.6.1 引发的问题
  • 不同表之间的执行顺序不同进而备份的时间不同
  • 某个表在时间差中进行了更新并且成功被备份，其关联表已备份完毕无法更新
  • 数据不一致
  • 视图逻辑不一致
1.6.2 针对视图逻辑的解决方法
  • 在可重复读隔离级别下开启事务可以得到一致性视图
  • 官方自带逻辑备份工具mysqldump
  • mysqldump 使用参数–single-transaction 在导数据前启动事务
  • 确保一致性视图
  • MVCC支持该过程中数据可以正常更新

1.7 一致性读需要引擎支持

  • MyISAM 是不支持事务的引擎
  • single-transaction 方法只适用于所有的表使用事务引擎的库
  • InnoDB 替代 MyISAM 的原因之一

1.8 set global readonly=true 实现全库只读

1.8.1 推荐FTWRL而非Readonly的原因

  • 有些系统中readonly 的值会用来做其他逻辑（比如判断主从库），修改 global 变量影响大
  • 异常处理机制上的差异：
    • FTWRL会随着连接（session）的断开而释放，整个库回到正常更新状态
    • readonly 在连接（session）断开后会保持，导致整个库长时间处于不可写状态，风险较高

2 表级锁

MySQL表级别锁分类：

  • 表锁
  • 元数据锁（meta data lock，MDL）

2.1 表锁

2.1.1 语法
lock tables … read/write
2.1.2 解锁：

  • 主动释放锁：

    unlock tables

  • 自动释放锁：

    客户端断开连接
2.1.3 lock table语法限制
  • 限制别的线程读写
  • 限定本线程的操作对象
2.1.4 示例

线程A执行：

lock tables t1 read, t2 write;

其他线程：

  • 写 t1、读写 t2 的语句阻塞

线程A：

  • 只能读 t1、读写 t2
  • 不能访问其他表
2.1.5 作用
  • 处理并发

2.2 元数据锁

2.2.1 特点和作用

特点：

  • 不需要显式使用，在访问一个表时自动加上
  • MySQL 5.5 版本引入

作用：

  • 保证读写的正确性
2.2.2 使用方式：
  • 增删改查操作（DML和DQL）：加 MDL 读锁
  • 表结构变更（DDL）：加 MDL 写锁
2.2.3 读锁和写锁：
  • 读锁之间不互斥，可多线程同时对一张表增删改查
  • 读写锁之间、写锁之间互斥
2.2.4 风险：
2.2.4.1 示例：小表加字段，整个库挂
  • 给表加字段，修改字段，或加索引，需扫描全表数据：
    • 索引根据表中的每一行的记录值创建，所以需要全表扫描；
    • 加字段或修改字段，要修改每一行记录中的对应列的数据，所以也要全表扫描
2.2.4.2 MDL申请锁以队列的形式：
  • 写锁获取优先级高于读锁
  • 写锁等待，当前操作阻塞，并阻塞该表后续操作
  • 申请拿到MDL锁后直到提交后才会释放该MDL锁
  • 队列为了防止锁饿死
2.2.4.3 特殊情况：
  • 如果事务包含DDL操作，MySQL会在DDL操作语句执行前，隐式提交commit，保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放
  • 例如 <begin;alter table ... ;select... ;>，此时一旦alter语句执行完成会马上隐式提交事务（autocommit=1），后面的select就在本次事务之外，其执行完成后不会持有读锁
2.2.4.4 Online ddl:
  • 为了解决MySQLddl读写互斥，严重影响性能

步骤：

  • 拿MDL写锁 - 确保没有其他ddl语句在执行
  • 降级成MDL读锁
  • 真正做DDL - 申请一块空间改表结构、填数据
  • 升级成MDL写锁 - 持有读锁，可避免其他ddl语句造成不一致性
  • 释放MDL锁 - 拿到写锁，代替表
2.2.4.5 问题：
  • 某表的查询语句频繁，且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满
2.2.5 如何安全给小表加字段：
2.2.5.1 长事务：
  • 解决长事务，事务不提交，就会一直占着 MDL 锁。
  • 在MySQL 的 information_schema 库的 innodb_trx 表中，查到当前执行中的事务。
  • 如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务
2.2.5.2 热点表，kill后马上会有新请求，则可以设置等待时间：
  • alter table 语句设定等待时间
  • 等待时间拿不到写锁放弃，不阻塞后面的业务语句，之后通过重试命令重复这个过程
2.2.5.3 开源实现：
  • MariaDB 已合并了 AliSQL 的这个功能
  • 这两开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

3 总结

  • 全局锁主要用在逻辑备份。对于全部是 InnoDB 引擎的库，使用–single-transaction 参数，对应用会更友好

  • 表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果应用程序里有 lock tables 这样的语句，可能的情况是：

    • 系统在用 MyISAM 这类不支持事务的引擎，要升级换引擎
    • 引擎已升级，但代码没升级。把 lock tables 和 unlock tables 改成 begin 和 commit解决
posted @ 2020-11-08 12:11  Pengc931482  阅读(146)  评论(0)    收藏  举报