MySQL-锁

1、全局锁

使用全局锁，整个数据库就处于只读状态了，对数据的增删改操作，比如 insert、delete、update等语句，和对表结构的更改操作，比如 alter table、drop table 等语句，都会被阻塞。

全局锁语句

mysql> flush tables with read lock;

释放全局锁语句

mysql> unlock tables;

2、表级锁

2.1 表锁

对表加共享锁，不仅其他会话写操作会阻塞，当前会话写操作也会被阻塞。

例如，对表 t1 加共享锁（SHARED_READ_ONLY），也就是读锁。

mysql> lock tables t1 read;

对表加排他锁，会阻塞其他会话读写操作，不会阻塞当前会话对该表读写操作，当前会话不能对其他表进行读写操作。

例如，对表 t1 加排他锁（SHARED_NO_READ_WRITE）,也就是写锁。

mysql> lock tables test.t1 write;

释放表锁

mysql> unlock tables;

2.2 元数据锁

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。当我们对数据库表进行操作时，会自动给这个表加上 MDL：

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

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

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

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

2.3 意向锁

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

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

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

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

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

3、行级锁

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

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；

• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；

• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

3.1 记录锁

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;

• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。