MySql学习（四）锁的基础知识

MySql锁的介绍

mysql锁分为以下几种

按锁的功能划分

共享读锁

读锁与读锁之间是可以共存的即线程A取得读锁，线程B也可以取得读锁

排他写锁

写锁与写锁以及读锁之间是互斥的线程A取得写锁，线程B只能等待线程A释放写锁才可以重新获得写锁或者读锁

按锁的粒度划分

全局锁（由MySql layer层也就是之前说的sql server层来实现）

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。
当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

实现方式

全局锁两种方法

1. FLUSH TABLES WRITE READ LOCK
2. set global readonly=true

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有几个原因：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。

二是，在异常处理机制上有差异。如果执行FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

三是，readonly 对super用户权限无效

注 ：业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。

即使没有被全局锁住，加字段也不是就能一帆风顺的，还有表级锁了

使用场景

全局锁的典型使用场景是，做全库逻辑备份（mysqldump）。重新做主从时候
也就是把整库每个表都 select 出来存成文本。
以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

数据库只读状态的危险性

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止。
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
注：上面逻辑备份，是不加–single-transaction参数

表锁

mysql表锁分为下面三种

1. 表锁
2. 元数据锁
3. 意向锁

表锁（由MySql layer层来实现）

表锁的添加

lock tables 表名 read; # 表示其他线程不能ddl 和 dml 中增删改，只能读取该表数据 
lock tables 表名 write; # 表示其他线程既不能读，也不能写 

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables
主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables
语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写
t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1
都不允许，自然也不能访问其他表。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables
命令来控制并发，毕竟锁住整个表的影响面还是太大

查看表锁

show open tables;

释放表锁

unlock tables;

表锁的使用场景

举一个最简单的例子，在统计报表的时候，如果统计的过程中有新的数据添加进来就会很麻烦，这个时候添加读锁，所有线程只能读数据不能写数据，这样就能很好的控制。
同步数据的时候也需要加表级锁。

元数据锁（meta data lock 由MySql layer层来实现）

元数据锁是mysql自动添加的为了防止DDL和DML并发的冲突 ，保证读写的正确性。
就是说你查询一张表的数据的时候自动添加MDL读锁，读锁与读锁之前是不冲突的所以大家都可以查询这张表，可是如果一个线程在修改表结构，这个时候自动添加MDL写锁，这时候所有线程都要等待该事物提交才可以对这张表进行DDL或者DML操作。

意向锁（存储引擎层实现）

意向锁是一种表锁，和元数据所以样无需手动添加

意向锁的目的

存储引擎为了解决表所与行级锁共存的问题设计了意向锁。
意向锁定协议如下：

1. 在事务可以获取表中某行的共享锁之前，它必须首先获取该表的IS锁。
2. 在事务可以获取表中某行的排它锁之前，它必须首先获取该表的IX锁。

如果一个锁与现有锁兼容，则将授予其请求的事务，但如果与现有锁冲突，则不授予该锁。事务进行等待直到现有冲突的锁被释放。如果请求的锁与现有锁定发生冲突，并且由于可能导致死锁，则会发生错误。
锁之间的冲突关系如下

意向锁不会阻止除全表请求（例如LOCK TABLES … WRITE）以外的任何内容。意向锁定的主要目的是表明有人正在锁定表中的行，或者打算锁定表中的行。

意向锁的存在是mysql在RR级别就可以避免幻读的出现。正常的应该是（Serializable）

举一个例子

一张表我有1000条数据，我想进行全表更新，那么我是不是应该首先判断有没有别的事物对这张表的某个记录加行级别的写锁。这样很耗费时间，而有了意向锁之后，事物A对表的某条记录取得行级别的X锁之前会先取得表级别的IX锁，然后再取得X锁。然后事物B要进行全表更新时也要先取得表级别的IX锁，这时候就会发生互斥然后等待，不用对每条记录去取得锁。

行级锁（由存储引擎层来实现）

行级锁锁的是某条数据或者是数据的间隙（避免幻读）
InnoDB的行级锁是通过给索引上的索引项加锁来实现的。因此只有通过索引条件检索出来的数据才能加行级锁，否则将转为表锁。

行级锁分为下面几种

1. 记录锁（record locks）

对数据库里面某条数据加锁

2. 间隙所（gap locks）

对两条数据之间的间隙加锁，其实就是对B+TREE中两个叶子结点加锁不允许新的节点插入进来。主要是为了防止幻读（比如我查询id > 0 and id < 4 本来是ID = 1 ID =2 两条数据，但是中间突然插入一条ID=3的数据这个就叫做幻读）

3. Next-key locks

是对记录以及记录之间的间隙加锁

而功能上分又分为

1. 共享读锁（S）
2. 排他写锁（X）

行锁的添加

读锁

select * from table where .... lock in share mode

写锁

select * from table where .... for update

间隙锁的添加

间隙锁其实和行锁是一样的，where条件是等值的话，范围的话是间隙所

读锁

select * from table where .... lock in share mode

写锁

select * from table where .... for update

行级锁与表锁的区别

表所：开销小，加锁快，粒度大，不会出现死锁，但是并发性低
行级锁：开销大，加锁慢，粒度小，会出现死锁，但是并发性高

按锁的实现方式划分

悲观锁

乐观锁