mysql-锁-详细for update

已于 2023-09-09 23:28:21 修改
阅读量1.5k 收藏 1
点赞数
分类专栏： 数据库 文章标签： 数据库
于 2023-03-16 00:12:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48155735/article/details/129540619
版权

1.共享与独占

online ddl

online ddl：https://0x7ffc.github.io/2022/mdl/

如何使用online ddl：https://help.aliyun.com/document_detail/41733.html?spm=a2c4g.11186623.4.2.2a504335nWEjej

解决MDL锁问题：https://help.aliyun.com/document_detail/94566.html?spm=a2c4g.11186623.2.7.2a504335H3f8Wz

Mysql数据库进阶之select for update(五)—系列文章不错

两万字详解InnoDB的锁–捡田螺的小男孩

加锁范围

3
表z的列b是辅助索引，若在会话A中执行下面的SQL语句：

SELECT*FROM z WHERE b=3 FOR UPDATE

很明显，这时SQL语句通过索引列b进行查询，该列不是唯一属性，因此其使用传统的Next-Key Locking技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚集索引（primay-key a），其仅对列a等于5的索引加上Record Lock。而对于辅助索引b，其加上的是Next-Key Lock，锁定的范围是(1，3)。特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，即还有一个辅助索引范围为(3，6)的锁。

针对 读写 写写 问题 加锁
读锁：共享锁 Share Lock S
写锁：独占锁 Exclusive Lock X

X锁S锁
X锁不兼容不兼容
S锁不兼容兼容

1.1 锁定读

1.1.1 对读锁加S锁

Select ...  LOCK IN SHRAR MODE;

1.1.2 对读锁加X锁

Select .... FOR UPDATE;

1.2 写操作

只有 X锁可以加

  • DELETE
    加X锁，执行DELETE MARK操作
  • UPDATE
    • X锁
  • Insert
    • 一般情况下 不加锁，隐式锁， 没提交前，防止别的数据访问。

2.表及锁

2.1 表锁的S锁 X锁

 LOCK TABLES t READ #：InnoDB存储引擎会对表t加表级别的S锁。
 LOCK TABLES t WRITE #：InnoDB存储引擎会对表t加表级别的X锁。

3

2.2 意向锁 innodb

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

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而 意向锁 就是其中的一种表锁。

避免 在添加表锁前 需要一行一行判断是否有锁，引申出意向锁。

目的：

  1. 协调行锁，表锁关系，支持表锁行锁共存。
  2. 表明某个事物正在某些行持有了锁。
  3. 意向锁是一种不与行级锁 冲突的锁。

结论：
1 InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
2 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。
3 IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
4 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

2.2.0 分类

  • 意向共享锁 （intention shared lock, IS）：事务有意向对表中的某些行加 共享锁 （S锁）

    -- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
-- 会自动加，不用管
SELECT column FROM table ... LOCK IN SHARE MODE;

  • 意向排他锁 （intention exclusive lock, IX）：事务有意向对表中的某些行加 排他锁 （X锁）

    -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
-- 会自动加，不用管
SELECT column FROM table ... FOR UPDATE;

2.2.1 解决的问题

现在有两个事务，分别是T1和T2，其中T2试图在该表级别上应用共享或排它锁，如果没有意向锁存在，那么T2就需要去检查各个页或行是否存在锁;如果存在意向锁，那么此时就会受到由T1控制的表级别意向锁的阻塞。T2在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。简单来说就是给更大一级别的空间示意里面是否已经上过锁。

在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了(不这么做的话，想上表锁的那个程序，还要遍历有没有航所)，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

2.2.2 小结

意向锁是怎么解决这个问题的呢?首先，我们需要知道意向锁之间的兼容互斥性，如下所示。
因为具体 行级别的 x锁 可以加在不同的行，那么就有多个不同的意向排它锁，兼容

意向共享锁(lS)意向排他锁(IX)
意向共享锁(IS)兼容兼容
意向排他锁（IX)兼容兼容

即**意向锁之间是互相兼容的**，虽然意向锁和自家兄弟互相兼容，但是它会与普通的排他/共享锁互斥。

意向共享锁(lS)意向排他锁(IX)
共享锁(S)表兼容互斥
排他锁（X)表互斥互斥

2.3 元数据锁 MDL

当对一个表做增删改查的时候，加MDL读锁，当要对表做结构的变更操作的时候，加MDL写锁。

