mysql从零开始（05）----锁

全局锁

使用

# 启用全局锁
flush tables with read lock
# 释放全局锁
unlock tables

开启全局锁后，整个数据库就处于只读状态了，这种状态下，对数据的增删改操作、对表结构的更改操作都会被阻塞。
另外，当会话断开，全局锁也会被自动释放

应用场景

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
如果不使用全局锁直接备份的话，可能会出现下面的情况：

用户A	用户B
开始备份用户A的银行余额
向B转账
结束备份	开始备份用户B的余额
这时,用户A的余额既没有减少,用户B的余额也增加了

缺点

如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

改进

要求数据库的引擎支持的事务支持可重复读的隔离级别（隔离级别相关的知识见：隔离），如InnoDB，但是比如MyISAM不支持事务，就不能应用以下的方法。
在备份数据库之前先开启事务，会先创建Read View，然后整个事务期间都用这个Read View，而且由于MVCC的支持，备份期间业务也可以对数据库进行更新操作。
因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。

表级锁

MySQL 里面表级别的锁有这几种：

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

表锁

使用

对表test使用表锁

# 表级别的共享表锁，也就是读锁；
lock tables test read;# 表级别的独占表锁，也就是写锁；
lock tables test write;# 释放表锁
unlock tables

如果本线程对某表加了「共享表锁」，那么所有线程接下来如果要对该表执行写操作的语句，都会被阻塞，直到锁被释放。当会话退出后，也会自动释放所有表锁。
另外，尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能。

元数据锁MDL

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

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

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更：当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL释放时机

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

可能导致的问题

考虑以下场景：

线程A	线程B	线程C
开启事务
select(此时自动加上MDL读锁)
	select(不会阻塞,因为读读事务不冲突)
		修改表字段(阻塞)

在线程C阻塞后,后续对该表的所有select语句都会被阻塞,可能导致数据库连接线程用光

原因

出现上面那种情况的原因是因为MDL锁的操作会形成一个队列,因为写锁的优先级高于读锁,一旦队列中出现写锁等待,后面加入的读锁请求都将在写锁后面等着
所以,如果要对表结构进行变更,应当先查看数据库中是否有比较长的事务,如果有的话可以考虑kill掉长事务,再做表结构的变更

意向锁

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」
也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。但是，普通select语句也可以加共享锁和独占锁，如下：

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

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突（允许多个事务同时更改不同行的数据），而且意向锁之间也不会发生冲突（也允许边读边写），只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

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

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

AUTO_INC锁

概念

在插入数据时，可以不指定主键的值，使用AUTO_INCREMENT修饰，让mysql来自动维护主键自增，这个自增主要通过AUTO_INC锁来实现的。
在插入数据时，会自动添加一个表级别的AUTO_INC锁，然后为被AUTO_INCREMENT修饰的字段赋值，等插入语句完成后，才会把锁释放掉。既当一条事务在插入时，其他事务如果也想插入，都会被阻塞，从而保证该字段是连续递增的。相应的，这个操作也会影响插入的性能。

InnoDB

InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

当它在插入数据的时候，同样会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，但是在它给该字段赋完一个自增的值之后，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

行级锁

锁定读

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。这也是InnoDB的最大的优势

普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

# 对读取的记录加共享锁
select ... lock in share mode;# 对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。
共享锁又称S锁，满足读读共享、读写互斥，独占锁又称X锁，满足写写互斥、读写互斥

行级锁的类型主要有：

• Record Lock：记录锁，也就是仅仅把一条记录锁上
• Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身
• Next-Key Lock：临键锁，Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

Record Lock

记录锁锁住的是一条记录。记录锁有S锁和X锁之分，并且满足读读共享、读写互斥、写写互斥

mysql > begin;
# X锁，之后其他事务都无法修改该记录
mysql > select * from test where id = 1 for update;# 释放锁
mysql > commit;

Gap Lock

Gap Lock 只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
间隙锁锁住的是一个字段之间的间隙，其他事务无法向这个间隙中插入字段，如表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，以此防止幻读。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态（等待状态并不意味着事务成功获取到了锁，正常状态才是）

插入意向锁并不是意向锁，它是一种特殊的间隙锁，属于行级别锁
两个事务不可能同时一个拥有间隙锁，另一个拥有该间隙的插入意向锁

Next-Key Lock

锁定一个范围，并且锁定记录本身。Next-key lock既能保护该记录，又能阻止其他事务将新纪录插到被保护记录前面的间隙中。
如表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

因为Next-key lock包含记录锁，所以不能有两个事务同时获取相同范围的Next-key lock。