Mysql-锁机制

redvelet

已于 2023-04-13 13:20:49 修改

阅读量238

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql mysql锁表锁、行锁

于 2023-04-13 13:20:13 首次发布

本文链接：https://blog.csdn.net/qq_45659753/article/details/130128124

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Mysql的锁机制

一、简介

锁是为了保证数据库中数据的一致性，使各种【共享资源】在被访问时变得【有序】而设计的一种规则。

tip MysQL中不同的存储引擎支持不同的锁机制：

InoDB支持【行锁】，有时也会升级为表锁。
MyIsam只支持表锁。

【表锁】：

特点：就是开销小、加锁快，不会出现死锁。
缺点：锁粒度大，发生锁冲突的概率小，并发度相对低。

【行锁】：

特点：就是开销大、加锁慢，会出现死锁。
锁粒度小，发生锁冲突的概率高，并发度高。

数据演示：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, 'jack', 99);
INSERT INTO `user` VALUES (2, 'rose', 19);
INSERT INTO `user` VALUES (3, 'mike', 24);
INSERT INTO `user` VALUES (4, 'smith', 22);
INSERT INTO `user` VALUES (7, 'james', 16);
INSERT INTO `user` VALUES (8, 'haha', 22);
INSERT INTO `user` VALUES (9, 'zara', 21);


SET FOREIGN_KEY_CHECKS = 1;

二、InnoDB的锁类型

InnoDB的锁类型主要有读锁(共享锁)、写锁(排他锁)、意向锁和MDL锁。

1、s锁

1.1 概述

读锁（共享锁，shared lock）简称S锁。

一个事务获取了一个数据行的读锁，其他事务也能获得该行对应的读锁，但不能获得写锁，即一个事务在读取一个数据行时，其他事务也可以读，但不能对该数行增删改的操作。

简而言之：就是可以多个事务读，但只能一个事务写。

tip

读锁是共享锁，多个事务可以同时持有，当有一个或多个事务持有共享锁时，被锁的数据就不能修改。

1.2 示例

读锁是通过【select… lock in share mode】语句给被读取的行记录或行记录的范围上加一个读锁，让其他事务可以读，但是要想申请加写锁，那就会被阻塞。

事务一：

begin;
select * from `user` where id = 1 lock in share mode;

事务二：

begin;
update `user` set name = 'jack&rose' where id = 1;

卡住了，说明程序被阻塞，确实加了锁。

在这里插入图片描述

s锁是可以被多个事务同时获取的，我们在两个不同的事务中分别对同一行数据加上s锁，结果都可以成功，如下图：

在这里插入图片描述

2、x锁

2.1 概述

写锁，也叫排他锁，或者叫独占所，简称x锁（exclusive）。

一个事务获取了一个数据行的写锁，既可以读该行的记录，也可以修改该行的记录。但其他事务就不能再获取该行的其他任何的锁，包括s锁，直到当前事务将锁释放。【这保证了其他事务在当前事务释放锁之前不能再修改数据】。

简而言之：就是只能自己操作该行数据，其它事务都不行。

tip：

写锁是独占锁，只有一个事务可以持有，当这个事务持有写锁时，被锁的数据就不能被其他事务修改。

2.2 示例

（1）一些DML语句的操作都会对行记录加写锁。

事务一：

begin;
update `user` set name = 'jack&rose' where id = 2;

事务二：

begin;
update `user` set name = 'jack&rose' where id = 2;

卡住了，说明程序被阻塞，确实加了锁。但是，我们发现其他事务还能读，有点不符合逻辑，这是应为mysql实现了MVCC模型，后边会详细介绍。

在这里插入图片描述

（2）比较特殊的就是select for update，它会对读取的行记录上加一个写锁，那么其他任何事务不能对被锁定的行上加任何锁了，要不然会被阻塞。

事务一：

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

事务二：

begin;
update `user` set name = 'name&jack' where id = 1;

卡住了，说明加了锁了。

在这里插入图片描述

（3）x锁是只能被一个事务获取，我们在两个不同的事务中分别对同一行数据加上x锁，发现后者会被阻塞，如下图：

在这里插入图片描述

3、记录锁（Record Lock）

记录锁就是我们常说的行锁，只有innodb才支持，我们使用以下四个案例来验证记录锁的存在：

1、两个事务修改【同一行】记录，该场景下，where条件中的列不加索引。

事务一：

begin;
update `user` set age = 88 where name = 'jack';

事务二：

begin;
update `user` set age = 99  where name = 'jack';

发现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

在这里插入图片描述

2、两个事务修改同表【不同行】记录，此时where条件也不加索引。

事务一：

begin;
update `user` set age = 99  where name = 'jack';

事务二：

begin;
update `user` set age = 99  where name = 'rose';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，表被【锁】住了。

在这里插入图片描述

3、两个事务修改【同一行】记录，where条件加索引

事务一：

begin;
update `user` set age = 99  where id = 1;

事务二：

begin;
update `user` set age = 100  where id = 1;

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

在这里插入图片描述

4、两个事务修改同表【不同行】记录，此时where条件加索引。

事务一：

begin;
update `user` set age = 99  where id = 1;

事务二：

begin;
update `user` set age = 99  where id = 2;

发现都可以顺利修改，说明锁的的确是行。

在这里插入图片描述

证明：行锁是加在索引上的，这是标准的行级锁。

总结：只有在修改时候的条件加上了索引才会锁住改行数据-行锁，不然就是所著整张表-表锁

