Mysql中的锁 - PorcoBlog

本文最后更新于14 天前，其中的信息可能已经过时，如有错误请留言

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

一、全局锁

加全局锁的命令

flush tables with read lock

解开全局锁的命令

unlock tables

一般是MyISAM引擎在备份数据的时候，需要使用全局锁。InnoDB由于支持可重复读级别的事务，在备份数据库之前，会先开启事务，会先创建Read View，因为读取的都是快照中的数据。数据库在备份期间，仍然可以更新数据。

二、表级锁

表级锁包括：

表锁；元数据锁；意向锁；AUTO-INC锁

1、表锁

表级共享锁（read）：所有线程都只可以读被锁住的表，但都不能写被锁住的表；

//表级别的共享锁，也就是读锁；
//允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作。
lock tables t_student read;

表级独占锁（write）：只有锁住这张表的线程，可以读写被锁住的表，其他线程不能读也不能写；

//表级别的独占锁，也就是写锁；
//允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）。
lock tables t_stuent write;

MyISAM只支持表锁，InnoDB尽量不使用表锁，因为影响并发性能，InnoDB支持行锁

2、元数据锁（MDL，Meta Data Lock）

MDL不需要显示地通过命令指定，数据库会自动给表加上MDL

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

对一张表进行CRUD操作时，加MDL读锁，可以防止其他线程对表结构进行变更，因为其他线程进行结构变更需要加MDL写锁，但因为当前线程已经添加了MDL读锁，所以其他线程必须等待当前线程处理结束。

对一张表进行结构变更操作时，加MDL写锁，那么其他线程也不能进行CRUD操作，因为其他线程无法获取MDL读锁，直到当前线程的事务提交，并释放MDL写锁。

MDL锁会产生一个问题，如果有一个长事务的线程，开启了事务之后，一直没有提交，那么这个线程会一直占据MDL读锁。那么如果有另外一个线程试图更改表结构，那么会因为获取不到MDL写锁，而被阻塞。

如果只是阻塞了试图更改表结构的线程，倒是还好，但是mysql的实现机制是所以获取MDL锁的线程形成一个队列，其中写锁的优先级更高，MDL写锁拿不到，后续所有其他线程试图申请MDL读锁，也都会被阻塞，然后就会有大量的线程被阻塞，数据库的线程很快会爆满。

所以需要注意，在做数据库表结构变更的时候，把长事务所在的线程事务先提交。

3、意向锁

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

意向共享锁（其实下面的命令是添加记录共享锁，mysql自动添加表级意向共享锁）：

select * from test_table where id = 3 lock in share mode;

意向独占锁（其实下面的命令是添加记录独占锁，mysql自动添加表级意向独占锁）：

select * from test_table where id = 3 for update;

其中对某些记录加的锁是行锁，后面会说。

意向共享锁的作用是，如果有另外一个线程，想给这张表添加一个表级的独占锁，会被阻塞。

意向独占锁的作用是，如果有另外一个线程，想给这张表添加一个表级的共享锁或者独占锁，都会被阻塞。

通过意向锁，可以不用扫描每一行记录，看看是否有行锁，效率更高。

而意向锁和表级锁是完全不同的，我本来以为是不是给表添加了意向共享锁或者意向独占锁，就相当于是给表添加了表级共享锁或者表级独占锁，其实不是的。

如果给表添加了表级共享锁，整张表是所有线程都不能写入数据的，意向共享锁没有这个功能。

如果给表添加了表级独占锁，那么其他线程整张表的数据都不能读或者写，实际上其他线程是可以读写的（没有被行锁锁住的记录）。

AUTO-INC锁

待完成…

三、行级锁

InnoDB支持行级锁，MyISAM只支持表级锁。

普通的select语句属于快照读，它读的是一个快照，不会加锁。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

其中共享锁，被称为S锁，独占锁称为X锁。

1、共享记录锁与独占记录锁

共享锁（S锁）

可以有多个线程对同一条记录加共享锁；

共享锁，读读兼容，读写互斥；

如果A线程拿到了共享锁，其他任何线程就不能对这条数据进行写操作，必须等待A线程的事务提交。

如果A线程、B线程同时拿到了共享锁，那么A、B线程都不能对这条记录进行写操作，因为A线程加的共享锁组织B线程的写操作，B线程加的共享锁，也阻止A线程的写操作。如果A要执行写操作，必须等待B的事务提交，如果B要执行写操作，必须等待A的事务提交，如果此时A执行写操作，B也执行写操作，那么就会死锁，例如下面两条事务，分别执行相应的代码，就会死锁。

-- A事务
start transaction;
select * from test_table where id = 3 lock in share mode;
update test_table
set name = 'name3_8'
where id = 3;
-- commit;

-- B事务
start transaction;
select * from test_table where id = 3 lock in share mode;
update test_table
set name = 'name3_9'
where id = 3;
-- commit;

独占锁（X锁）

独占锁（X锁）只有一个线程能加锁成功，其他线程对于被加锁的记录，想要读或者写都会被阻塞。

独占锁与共享锁是互斥的，也就是如果A线程对记录加了独占锁，B线程就不能给这条记录加共享锁，也不能加独占锁。或者A线程对记录加了共享锁，那么B线程就不能对这条记录加独占锁，必须等A的事务提交。

2、记录锁、间隙锁和临键锁

记录锁（Record Lock）

记录锁，锁住某一条记录。

记录锁分为S锁与X锁，也就是前面提到的共享记录锁与独占记录锁。

间隙锁（Gap Lock）

间隙锁只存在于可重复读隔离级别下，目的是为了解决可重复读隔离级别下的幻读现象。

如果表中有一个范围id为（3，5）的间隙锁，那么其他事务就无法在（3，5）中间插入数据，这样可以有效防止幻读发生。

间隙锁也要在S型间隙锁和X型间隙锁，但是并没有什么区别；间隙锁之间不会互斥，比如A事务持有范围id为（3，5）的间隙锁，B事务也可以持有相同范围id为（3，5）的间隙锁。

不管是S型的间隙锁还是X型的间隙锁，都能阻止其他事务向锁定的记录范围内插入数据，也就是说以下两条记录，都会阻止其他线程向[3，5]范围内插入数据。

但以下两条记录仍然会互斥，虽然间隙锁不会引起互斥，但是记录锁的独占锁与共享锁是互斥的。

start transaction;
select * from test_table where id >= 3 and id <= 5 lock in share mode;

start transaction;
select * from test_table where id >= 3 and id <= 5 for update;

临键锁（Next-Key Lock）

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

-- A事务
start transaction;
select * from test_table where id >= 3 and id <= 5 lock in share mode;

-- B事务
start transaction;
select * from test_table where id >= 3 and id <= 5 for update;

我理解A事务会添加[3，5]的共享记录锁+[3，5]的间隙锁，B事务会添加[3，5]的独占记录锁+[3，5]的间隙锁。

A事务加锁之后，其他事务不能更改相应的记录，也无法在范围内插入其他记录。

B事务加锁之后，其他事务无法访问被加锁的记录，也无法在范围内插入其他记录。

插入意向锁

很难理解这个东西。。。