MySQL的锁

MySQL中，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁

全局锁如何使用？

1
flush tables with read lock

执行后，整个数据库就处于只读状态了。其他线程执行以下操作，都会被阻塞：

  • 对数据的增删改操作，例如insert、delete、update
  • 对表结构的更改操作，例如alter table、drop table

执行这条命令来释放全局锁：

1
unlock tables

全局锁应用场景是什么？

全局锁主要用于全库逻辑备份，这样在备份期间，不会因为数据或者表结构的更新，而导致备份与原件不一致的情况。

但是加上全局锁后，整个数据库都是只读状态，那么这段时间内业务只能读数据，而不能更新数据，导致业务停滞。

那用什么方式可避免备份时全局锁影响业务？

如果数据库引擎支持的事务支持可重复读的隔离级别（RR），那么在备份数据库之前先开启事务，会先创建Read View，然后整个事务执行期间都在用这个Read View，而且由于MVCC的支持，备份期间业务仍然可以对数据进行更新。

因为在RR的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的Read View，这样备份期间内备份的数据一直是在开启事务时的数据。

备份数据库的工具是mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

表级锁

表级锁包括：

  • 表锁
  • 元数据锁MDL
  • 意向锁
  • AUTO-INC锁

表锁

对某个表加锁，可以使用如下的命令：

1
2
3
4
5
//表级别的共享锁，即读锁
lock tables mytable read;

//表级别的独占锁，即写锁
lock tables mytable write;

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

例如，本线程对mytable加了「共享锁」，那么不仅是其他线程，即便是本线程，接下来对于mytable的写操作都是会被阻塞的。

要释放表锁，使用下面的命令，会释放当前会话的所有表锁：

1
unlock tables

元数据锁MDL

不需要显式地使用MDL，对数据库表进行操作时，会自动给这个表加上MDL：

  • 对一张表进行CRUD操作时，加的是MDL读锁
  • 对一张表进行结构变更时，加的是MDL写锁

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL不需要显式调用，那么何时释放？

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个场景：

  1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
  2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
  3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

之所以线程C阻塞会导致后续select线程均被阻塞，这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全地对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果有，可以考虑kill掉这个长事务，然后再做表结构的变更。

意向锁

  • 在使用InnoDB引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」
  • 在使用InnoDB引擎的表里对某些记录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

AUTO-INC锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

行级锁

InnoDB引擎是支持行级锁的，MyISAM不支持行级锁。

行级锁分为三类：

  • Record Lock，记录锁，仅将一条记录锁住
  • Gap Lock，间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock，前两者的组合，锁定一个范围的同时锁定记录本身

Record Lock

Record Lock是记录锁，记录锁有S锁和X锁之分：

  • 当一个事务对一条记录加了S锁后，其他事务也可继续对该记录加S锁（S锁与S锁兼容），但不可对该记录加X锁（S锁和X锁不兼容）
  • 当一个事务对一条记录加了X锁后，其他事务不可对该记录加S锁（S锁和X锁不兼容），也不可对该记录加X锁（X锁和X锁不兼容）
1
2
mysql > begin;
mysql > select * from mytable where id = 1 for update;

例如，以上就是对mytable中id=1的记录加上了X锁。

Gap Lock

Gap Lock只存在于可重复读的隔离级别，目的是为了解决RR隔离级别下幻读的问题。

假设，表中有一个范围id为(3, 5)的Gap Lock，则其他事务就无法插入id=4的记录了。

Next-Key Lock

Next-Key Lock是临键锁，锁定一个范围的同时锁定记录本身。

假设，表中有一个范围id为(3, 5]的临键锁，那么其他事务不能插入id=4的记录，也不能修改id=5的记录。