MDL 的作用是，保证读写的正确性

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显式使用，在访问一个表的时候会被自动加上。

3.行锁

3.1 记录锁

记录锁也就是仅仅把一条记录锁上
3

3.2 间隙锁 gap locks

gap锁的提出仅仅是为了防止插入幻影记录而提出的 。

意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 ，其实就是
id列的值(3, 8)这个区间的新记录是不允许立即插入的。

两个有界之间的区间

间隙锁解决mvcc
MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为Gap Locks的锁，官方的类型名称为：LOCK_GAP，我们可以简称为gap锁。比如，把id值为 8 的那条记录加一个gap锁的示意图如下。
3
图中id值为 8 的记录加了gap锁，意味着不允许别的事务在id值为 8 的记录前边的间隙插入新记录，其实就是id列的值( 3 , 8 )这个区间的新记录是不允许立即插入的。比如，有另外一个事务再想插入一条id值为 4 的新记录，它定位到该条新记录的下一条记录的id值为 8 ，而这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间( 3 , 8 )中的新记录才可以被插入。

3.2.2 问 间隙锁可以叠加

虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁（不论是共享gap锁还是独占gap锁)，并不会限制其他事务对这条记录加记录锁或者继续加gap锁。

mysql> select * from student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
|  3 | 李四   | 一班   |
|  8 | 王五   | 二班   |
| 15 | 赵六   | 二班   |
| 20 | 钱七   | 三班   |
+----+--------+--------+
5 rows in set (0.01 sec)
session 1session 2
select *from student where id =5 lock in share mode;
select * from student where id =5 for update;

这里session 2并不会被堵住。因为表里并没有id=5这个记录，因此session 1加的是间隙锁（3,8)。而session 2也是在这个间隙加的间隙锁。它们有共同的目标，即:保护这个间隙，不允许插入值。但，它们之间是不冲突的。

3.2.3 可能导致死锁

gap 区间 (3,8)

select * from user where id=5 lock in share  mode; # 1. 间隙锁
insert into user (id,name) values(7,'lisi'); # 4 因为2的间隙锁(锁定 3-8)，阻塞

select * from user where id=5 for update; # 2 间隙锁
insert into user (id,name) values(6,'张三'); # 3 应为1的间隙锁 (锁定 3-8)， 阻塞住

导致了死锁。

3.3 临键锁 默认

记录锁和间隙锁的合体
有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB就提出了一种称之为Next-Key Locks的锁，官方的类型名称为：LOCK_ORDINARY，我们也可以简称为next-key锁。Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。

# 区间(3,8]
begin;
select * from student where id <= 8 and id > 3 for update;

111
区间 (8,15]
3

222
2
阻塞

3.4 插入意向锁 事物在等待时也需要锁

3

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了gap锁（next-key锁也包含gap锁），如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是 **InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构** ，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB就把这种类型的锁命名为Insert Intention Locks，官方的类型名称为：LOCK_INSERT_INTENTION，我们称为插入意向锁。插入意向锁是一种Gap锁，不是意向锁，在insert
操作时产生。

4.悲观锁与乐观锁

select ..... for update 是悲观锁
因为innodb默认是 临键锁，会把所有扫描的行都加锁。

乐观锁
适用于读多场景。

  1. 版本号机制
    在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
  2. 时间戳机制
    时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行
    比较，如果两者一致则更新成功，否则就是版本冲突。

秒杀案例

乐观锁版本号
2
问题和修改
3
条件库存作为条件
2

5. 显式锁 隐式锁

trx_id roll_pox row_id 三个隐藏字段，是行格式的真实数据，只在聚簇索引中有，
二级索引无

6 死锁

如何处理死锁

查看死锁日志
原因：
相同表的 行锁冲突
间隙锁 死锁 ，降低隔离级别，rr->rc，避免间隙锁。

解决死锁：

  • 超时等待，事物超时自动回滚(innodb_lock_wait_timeout 默认50s)
  • 主动死锁检测，事物请求锁的时候采用 wait-for graph 等待图的方式进行死锁检测（innodb_deadlock_detect 默认on）
show engine innodb status;

2

监控分析锁

# 查询InnoDB锁的整体情况
# 可以重点查看Innodb_row_lock_waits和Innodb_row_lock_time_avg这两个值
# 如果数值较大，说明锁之间的竞争大
show status like 'innodb_row_lock%';

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.01 sec)
  • Innodb_row_lock_current_waits：当前正在等待锁定的数量；
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；（等待总时长）
  • Innodb_row_lock_time_avg：每次等待所花平均时间；（等待平均时长）
  • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数；（等待总次数）

