mysql锁和MVCC

目录

mysql日志和MVCC

mysql日志6种日志

  • 事务日志
    • redo
    • undo
  • 慢查询日志，记录了所有超指定时间的sql
    • slow_query_log ，开关，默认关闭
    • slow_query_log_file ，文件地址
    • long_query_time 默认10秒
  • 通用查询日志，里面记录了所有sql 和执行时间
    • general_log ，是否开启，默认关闭
    • general_log_file ，日志文件位置，默认在mysql根目录
  • binlog日志 ,记录者mysql 的dml 和ddl日志，也就是数据变动和表结构变动,用于数据备份和主从同步，默认开启
    • log_bin 开关
    • log_bin_basename 日志二进制文件名字，会用这个名字的文件+后缀编号创建文件，mysql重启一次编号变一次
    • sql_log_bin 同步sql 变动的开关，关闭后不会同步改动
    • binlog_expire_logs_seconds，日志过期时间，默认30天
    • max_binlog_size ，日志文件最大多少，默认1G
    • show binary logs; 查看日志文件列表
    • show binlog envents in "文件名" 开始位置 limit n,m 查看事件
    • mysqlbinlog -v "binlog日志" 查询日志文件的内容
    • binlog_cache_size binglog binlog缓存的大小， 写入硬盘之前先写入缓存，这块内存的大小(每个线程)
    • sync_binlog binlog的刷盘方式，0是每次事务提交写磁盘，等操作系统刷盘，1，写硬盘并且刷盘，1>表示每次提交事务写磁盘，几次事务才刷盘一次。
  • 中继日志，用于从服务器的主从同步
  • 错误日志，名字是错误日志，实际记录了4个日志级别，system，error ，warning，note，默认开启
    • log_error ，日志文件名字
    • 可以通过performance_schema.error_log查询和日志里面内容一样
  • 数据定于语句日志
#重新生成日志文件，对slow_query—log和general_log 有效
#创建文件，用户和用户组都是mysql，-m 是权限
install -o mysql -g mysql -m 0644 /dev/null /var/log/mysqld.log
mysqladmin -u root -p flush-logs;

通过binlog恢复数据

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] --database=数据库名字 filename|mysql –u user -p pass -v 目标数据库;
  • filename：是日志文件名。
  • option：可选项，比较重要的两对option参数是--start-date、--stop-date 和 --start-position、-- stop-position。
    • --start-date 和 --stop-date：可以指定恢复数据库的起始时间点和结束时间点。
    • --start-position和--stop-position：可以指定恢复数据的开始位置和结束位置。

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复。

PURGE MASTER LOGS：删除指定日志文件**

#删除指定文件编号之前的文件
PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’ 

#删除指定时间之前的文件
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

#清除所有binlog日志
reset master;

redo日志

redo log是数据保证能写到磁盘的机制，也是数据库持久化特性的基础，内存里面数据修改的时候，存盘才能保证数据不丢，但是数据库中存的最终数据，类似总账，redo log 类似流水账，比最终结果写入快很多，只要 redo log file中有了记录，那么就能能通过它把最终结果写入。

事务中数据发生变更，先写到内存(redo log buffer)，然后写入log文件(redo log file)，然后调用操作系统刷盘
image-20250910183455665

redo日志的一些相关参数

  • innodb_flush_log_at_trx_commit
    • 1 默认值，每次事务提交写入日志，每次刷盘(不管怎么操作应用mysql还是操作系统异常都能保证正确性)
    • 2 每次事务提交都写入日志文件，固定时间刷盘(只要操作系统不是异常关闭就能保证数据一致性)
    • 0 每次事务提交的时候不写入日志文件，固定时间写入日志文件，这意味着没有提交也会定时存盘，然后刷盘(不能保证数据的一致性)
  • innodb_flush_log_at_timeout ，指定时刷盘的时间
  • innodb_log_group_home_dir，存放日志文件的目录
  • innodb_log_files_in_group, redo_log 文件有几个,默认2个
  • innodb_log_file_size，一个redo_log文件有多大，默认48MB

