由select for update锁等待问题引发的深入思考

这篇具有很好参考价值的文章主要介绍了由select for update锁等待问题引发的深入思考。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

关于MySQL的加锁机制,其实十分复杂,不同的隔离级别,是否是主键或索引,锁的粒度等等。很多工作了很多年的MySQL DBA也不能把各种加锁场景一一讲清楚。有时候一个简单的锁等待场景都值得深入研究,大家更多的是知其然而不知其所以然。本文介绍的是一个很常见的锁等待问题,但很少有人知道其中的原理。

一、实验场景

本文实验和研究的MySQL版本为8.0.31,数据库的隔离级别设置为RC,创建一张表,并在表中插入数据:

create table siri(
id int not null auto_increment,
a int not null,
b int not null,
c int not null,
primary key (id),
unique key uniq_a (a),
key idx_c (c)
)

insert into siri values (1,1,1,1),(2,2,2,2),(4,4,4,4),(6,6,6,4);

好的,现在可以开始模拟实验场景了:

实验一:

Session1

Session2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from siri where b=1 for update;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  1 | 1 | 1 | 1 |

+----+---+---+---+

1 row in set (0.00 sec)

 
 

mysql> select * from siri where b=4 for update;

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

实验二:

Session1

Session2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from siri where id=1 for update;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  1 | 1 | 1 | 1 |

+----+---+---+---+

1 row in set (0.00 sec)

 
 

mysql> select * from siri where b=4 for update;

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

实验三:

Session1

Session2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from siri where b=1 for update;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  1 | 1 | 1 | 1 |

+----+---+---+---+

1 row in set (0.00 sec)

 
 

mysql> select * from siri where id=4 for update;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  4 | 4 | 4 | 4 |

+----+---+---+---+

1 row in set (0.00 sec)

从以上三个实验可以看出,session2是否被堵塞与session1中语句的条件字段是否是索引无关,而与session2select for update语句的条件字段有关,session2中条件字段无索引则会被堵塞。
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139907486244056:1220:139907418869440
ENGINE_TRANSACTION_ID: 3816000
            THREAD_ID: 52900
             EVENT_ID: 44
        OBJECT_SCHEMA: test
          OBJECT_NAME: siri
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139907418869440
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139907486244056:59:4:2:139907418866384
ENGINE_TRANSACTION_ID: 3816000
            THREAD_ID: 52900
             EVENT_ID: 44
        OBJECT_SCHEMA: test
          OBJECT_NAME: siri
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139907418866384
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
2 rows in set (0.00 sec)
mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2023-11-16 14:23:49
                    wait_age: 00:00:02
               wait_age_secs: 2
                locked_table: `test`.`siri`
         locked_table_schema: test
           locked_table_name: siri
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 3816028
         waiting_trx_started: 2023-11-16 14:23:49
             waiting_trx_age: 00:00:02
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 54820
               waiting_query: select * from siri where b=4 for update
             waiting_lock_id: 139907486245672:59:4:2:139907418878432
           waiting_lock_mode: X,REC_NOT_GAP
             blocking_trx_id: 3816020
                blocking_pid: 54783
              blocking_query: NULL
            blocking_lock_id: 139907486244056:59:4:2:139907418866384
          blocking_lock_mode: X,REC_NOT_GAP
        blocking_trx_started: 2023-11-16 14:16:49
            blocking_trx_age: 00:07:02
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 54783
sql_kill_blocking_connection: KILL 54783
1 row in set (0.01 sec)

查询上面监控视图可以发现,在实验一和实验二中,session1所申请的锁资源也是一样的,一个是表级别的IX锁,一个是行级别的X锁。而造成锁等待的锁是行锁。所以这时候就有一个疑问了,行锁锁定的是b=1这一行,为啥session2中我们要申请b=4这一行的行锁会发生锁等待呢?其实原因也显而易见了:字段b无索引,申请b=4这一行的行锁会扫描全表,也就是说对表数据的每一行都会申请X锁。而在实验三中,可以走主键索引直接定位到b=4这一行,所以就不会造成锁等待了。

下面再看一个实验四:

Session1

Session2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from siri where b=1 for update;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  1 | 1 | 1 | 1 |

+----+---+---+---+

1 row in set (0.00 sec)

 
 

mysql> update siri set c=4 where b=4;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

可以发现,session2中直接对b=4这一行进行update是可以直接成功的,不会被阻塞。这说明update的加锁流程和select for update是不一样的。可以推测一下这两种加锁流程有什么区别:session2update进行更新时也会扫描全表,但是遇到第一个锁等待时会做一个判断,发现锁住的行不是需要update的行时,则会跳过这个锁,这样就不会影响真正需要update的行,而select for update则不会做这个跳过,会一直等待锁。

二、解读源码

为了验证我的猜想,深究背后的原理,还是得在实际场景下调试一下源码,阅读源码才能更好的了解为什么是这样的。

mysql源码中,负责给行加锁的函数是sel_set_rec_lock,我们可以在该函数处打下断点看看select for updateupdate这两种sql在申请锁的流程上面有什么区别。