4、间隙锁（GAP Lock）

间隙锁帮我们解决了mysql在rr级别下的一部分幻读问题。

间隙锁是按照索引值的范围进行加锁，而不是按照行进行加锁，这意味着即时数据行在锁范围外，如果它们的索引值在锁范围内，它们也会被锁住。

间隙锁生成的条件：

1、A事务使用where进行范围检索时未提交事务，此时B事务向A满足检索条件的范围内插入数据。

2、where条件必须有索引。

事务一：

begin;
select * from `user` where id between 3 and 7 lock in share mode;

事务二：

begin;
insert into `user` value(5,'jacky',66);

发现卡住了，第一个事务会将id在3到7之间的数据全部锁定，不允许在缝隙间插入。

在这里插入图片描述

事务三：

begin;
insert into `user` values (10,'jacky',66);

插入一个id为10的数据，竟然成功了，因为10不在事务一的检索的范围。

在这里插入图片描述

5、记录锁和间隙锁的组合（next-key lock）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含【索引记录】，又包含【索引区间】。

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

6、MDL锁

MySQL 5.5引入了meta data lock，简称MDL锁，用于保证表中元数据的信息。在会话A中，表开启了查询事务后，会自动获得一个MDL锁，会话B就不可以执行任何DDL语句，不能执行为表中添加字段的操作，会用MDL锁来保证数据之间的一致性。

元数据就是描述数据的数据，也就是你的表结构。意识是在你开启了事务之后获得了意向锁，其他事务就不能更改你的表结构。MDL锁都是为了防止在事务进行中，执行DDL语句导致数据不一致。

7、死锁问题

发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件：

互斥条件，在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。
不可剥夺条件，某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。
请求与保持条件，进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
循环等待条件，系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，如图1-7所示。

如下：

在这里插入图片描述

InnoDB使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图（wait-for graph）的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务。

在这里插入图片描述

tip 避免死锁：在MySQL中，通常通过以下几种方式来减少死锁的概率和影响：

减少概率：

尽量控制事务的大小，减少事务持有锁的时间，尽早提交事务或尽快释放锁
合理设计索引，尽量缩小锁的范围。
尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。
使用较短的事务和较小的锁
在事务中按照固定的顺序获取锁
如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。

避免范围扩大：

尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁

减小影响：

使用超时机制：在事务发送死锁时，使用超时机制自动回滚并释放锁

三、表锁

1、对于InnoDB表，在绝大部分情况下都应该使用【行级锁】，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新【大部分或全部数据】，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

2、在InnoDB下，主动上表锁的方式如下：

lock tables `user` write,`user` read;
select * from `user`;
commit;
unlock tables;

使用时有几点需要额外注意：

使用【LOCK TALBES】虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能感知MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
在用LOCAK TABLES对InnoDB加锁时要注意，事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。
表锁的力度很大，慎用。
总结：
1. 【lock tables】加锁：不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的
2. 【unlock tables】释放锁：commit和rollback不会释放锁，只有unlock tables才会释放锁，且会自动提交事务

四、从另一个角度区分锁的分类

1、乐观锁

概述：

乐观锁是一种乐观思想的锁，它假设数据不会同时被多个事务修改，因此不会像悲观锁那样直接阻塞等待锁的释放。

相反，乐观锁允许多个事务同时读取和修改相同的数据，但在提交更新操作时，会检查数据是否已被其他事务修改。如果数据已被修改，更新操作将被回滚。

乐观锁的实现原理：

通常是通过在数据表中添加一个版本号或时间戳字段，每次更新数据时，都会将版本号或时间戳字段的值加1。当其他事务尝试修改该数据时，会检查版本号或时间戳字段的值是否与当前数据相同，如果不同则更新操作将失败。

基于版本号的乐观锁的示例：

假设有一个订单表order，包含订单号、商品编号和商品数量三个字段，其中订单号为主键，版本号为字段version。

现在有两个事务T1和T2，分别想要修改订单号为001的商品数量，T1修改后的数量为10，T2修改后的数量为20。

初始状态下，订单号为001的商品数量为5，版本号为1。

T1读取订单号为001的商品数量和版本号，并将数量更新为10，版本号加1。

T2读取订单号为001的商品数量和版本号，发现版本号仍为1，因此将数量更新为20，版本号加1。

T1提交更新操作时，发现版本号已经不为1，而是2，说明订单已被T2修改过了，因此T1的更新操作会失败并回滚。

适用场景：

乐观锁适用于并发读写较多的场景，可以有效地提高并发度和吞吐量，但也有一定的缺点，即可能存在更新冲突的问题。因此在实际应用中，需要根据具体情况选择适当的锁机制来保证数据的一致性和可靠性。

乐观锁：

乐观锁大多是基于数据【版本记录机制】实现，一般是给数据库表增加一个 “version” 字段。

读取数据时，将此版本号一同读出，

更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

事务一：

select * from `user` where id = 1;

事务二：

select * from `user` where id = 1;
update `user` set score = 99，version = version + 1 where id = 1 and version = 1;
commit;

事务一：

update `user` set score = 100，version = version + 1 where id = 1 and version = 1;
commit;

发现更新失败，应为版本号被事务二、提前修改了，这使用了不加锁的方式，实现了一个事务修改期间，禁止其他事务修改的能力。

2、悲观锁

总结一句话：总有刁民想害朕

悲观锁依靠数据库提供的锁机制实现。

MySQL中的共享锁和排它锁都是悲观锁。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。此处不赘述。

redvelet

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录