undo日志

undo日志在事务写数据之前(写内存数据之前)产生,undo日志的目的主要是回滚，在事务需要回滚的时候，内存里面的数据需要做反向的操作(不是还原是追加逆向操作)，这时候就需要读取undo日志，回滚的时候做逆向操作修改内存数据，并且删除对应undo日志，在redo log buffer(redo 日志内存)里面写入数据，然后在事务提交的时候写入redo file。如果事务不是正常提交就服务器重启，内存里面的数据会丢失，undo日志里面的数据会被舍弃，结果相当于事务回滚。

undo 本质是记录未提交的事务修改记录(同时也会保持已提交，但是可能还需要被快照读读到的数据)。undo不需要存盘，undo在当前最小未提交事务提交的时候，undo日志中的事务ID比这个ID小的可以开始被清除。

相关参数

  • innodb_rollback_segments undo 回滚段的数量，默认128，一个回滚段可以记录1024个 undo log segment。
  • innodb_max_undo_log_size undo日志的大小，默认1G
  • innodb_undo_directory undo日志目录，
  • innodb_undo_tablespaces ，undo日志文件数量，默认2

回滚段中数据的分类

  1. 未提交的回滚数据
  2. 已提交但未过期的回滚数据，别的事务还可能会读
  3. 事务已经提交并且已经过期的数据，比它早的事务都已经完成了，不会有事务读取它了，可以回收

有了undo日志才有回滚，undo日志是数据源原子性的基础。

事务数据更新的过程中undo，redo日志的作用

image-20250910222509880

undo日志和数据记录之间的关系

更新记录的时候，硬盘对应的数据页会读取到内存，然后里面对应的一条记录如果更新，记录信息里面写入undo日志的ID(rollback point),回滚的时候更具undo日志形成的链做回滚操作就行了

image-20250910224111232

、

锁和MVCC

数据库解决读写冲突可以有两种方案

  • 读写都加锁，读加读锁，写加写锁
  • 写加锁，读用MVCC

需要注意数据查询才有MVCC，MVCC解决的是有写的情况下应该怎么去读数据，要做到无锁的的读，还有事务的隔离性(怎么避免脏数据，避免不可重复读数据，避免幻读数据)，MVVCC只有innodb才支持
DDL(比如表结构的修改)是不受事务控制的，也不走MVCC，ddl的改变只有写锁，没有隔离性，没有回滚。

MVCC(multi-versioned concurrency control),需要ReadView 和 undo日志的支持，undo日志记录了数据的修改过程，ReadView 记录了读取数据的时候当前状态标记，也叫作快照。

ReadView

ReadView就是一份快照标记，通过这份标记可以计算出undo日志中那些数据应该被读到

readView主要参数

  • 产生快照的时候的所有活跃事务ID
  • 最小事务ID(最小为提交的事务ID)
  • 最大事务ID
  • 当前事务ID

UNDO日志

undo日志就是数据的修改记录，它是一个反向链表，查询到数据以后，数据行里面有个回滚指针，指向undo日志的链节点，根据ReadView确定undo日志的那个节点是应该被读取到的记录(其实找到和当前记录有关的可以读取的最新一次修改就行)。
undo日志主要参数

  • 修改数据事务ID
  • 修改的数据

过程大概是这样的，不同事务写数据前会记录undo日志，并且形成一个链。
读数据的时候会产生一个ReadView，在可重复读的隔离级别下第二次读取数据会重用上一次的ReadView。拿到ReadView以后去遍历undo日志链。

通过ReadView遍历undo日志过程

