MySQL原理（三）锁定机制（2）表锁行锁与页锁

前面提到，mysql锁按照操作颗粒分类，一般认为有表级锁、行级锁、页面锁三种。其实还有一种特殊的全局锁。

锁	场景	问题
全局锁	全库逻辑备份	加了全局锁之后，整个数据库都是【只读状态】，如果数据库里有很多数据，备份就会花费很多的时间，这样会造成业务停滞。
表锁	当存储引擎不支持行级锁时，使用表锁。 SQL 语句没有匹配到索引时，使用表锁。	表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。
元数据锁（MDL）	对表做增删改查时，会加上 MDL 读锁。 对表结构做变更时，会加上 MDL 写锁。	加上 MDL 锁后，后续所有对该表的访问都会被阻塞。
行锁	增删改查匹配到索引时，会使用行级锁。	多个事务在互相等待对方的行锁释放，导致死锁。

规范使用事务，及时提交事务，避免使用大事务，DDL 操作及备份操作放在业务低峰期执行。

MySQL 各种锁的显式加锁解锁方式：

锁	显式加锁	显式解锁
全局锁	flush tables with read lock; // 即FTWRL，推荐使用	unlock tables;
表锁	lock table tableName read; # 表读锁/表共享锁 lock table tableName write;# 表写锁/表排他锁	unlock tables; # 客户端断开的时候也会自动释放锁。
行锁	select … from … lock in share mode; # 行共享锁 SELECT * FROM student FOR UPDATE; # 行排他锁	commit; rollback; # 事务提交或回滚释放：

一、全局锁

1、介绍

全局锁就是对整个数据库实例加锁，主要被备份工具使用，使用 逻辑方式进行备份（mydumper，mysqldump） 或 物理方式进行备份（percona-xtrabackup） 。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock ，简称 FTWRL ，保证了数据的一致性，在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

2、加锁解锁

# 加锁
flush tables with read lock; # 即FTWRL，推荐使用 # 解锁
unlock tables; 

3、弊端

加了全局锁之后，整个数据库都是【只读状态】，如果数据库里有很多数据，备份就会花费很多的时间，这样会造成业务停滞，有很大的弊端：

（1）备份期间，业务只能读数据，而不能更新数据；
（2）由于 FTWRL 需要关闭所有表对象，当这个时候还有长时间的 select 堵塞 FTWRL， 因为FTWRL会释放所有空闲的table缓存，如果有占用者占用某些table缓存，则会等待占用者自己释放这些table缓存，再去关闭表对象，执行命令时容易导致库卡死；
（3）如果在主库上备份。那么在备份期间都不能执行写入操作；
（4）如果在从库上备份。那么在备份期间，从库不能执行主库同步过来的 binlog，从而造成主从延时。

4、优化方法

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

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

二、表级锁

表锁 是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

不依赖于储存引擎（不管你是MySQL的什么存储引擎，对于表锁的策略都是一样的），开销小（因为力度大）。表锁不会产生死锁问题。但是表锁会影响并发率

1、表锁与元数据锁

MySQL里面表级别的锁有两种：一种是 表锁，一种是 元数据锁（metadata lock，MDL） 。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。默认使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

1.1、表锁

# 隐式上锁（默认，自动加锁自动释放
insert、update、delete 上写锁
# 显式上锁（手动）
lock table tableName read; # 读锁/共享锁
lock table tableName write;# 写锁/排他锁#解锁
unlock tables; # 客户端断开的时候也会自动释放锁。# 查看表上加过的锁
show open tables;

 表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。 

• 表级读锁 : 当前表加read锁，当前连接和其他的连接都可以读操作；但是当前连接写操作会报错，其他连接写操作会被阻塞。

• 表级写锁 : 当前表加write锁，当前连接可以对表做读写操作，其他连接对该表所有操作（读写操作）都被阻塞。

1.2、元数据锁

1.2.1、介绍：

metadata lock，简称 MDL。

Metadata lock 机制是为了保证数据一致性存在的。当有用户A查询一个表中的数据时，另一个用户B要对这个表结构做变更，删了一列，那么用户A拿到的结果跟表结构就不一致了，就会出现 waiting for table metadata lock。

Metadata lock 加锁过程是系统自动控制，无法直接干预，自动提交模式下，单语句就是一个事务，执行完了，事务也就结束了。

Metadata lock 是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请 Metadata lock ，DML操作需要 Metadata lock 读锁，DDL操作需要 Metadata lock 写锁，读锁和写锁的阻塞关系如下：

• 读锁和写锁之间相互阻塞，即同一个表上的DML和DDL之间互相阻塞。
• 写锁和写锁之间互相阻塞，即两个会话不能对表同时做表定义变更，需要串行操作。

