MySQL锁--02---行锁 (记录锁(Record Locks))

已于 2023-11-24 16:38:37 修改
阅读量2.6k 收藏 12
点赞数 3
分类专栏： MySQL 文章标签： mysql 数据库 database
于 2021-12-17 22:28:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48052161/article/details/121963813
版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前置知识

行锁特点

  • 偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：

  1. 支持事务
  2. 采用了行级锁

行锁支持事务

ACID

在这里插入图片描述

并发事务带来的问题

在这里插入图片描述

事务隔离级别

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

常看当前数据库的事务隔离级别：

SHOW VARIABLES LIKE ‘tx_isolation’;

在这里插入图片描述

MySQL-行锁

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：

又称为读锁，简称S锁，

  • 共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据
  • 但是只能读不能修改。
排他锁（X）：

又称为写锁，简称X锁，排他锁就是不能与其他锁并存

  • 如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，
  • 但是获取排他锁的事务是可以对数据就行读取和修改。

InnoDB 加锁规则

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加写锁（排它锁 x锁)；
对于普通SELECT语句，InnoDB不会加任何锁；

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

给记录集加共享锁或排他锁

在这里插入图片描述

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB 行锁争用情况

show status like ‘innodb_row_lock%’;

在这里插入图片描述

在这里插入图片描述

  • 当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
最后可以通过 SELECT * FROM information_schema.INNODB_TRX\G;
来查询正在被锁阻塞的sql语句

行锁到底锁住的是什么？

InnoDB 的行锁，就是通过锁住索引来实现的

在这里插入图片描述

问题一：为什么表里面没有索引的时候，实验一锁住一行数据会导致锁表？

案例1 ：

  • 这个实验操作是操作没有索引的t1，t1里面有4条数据：1、2、3、4。

在这里插入图片描述

  • 现在我们在两个会话里面手工开启两个事务。在第一个事务里面，我们通过where id =1
    锁住第一行数据。在第二个事务里面，我们尝试给id=3的这一行数据加锁，大家觉得能成功吗？

在这里插入图片描述

  • 第一个事务锁住了id=1的这行数据，为什么我不能操作id=3的数据呢？我们再来操作一条不存在的数据，插入id=5。它也被阻塞了。
  • 实际上这里整张表都被锁住了。

分析：

  1. 如果我们定义了主键(PRIMARYKEY)，那么 InnoDB 会选择主键作为聚集索引
  2. 如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引
  3. 如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增

所以，实验一为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

问题二：为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

  • 在辅助索引里面， 索引存储的是二级索引和主键的值。 比如name=4，存储的是name的索引和主键id的值4。
  • 而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。
    在这里插入图片描述

小结一下：

  • 行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁
  • 除了直接在主键索引加锁，我们还可以通过辅助索引找到相应主键索引后再加锁

行锁— 案例 1

建表: test_innodb_lock

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;
 
insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');


select * from test_innodb_lock;

在这里插入图片描述

加索引

create index test_innodb_a_ind on test_innodb_lock(a);
 
create index test_innodb_lock_b_ind on test_innodb_lock(b);

在这里插入图片描述

设置: 手动提交事务

set autocommit = 0

在这里插入图片描述

跟新

在这里插入图片描述

行锁— 案例 2

错误操作导致 ==> 索引失效 ==> 行锁升级为表锁

正常情况

在这里插入图片描述

不规范的sql,导致行锁变表锁

由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁

在这里插入图片描述

分析:

  1. b列是索引列,且b列字段是varchar 类型
  2. 写sql 语句时的时,忘记加’'符号
  3. mysql底层会自动类型转化,帮助把int 转化为varchar 类型
  4. 但转化的同时会索引失效,将行锁变表锁

mysql底层会自动类型转化,转化的同时会索引失效

INNODB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁

总结

  • InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
  • 但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

  • 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，及索引范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离（但是需要业务层面满足需求）
