mysql中的锁

环境

此次实验所使用的mysql版本为mysql8:

在这里插入图片描述

因为我们可以在performance_schema.data_locks中看到锁的情况。

引擎是innodb。事务隔离级别是RR。

表锁

mysql中的锁可以按照锁的粒度分,表锁就是其中的一种(另一种是行锁)。

虽然锁一整张表在实际操作中是不明智的,但我们还是探究一下。

准备的表:

CREATE TABLE `test`.`test_innodb_lock` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `score` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我们有主键索引id以及二级索引score

然后插入一些数据:

INSERT INTO `test`.`test_innodb_lock`(`id`, `name`, `score`) VALUES (1, 'Jack', 100);
INSERT INTO `test`.`test_innodb_lock`(`id`, `name`, `score`) VALUES (2, 'Rose', 60);
INSERT INTO `test`.`test_innodb_lock`(`id`, `name`, `score`) VALUES (3, 'Ocean', 77);

为了看到performance_schema.data_locks中锁的情况,我们需要设置:

set autocommit=0;
S锁

首先看看表级别的读锁:

在这里插入图片描述
在这里插入图片描述

读锁可以被多次获取(session1和session2同时获取到了读锁)。

在这里插入图片描述

我们可以看到in_use的数量增加了。

然后我们查看锁的情况:

SELECT
  ENGINE,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks;

在这里插入图片描述

可以看到一个S锁(Share Lock)。

在读锁锁整张表的情况下:

在这里插入图片描述

三个session都能够读。

但是:

在这里插入图片描述

锁表的session1和session2报错,旁观者session3阻塞。

在这里插入图片描述
我们的确可以看到一个阻塞的语句。

session1和session2回滚并解锁表。

X锁

重新做实验。

在这里插入图片描述
在session1获取表级别的写锁之后,session2无法再次获取。我们取消session2的获取操作。

在这里插入图片描述

现在我们看到了一把X锁(Exclusive Lock)。

在这里插入图片描述
持有锁的session1能读能写,其他session则不行。

我们可以看一下mysql官网的总结:

在这里插入图片描述

行级锁和意向锁

我们不会主动去锁表,我们常用的是行级别的锁。

行级别的锁有很多。我们看二级索引score,它有几个值:60,79,100,由此可以划分区间。

小于60
60
60至79
79
79至100
大于100

如果我们要添加锁,我们可以锁一行,比如score=60这一行,也可以锁区间,比如60至79这个区间,一切都要看innodb怎么去实现了。

S行锁

在这里插入图片描述

全部开启事务。

在这里插入图片描述
我们看到了两种锁:IS表示Intention Share Lock,S表示Share Lock,REC_NOT_GAP表示Record Lock but Not a Gap Lock(行锁而非间隙锁)。所以S,REC_NOT_GAP就表示这是一个行锁,是个共享锁,不是一个间隙锁。

IS是一种表锁,如果一个session想去获取一个S锁,就必须先去获取IS锁,即使你最终可能获取S锁失败了,你也要去持有IS锁(表达自己的一个意愿)。

在这里插入图片描述
S锁是一种行级的共享锁,作用在主键上(一定要有主键,你没有人家给你生成一个)。

既然id=1的行被session1持有了行锁,我们看看session2能做些什么?

在这里插入图片描述

可以查,但是不能修改。

在这里插入图片描述

我们可以看到有人试图去获取id=1这一行X锁(写锁),显然这是session2的意图。在waiting地去获取id=1条记录的X锁时,他已经成功地获取了IX锁(Intention Exclusive Lock)。

在这里插入图片描述

当session2因为无法获取写锁而超时时:

在这里插入图片描述

IX锁还是被session2持有着,这个Intention还是保留着。

在这里插入图片描述

session2rollback

在这里插入图片描述

此时IX锁才被释放。


和表级S锁一样行级的S锁也可以被多个session获得。

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

读锁的含义其实就是当前事务进入了读的状态,当然所有事务都可以这么做。但是,如果你想改,不好意思,没有一个事务中能改,不管你持不持有读锁。只有当这一行的读锁全部释放,它才能够被修改。


X行锁

同理,我们可以看看行级的X锁。

在这里插入图片描述
和料想的一样,一个IX锁,一个X锁。

在这里插入图片描述
在这里插入图片描述
session2只能拿到IX锁,拿不到X锁。

在这里插入图片描述
当然,session2也拿不到读锁。

在这里插入图片描述

只有持有写锁的session1才能修改。

所以for update或者说X锁的含义究竟是什么?他就像是在说:我锁定了(session1),接下来要有写操作了,其他人不能进入read mode,也不能进入write mode,其他人就等我完事吧。