#可以通过INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS这三个表
#分析可能存在的锁的问题
select * from information_schema.INNODB_TRX; # 查看所有事物
select * from information_schema.INNODB_LOCKS; # 查看锁
select * from information_schema.INNODB_LOCK_WAITS; # 查看锁等待

7. 间隙锁锁的问题 锁了什么

间隙锁是在可重复读隔离级别下才会生效的： next-key lock 实际上是由间隙锁加行锁实现的，如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。而在读提交隔离级别下间隙锁就没有了，为了解决可能出现的数据和日志不一致问题，需要把binlog 格式设置为 row 。也就是说，许多公司的配置为：读提交隔离级别加 binlog_format=row。业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

next-key lock的加锁规则

总结的加锁规则里面，包含了两个 “ “ 原则 ” ” 、两个 “ “ 优化 ” ” 和一个 “bug” 。

  1. 原则 1 ：加锁的基本单位是 next-key lock 。 next-key lock 是前开后闭区间。
  2. 原则 2 ：查找过程中访问到的对象才会加锁。任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁。
  3. 优化 1 ：索引上的等值查询，给唯一索引加锁的时候， next-key lock 退化为行锁。也就是说如果
    InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁
  4. 优化 2 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的
    时候， next-keylock 退化为间隙锁。
  5. 一个 bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

8.mvcc

多版本 并发控制
多版本 指的是 undo log 多个历史版本。
隐藏字段，undo log 版本链,readview
行格式 3个隐藏字段 row_id,trx_id,roll_pointer

readview

一个事物 想要查询记录，读取哪个版本呢？需要用readview 解决可见性问题。

readview 就是一个事物A，使用mvcc 机制进行快照读产生的读视图。构造一个数组，记录并维护当前系统 活跃的事物id (活跃 ，启动了，还没提交)

针对 读已提交， 可重复读 隔离级别下，必须保证 读到了 已经提交的事务修改过的数据。

read view 4个内容

  1. creator_trx_id
    创建这个readvieew 的事务id
  2. trx_ids 活跃的 读写事务的列表。(开启了事务，没提交。)
  3. up_limit_id 活跃事务id 中 最小的。
  4. low_limit_id
    表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为 1 ，2 ， 3 这三个事务，之后id为 3 的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括 1 和 2 ，up_limit_id的值就是 1 ，low_limit_id的值就是 4 。
2
隔离级别

readview 规则

4.3 ReadView的规则

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。

  • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_ids列表中。
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

整体流程

读已提交： 每次读，都获取一次readview
可重复读：只在事务第一次读时，获取一次readview，后续都使用最初的readview。

  1. 首先获取事务自己的版本号，也就是事务 ID；
  2. 生成 ReadView；
  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
  5. 最后返回符合规则的数据。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

lnnoDB中，MVCC是通过Undo Log + Read View进行数据读取，Undo Log保存了历史快照，而Read View规则帮我们判断当前版本的数据是否可见。

mvcc 解决幻读

事务A trxid=10，事务b trxid=20。
事务a 读取readview
creatorid 10
up limit trx id=10，
low limit trx id =21，
trx ids=[10,20]

产生的readview 在第一次的时候产生，后续插入，在readview 中没有。所以读取不到，解决的幻读。

使用mvcc 解决 的好处

  1. 读写之间 阻塞的问题。通过mvcc 可以让读写互不阻塞，读步阻塞写，写不阻塞读。
  2. 降低了死锁概率。mvcc 采用了乐观锁的方式，读不加锁，对于写，也只锁定必要的行。
  3. 解决快照读。

9 binlog 日志

  1. 用于数据恢复，数据库意外停止，误删除操作，通过binlog 恢复数据。
  2. 用于数据复制。数据库主从，通过binlog来实现数据的同步。 通过增量备份，完成数据库的无损恢复。

binlog 格式

  • statement
    每条修改数据的sql 都记录在binlog 中，不需要记录每一行的变化，减少binlog 日志量，减少io。
    但是 一些 uuid() now()等函数，存储过程，不能复制。

  • row
    记录那条数据被修改了，被修改成什么样了。

不会出现一些 函数，存储过程 不能复制的情况

