全局锁

使用 FLUSH TABLES WITH READ LOCK (FTWRL) 命令即可开启全局数据库读锁，所有写相关的操作都会被阻塞，如新增、更新、删除数据、新建表、修改表结构、更新类的事务提交，其主要作用是全库的数据备份。

开启全局锁产生两个影响，分别是主库无法对外提供更新服务，在从库开启，Binlog 无法同步，加大主从的同步延迟。

如何避免开启 FTWRL 全局读锁备份避免数据库无法提供服务问题？还记得之前学习事务的隔离级别吗，其中就有一个「可重复读」**的隔离级别，只要开启的是这个隔离级别的备份就是安全的，前提是表引擎是 InnoDB 而非 MyISAM，同时，官方提供 mysqldump —single-transaction 来开启事务（默认是可重复读）方式备份，详情参考 mysqldump —help。

表级锁

表级锁的命令开启语法 LOCK TABLES，对应的解锁命令是 UNLOCK TABLES。

LOCK TABLES 语法

  1. LOCK TABLES
  2.     tbl_name [[AS] alias] lock_type
  3.     [, tbl_name [[AS] alias] lock_type] ...
  5. lock_type: {
  6.     READ [LOCAL]
  7.   | [LOW_PRIORITY] WRITE
  8. }
  10. UNLOCK TABLES

当前 Session 只能访问加锁后的表，如果需要访问多个表，则需要加锁多个表，如下所示。

  1. mysql> LOCK TABLES t READ;
  2. mysql> SELECT * FROM t;
  3. mysql> SELECT * FROM t2;
  4. ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

同一条 SQL 内不能对同一个表引用多次，如果想要引用多次，则需要增加别名方式访问，如下所示。

  1. mysql> LOCK TABLES t WRITE, t AS t1 READ;
  2. mysql> INSERT t SELECT * FROM t;
  3. ERROR 1100: Table 't' was not locked with LOCK TABLES
  4. mysql> INSERT t SELECT * FROM t AS t1;

表别名也需要加锁声明，如下所示。

  1. mysql> LOCK TABLES t READ;
  2. mysql> SELECT * FROM t AS t1;
  3. ERROR 1100: Table 't1' was not locked with LOCK TABLES

UNLOCK TABLES

有四种情况都可以解锁，分别是显式调用 UNLOCK TABLES 命令，重新使用 LOCK TABLES 命令，开启事务（START TRANSACTION），当前 Session 断开连接（不管是主动还是被动）。

现在，我们通过 SET autocommit=0 来开启事务，不使用 START TRANSACTION 的原因是由于该开启方式会释放之前的锁，并显式的使用 UNLOCK TABLES 命令解锁，如下所示。

  1. mysql> SET autocommit=0;
  2. mysql> LOCK TABLES t1 WRITE, t2 READ, ...;
  3. ... do something with tables t1 and t2 here ...
  4. mysql> COMMIT;
  5. mysql> UNLOCK TABLES;

注意，COMMIT 和 ROLLBACK 都不会释放 t1 / t2 上的锁。

MDL 锁 | Metadata Lock

MDL 锁又称为 DDL 的读写锁，对于表数据的 DML/DQL（增删改查）操作时共享读锁，但对于表的 DDL（修改表结构）操作就相当于写锁，写锁属于互斥锁，有且只能有一个线程占有。

DML: Data Modification Language（表数据修改）
DQL: Data Query Language（表数据查询）
DDL: Data Define Language（表结构定义）

如果其他线程获取了读锁，写锁必须等读锁都释放，请看以下场景。

Session A Session B Session C Session D
begin;
select … T;
select … T;
alter table T …; (blocked)
select … T; (blocked)

Session C 必须等 Session B 的查询结束，否则会被阻塞，更糟糕的是 Session D 这种读锁也被阻塞了。那么只有 Session A 和 Session B 的事务提交了之后 MDL 读锁才会被释放，所以存在长事务的情况下盲目修改表结构是很危险的事情。

既然知道了危害，我们就可以对症下药，通过以下方式避免

  1. 执行 DDL 前查询否存在长事务，查询 information_schema.innodb_trx，并将其杀死，显然这是治了头坏了脚的做法
  2. 很自然想到的是，执行 DDL 时使得线程不阻塞即可
    1. alter table T NOWAIT ….（只能在 MariaDB 中可用）
    2. alter table T WAIT N …（只能在 MariaDB 中可用）

行级锁

对于同一行的数据进行修改获取的锁是互斥的，必须等到前一个线程的修改完毕且事务已提交才允许下一条线程的修改。由于一条线程对同一个表的多条数据上锁，因此并发修改同一个表的时候就会容易触发死锁，以下方式可重现死锁状态。

Session A Session B
begin;
update T set k=k+1 where id=1; begin;
update T set k+2 where id=2;
update T set k=k+1 where id=2;
commit; update T set k=k+1 where id=1;
commit;

解决死锁的配置

  • innodb_lock_wait_timeout 默认 50s；
  • innodb_deadlock_detect 默认 on（开启）因此我们平时并发量少时并不会察觉死锁的存在，由于死锁检查需要需要大量的 CPU 资源，所以最好的方法还是通过约定开发规范；


约定开发避免**

  • 同一个事务里不能对同一个表的多条记录更新；
  • 避免使用长事务；

Next-Key lock

Next-key lock 由间隙锁及行锁组成，这是一个作用于索引上的锁。所谓的间隙锁（gap lock）就是两个索引值之间的间隙，例如，数据库中某一列的索引上记录了 11, 17, 22 三个值，那么可产生间隙锁 (11, 17) 和 (17, 22)，该间隙中不能插入新的索引叶子节点，简单来说就是禁止 SQL 的 insert / update DML 操作，而行锁（record lock）则是索引值上的锁。还是以 11, 17, 22 三个值来举例，行锁则是在 11, 17, 22 三条上的锁，通过间隙锁和行锁组合而成的 next-key lock 则变成 (11, 17] 和 (17, 22]，是一个前开后闭的区间。

Next-key lock 的引入是为了解决可重复读隔离级别下的幻读问题，也是 select … lock in share mode / select … for update 的加锁方式。