读锁和读锁之间不会产生阻塞。也就是增删改查不会因为 Metadata lock 产生阻塞，可以并发执行，日常工作中大家看到的Metadata lock 之间的锁等待是innodb行锁引起的，和 Metadata lock 无关。

申请 Metadata lock 的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 Metadata lock 后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。

InnoDB行锁分类和 Metadata lock 很类似，也主要分为读锁和写锁，或者叫共享锁和排他锁，读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁，一个是行锁，且行锁中的读写操作对应在 DML 中都属于读锁。

注意： 支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

1.2.2、解决方法

MDL 锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。要尽量避免 MDL 锁阻塞的发生：

（1）开启 metadata_locks 表记录 MDL 锁。

（2）设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。

（3）规范使用事务，及时提交事务，避免使用大事务。

（4）增强监控告警，及时发现 MDL 锁。

（5）DDL 操作及备份操作放在业务低峰期执行。

2、类型

mysql的表级锁定主要分为两种：读锁定、写锁定。mysql中主要通过4个队列来维护这两种锁定：两个存放当前正在锁定中的读和写锁定信息，另外两个存放等待中的。

Current read-lock queue (lock->read)；Pending read-lock queue (lock->read_wait)；

Current write-lock queue (lock->write)；Pending write-lock queue (lock->write_wait)。

2.1、读锁定

一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：

（1）请求锁定的资源当前没有被写锁定；

（2）写锁定等待队列（Pendingwrite-lockqueue）中没有更高优先级的写锁定等待；

如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入Currentread-lockqueue中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列Pendingread-lockqueue中等待资源的释放。

2.2、写锁定

当客户端请求写锁定的时候，MySQL首先检查在Currentwrite-lockqueue是否已经有锁定相同资源的信息存在。如果Currentwrite-lockqueue没有，则再检查Pendingwrite-lockqueue，如果在Pendingwrite-lockqueue中找到了，自己也需要进入等待队列并暂停自身线程等待锁定资源。反之，如果Pendingwrite-lockqueue为空，则再检测Currentread-lockqueue，如果有锁定存在，则同样需要进入Pendingwrite-lockqueue等待。当然，也可能遇到以下这两种特殊情况：

（1）请求锁定的类型为WRITE_DELAYED;

（2） 请求锁定的类型为WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE，同时Currentreadlock是READ_NO_INSERT的锁定类型。

当遇到这两种特殊情况的时候，写锁定会立即获得而进入Current write-lock queue 中，如果刚开始第一次检测就Currentwrite-lockqueue中已经存在了锁定相同资源的写锁定存在，那么就只能进入等待队列等待相应资源锁定的释放了。

3、规则

读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：

（1） 除了READ_HIGH_PRIORITY的读锁定之外，Pendingwrite-lockqueue中的WRITE写锁定能够阻塞所有其他的读锁定；

（2）READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pendingwrite-lockqueue中的写锁定；

（3）除了WRITE写锁定之外，Pendingwrite-lockqueue中的其他任何写锁定都比读锁定的优先级低。

随着MySQL存储引擎的不断发展，目前MySQL自身提供的锁定机制已经没有办法满足需求了，很多存储引擎都在MySQL所提供的锁定机制之上做了存储引擎自己的扩展和改造。

4、扩展

虽然对于我们这些使用者来说MySQL展现出来的锁定（表锁定）只有读锁定和写锁定这两种类型，但是在MySQL内部实现中却有多达11种锁定类型，由系统中一个枚举量（thr_lock_type）定义，各值描述如下：

锁定类型	说明
IGNORE	当发生锁请求的时候内部交互使用，在锁定结构和队列中并不会有任何信息存储
UNLOCK	释放锁定请求的交互用所类型
READ	普通读锁定
WRITE	普通写锁定
READ_WITH_SHARED_LOCKS	在Innodb中使用到，由如下方式产生如：SELECT...LOCKINSHAREMODE
READ_HIGH_PRIORITY	高优先级读锁定
READ_NO_INSERT	不允许ConcurentInsert的锁定
WRITE_ALLOW_WRITE	这个类型实际上就是当由存储引擎自行处理锁定的时候，mysqld允许其他的线程再获取读或者写锁定，因为即使资源冲突，存储引擎自己也会知道怎么来处理
WRITE_ALLOW_READ	这种锁定发生在对表做DDL（ALTERTABLE...）的时候，MySQL可以允许其他线程获取读锁定，因为MySQL是通过重建整个表然后再RENAME而实现的该功能，所在整个过程原表仍然可以提供读服务
WRITE_CONCURRENT_INSERT	正在进行ConcurentInsert时候所使用的锁定方式，该锁定进行的时候，除了READ_NO_INSERT之外的其他任何读锁定请求都不会被阻塞
WRITE_DELAYED	在使用INSERTDELAYED时候的锁定类型
WRITE_LOW_PRIORITY	显示声明的低级别锁定方式，通过设置LOW_PRIORITY_UPDAT=1而产生
WRITE_ONLY	当在操作过程中某个锁定异常中断之后系统内部需要进行CLOSETABLE操作，在这个过程中出现的锁定类型就是WRITE_ONLY

