mysql三种锁

Wesley13
• 阅读 562

不使用索引

CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql三种锁

BEGIN;
-- 手动开启一个事务,并在id = 1这条数据上加上排它锁
SELECT * from test1 WHERE id = 1 for UPDATE;

mysql三种锁

BEGIN;
-- 手动开启另外一个事务,此时给id=2的这条数据进行加排它锁,结果会如何?
SELECT * from test1 WHERE id = 2 for UPDATE

mysql三种锁

发现此时居然查询id=2的数据事务被卡住了。这是为什么呢?当表没有创建索引时或者查询语句没有命中索引时,锁住的是整个表的数据,因为没有命中索引故其会去扫描全表数据。 当一张表没有索引时,innoDB会创建一个隐藏主键索引,当通过隐藏的主键索引去检索时,将该表中所有的隐藏索引检索一遍 例子:如果手动开始事务,并在id=1的数据上手动加上排它锁.如果此时再去查询id=2的数据时,发现此语句卡住了。 故得出没有建立索引的表,一旦锁住数据及为锁住整张表。

主键索引

CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql三种锁

BEGIN;
-- 主键索引
SELECT * from test2 WHERE id = 1 FOR UPDATE;

mysql三种锁

BEGIN;
-- 手动开启其他事务
SELECT * from test2 WHERE id = 5 for update;

mysql三种锁 此时说明,主键索引时只会锁住匹配到的索引项,而不会影响其他事务操作其他索引

唯一索引

CREATE TABLE `test3` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql三种锁

BEGIN;
-- 唯一索引
SELECT * from test3 where name = '李四' FOR update;

mysql三种锁

BEGIN;
-- 唯一索引
SELECT * from test3 where id= 5 FOR update;

mysql三种锁 注意:此时SELECT * from test3 where id= 5 FOR update为什么执行卡住了?唯一索引锁定时,先通过唯一索引然后找到对应主键索引,也就是辅助索引--->主键索引的一个过程,所以查询id = 5的数据时也被锁住了。 通过上面几个例子发现mysql innodb是通过锁住索引来实现行锁的

mysql innodb 为什么不会出现幻读?

详见下面InnoDB的行锁,如下图 临界锁的操作 mysql三种锁

临界锁(Next-key Lock)锁定范围加记录

mysql三种锁

BEGIN;
-- 临界锁,锁住对应的范围,防止幻读。
-- 按道理此时应该锁住,6,7,8(已存在),9,10
SELECT * from test2 WHERE id > 5 and id <11 FOR UPDATE;

mysql三种锁

BEGIN;
-- 此时测试插入id=7 的值,按道理应该插入不进去,因为锁住的范围是(5,8]和(8,12]
insert into test2 VALUES(7,'试试');

mysql三种锁

BEGIN;
SELECT * from test2 WHERE id = 12 FOR UPDATE;

mysql三种锁

刚才不是id>5 and id <11的么?此时为什么id =12也被锁了呢?因为此时锁住的范围是(5,8]和(8,12]

Gap Lock(间隙锁)

mysql三种锁

BEGIN;
-- 间隙锁,因为id =7 这条数据不存在,故锁退化成了间隙锁,那么此时id=7 落在了(5,8)这个区间
SELECT * from test2 where id = 7 for UPDATE;

mysql三种锁

BEGIN;
-- 因为锁退化成了间隙锁,那么此时id=7 落在了(5,8)这个区间,故id =6 也一起被锁住了
INSERT into test2 VALUES(6,'卡卡');

mysql三种锁

Record Lock(记录锁)

mysql三种锁

注意: 测试在test2表中,也就是主键索引。

BEGIN;
-- 在事务1中在id =5 的主键项锁定
SELECT * from test2 where id = 5 for update;

mysql三种锁

mysql三种锁

本文是摘抄的,原文路径: https://my.oschina.net/u/3370769/blog/3000934

自己补充点东西把,自己不理解的地方也做下笔记。

间隙锁(gap lock),锁住的是一个区间范围,比如锁住区间(1,3),如果插入2的数据是插入不进去的,1和3是开区间,因此,1和3不受影响。

临界锁(next key lock),锁住索引上的记录+前面的间隙锁,是一个左开右闭区间,比如(1,3]。一个next-key锁=索引上的记录锁+锁住前面间隙的gap lock。

点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
1年前
Java爬虫之JSoup使用教程
title:Java爬虫之JSoup使用教程date:201812248:00:000800update:201812248:00:000800author:mecover:https://imgblog.csdnimg.cn/20181224144920712(https://www.oschin
Stella981 Stella981
1年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
1年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
Stella981 Stella981
1年前
Angular material mat
IconIconNamematiconcode_add\_comment_addcommenticon<maticonadd\_comment</maticon_attach\_file_attachfileicon<maticonattach\_file</maticon_attach\
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
3A网络 3A网络
4个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
3A网络 3A网络
4个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t
京东云开发者 京东云开发者
2个月前
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
helloworld_34035044 helloworld_34035044
6个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为