MySQL锁—全局锁、表级锁、行级锁详解

本文主要是介绍MySQL锁—全局锁、表级锁、行级锁详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL 锁

MySQL的锁按照锁的粒度可以分为全局锁、表级锁和行级锁。

一、全局锁

1. 概念

  全局锁，是对整个数据库实例加锁，加锁后整个实例处于只读状态，后续的DML、DDL语句以及已经执行更新操作的事务提交语句都将被阻塞。

2. 应用场景

  数据的一致性备份

3. 语法示例

--加全局锁
flush tables with read lock; 
/*以
主机192.168.206.131
用户名root
密码123456
数据库test
为例*/
--注意，mysqldump是MySQL提供的一个数据备份工具，不是MySQL的命令，要在命令行窗口中执行
mysqldump -h192.168.206.131 -uroot -p123456 test > test.sql 
--释放锁
unlock tables;

4. 特点

  对整个数据库加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间不能执行任何更新操作，业务基本上属于停摆状态。

• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志( binlog )，会导致主从延迟。

  在InnoDB引擎中，我们可以在备份时加上 --single-transaction 参数来完成不加锁的一致性数据备份。底层实现是：

• 设置事务的隔离级别为可重复读，即REPEATABLE READ
• start transaction with consistent snapshot
• 完成备份操作
• 提交事务

  在Repeatable Read隔离级别下，一致性视图是在执行start transaction with consistent snapshot时创建的。因此，即使在备份过程中有其他事务更新数据，也没有影响，从而达到了数据的一致性。

二、表级锁

  表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率高，并发度低，应用在MyISAM、InnoDB、BDB等存储引擎中。
主要分为以下三类：

• 表锁
• 元数据锁
• 意向锁

1. 表锁

  表锁分为表共享读锁和表独占写锁两种。

-- 表名user 加锁
lock tables user read/write;
--释放锁
unlock tables;

  其特点为：加了读锁，当前客户端可以读，写会报错，其他客户端可以读，写会阻塞。加了写锁，当前客户端可以读也可以写，其他客户端的读和写都会被阻塞。

2. 元数据锁

  元数据锁( MDL )加锁过程是系统自动控制，无需显式使用。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
  这里的元数据，可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
  在MySQL 5.5中引入了MDL，当对一张表进行增删改查的时候，加元数据共享锁( SHARED_READ 或 SHARED_WRITE )；当对表结构进行变更操作的时候，加元数据排他锁( EXCLUSIVE )。其中元数据共享锁之间是兼容的，元数据排他锁和元数据共享锁之间是互斥的。也就是说，如果我们在当前客户端开启了事务，并对某张表进行了增删改查操作，但未提交，那么在另一个客户端中，我们同样可以进行增删改查，但一旦我们想修改表的结构，则会被阻塞。
  可以通过下面的SQL查看数据库中的元数据锁情况：

select object_type,object_schema,object_name,lock_type,lock_duration from
performance_schema.metadata_locks;

3. 意向锁

  试想这样一个场景，客户端A对某张表的某行数据进行UPDATE操作，对该行加了行锁，而客户端B想要对这张表加表锁，那么客户端B就需要遍历整张表来判定这张表有没有行锁，有哪种行锁，这就导致客户端B的效率很低。所以，为了避免DML在执行时加的行锁与表锁冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
  应用了意向锁之后，客户端A，在对某张表执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。而客户端B，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。一旦事务提交，意向共享锁和意向排他锁都会自动释放。

• 意向共享锁( IS )：由语句select … lock in share mode添加。与表锁共享锁( read )兼容，与表锁排他锁( write )互斥。
• 意向排他锁( IX )：由insert、update、delete、select…for update添加。与表锁共享锁( read )及排他锁( write )都互斥，意向锁之间不会互斥。

  可以通过下面的SQL查看数据库中的元数据锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

三、行级锁

  行级锁，每次操作锁住对应的行数据。锁定粒度小，发生锁冲突的概率低，并发度高。应用在InnoDB存储引擎中。
  InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

• 行锁( Record Lock )：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
• 间隙锁( Gap Lock )：锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。
• 临键锁( Next-Key Lock )：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

1. 行锁

  InnoDB实现了以下两种类型的行锁：

• 共享锁( S )：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排他锁( X )：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

	S(共享锁)	X(排他锁)
S(共享锁)	兼容	互斥
X(排他锁)	互斥	互斥

  常见的SQL语句，在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT …	不加锁
SELECT … LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

  默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

  同样，可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

2. 间隙锁&临键锁

  默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁。
• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key锁退化为间隙锁。
• 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止。

下面我们模拟一下这三种场景：
  如果某张表以id为主键，只有id为3和10的数据，客户端A开启事务，对id为6的数据进行UPDATE操作，那么会在索引项3和10之间(不包含3和10)加入间隙锁，此时客户端B无法插入id在3和10之间的任何数据。

  如果某张表在age字段上建立了非唯一普通索引，只有age为21、23和28的数据，客户端A开启事务，对age为23的数据进行SELECT … LOCK IN SHARE MODE操作，那么会在对索引项23加入间隙锁，同时在索引项23和28之间加入间隙锁，此时客户端B无法插入age在21和28之间的任何数据，同时由于客户端A对索引项23加入了共享锁，客户端B也无法对索引项23再加入排他锁。

  如果某张表以id为主键，有id为3~10的数据，客户端A开启事务，对id>=6的数据进行SELECT … LOCK IN SHARE MODE操作，那么会对索引项6加入行锁，在索引项7、8、9、10加入临键锁，在supremum pseudo-record(可以理解为正无穷处)也加入临键锁。

  间隙锁的唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

注：行级锁的机制可以避免一部分幻读的产生，但无法完全避免。例如某张表中存在id为3和10的数据，在客户端A开启事务对id为6的数据进行SELECT …，只会对该表加入元数据共享锁，客户端B在不受影响的情况下插入了id为6的数据并提交了事务，客户端A想要插入id为6的数据则发现id为6的数据已经存在，还是出现了幻读现象。

这篇关于MySQL锁—全局锁、表级锁、行级锁详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---