但是会产生大量日志。

  • mixed

再谈binlog

binlog 写入时机：
在事务执行过程中，先把日志写入到 binlog cache，事务提交的时候，再把binlog cache 写道binlog 文件中。因为一个事务的binlog 不能被拆开，无论事务多大，都要一次性的被写入，所以系统会给每个线程分配一个内存块，作为binlog cache。

binlog 刷盘 如下：
3

  • 上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
  • 上图的fsync，才是将数据持久化到磁盘的操作

参数策略

3

write和fsync的时机，可以由参数sync_binlog控制，默认是 0 。

  • 0 :
    为 0 的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。

  • 1
    为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同 redo log 刷盘流程 一样。

  • N （N>1）
    表示每次提交事务都write，但累积N个事务后才fsync

binlog redolog 对比

redo log 是物理日志，记录的内容是在某个数据页上，某个偏移量上，对数据做了修改，属于innodb 存储引擎层产生的。
binlog 是逻辑日志,记录的内容是给 id=2，修改字段c的内容，属于mysql server 层。

  • 持久化的侧重点不同。
    • redolog 让 innodb 存储引擎又了崩溃恢复的能力。
    • binlog 保证了myslq 集群架构的一致性。

两阶段提交

执行更新，会记录redolog binlog 两块日志。redolog 在事务执行过程中不断写入，binlig 在事务提交的时候，才会写入。写入实际不一样 。

redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，sQL语句为update Tset c=1 where id=2。

假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢?

·由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是o，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。 mysql 的主 根据redolog 恢复，从库根据主库的binlog 恢复，导致数据不一致。

3
4

引入两阶段提交

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

prepare 阶段 在开始事务，更新数据的时候，不断写入redo log 日志的，标记为prepare阶段.
在提交事务后，写入binlog日志，接着在redolog 日志上阶段更新 ，更新 为commit 阶段。
使用 两阶段提交 后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redolog还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

10 innodb 架构图

3

innodb 内存结构

3
3

buffer pool

缓冲池 以page 为单位，底层采用链表数据结构管理page，page 分为三种类型

  • free page 空闲page ，未被使用，
  • clean page 被使用page ，数据没有被修改过。
  • dirty page 脏页，被使用的page，数据被修改，数据与磁盘的数据产生不一致。
free 链表

把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作 free链表

有了这个 ·free链表 之后事儿就好办了，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 free链表 中取一个空闲的缓存页，并且把该缓存页对应的 控制块 的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的 free链表 节点从链表中移除，表示该缓存页已经被使用了～

flush 链表

如果我们修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为 脏页
我们不得不再创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫flush链表。

LRU链表的管理

Buffer Pool 对应的内存大小毕竟是有限的，如果需要缓存的页占用的内存大小超过了 Buffer Pool 大小，也就是 free链表 中已经没有多余的空闲缓存页的时候岂不是很尴尬，发生了这样的事儿该咋办？当然是把某些旧的缓存页从 Buffer Pool 中移除，然后再把新的页放进来喽～ 那么问题来了，移除哪些存页呢

lru 链表：
innodb 使用 改进的lru

1. 针对预读

所谓 预读 ，就是 InnoDB 认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到 Buffer Pool 中

问题：
预读到的数据页 可是如果用不到呢？这些预读的页都会放到 LRU 链表的头部，但是如果此时 Buffer Pool 的容量不太大而且很多预读的页面都没有用到的话，这就会导致处在 LRU链表 尾部的一些缓存页会很快的被淘汰掉，也就是所谓的 劣币驱逐良币 ，会大大降低缓存命中率。

2. 扫描全表

·当需要访问这些页时·，会把它们·统统都加载到 Buffer Pool 中,这严重的影响到其他查询对 Buffer Pool 的使用，从而大大降低了缓存命中率

描述

设计 InnoDB 的大叔规定，当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部。

冷区 移入 热区
，在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中 记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部。
innodb_old_blocks_time 控制的，你看：

mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_time';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000 |
+------------------------+-------+
1 row in set (0.01 sec)
热区优化

也就是说如果某个缓存页对应的节点在 young 区域的 1/4 中，再次访问该缓存页时也不会将其移动到 LRU 链表头部）

change buffer

更改缓冲区 ，针对非唯一的二级索引页，在dml 语句中，如果数据在bufferpool 不存在，不会直接操作磁盘，而是将数据变更存在 change buffer 上，在未来读取数据的时候，将数据合并到buffer pool，然后刷新到磁盘上，。