我们的表里有主键索引id和二级索引score,暂时我们都在使用主键索引,我们可以先看看如果查询语句不使用索引会怎样(使用二级索引score作为查询条件的话情况会更加复杂)。

在这里插入图片描述

三条记录都被锁住了。而且是我们没有接触到的一种锁,单纯的S锁(next-key lock),等下我们会介绍这个锁。

当前就认为id=1,id=2,id=3三条记录都添加了行锁就行。

在这里插入图片描述

此时session2对id=3的记录也不能更新了。

作为对比,我们看看如果使用主键索引的情况:

在这里插入图片描述

在这里插入图片描述
只锁id=1一行的话session2是可以更新id=3的数据的。


另外,我们常用的其他当前读:update和delete,mysql也是给加了写锁的。

在这里插入图片描述
在这里插入图片描述


Gap锁和Next-key锁

gap锁就是间隙锁,我们上面看到过,我们不仅能对一行加锁,还能对行与行之间的间隙加锁。

next-key锁就是锁两个东西,一个是锁行,另一个是锁行前面的那个间隙,就是这两把锁的合并。

为了更好的演示,我们修改一下数据:

在这里插入图片描述

我们为二级索引加写锁:

在这里插入图片描述
这里:

  • IX是意向锁,要拿到X必须先拿到IX锁。
  • X是next-key锁,锁住score=60对应的行和score在50至60之间的数据。
  • X,REC_NOT_GAP是行锁,锁住id=21这一行。
  • X,GAP是间隙锁,锁住score在60至70间的数据。

边界数据score=50是否锁住需要查看。

我们先看插入的情况:

在这里插入图片描述

50(包含)到60,60到76都不能插入,但是score=77可以。这里就可以看出next-key lock和gap lock之间的区别了。

在这里插入图片描述

对于update操作,51(包含)到60,60到76都不行,score改成50或者77可以。

从这里也可以看出gap lock和next-key lock作用范围也太大了。


在这里插入图片描述

如果没有使用索引,则给全表加上next-key lock。

在这里插入图片描述

这时候没有任何插入操作能够成功。

当我们使用RR的时候,需要注意gap lock和next-key lock带来的开销。

死锁

死锁就是两个事务(或者多个)互相持有并去索取锁(和java死锁一样)。

在这里插入图片描述

session1先去持有id=2这一行的S锁。

在这里插入图片描述

在这里插入图片描述

session2尝试去获取id=2这行的X锁,由于SX互斥,session2等待。

在这里插入图片描述

在session2等待的过程中,session1去获取id=2这行的X锁。这时候死锁就发生了:

在这里插入图片描述

session2已经发出请求X锁的需要,等待session1释放S锁;session1又去请求正被session2“持有”的X锁,互相盯着对方碗里的锁,就会发生死锁。(session1的S锁不能直接升级成X锁,因为它已经被session2请求了)

innodb自己会发现死锁并打破循环。

在这里插入图片描述

此时session1同时获取id=2这一行的S锁和X锁。

使用

SHOW ENGINE INNODB STATUS

我们可以看到更多细节:

session2的情况:

在这里插入图片描述

session1的情况:

在这里插入图片描述

  • 3
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MySQLLocking)用于控制并发访问数据库资源以避免数据一致性问题。MySQL支持多种类型的,这些可以分为以下几类: 1. **共享(Shared Locks, S)**: - 允许读取数据,但阻止其他事务对同一行进行写操作。如果多用户同时请求共享,则所有请求都会立即获得。 ```sql SELECT * FROM table WHERE id = 1; -- 获取S ``` 2. **排他(Exclusive Locks, X)**: - 只允许一个事务独占一行,阻止其他事务进行读取或写入操作。 ```sql INSERT INTO table VALUES (1, 'value'); -- 获取X UPDATE table SET column = 'new_value' WHERE id = 1; -- 获取X DELETE FROM table WHERE id = 1; -- 获取X ``` 3. **意向锁(InnoDB Only, IX)**: - InnoDB存储引擎特有的,用于定表级,允许事务定整个表以便在其范围内进行插入或删除操作。 ```sql LOCK TABLES table WRITE; -- 获取IX ``` 4. **行级乐观(Row-Level Optimistic Locking, ROWX)**: - MySQL的默认行为是行级定,但可以通过`SELECT ... FOR UPDATE`语句实现乐观,它会检查行的版本号是否与先前读取时一致。 5. **死锁(Deadlocks)**: - 当两个或更多的事务因等待对方释放资源而互相阻塞时,就会发生死锁。 6. **自旋(Spin Locks)**: - 这不是MySQL的标准机制,而是某些库或优化策略使用的高级概念,它让进程在获取失败时循环尝试,直到成功。 了解这些的类型有助于管理和优化并发性能,尤其是在高并发环境下。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值