/** Sets a lock on a record.
mostly due to we cannot reposition a record in R-Tree (with the
nature of splitting)
@param[in]      pcur            cursor
@param[in]      rec             record
@param[in]      index           index
@param[in]      offsets         rec_get_offsets(rec, index)
@param[in]      sel_mode        select mode: SELECT_ORDINARY,
                                SELECT_SKIP_LOKCED, or SELECT_NO_WAIT
@param[in]      mode            lock mode
@param[in]      type            LOCK_ORDINARY, LOCK_GAP, or LOC_REC_NOT_GAP
@param[in]      thr             query thread
@param[in]      mtr             mtr
@return DB_SUCCESS, DB_SUCCESS_LOCKED_REC, or error code */
static inline dberr_t sel_set_rec_lock(btr_pcur_t *pcur, const rec_t *rec,
                                       dict_index_t *index,
                                       const ulint *offsets,
                                       select_mode sel_mode, ulint mode,
                                       ulint type, que_thr_t *thr, mtr_t *mtr) {
  trx_t *trx;
  dberr_t err = DB_SUCCESS;
  const buf_block_t *block;

  block = pcur->get_block();

  trx = thr_get_trx(thr);
  ut_ad(trx_can_be_handled_by_current_thread(trx));

  if (UT_LIST_GET_LEN(trx->lock.trx_locks) > 10000) {
    if (buf_LRU_buf_pool_running_out()) {
      return (DB_LOCK_TABLE_FULL);
    }
  }

  if (index->is_clustered()) {
    err = lock_clust_rec_read_check_and_lock(
        lock_duration_t::REGULAR, block, rec, index, offsets, sel_mode,
        static_cast<lock_mode>(mode), type, thr);
  } else {
    if (dict_index_is_spatial(index)) {
      if (type == LOCK_GAP || type == LOCK_ORDINARY) {
        ib::error(ER_IB_MSG_1026) << "Incorrectly request GAP lock "
                                     "on RTree";
        ut_d(ut_error);
        ut_o(return (DB_SUCCESS));
      }
      err = sel_set_rtr_rec_lock(pcur, rec, index, offsets, sel_mode, mode,
                                 type, thr, mtr);
    } else {
      err = lock_sec_rec_read_check_and_lock(
          lock_duration_t::REGULAR, block, rec, index, offsets, sel_mode,
          static_cast<lock_mode>(mode), type, thr);
    }
  }

  return (err);
}

mysqldebug模式中执行select * from testdb.siri where b=4 for updategdb中命中sel_set_rec_lock函数断点,函数堆栈信息如下:

#0  sel_set_rec_lock (pcur=0x7f52040e3ef8, rec=0x7f521e05c07d "\200", index=0x7f52040e8028, offsets=0x7f52146f3bc0, sel_mode=SELECT_ORDINARY, mode=3, type=1024, 
    thr=0x7f52040e4700, mtr=0x7f52146f3ef0) at /root/gdb_mysql/mysql-8.0.32/storage/innobase/row/row0sel.cc:1142

执行update testdb.siri set c=2 where b=4,函数堆栈信息如下:

#0  sel_set_rec_lock (pcur=0x7f52040e3ef8, rec=0x7f521e05c07d "\200", index=0x7f52040e8028, offsets=0x7f52146f3630, sel_mode=SELECT_SKIP_LOCKED, mode=3, type=1024, 
    thr=0x7f52040e4700, mtr=0x7f52146f3960) at /root/gdb_mysql/mysql-8.0.32/storage/innobase/row/row0sel.cc:1142

发现了两者的区别吗?区别在于sel_mode这个参数是不同的:对于select for updatesel_modeSELECT_ORDINARY;对于updatesel_modeSELECT_SKIP_LOCKEDsel_mode参数的定义如下:

enum select_mode {
  SELECT_ORDINARY,    /* default behaviour */
  SELECT_SKIP_LOCKED, /* skip the row if row is locked */
  SELECT_NOWAIT       /* return immediately if row is locked */
};

row_search_mvcc函数中,通过以下代码来判定这条sql是否为半一致性读(semi-consistent read)。

/* in case of semi-consistent read, we use SELECT_SKIP_LOCKED, so we don't
waste time on creating a WAITING lock, as we won't wait on it anyway */
const bool use_semi_consistent =
    prebuilt->row_read_type == ROW_READ_TRY_SEMI_CONSISTENT &&
    !unique_search && index == clust_index && !trx_is_high_priority(trx);
err = sel_set_rec_lock(
    pcur, rec, index, offsets,
    use_semi_consistent ? SELECT_SKIP_LOCKED : prebuilt->select_mode,
    prebuilt->select_lock_type, lock_type, thr, &mtr);

update语句是半一致性读,因此use_semi_consistenttrueselect_modeSELECT_SKIP_LOCKED,这表示会话不会浪费时间在创建锁等待上,可以跳过持有锁的行。而对于select for update语句,use_semi_consistentfalseselect_modeSELECT_ORDINARY,表示会话会创建一个锁等待,直到等待超时。