自适应hash index

优化buffer pool 数据的查询。
参数 adaptive_hash_index 默认开启。

log buffer

日志缓冲区，用来保存写入到磁盘的 log 日志数据。
3

磁盘结构

3

  • Doublewrite buffer files：
    双写缓冲区，Innodb 存储引擎将数据页从buffer pool 刷新到磁盘前，先将数据页 写入双写缓冲区文件中，用于系统异常是恢复数据。
    3

后台线程

将innodb 缓存池的数据 刷新到磁盘中

  • Master Thread
    将缓冲池的数据刷 异步刷新到磁盘，
  • IO Thread
  • purge thread
    回收事务已经 提交的undo log
  • page clear thread
    协助 master thread 脏页刷新。

3

buffer pool 体系结构

3

11 online ddl

3

3

12 sql 优化

莫等闲！
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库-MySQL中for update的作用和用法
daobuxinzi的博客
03-13 6037
一、for update定义 for update是一种行级锁，又叫排它锁。 一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。 如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。 行锁永远是独占方式锁。 只有当出现如下之一的条件，才会释放共享更新锁： 执行提交（COMMIT）语
for UPDATE导致锁表的问题
weixin_45642669的博客
04-19 6146
select for update会导致一些问题
MySQL行级锁for Update？
最新发布
m0_51577196的博客
05-16 466
mysql行级锁for update
MySQL中的update操作会不会自动加锁
alian
05-20 7464
先阐述结论 MySQL5.5版本以后默认用InnoDB存储引擎，并且采用可重复读的隔离级别，在进行update操作会进行加锁的！！！ 如果涉及到索引查询则会加行锁，如果需要查整个表，则会加间隙锁。 看例子 1.建表 create table orders( id int not null primary key, count int not null )engine = innodb default charset = utf8mb4; 2.插入数据 insert orders values(1,
SELECT...FOR UPDATE
雨后是冰雹
05-20 2465
悲观锁(Pessimistic Locking) ，顾名思义，在处理多个并发操作时，同一时间点，只能有一个线程可能访问其资源，其他线程需等待锁的释放，才能进行操作。乐观锁的核心思想是：在进行数据更新前，需先检查数据是否被其他事务修改过，如果没有修改，则进行更新操作，如果有修改，则进行冲突处理。当一个事务需要对某个数据进行操作时，会先获取锁，同时，其他事务如果也需要访问该数据，就需要等待锁的释放。是一种数据库查询语句，用于在事务中对选定的行或表进行加锁，以防止其他事务同时对这些数据进行修改。
mysql数据库，悲观锁。for update 的用法。
alin1215的博客
12-25 2130
一、for update定义 for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行...
mysql SELECT FOR UPDATE语句使用示例
09-10
MySQL中的`SELECT FOR UPDATE`语句是在事务处理中用于实现数据锁定的一种机制，它主要用于解决多用户并发操作时的数据一致性问题。在InnoDB存储引擎下，MySQL默认的事务隔离级别是`REPEATABLE READ`，这允许事务在...
Mysql 数据库死锁过程分析(select for update)
09-10
避免这种死锁的一种方法是在设计事务时遵循一定的顺序规则，例如，确保所有事务按照相同的顺序访问和修改数据，或者使用`FOR UPDATE SKIP LOCKED`（Oracle支持，但MySQL不直接支持）来跳过已锁定的行。另外，可以...
mysql-4.pdf
04-23
FOR UPDATE`或`SELECT ... LOCK IN SHARE MODE`时，InnoDB可能会使用间隙锁。 InnoDB的内存结构包括缓冲池（Buffer Pool）、重做日志缓冲区（Redo Log Buffer）、InnoDB高速缓存（InnoDB Cache）等组件。缓冲池...
Mysql 锁机制的详细说明
08-22
【MySQL 锁机制详解】 MySQL 的锁机制是数据库管理系统中用于协调多个并发事务对数据进行访问的一种机制，确保数据的一致性和完整性。在开发中，虽然我们可能并不总是主动使用锁，但它们在多用户环境下确保数据正确...
MySQL锁类型以及子查询锁表问题、解锁1
08-03
FOR UPDATE`语句就是一种行级锁的用法，用于在更新数据前锁定选定的行，防止其他事务在此期间对这些行进行修改。当在查询条件中明确指定了主键，并且查询能够匹配到具体行时，InnoDB会实施行锁。例如： ```sql ...
详解mysql的for update
咖啡色的羊驼
07-16 2万+
前言近期开发与钱相关的项目，在高并发场景下对数据的准确行有很高的要求，用到了for update，故总结一波以便日后留恋。本文讲解几个点： 1.for update的使用场景 2.for update如何使用 3.for update的锁表 4.for update的注意点 5.for update的疑问点for update的使用场景如果遇到存在高并发并且对于数据的准确性很有要求的场景，
锁表原因及解决思路
u011194983的专栏
10-26 3万+
1、锁表发生在insert  update 、delete 中    2、锁表的原理是 数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户  3、锁表的原因    第一、 A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表
Oracle——关于for update的锁表与解锁
痛觉残留的博客
10-22 7502
前传：上周工作时，犯了件蠢事，select数据的时候加入了for update却忘记了加条件，测试环境的数据过多，查询时间太长，于是就把这个进程给kill了，结果造成了锁表。结论：for update语句一旦运行就会把表锁住，防止他人和你一同更改，第二个连接进来再进行select 。。。for update会造成阻塞，即第二个连接无法查询出数据。在前传中，我将进程kill了，但是没有提交该事物，所以
for update造成的锁表以及解锁
Just_do_it_HZF的博客
01-01 3977
记录一次生产由于for update导致无法操作表的情况 当使用select语句查询表时，后面跟着for update，当没有commit的话就会造成别人无法操作 select * from AA for update; 针对以上情况，解决方案如下： （一）查询锁表进程SQL语句： select * from v$session t1, v$locked_object t2 wh...
MySQL死锁产生原因和解决方法
ludongguoa的博客
10-25 8697
Mysql锁类型 一、锁类型介绍： MySQL有三种锁的级别：页级、表级、行级。 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 算法： next KeyLocks锁，同时锁住记录(数据)，并且锁住记录前面的Gap Gap锁，不锁记录，仅仅.
mysql悲观锁总结和实践
chenzhou123520的专栏
05-07 1552
最近学习了一下数据库的悲观锁和乐观锁，根据自己的理解和网上参考资料总结如下：   悲观锁介绍（百科）： 悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁...
mybatis 使用for update，对数据进行行级锁定
热门推荐
XiChuan的博客
10-31 4万+
for upate使用场景在Spring Date JPA之 for update中已经介绍过了，想要了解，可以看博客：https://blog.csdn.net/zc_ad/article/details/83578487，在这边会介绍一下在mybatis中的使用场景。使用了mybatis-plus，前面有些过基础教程，地址:https://blog.csdn.net/zc_ad/article...
MySQL的 乐观锁 悲观锁SELECT ...for update
m0_37932636的博客
06-30 1314
最近的项目中，因为涉及到Mysql数据中乐观锁和悲观锁的使用，所以结合项目和网上的知识点对乐观锁和悲观锁的知识进行总结。 转载自：https://www.cnblogs.com/wxgblogs/p/6849064.html 自我补充的内容： mysql console-1 mysql console-2 当先执行图1的内容，图二查询对应的数据，数据库锁的级别 表内容： 悲观锁介绍 　　悲观锁是对数据被的修改持悲观态度（认为数据在被修改的时候一定会存在并发问题），因此在整个数据处
mysql FOR UPDATE
08-18
在MySQL中，`FOR UPDATE` 是一种用于 SELECT 查询语句的锁定机制。当在 SELECT 语句中使用 `FOR UPDATE` 时，它将锁定所选行，以防止其他事务对这些行进行修改或删除。 使用 `FOR UPDATE` 的语法如下： ```sql SELECT * FROM table_name WHERE condition FOR UPDATE; ``` 在执行这个语句时，MySQL将获取一个行级锁，以确保在当前事务中对所选行的修改不会与其他事务相冲突。这样可以避免并发问题，确保数据的一致性。 需要注意的是，`FOR UPDATE` 在事务中使用才有效。在没有开启事务的情况下，`FOR UPDATE` 语句将被忽略。 示例： ```sql START TRANSACTION; SELECT * FROM orders WHERE status = 'pending' FOR UPDATE; -- 在这个事务中，对于满足条件的行，其他事务无法修改或删除它们 COMMIT; ``` 使用 `FOR UPDATE` 可以确保在事务中对所选行进行独占访问，从而避免并发问题。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值