一文详解mysql 的锁_mysql锁

MySQL锁是用于管理数据库中的并发操作的一种机制，它可以确保数据的一致性和完整性。
按范围划分：包括全局锁、表级锁、页级锁和行级锁。
按类型划分：包括间隙锁、临键锁和记录锁。
按级别划分：包括共享锁（S锁）和排它锁（X锁），以及意向锁。

全局锁

全局锁是MySQL中锁的一种粒度，它影响的是整个数据库实例。当施加全局锁时，数据库中的所有表都无法进行写操作，即所有的数据更新语句（增删改）和数据定义语句（如创建、修改、删除表结构等）都会被阻塞。

典型的使用场景是在执行全库逻辑备份时，通过施加全局锁来确保数据的一致性，从而防止备份过程中的数据不一致问题。这对于保证备份数据的完整性和准确性非常关键。

-- 施加全局读锁（共享锁）
FLUSH TABLES WITH READ LOCK;

-- 执行备份操作...

-- 释放全局读锁
UNLOCK TABLES;

1
2
3
4
5
6
7
8

表级锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

手动增加表锁：

lock table 表名称 read(write),表名称2 read(write);
1

例如lock tables t1 read, t2 write; 命令，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能在unlock tables之前访问其他表。
查看表上加过的锁：

show open tables;
1

删除表锁：

unlock tables;
1

页级锁

页级锁的锁定粒度和加锁时间介于表级锁和行级锁之间，适用于对相邻一组记录的操作。
以下是页级锁的示例：

-- 开启事务
START TRANSACTION;

-- 执行查询操作，并使用LOCK IN SHARE MODE对结果集进行共享锁定
SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;

-- 执行更新操作，并使用FOR UPDATE对结果集进行排他锁定
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition FOR UPDATE;

-- 提交事务
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12

SELECT … LOCK IN SHARE MODE语句会将查询结果集进行共享锁定，允许其他事务读取该结果集，但不允许对其进行修改。而UPDATE … FOR UPDATE语句则会将更新的结果集进行排他锁定，防止其他事务对该结果集进行读取或修改。

行锁

每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高，但是加锁慢、开销大，容易发生死锁现象。
InnoDB与MYISAM的最大不同有两点：

• InnoDB支持事务
• InnoDB支持行级锁

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

共享锁和排他锁

• 读锁／共享锁（Shared Lock，S锁）：针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。

• 写锁／排它锁（Exclusive Lock，X锁）：允许事务对某些数据进行删除或更新。如果当前操作还没完成，其他事务的S和X锁是会被阻塞的，确保在多个事务中，对同一资源，只有一个事务能写入，并防止其他用户读取正在写入的资源。

共享锁称为读锁，但不是读一定获取共享锁。正常情况下，select某一条记录时，只需要获取该记录的共享锁。但是，在有些情况下可能select记录时就获取记录的排他锁，来禁止别的事务来读取该记录，为此，MySQL提供了两种特殊的select语句：
对读取的记录加S锁：

SELECT... LOCK IN SHARE MODE; 

SELECT ．．． FOR SHARE ［NOWAIT｜SKIP LOCKED］；-- 8.0特性
——NOWAIT表示不等待直接报错，
——SKIP LOCKED表示立即返回，但返回的结果不包含被锁定的行
1
2
3
4
5

此时允许其他事务读取该记录（给该记录加S锁），但是不允许其他事物给该记录加X锁，需要阻塞等待当前事务提交后获取锁。

对读写的记录加X锁：

select * from user where id=1 for update;
1

如果当前事务中有该语句，会给记录增加X锁，不允许其他事务获取该记录的S锁和X锁。

意向锁

意向锁的作用就是加快表锁的检查过程。
意向锁是由存储引擎自己维护的，用户无法手动获取，在为数据行加共享／排他锁之前，InooDB会先获取该数据所在表的对应意向锁。

• 意向共享锁（intention shared lock，IS）
  在使用InnoDB引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」：

——事务要获取某些行的S锁，必须先获得表的 IS 锁。
SFLFCT column FROM table ... LOCK IN SHARE MODE;
1
2

• 意向排他锁（intention exclusive lock，IX）
  在使用InnoDB引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」：

——事务要获取某些行的X锁，必须先获得表的IX锁。
SELECT column FROM table ... FOR UPDATE; 
1
2

间隙锁

间隙锁的作用是防止其他事务在当前事务未提交的情况下插入新的记录，从而避免幻读现象的发生。当一个事务执行了范围查询操作时，MySQL会自动为该范围加上间隙锁，以防止其他事务在该范围内插入新的记录。

需要注意的是，间隙锁的加锁范围是左开右闭区间，即不包括范围的左右端点。例如，如果一个事务执行了以下范围查询操作：

SELECT * FROM table_name WHERE column > 10 AND column < 20;

1
2

那么MySQL会为该范围加上间隙锁，锁定从10到20之间的所有记录，但不包括10和20这两个值本身。

临键锁(Next-key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。
每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

记录锁

记录锁也叫行锁，例如：

select * from emp where empno = 1 for update;
1

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。