MySQL 的锁

Question

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。加了全局读锁后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。其中一个场景是 全局锁的典型使用场景是，做全库逻辑备份 。通过全局读锁来备份数据库可能面临如下风险

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

为了解决上面的问题，mysql 官方提供了 mysqldump 工具，如果使用 --single-transaction 导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。既然有了 mysqldump 这个参数，为什么还要 FTWRL ？是因为 --single-transaction 仅适用与支持事务的存储引擎，也就是 MyISAM 不支持，所以这个主要是用来备份 MyISAM 的，保证其在备份的时候不会导致数据不一致

表级锁

分成两种：表锁、元数据锁。

表锁是在 Server 层实现的。ALTER TABLE 之类的语句会使用表锁，忽略存储引擎的锁机制。表锁的语法是 lock tables … read/write 。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

行锁

查找哪个事务导致锁表

有时候数据库没有响应，运行 show processlist 发现有部分状态是 Waiting for table metadata lock ，为了查找是什么事务导致，查看 这里

SHOW ENGINE INNODB STATUS \G
-- Look for the Section -
TRANSACTIONS

-- 或者查找 INFORMATION_SCHEMA 数据库
-- all the locks transactions are waiting
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- list of blocking transactions
SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);
--
SELECT INNODB_LOCKS.*
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
  ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);
-- 查看特定的标
SELECT * FROM INNODB_LOCKS
WHERE LOCK_TABLE = db_name.table_name;