知行合一。。。
关注
  • 3
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
V5-407_RTX实验_任务锁.7z
04-13
在FreeRTOS中，任务锁（Task Locks）或者称为任务互斥锁（Task Mutexes），是一种同步机制，用于保护共享资源，防止多个任务同时访问导致数据不一致。当一个任务获得锁后，其他试图获取同一锁的任务将被阻塞，直到锁...
web-locks:网络锁API
03-12
网络锁API 基于 ， ， ， 和 实现。 请参阅规范： 和文档： 此实现是技术堆栈的一部分，是基于并行编程和工作负载微隔离的Node.js应用服务器的第一个试验项目所需的。 Web Locks API打算在将来合并到Node.js中。...
【MySQL】MySQL锁（二）表锁与行锁测试
硬核项目经理
04-15 599
MySQL锁（二）表锁与行锁测试上篇文章我们简单的了解了一大堆锁相关的概念，然后只是简单的演示了一下 InnoDB 和 MyISAM 之间 表锁 与 行锁 的差别。相信大家还是意犹未尽的，今天我们就来用代码说话，实际地操作一下，看看如何进行手动的加 表锁 与 行锁 ，并进行一些相关的实验测试。手动锁表首先来看 锁表 相关的操作。一般来说，我们手动锁表大部分情况下是为了增加从库或者进行数据迁移的时候...
深入了解mysql--gap locks,Next-Key Locks
嘎嘎的博客
02-14 1万+
Next-Key Locks Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁，官网上有介绍，Next-Key Locks是行锁和gap锁的组合。行锁是什么我们都很清楚，这篇文章主要简单分析一下mysql中的gap锁是什么。innodb默认的锁就是Next-Key locks。 GAP锁 gap锁，又称为间隙锁。存在的主要目的就是为了防止在可重复读的事...
MySQL数据库锁：Record Lock，Gap Lock 和 Next-Key Lock，锁住的是什么？
CTZL123456的博客
06-07 986
记录锁，它是针对索引记录的锁，锁定的总是索引记录。在多用户数据库系统中，多个事务可能会同时尝试读取或修改同一条记录，确保只有一个事务能在某一时刻修改该记录，其他事务只能读取，或者在写锁释放后再进行修改。Gap Lock，间隙锁，它是一种行级锁，锁住两个索引记录之间的间隙，而不是实际的数据记录，由InnoDB隐式添加。如下图：(1,3) 表示锁住记录1 和记录3 之间的间隙，这样记录2 就无法插入，间隙可能跨越单个索引值、多个索引值，甚至是空。在InnoDB中，间隙锁是通过索引来实现的。
MySQL-锁表和解锁
weixin_45203607的博客
02-19 1万+
介绍 锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。 加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。 锁机制 共享锁与排他锁 共享锁（读锁）：其他事务可以读，但不能写。 排他锁（写锁） ：其他事务不能读取，也不能写。 粒度锁 MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的
0401锁详解-MySQL-数据库
gaogzhen的博客
07-02 558
0401锁详解-MySQL-数据库
MySQL锁机制详解-表锁与行锁
热门推荐
huangjhai的博客
09-04 1万+
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源，如CPU、RAM、I/O等的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题。此外，锁冲突也是影响数据库并发访问性能的一个重要因素。
MySQL-行级锁（行锁、间隙锁、临键锁）
最新发布
小丁的博客
07-01 600
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。
MySQL行级锁-详解！
2301_79437276的博客
05-24 1026
唯一索引等值查询：当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。非唯一索引等值查询：当查询的记录「存在」时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后。
【mysql】表锁、行锁、间隙锁、共享锁(读锁)、排他锁(写锁)、Next-Key Locks 之间的关系
m0_45406092的博客
01-23 1766
参见：《【mysql】MySQL中的锁（表锁、行锁、间隙锁、共享锁、排他锁）》
Mysql-锁机制
m0_57697768的博客
04-25 320
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对 并发操作进行控制 ，因此产生了 锁。同时 锁机制 也为实现MySQL的各个隔离级别提供了保证。锁冲突 也是影响数据库 并发访问性能 的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。
MySQL-锁
qq_46096136的博客
09-25 7105
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、 RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。MySQL中的锁，按照锁的粒度分，分为以下三类： 全局锁：锁定数据库中的所有表。表级锁：每次操作锁住整张表。行级锁：每次操作锁住对应的行数据。全局锁：锁定数据库中的所有表。
cpp-锁的全栈工程师之路fighting
08-16
三、读写锁（Read-Write Locks） 读写锁允许多个线程同时读取共享资源，但只允许一个线程写入。这提高了并发性能。在C++中，读写锁由`std::shared_mutex`实现。`std::shared_lock`用于读取，而`std::unique_lock`...
digital-electronic-locks.zip_数码管 密码锁
07-14
基于矩阵键盘和数码管的简易电子密码锁，带proteus仿真，可直接看效果，程序有详细的注释！
redlock-rb：Redlock是Ruby中基于redis的分布式锁实现
02-02
Redlock-使用Redis的Ruby分布式锁。 在许多环境中，不同的进程需要以互斥的方式使用共享资源进行操作时，分布式锁是非常有用的原语。 有许多库和博客文章描述了如何使用Redis实现DLM（分布式锁管理器），但是每个...
sql函数--08---REGEXP_REPLACE的使用方法
时光里的博客
11-03 1万+
https://blog.csdn.net/JohnnyChu/article/details/111184962 https://blog.csdn.net/gxftry1st/article/details/22489275? https://blog.csdn.net/haiross/article/details/42103289?
MySQL基础--04---日期时间类型与格式化datetime、timestamp
时光里的博客
02-21 1万+
MySQL中的日期时间类型 MySQL中常用的几种时间类型有：date、datetime、time、year、timestamp 详细解释 datetime 时间日期型 格式是YYYY-mm-dd HH:ii:ss，表示的范围是从1000到9999。但是有零值，0000-00-00 00:00:00； date：日期 就是datetime中的date部分； time：时间(段) 指定的某个区间之间，从-时间到+时间(有负时间表示)； timestamp：时间戳 并不是常规意义时间戳(如：14253685
sql函数--04---ROW_NUMBER() OVER()函数用法详解
时光里的博客
10-26 1万+
ROW_NUMBER() OVER()函数 使用范围: 这个函数可以Oracle中使用 MySQL用不了 语法格式： row_number() over(partition by 分组列 order by 排序列 desc) 注意: 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 案例 1 表数据： create table TEST_ROW_NUMBER_OVER(
1206 - The total number of locks exceeds the lock table size
11-11
当MySQL中的锁表大小被超过时，就会出现“1206 - The total number of locks exceeds the lock table size”这个错误。这个错误通常是由于在执行大量事务或者在执行大量插入操作时，MySQL尝试获取锁的数量超过了锁表的大小限制所导致的。为了解决这个问题，可以尝试以下几种方法： 1.增加锁表大小限制。可以通过修改MySQL配置文件中的“innodb_buffer_pool_size”参数来增加锁表大小限制。但是需要注意的是，如果增加锁表大小限制过多，可能会导致系统性能下降。 2.优化查询语句。可以通过优化查询语句来减少锁的数量。例如，可以使用索引来加速查询，或者使用“SELECT ... FOR UPDATE”语句来锁定需要更新的行。 3.减少事务数量。可以尝试将多个事务合并为一个事务，从而减少锁的数量。 4.减少并发插入操作。可以尝试将并发插入操作转换为批量插入操作，从而减少锁的数量。 <<引用>> <<引用>>
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值