可重复度隔离界别的情况下

  • undo日志链里面修改数据事务ID小于ReadView里面最小事务ID的都是已经提交了的，可以读到
  • 比ReadView最大事务ID大的undo日志记录都是后来产生的，都不读
  • 处于最大事务ID和最小事务ID中间，等于当前ReadView事务ID的undo日志可以会读取
  • 处于最大事务ID和最小事务ID中间，处于活跃事务ID的记录都不读取(因为还没有提交)
  • 处于最大事务ID和最小事务ID中间，不处于活跃事务ID的记录都都读取(因为在产生ReadView 之前已经提交）

不同隔离级别的区别

脏写只有同时写才会出，任何隔离级别都是加独占锁才写的，所以不会出现脏写。

  • 读未提交，不走ReadView，任务undo日志都可以读，不管是否提交
  • 读已提交隔离级别，每次读都会产生一个新的ReadView，所以两次读的数据可能不一样，会出现不可重复读问题
  • 可重复度隔离级别，在一个事务中，第一次读取的时候产生ReadView，后面共用这份，解决了不可重复读，但是会出现幻读(查询不到别的事务添加的记录，但是在当前事务添加会报错，或者有多条数据)。
    • 需要注意，生成的ReadView是对事务内所有查询生效，不是一份数据一个，ReadView不是事务开始默认生成，是第一次查询以后生成，如果生成新的，那么所有查询都会获取当前最新的已提交数据。
    • undo日志上的当前读记录只是对当前锁定的数据有效，所以undo日志是对记录级别做的修改记录
  • 序列化级别，读不走ReadView，而是走加锁，加共享锁，这时候读会排斥写，读不排斥读

在可重复读的基础上解决幻读和序列换加锁其实是一样的原理，区别在于在可重复读的基础上加锁只有当前一条查询是加锁，序列化的隔离级别所有查询都是加锁。

for update 类似所有的修改操作，会在undo日志后面加一条全新的记录，不修改数据，但是获取到最新的值，效果就是把快照读变长了当前读(添加的undo日志是最新的记录，记录ID是当前事务ID，可以被读到)

幻读和不可重复读

幻读其实描述的是查询不到一条记录，然后添加这条记录却会报错的情况(也可能是查询的到，但是实际没有)。最常见的就是我们在用户注册的时候，查询了用户名不存在然后插入对应的记录，然后报唯一键冲突。解决这个办法很简单，把查询语句的快照读变成当前读

select * from user where userName = "xxx" for update;

这时候如果比当前事务晚的事务插入并且提交了数据，是可以被当前事务查到的，并且for update 锁定了这条记录(这是读锁)，别的事务不能写入，只能当前事务提交以后才能写入，别的事务如果用当前读( select for update)来读取数据，这时候如果数据不存在加读锁(如果存在加写锁)，如果加的都是读锁那么会在真实提交的时候锁升级成写锁，这时候会等待另外一个释放读锁，或者等另外一个也升级写锁，mysql检查死锁会鬼姑娘其中代价小的一个。

幻读在可重复度的级别才会出现，如果是读已提交的级别，不会出现，因为幻读依赖不可重复读的快照。

mysql锁规律

  • mysql的读数据一般不加锁，除了for update 还有 一些 手动加锁的语句除外
  • mysql的锁是如果不能命中索引就会使用表锁，可以命中索引就会使用行级锁或者间隙锁。
  • for update 查询记录不存在的时候加读锁，记录存在的时候加写锁。
  • for update 以后，记录不存在加读锁，然后真实修改数据( 插入，修改，删除 )的时候会锁升级，读锁升写锁，如果这时候有多个持有读锁，那么第一个升级的会等待，直到别的释放读锁，或者也升级写锁(这时候死锁检查会回滚其中一个)

加锁的区别

  • select xxxx for update ，数据存在的时候加写锁，数据不存在的时候加读锁(这时候和 for share 一样)
  • select xxxx for share （8.0才能用），总是假的读锁，并且在后面真实修改数据的时候升级读锁位写锁
  • select xxxx lock in share mode
  • delete,update,insert 加的都是写锁
  • 修改表之类的加表级别的锁

mysql8.0可以指定等锁方式，加载加锁语句后面

  • nowait 不等待，立即报错

    select  xxxx   for update nowait

  • skip locked，只返回没锁定的数据，跳过锁定数据

锁的粒度

  • 表级别

    • 一般用在myisam(只有表级别的锁)，innodb有表级锁，但是一般我们用行级的锁

      #锁定指定表
lock tables goods read/write;

#解锁所有锁定的表
unlock tables;

#查询锁定的表
show open tables where in_use>0;

  • 意向锁
    在加行级别读写锁的时候，会自动在表级别加上意向读写锁,这个锁随着行的锁的释放而释放并且通过收尾open tables 里面查不到锁定记录。

    意向锁会影响表锁的获取，也就是会影响表结构的修改，这时候表的修改会阻塞等待。

  • 行
    mysql加锁 的时候可以命中确定的行就是行锁，行锁是可以加写锁或者行锁
    行锁如果获取所得顺序不一样，可能出现死锁。

  • 间隙锁
    mysql加锁的时候如果加锁的记录不存在，就是间隙锁，间隙锁是一个读锁，并且会在使用的时候变长写锁
    间隙锁默认是前后开区间，不包含上下边界
    间隙锁的锁升级过程很容易出现死锁。

    #当where语句查询的数据不存在的时候，加上间隙锁，并且下面三种方式加的锁一样。
select xxx  for update;
select xxx  for share;
select xxx  lock in share mode;

  • 临间锁
    行锁+间隙锁就是临间锁，数据存在锁行，数据不存在锁间隙，临间锁就是可以锁定间隙的同时锁定边界

    where id >3 and id <=5 lock in share mode;

  • 页锁
    介于行锁和表所之间，mysql锁空间是有限的，如果获取了太多行锁可能会自动升级成页锁或者表锁。

  • 全局锁

    #加锁
flush tables with read lock;

#解锁
unlock tables;

查看阻塞情况

查询执行中的任务

# 下面连个都可以，会出现没有断开的连接，或者阻塞的任务，或者当前还在执行的任务，通过时间和描述可以看出是否阻塞
show PROCESSLIST;

select * from information_schema.`PROCESSLIST`;

查看执行中的事务

#查看未提交的事务
select * from information_schema.INNODB_TRX;

mysql锁超速时间默认是50秒innodb_lock_wait_timeout=50

mysql锁相关参数

#innodb锁有关的状态信息
show status like "%row_lock%";
  • Innodb_row_lock_current_waits ，当前等锁数量
  • Innodb_row_lock_time，总等锁时间
  • Innodb_row_lock_time_avg，等锁平均时间
  • Innodb_row_lock_time_max，最大等锁时间
  • Innodb_row_lock_waits ，等锁次数

查看mysql锁情况

  • lock_DATA 锁定的上界

  • lock_mode， S/X,REC_NOT_GAP/GAP

    • S表示共享，X排它

    • REC_NOT_GAP表示 记录锁。GAP，表示间隙，也就是不包含上下边界。不写表示包含上界

    • 如果需要表示锁定一个区间包含上界和下界需要两条锁。

    • IX的是意向独占锁，是加载表上面的

#查询已经获取的锁
select * from performance_schema.data_locks;

#查询正在等的锁
select * from performance_schema.data_lock_waits

下面是一条锁定无穷大上界，并且包含上界的锁

image-20250911222812096

如果上下边界都需要那么是两条锁(一条锁定下边界，一条锁定间隙还包括上边界)
image-20250911230158295

加锁位置

  • 查询命中主键索引的会一主键索引加锁
  • 查询使用其他索引的会使用命中索引加锁，并且，如果是精确数据还对对主键索引加锁
  • 主键索引加锁依赖记录上面隐藏字段的trx_id
  • 对update语句加锁位置除了where后面能命中索引的地方，还有set里面能命中索引的地方(如果set和索引相关，锁定set 修改后的值对应的索引)
  • 普通索引加锁依赖页里面，Page_MAX_TRX_ID，和回表查找对应记录的trx_id

悲观锁和乐观锁

  • 悲观锁就是真实的锁
  • 乐观锁，是一种无锁机制,适合几乎很少写的情况，是通过检查变更来确定锁的过程中是否有变动，乐观锁必须要考虑重试机制，打复杂逻辑中直接报错是不合理的，数据库加version字段是一种乐观锁
#悲观锁的写法，依赖数据库写锁，锁定时间从update语句开始到事务解锁，这些写法需要注意，考虑要在  where后面加上金额的限制，避免负值之类的超限问题。
update  xxx  set   a = a+#{xxx} where xxx and a+#{xxx}>=0;

#乐观锁的写法，依赖修改记录条数判定是否被别的事务冲掉，锁定时间从查询开始到事务结束，比第一种写法锁定时间略长，并且需要考虑重试，优点在于修改不依赖增量。
update xxx set a = #{xxx},version = version+1 where xxxx and version = #{version}


#会出问题的写法
update  xxx  set   a = #{xxx} where xxx;



数据库的update本来就要获取写锁才能执行，所以不能提高效率，都是要等待，乐观锁的写法修改的值可以是任意值,悲观锁的写法目标值依赖当前值的增量，写法3不能感知并发的更新，在有并发要求的地方不能用。

备注有些资料认为select xxx for update,才是悲观锁的写法，这种是错误的，首先 for update这种写法确实是悲观锁它是提前拿到锁，避免查询到update的过程中有人插队,但是update语句本来就是带有写锁的，只要是用增量就可以缩短锁定时间到update 语句开始。

即便是 for update的写法，其实和version的乐观锁写法在数据库中性能区别不大，

  • 因为for update的时候获取写锁，这以后数据不能改，直到事务完成，但是mysql读数据不加锁，所以可以读。
  • version的写法，查询的时候得到最新的version值，无锁，但是到事务结束，别的事务不能写，只要写了两个事务就会有一个冲突重试，效率并不比for update高，还要加入重试机制。重试的代价可能比等待更高。

mysql的一些其他的锁

主键自增锁

由innodb_autoinc_lock_mode参数控制，默认单个插入是不需要事务提交就会释放获取ID的锁。

MDL锁(元数据锁)

在读增删改数据的时候，会对表级别加上MDL读锁，这时候会阻止表结构的修改(这时候获取不到MDL写锁)

mysql的锁之间的关系好像是公平锁，先来的读锁会阻塞写锁，即便这个写锁没有获取到，后面也不能再获取新的读锁。或者是MDL锁的时候是公平的？

插入意向锁

首先这就是一个写锁，或者插入请求排队等待写的过程，前提条件是先获取了间隙锁然后在插入数据，比如事务A获取了间隙锁，这时候事务B需要获取对应的写锁才能插入，这时候需要等待A执行完释放了锁，事务B才能获取锁执行。只要把间隙锁是读锁，本事务用到这个间隙锁的写锁会把读锁升级为写锁理解，就能明白插入意向锁就是一个逻辑概念。

隐式锁和显示锁
  • 显示锁，加锁以后能在performance_schema.data_locks;里面查到的就是显示锁
  • 隐式锁，mysql的插入默认不会加行锁，只会加一个表级的意向锁，如果这时候另外一个线程插入了这份数据(获取同一个锁)，有挣锁的时候插入隐式锁就会变成显示，这时候会有两条行锁，一条锁获取到锁(前面的隐式锁显形)，后面一条是等待状态(后一个争锁等锁)。这和Java的偏向锁很像，无锁竞争是无锁，有锁竞争就变成有锁。

posted on 2025-09-14 01:37  zhangyukun  阅读(9)  评论(0)    收藏  举报

导航