因此,对于实验四中的现象update不会被堵塞的原因已经比较清楚了,updatemysql内部被定义成了半一致性读(SELECT_SKIP_LOCKED),因此实验四的session2update进行全表扫描读取主键时,读取到b=1这一列时,会跳过session1所持有的位于b=1行上的行锁,所以也就不会发生锁等待的现象。相反,实验二中select for updatemysql内部定义为普通读(SELECT_ORDINARY),读取到b=1这一列时,会被session1所持有的位于b=1行上的行锁堵塞,发生锁等待的现象。文章来源地址https://www.toymoban.com/news/detail-837643.html

到了这里,关于由select for update锁等待问题引发的深入思考的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 一个线上问题引发的思考——Elasticsearch 8.X 如何实现更精准的检索?

    ——问题来自:死磕Elasticsearch 知识星球微信群 这个问题涉及到业务细节,至今没有定论。不过,该问题引发了我的思考。 我们使用 Elasticsearch 到底用来做什么? 除了 Elasticsearch 早已不是10年前因“菜谱”而火出技术圈的搜索引擎组件,它早已不是“单兵作战”,而是 ELKB

    2023年04月08日
    浏览(35)
  • springboot~关于md5签名引发的问题

    事实是这样的,我有个接口,这个接口不能被篡改,于是想到了比较简单的md5对url地址参数进行加密,把这个密码当成是sign,然后服务端收到请求后,使用相同算法也生成sign,两个sign相同就正常没有被篡改过。 接口中的参数包括userId,extUserId,时间,其中extUserId字符编码,中

    2023年04月23日
    浏览(34)
  • Java 并发之《深入理解 JVM》关于 volatile 累加示例的思考

    在周志明老师的 《深入理解 JVM》一书中关于 volatile 线程安全性有一个示例代码(代码有些许改动,语义一样): 老师的目的是为了说明在多线程环境下 volatile 只能保证可见性而不是线程安全的。但是当在我的 IDEA 下运行时,发现程序是没有输出结果且始终是

    2024年01月20日
    浏览(53)
  • 一次网络不通 “争吵” 引发的思考

    \\\"你到底在说什么啊,我 K8s 的 ecs 节点要访问 clb 的地址不通和本地网卡有什么关系...\\\" 气愤语气都从电话那头传了过来,这时电话两端都沉默了。过了好一会传来地铁小姐姐甜美的播报声打断了刚刚的沉寂「乘坐地铁必须全程佩戴口罩,下一站西湖文化广场...」。 pod 需要访

    2024年02月12日
    浏览(39)
  • 一个概率论例题引发的思考

    浙江大学版《概率论与数理统计》一书,第13章第1节例2: 这个解释和模型比较简单易懂。 接下来,第13章第2节的例2也跟此模型相关: 在我自己的理解中,此题的解法跟上一个题目一样,其概率如下面的二维矩阵,第二级传输也就是n为2,矩阵一共有4中可能的概率,求其期

    2024年02月12日
    浏览(41)
  • 关于前后端JSON解析差异问题与思考

      一、问题回顾 在一次涉及流程表单的需求发布时,由于表单设计的改动,需要在历史工单中的一个json字段增加一个属性,效果示意如下: 由于历史数据较多,采用了通过odc从数据库查询数据,线下开发数据处理脚本,更新数据后生成sql去线上执行,脚本示例如下。 在数据

    2024年02月09日
    浏览(50)
  • 【区块链】Ankr被黑引发的思考

    三明治交易、夹子机器人、抢跑、抢新、抢购、秒杀,相信这些词你都听说过了,区块链上的各种套利操作,基本上都有一个大前提,就是监听链上最新的未打包交易,才能在第一时间抢占先机。 前段时间Ankr被黑,黑客从中获利约500万美元,然而,让人惊讶的是,另一个套

    2024年02月02日
    浏览(48)
  • 由C# yield return引发的思考

        当我们编写 C# 代码时,经常需要处理大量的数据集合。在传统的方式中,我们往往需要先将整个数据集合加载到内存中,然后再进行操作。但是如果数据集合非常大,这种方式就会导致内存占用过高,甚至可能导致程序崩溃。     C# 中的 yield return 机制可以帮助我们

    2024年02月07日
    浏览(47)
  • JavaEE 初阶篇-深入了解多线程安全问题(指令重排序、解决内存可见性与等待通知机制)

    🔥博客主页: 【 小扳_-CSDN博客】 ❤感谢大家点赞👍收藏⭐评论✍ 文章目录         1.0 指令重排序概述         1.1 指令重排序主要分为两种类型         1.2 指令重排序所引发的问题         2.0 内存可见性概述         2.1 导致内存可见性问题主要涉及两个方面      

    2024年04月15日
    浏览(48)
  • ChatGPT引发的人机交互发展历程与升级思考

    ChatGPT自从去年12月火爆以来一直热度不减,最近正好研读了科技之巅,书中详细阐述了人机交互、人工智能、算力算法等技术的发展历史,本文主要围绕ChatGPT引发的人机交互方面的思考。 在讨论人机交互之前,首先需要说明的一点,目前计算机发展的结果已经从原来作为科

    2023年04月24日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包