mysql的锁

整理自 丁奇的MySQL实战45讲

1. 全局锁

• 命令
  Flush tables with read lock (FTWRL)
  使用这个命令后，其他线程的以下语句会被阻塞：

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

• 场景
  做全库逻辑备份，也就是把整个库的所有表都select出来存成文本

• 风险

  • 备份期间都不能执行更新，业务基本就得停摆
  • 如果你在从库备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

• 替代方法

  • mysqldump -single-transaction，导数据时会启动一个事务，来确保拿到一致性视图。而由于MVCC（多版本并发控制）的支持，这个过程中的数据可以正常更新。缺点：只有innodb支持。
  • set global readonly=true的方式，不建议使用，原因
    1. 在有些系统中，readonly的值会被是用来做其他逻辑。比如用来判断一个库是主库还是从库。因此修改global变量的方式，影响范围更大，我不建议使用。
    2. 在异常处理机制上有差异。如果执行FTWRL命令之后，由于客户端发生异常断开。那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库的设置readonly之后，如果客户端发生异常。在数据后就会一直保持readonly的状态。这样会导致整个库长时间处于不可写的状态，风险较高。

2. 表级别锁
   1. 表锁
      • 语法： lock tables ... read/write;
        与FTWRL类似，可以用unlock tables 主动释放锁；也可以在客户端切断的时候自动释放。需要注意的是，lock tables语法除了会限制别的县城的读写外，也限定了本线程接下来的操作对象。
   2. 元数据锁（meta data lock,MDL）
      MDL 不需要显式使用，在访问一个表的时候会自动被加上。MDL的作用是，保证书写的正确性。
      MySQL5.5版本中引入了MDL，当对一个表做增删改查操作时，加MDL读锁；当需要对表结构变更操作时，加MDL写锁。
      • 读锁之间不互斥。因此，你可以有多个线程同时对一张表增删改查。
      • 读写锁之间、写锁之间是互斥的。用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段。其中一个要等另外一个执行完才能开始执行。
3. 行锁

• 行锁属于引擎层实现的，比如MyISAM引擎就不支持。
• 两阶段锁协议
  在innodb事务中，行锁是在需要的时候才加上的。但并不是不需要了就立即释放。而是等到事务结束才释放。