select ... for update 语句的分析

最新推荐文章于 2023-12-28 11:01:36 发布
最新推荐文章于 2023-12-28 11:01:36 发布
阅读量1.5k 收藏 5
点赞数 1
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a860MHz/article/details/113405551
版权

1. 前言

本文将介绍select … for update语句相关知识，以及对可能导致的死锁、影响、相关代码进行分析。

如无特殊说明，本文涉及的MySQL版本为8.0.22，事务隔离级别为RR，数据库引擎为INNODB。

2. 实验数据准备

创建一个测试表t_a：

CREATE TABLE `test_a` (
  `id` int NOT NULL,
  `a` int NOT NULL,
  `b` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入测试数据：

INSERT INTO `test`.`test_a` (`id`, `a`, `b`) VALUES ('5', '5', '5');
INSERT INTO `test`.`test_a` (`id`, `a`, `b`) VALUES ('10', '10', '10');
INSERT INTO `test`.`test_a` (`id`, `a`, `b`) VALUES ('15', '15', '15');
INSERT INTO `test`.`test_a` (`id`, `a`, `b`) VALUES ('20', '20', '20');

2. 当前读与一致性读

通常的在RR下，开启一个事务时，会记录下当前系统的活跃事务数组和max_trx_id全局变量；并将活跃事务数组中的trx_id最小值置为低水位，max_trx_id加1置为高水位。同时利用每一行记录的隐藏列db_trx_id和db_roll_pointer确定实际返回的数据。

  • 对于db_trx_id小于低水位的row，系统认为该版本对于当前事务是可见的；
  • 对于db_trx_id大于等于高水位的row，系统认为该版本对于当前事务是不可见的；
  • 对于db_trx_id大于等于低水位且小于高水位的row，如果db_trx_id在活跃事务数组中，则系统判定为不可见；否则对当前事务时可见。

如果判断为不可见，系统将借助另一个隐藏列回滚指针db_roll_pointer，找到undo日志中上一个版本的位置，并检查该行数据的db_trx_id；如此循环，直到找到可见的db_trx_id。

2.1 实验1

打开两个命令行，按时间线操作，即使session B查询时session A已经提交，但查询结果仍为a=10。
在这里插入图片描述

2.2 实验2

打开两个命令行，按时间线操作，session B在session A提交后开始事务，但查询结果为a=11。
在这里插入图片描述

2.3 实验3

在session B中增加了一条的select … for update语句，要求查询时加上X锁，返回结果变成了a=11。
在这里插入图片描述
使用select … lock in share mode 和 select … for update语句时，读取操作为当前读，直接读取该行最新值，不再基于undo log回滚数据，并对读取记录加S锁或X锁，阻塞其他事务的修改操作。

3. 间隙锁、插入意向锁和死锁

当select … for update 按主键等值查询，且能够查到记录时，逻辑较为明确；但如果查询对象不存在，引擎会对扫描过的间隙上锁，可能导致死锁。

3.1 实验4

首先关闭死锁检测

SET GLOBAL innodb_deadlock_detect = off;

在这里插入图片描述
session A和session B由于死锁，都阻塞直到超时，并抛出如下异常：

ERROR: 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.2 实验5

然后让我们打开死锁检测后，按实验4的步骤重新跑一遍。

SET GLOBAL innodb_deadlock_detect = on;

在这里插入图片描述
这一次，死锁检测发现死锁，session A执行成功，session B被引擎回滚。

使用命令show engine innodb status; 查看引擎死锁状态，

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-30 10:36:45 0x700004c8d000
*** (1) TRANSACTION:
TRANSACTION 2112, ACTIVE 20 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 12, OS thread handle 123145392267264, query id 480 localhost 127.0.0.1 root update
INSERT INTO `test`.`test_a` (`id`, `a`, `b`) VALUES ('7', '7', '7')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `test`.`test_a` trx id 2112 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000083d; asc      =;;
 2: len 7; hex 02000000890237; asc       7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `test`.`test_a` trx id 2112 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000083d; asc      =;;
 2: len 7; hex 02000000890237; asc       7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 2113, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 14, OS thread handle 123145392570368, query id 481 localhost 127.0.0.1 root update
INSERT INTO `test`.`test_a` (`id`, `a`, `b`) VALUES ('7', '7', '7')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `test`.`test_a` trx id 2113 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000083d; asc      =;;
 2: len 7; hex 02000000890237; asc       7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `test`.`test_a` trx id 2113 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000083d; asc      =;;
 2: len 7; hex 02000000890237; asc       7;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000a; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

果然发生了死锁，相关信息如下：

  • index PRIMARY of table test.test_a，表示主键索引
  • lock_mode X locks gap before rec insert intention waiting，表示插入意向锁，gap表示间隙锁而非行锁
  • n_fields 5; compact format; info bits 0及之后的5行，表示gap锁之后的记录。从这条信息也可看出，间隙锁的范围其实就是由间隙右边的记录定义的。
    0: len 4; hex 8000000a; // id=10
    1: len 6; hex 00000000083d; // trx_id，事务id
    2: len 7; hex 02000000890237; // roll_pointer，回滚指针
    3: len 4; hex 8000000a; // a=10
    4: len 4; hex 8000000a; // b=10

由于间隙锁之间是不冲突的，session A和session B的select … for update语句都能拿到(5,10)这个间隙锁。
但是insert这一动作，与session A和session B所持有的间隙锁都冲突了。
因此，当session A执行insert语句时，需要等待session B释放(5,10)间隙锁；而session B执行insert语句时，又需要等待session A释放(5,10)间隙锁；死锁产生了。

引擎在解决死锁时通常有两种方案：

  • 超时等待，如果关闭innodb_deadlock_detect，session会在innodb_lock_wait_timeout后超时，innodb_lock_wait_timeout默认50s。
  • 死锁检测，如果打开innodb_deadlock_detect，引擎会消耗一定的CPU算力进行死锁检测，发现死锁后，主动回滚部分事务，让另一部分事务得以正确执行。

4. 源码分析

4.1 间隙锁

获取锁时判断是否等待的逻辑如下：

UNIV_INLINE
bool lock_rec_has_to_wait(
    const trx_t *trx,    // 新锁事务id
    ulint type_mode,     // 锁类型，S锁、X锁、间隙锁、行锁、插入意向锁
    const lock_t *lock2, 
    bool lock_is_on_supremum) // 是否对supremum上锁，supremum是innodb为了简化代码设置的虚拟行记录，可以理解为无穷大
{
  ut_ad(trx && lock2);
  ut_ad(lock_get_type_low(lock2) == LOCK_REC);

  const bool is_hp = trx_is_high_priority(trx);
  if (trx != lock2->trx &&
      !lock_mode_compatible(static_cast<lock_mode>(LOCK_MODE_MASK & type_mode),
                            lock_get_mode(lock2))) {
	// 检查事务优先级，如果是高优任务且lock2是等待状态，则无需wait
    if (is_hp && lock2->is_waiting() && !trx_is_high_priority(lock2->trx)) {
      return (false);
    }

	// 如果是申请间隙锁或右侧记录为supremum，且非插入意向锁，则无需等待
    if ((lock_is_on_supremum || (type_mode & LOCK_GAP)) &&
        !(type_mode & LOCK_INSERT_INTENTION)) {
      return (false);
    }

    // 如果不是申请插入意向锁，且lock2为行锁锁，则无需等待
    if (!(type_mode & LOCK_INSERT_INTENTION) && lock_rec_get_gap(lock2)) {
      return (false);
    }

	// 如果申请的是间隙锁，且lock2为记录锁，则无需等待
    if ((type_mode & LOCK_GAP) && lock_rec_get_rec_not_gap(lock2)) {
      return (false);
    }

	// 略去无关代码

    return (true);
  }

  return (false);
}

4.2 死锁检测

死锁检测逻辑如下：

static void lock_wait_find_and_handle_deadlocks(
    const ut::vector<waiting_trx_info_t> &infos, // 所有wait事务
    const ut::vector<int> &outgoing, // infos[id].trx等待infos[outgoing[id]].trx
    ut::vector<trx_schedule_weight_t> &new_weights) { // 事务权重，发现死锁后，按权重判断回滚哪个事务
  ut_ad(infos.size() == new_weights.size());
  ut_ad(infos.size() == outgoing.size());
  ut_ad(infos.size() < std::numeric_limits<uint>::max());
  const auto n = static_cast<uint>(infos.size());
  ut_ad(n < static_cast<uint>(std::numeric_limits<int>::max()));
  ut::vector<uint> cycle_ids;
  cycle_ids.clear();
  // 状态保存容器
  ut::vector<uint> colors;
  colors.clear();
  colors.resize(n, 0);
  uint current_color = 0;
  // 遍历全部wait事务
  // 下面的代码就是个DFS，看起来像个leetcode medium题 :)
  for (uint start = 0; start < n; ++start) {
    if (colors[start] != 0) {
	  // 如果已经遍历过，则跳过
      continue;
    }
    ++current_color;
    for (int id = start; 0 <= id; id = outgoing[id]) {
      ut_ad(id != outgoing[id]);
      if (colors[id] == 0) {
        // 记录循环次数
        colors[id] = current_color;
        continue;
      }

      if (colors[id] == current_color) {
		// DFS时发现了一个循环，判断为死锁
        lock_wait_extract_cycle_ids(cycle_ids, id, outgoing);
        if (lock_wait_check_candidate_cycle(cycle_ids, infos, new_weights)) {
          MONITOR_INC(MONITOR_DEADLOCK);
        } else {
          MONITOR_INC(MONITOR_DEADLOCK_FALSE_POSITIVES);
        }
      }
      break;
    }
  }
  MONITOR_INC(MONITOR_DEADLOCK_ROUNDS);
  MONITOR_SET(MONITOR_LOCK_THREADS_WAITING, n);
}

5. 总结

select … for update 语句是常见的数据库悲观锁实现方式，但是由于当前读和间隙锁的部分特殊逻辑，可能与不加锁的查询语句语义不同，在生产环境下使用时需要结合相关场景。

860MHz
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
【Oracle笔记】select for update的用法及实例解析
程序员云帆哥的博客
01-28 3万+
一、它有什么作用： select for update 是为了在查询时,避免其他用户以该表进行插入,修改或删除等操作,造成表的不一致性。 二、举几个例子： select * from t for update 会等待行锁释放之后，返回查询结果。 select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果 select * from t fo
SELECT...FOR UPDATE
雨后是冰雹
05-20 2336
悲观锁(Pessimistic Locking) ，顾名思义，在处理多个并发操作时，同一时间点，只能有一个线程可能访问其资源，其他线程需等待锁的释放，才能进行操作。乐观锁的核心思想是：在进行数据更新前，需先检查数据是否被其他事务修改过，如果没有修改，则进行更新操作，如果有修改，则进行冲突处理。当一个事务需要对某个数据进行操作时，会先获取锁，同时，其他事务如果也需要访问该数据，就需要等待锁的释放。是一种数据库查询语句，用于在事务中对选定的行或表进行加锁，以防止其他事务同时对这些数据进行修改。
Select For update语句浅析
ciqu9915的博客
05-04 5732
Select … for update语句是我们经常使用手工加锁语句。通常情况下，select语句是不会对数据加锁，妨碍影响其他的DML和DDL操作。同时，在多版本一致读机制的支持下，select语句也不会被其他类型语句所阻碍。...
MySQL 因 select for update 导致的死锁问题 原因及解决方案
zhwbqd的专栏
12-01 1万+
问题描述:  检查程序运行日志的时候, 发现了很多DB死锁, 我这边程序中使用的是悲观锁， 因为考虑到不想让调用端重试。 死锁原因: 有多个请求同时希望insert表, 程序中逻辑如下: select for update where uid = ? if ［obj ］not exist insert  else update 死锁原因就是 select for upda
mysql SELECT ... FROM UPDATE
webnoties的专栏
06-24 1274
SELECT * FROM products WHERE id='3' FOR UPDATE;
mysql SELECT FOR UPDATE语句使用示例
09-10
MySQL中的`SELECT FOR UPDATE`语句是在事务处理中用于实现数据锁定的一种机制，它主要用于解决多用户并发操作时的数据一致性问题。在InnoDB存储引擎下，MySQL默认的事务隔离级别是`REPEATABLE READ`，这允许事务在...
Mysql 数据库死锁过程分析(select for update)
09-10
最近有项目需求，需要保证多台机器不拿到相同的数据，后来发现... for update经常导致数据库死锁问题，下面小编给大家介绍mysql 数据库死锁过程分析(select for update)，对mysql数据库死锁问题感兴趣的朋友一起学习吧
根据update语句自动生成select语句
08-03
根据update语句自动生成select语句.
oracle 多表做update insert语句.docx
01-12
今天，我们将讨论 Oracle 中的 Update 语句，包括 Update 语句的基本语法、Update 语句中使用 Select 语句、Update 语句中使用 Join 语句、Insert 语句的使用等。 一、Update 语句的基本语法 Update 语句的基本...
数据库中Select For update语句的解析
热门推荐
wblearn的博客
10-25 4万+
——————————— Oracle —————————————————– Oracle 的for update行锁键字: oracle 的for update行锁 　　SELECT…FOR UPDATE 语句的语法如下： 　　SELECT … FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED]; 其中： 　　OF 子
select from update
weixin_34187822的博客
11-16 622
2019独角兽企业重金招聘Python工程师标准>>> ...
kingbase 使用SELECT INTO 的 FOR UPDATE方法
weixin_39339737的博客
12-28 383
Oracle 和 KingbaseES 均支持 SELECT INTO 的 FOR UPDATE 子句，但二者的差异是：KingbaseES 的 FOR UPDATE。也就是说，其它企图对这些记录进行 UPDATE 、DELETE 或 SELECT FOR UPDATE 操作。FOR UPDATE* 子句的作用是锁住特定 *SELECT 检索结果记录，用来避免这些记录在当前事务结束前被其它。的事务将被阻塞，直到当前事务结束。insert into student values (2,'哈哈');
Mybatis Select...for update用法
ToBeABetterOne的专栏
10-13 3万+
Mybatis Select…for update用法最近有需求批量处理大量数据，由于数据量很大，如果加分布式锁让一个线程跑需要太长时间，所以考虑集群中二十几台机器并行执行，每次取1000条数据处理。选择了使用select…for update悲观锁，每次把取出来的1000条数据加锁之后更改状态字段再commit，从而保证所有线程不重复取数据。很容易想到的用法就是把select for upate和
mysql for update悲观锁能否锁住一条不存在的记录
u013269532的专栏
07-22 6175
行锁的3种算法 锁是针对索引加锁，如果查询条件无索引，全表扫描、全表锁。不讨论！ 1.Record LOCK 单行记录上的锁 2.Gap LOCK 间隙锁，锁定一个范围，但不包含记录本身 3.Next-KEY Lock：Gap LOCK+Record Lock，锁定一个范围，并且锁定记录本身 Innodb对于行的查询都是采用Next-KEY Lock，但是当查询索引时含有唯一...
MySQL select ** for update 之后就无法读？
u014240299的专栏
09-25 2284
你知道的越多，不知道的也越多！ 想必大家早有所闻，在select语句后加上 for update 是mysql里的读锁（s锁），加上lock share in mode 是写锁（x锁）。 读锁：共享锁就是多个事务对于同一数据可以共享一把锁（其它事务可以抢占读锁），都能访问到数据，但是只能读不能修改； 写锁：排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排 他锁，其他事务就不能对该行记录做其他操作，也不能获取该行的锁。 那面对文章标题的灵魂拷问？到底可以读么？ 答案： 可以读，可以读，可以读。.
记录不存在如何加锁MySQL_innodb select for update 没有满足条件的记录的情况下 是怎么加锁的呢
wade1010的专栏
11-30 790
在REPEATABLE-READ隔离级别下，如果两个线程同时对同一张表用SELECT…FOR UPDATE加锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。
MySql - 快速执行：在update时使用select赋值
简简单单Onlinezuozuo
01-08 1万+
MySql - 快速执行：在update时使用select赋值 # 1.MySQL不允许SELECT FROM后面指向用作UPDATE的表。 2.MySQL不允许UPDATE在SET时直接SELECT 3.Currently, you cannot update a table and select from the same table in a subquery.[
【MySQL】当前读、快照读、MVCC
weixin_30492047的博客
04-18 282
当前读: 　　select...lock in share mode (共享读锁) 　　select...for update 　　update , delete , insert 　　当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。 　　例如，假设要update一条记录，但是另一个事务已经delete这条数据并且...
一次 select for update 的悲观锁使用引发的生产事故
Defonds 的专栏
11-11 2万+
1.事故描述本月 8 日上午十点多，我们的基础应用发生生产事故。具体表象为系统出现假死无响应。查看事发时间段的基础应用 error 日志，没发现明显异常。查看基础应用业务日志，银行结果处理的部分普遍很慢，大都在十分钟以上。2.AWR 报告向 DBA 要了一下那个时间段的 AWR 报告，发现以下三个地方有些异常：2.1.CPU 利用率过高如上图所示，CPU利用率：1883.25分钟DB时间/(4核心
update .. select
最新发布
01-04
而select...for update语句可以在事务中获取行级锁，确保数据的一致性。 至于再次加锁时返回的lockedConfig内容是什么，需要根据具体的代码和业务逻辑来确定。lockedConfig可能是一个对象或数据结构，用于存储被...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值