三、行级锁

1、介绍

顾名思义，MySQL的行锁每次操作锁住一行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，但开销大，加锁慢，而且会出现死锁。行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为所知的Innodb存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。

2、原理

考虑到行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别，而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，InnoDB行锁是通过对索引数据页上的记录加锁实现的，所以即使访问的不同记录，只要使用的是同一索引项，也可能会出现锁冲突。

Innodb的行级锁定分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

3、实现算法

行锁主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

（1）RecordLock锁（记录锁）：锁定单个行记录的锁，如果表中没有主键和任何一个索引，那InnoDB会使用隐式的主键来进行锁定。（RC、RR隔离级别都支持）

（2）GapLock锁（间隙锁）：锁定索引记录间隙，确保索引记录的间隙不变，但不包含记录本身。（RR隔离级别支持）

（3）Next-key Lock 锁（记录锁+间隙锁）：记录锁和间隙锁组合，锁定数据前后范围，并且锁定记录本身。（RR隔离级别支持）

注意： 在RR隔离级别，InnoDB对于行的查询都是采用 Next-Key Lock 的组合锁定算法，但是 在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock 会降级为 Record Lock ，仅锁住索引本身而非范围。

对于InnoDB，如果设置 AUTOCOMMIT = 1，如果没有 显式explicit（即写出begin transaction语句），任何一个语句，都是独立的一个事务，每个语句前面隐式（implicit）加了begin transaction，然后随后自动commit。如果设置AUTOCOMMIT = 0，没有写commit语句，那么所有的语句都在一个事务里，等着最后写一个commit去提交不管commit语句出现没有，都会有Redo Log和Undo Log（commit语句的标志会记录在Redo Log里）。

下面具体看下针对不同的sql语句采用的是那种加锁方式：

1）select ... from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁。2）select ... from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。3）select ... from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。4）update ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。5）delete ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。6）insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

4、两阶段加锁协议

两阶段加锁协议-S2PL（Strict-2PL）：在一个事务里面，分为加锁(lock)阶段和解锁(unlock)阶段,也即所有的lock操作都在unlock操作之前，只有提交(commit)或者回滚(rollback)时才是解锁阶段，

事务A	事务B
begin; update user set k=k+1 where id = 1 update user set k=k+1 where id = 2
	begin; update user set k=k+1 where id = 1
commit

事务A持有的两个记录的行锁都是在commit的时候才释放的，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。

在InnoDB事务中，行锁是在需要的时候才加，但并不是不需要了就立刻释放，而是事务结束时释放。如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的语句尽量往后放。

5、Innodb 各事务隔离级别下锁定及死锁

Innodb实现的在ISO／ANSISQL92规范中所定义的ReadUnCommited，ReadCommited，RepeatableRead和Serializable这四种事务隔离级别。同时，为了保证数据在事务中的一致性，实现了多版本数据访问。

6、死锁和死锁检测

并发系统中出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，会导致几个线程无限等待，称为死锁。

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

7、死锁解决策略

（1）一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
（2）另一种策略是，发起 死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

四、页级锁

每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般，页级锁和行级锁一样，会发生死锁。应用在 BerkeleyDB存储引擎中，一般很少见，了解一下即可。

五、合理利用锁机制优化MySQL

1、MyISAM 表锁优化建议

1.1、缩短锁定时间

（1）尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；

（2）尽可能的建立足够高效的索引，让数据检索更迅速；

（3）尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；

（4）利用合适的机会优化MyISAM表数据文件；

1.2、分离能并行的操作

MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：

（1）concurrent_insert=2，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行ConcurrentInsert;

（2）concurrent_insert=1，当MyISAM存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行ConcurrentInsert;

（3）concurrent_insert=0，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许ConcurrentInsert。

1.3、合理利用读写优先级

MySQL的表级锁定对于读和写是有不同优先级设定的，默认写优先级要大于读优先级。所以，通过设置系统参数选项low_priority_updates=1，可以将写的优先级设置为比读的优先级低，即可让告诉MySQL尽量先处理读请求。

2、Innodb 行锁优化建议

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

（1）尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；

（2）合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

（4）尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

（5）在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；