MySQL——锁

MySQL锁的概述

锁是计算机协调多个进程或线程并发访问某- -资源的机制。在数据库中，除传统的计算资源(CPU、 RAM、1/0)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

MySQL锁的分类

• 全局锁：锁定数据库中的所有表
• 表级锁：每次操作锁住整张表
• 行级锁：每次操作锁住对应的行数据

1.全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态,后续的DML的写语句，DDL语句， 以及更新操作的事务提交语句都将被阻塞。
典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

#全局锁加锁操作
flush tables with read lock;
#全局锁解锁操作
unlock tables;

测试数据库备份案例：
在窗口A加全局锁：

在会话窗口B执行查询操作，结果显示正常，说明查询操作并没有被锁。

在会话窗口B执行更新操作，我们会看到光标会一直闪烁，说明操作备阻塞。

在会话窗口C中执行备份操作，提示报错，原因是mysqldump不是sql语句，所以不应该在mysql命令行中执行。

退出mysql，在命令行中执行备份指令，此时提醒我们执行成功。并且在D盘中发现了我们备份的数据库文件

mysqldump -h 192.168.200.202 -uroot -p1234 db01 > D:/db01.sql
#这条指令的意思是连接ip为192.168.200.202远程主机，将db01数据库备份到D盘中，文件名为db01.sql

最后，不要忘了释放全局锁

1.1全局锁的特点

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

• 如果在主库上备份，那么备份期间都不能对数据库执行更新操作，业务基本就得停摆
• 如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog) , 会导致主从延迟。

在InnoDB引擎中提供了一个参数， --single-transaction ，在备份时加上这个参数可以完成不加锁的一致性数据备份。在InnoDB底层实际上是通过快照读来实现这个操作的。

mysqldump --single-transaction -uroot -p1234 db01 > db01.sql

2.表级锁

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

表级锁主要分为以下三类：

• 表锁
• 元数据锁（meta data lock ，MDL）
• 意向锁

2.1表锁

表锁又分为以下两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

表锁的语法：

#表锁加锁方式，表名可以填写多个，可以锁住多张表，read为表共享读锁，write为表独占写锁
lock tables 表名... read|write;
#释放锁操作
unlock tables/与客户端断开连接

添加表共享读锁的情况

表共享读锁只会允许事务执行读操作

添加表独占写锁的情况

表独占写锁只允许当前事务执行读和写操作

2.2 元数据锁（meta data lock，MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。

元数据锁案例讲解
查看数据库中存在的元数据锁

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

只要存在修改表结构行为，就会加排他锁EXCLUSIVE。EXCLUSIVE会与SHARED_READ和SHARED_WRITE排斥，导致阻塞，直到事务提交，SHARED_READ和SHARED_WRITE被释放。

2.3 意向锁

#查看意向锁和行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

意向锁是用于提高表锁获取效率的
如何理解这句话？
比如事务A要对user表id为1的数据执行update操作，那么事务A会给id为1的这行数据加行锁。
事务B此时想给user添加一个表锁，为了避免锁的冲突（当前表的表锁和表中的行锁的冲突），数据库就会一行一行的遍历user表中的数据，来判断是否存在行锁，如果都不存在行锁，则添加表锁成功，如果存在行锁，则添加不成功。

这样加表锁的效率太低，数据库为我们提供了一个意向锁，有了意向锁的存在，添加表锁的过程就变成这样：
事务A要对user表id为1的数据执行update操作，那么事务A会给id为1的这行数据加行锁。并且会生成一个意向锁。
事务B此时想要给user表添加一个表锁，他现在不会一条条数据的去遍历了，而是直接判断意向锁是否存在，如果存在，则说明有行锁存在，加表锁操作阻塞。如果不存在，则加表锁成功。
不用去每条数据遍历了，是不是快了很多。

3.行级锁

行级锁，每次操作锁住对应行的数据，锁的粒度最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁,主要分为以下三类:

3.1 行级锁的分类

• 行锁(Record Lock) : 锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  下图是B+树的索引结构的叶子节点，便于理解行锁
  
• 间隙锁（Gap Lock）： 锁定索引记录间隙(不含该记录) ,确保索引记录间隙不变,防止其他事务在这个间隙进行insert, 产生幻读。在RR隔离级别下都支持。
  
• 临键锁(Next-Key Lock) :行锁和间隙锁组合,同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
  

3.1.1 行锁

InnoDB实现了以下两种类型的行锁：
1.共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
简单来说就是共享锁与共享锁兼容，与排它锁冲突。
2.排他锁(X) ：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
简单来说就是排他锁与共享锁和其它排他锁冲突。

下表的是SQL操作会加的锁的类型以及说明

（for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。）

行锁-演示
默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
（关于可重复读解决部分幻读）

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

3.1.2 间隙锁/临键锁

默认情况下，InnoDB在Repeatable Read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。（关于可重复读解决部分幻读）

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,InnoDB将next-key锁优化为间隙锁。
2. 索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
3. 索引上的范围查询(唯一索引) 会访问到不满足条件的第一个值为止。在该值和恰好满足条件的最后一个值的间隙加临键锁（该值和间隙都加锁），在该值加行锁，在该值到真无穷或负无穷加临键锁。

先来测试第一条：
查询出stu表中的所有数据

select * from stu;

开启一个事务A，更新不存在的数据，此时id为5的数据不存在，InnoDB会在id为3和8两条数据之间添加间隙锁（不包含3和8）

begin;
update stu set age = 10 where id = 5;

A
开启另外一个会话，查询数据库中的表锁行锁，可以看到第二行就是一个lock-type为RECORD的行锁，且是X（代表排他锁），GAP（间隙锁）

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

那么此时我们在这个会话中向id为3和8的数据之间插入数据结果会怎么样呢？
开启事务B，执行SQL
光标一直闪烁，处于阻塞状态，说明3和8之间被锁住了。

begin;
insert into stu values(7,'Ruby',7);

提交事务A提交了，事务B的SQL就立马通过了。

间隙锁是给两条记录之间的间隙加的锁
第二条，第三条可以看视频理解
1.索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
2.索引上的范围查询(唯一索引) 会访问到不满足条件的第一个值为止

锁的分类