问题处理

场景

最近遇到的一个问题，在使用mysqldump备份数据的时候提示
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
备份使用的语句是
mysqldump --login-path=3306 --databases database --tables table --skip-add-locks --where="id in (select id from t1)" --replace --no-create-info >backup.sql

处理方法

从错误来看是读锁导致的问题，处理方法很简单，table是InnoDB表，在备份语句中加上--single-transaction就可以了。

原因分析

默认情况下，使用mysqldump的时候会使用lock-tables给要备份的表加上一个读锁，在上面的场景中，即给table表加上读锁。
然而，由于在这个备份语句中，--where条件里还有一个select id from t1，实际备份时执行的语句是SELECT /*!40001 SQL_NO_CACHE */ * FROMtableWHERE id in (select id from t1);。
在一个会话中，只能访问已经锁定的表，即table，不能访问未锁定的表t1，所以会提示错误ERROR 1100

--single-transaction 表示将事务隔离级别设置为RR，并且在备份前使用START TRANSACTION启动一个事务进行备份，对于InnoDB表，使用事务可以保持数据的一致性，并且不需要锁表。这个选项和lock-tables是互斥的，使用这个选项不会再加读锁。

其他关联知识点

锁相关

加锁

# 读锁
LOCK TABLES t1 READ
# 写锁
LOCK TABLES t2 WRITE
# 同时给多个表加锁
LOCK TABLES t1 READ, t2 READ
# 给所有表加读锁
FLUSH TABLES WITH READ LOCK

解锁

UNLOCK TABLES可以解锁所有当前会话中锁定的表
在使用LOCK TABLES进行加锁时，会解锁之前锁定的表。

mysqldump相关

--no-create-info 在结果中不写入建表语句
--no-data 不备份表内容
--skip-add-locks 这个选项并不表示在备份时不加锁，而是代表在备份的结果中，不添加LOCK TABLES和UNLOCK TABLES语句。添加锁表和解锁的语句可以在恢复备份时增加插入速度。

参考文档

mysqldump — A Database Backup Program
LOCK TABLES and UNLOCK TABLES Statements