发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

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

一一麻麻

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
显示全部楼层
关于MySQL的加锁机制，其实十分复杂，不同的隔离级别，是否是主键或索引，锁的粒度等等。很多工作了很多年的MySQL DBA也不能把各种加锁场景一一讲清楚。有时候一个简单的锁等待场景都值得深入研究，大家更多的是知其然而不知其所以然。本文介绍的是一个很常见的锁等待问题，但很少有人知道其中的原理。
一、实验场景
本文实验和研究的MySQL版本为8.0.31，数据库的隔离级别设置为RC，创建一张表，并在表中插入数据：
  1. create table siri(
  2. id int not null auto_increment,
  3. a int not null,
  4. b int not null,
  5. c int not null,
  6. primary key (id),
  7. unique key uniq_a (a),
  8. key idx_c (c)
  9. )
  11. 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中语句的条件字段是否是索引无关，而与session2中select for update语句的条件字段有关，session2中条件字段无索引则会被堵塞。
  1. mysql> select * from performance_schema.data_locks\G
  2. *************************** 1. row ***************************
  3.                ENGINE: INNODB
  4.        ENGINE_LOCK_ID: 139907486244056:1220:139907418869440
  5. ENGINE_TRANSACTION_ID: 3816000
  6.             THREAD_ID: 52900
  7.              EVENT_ID: 44
  8.         OBJECT_SCHEMA: test
  9.           OBJECT_NAME: siri
  10.        PARTITION_NAME: NULL
  11.     SUBPARTITION_NAME: NULL
  12.            INDEX_NAME: NULL
  13. OBJECT_INSTANCE_BEGIN: 139907418869440
  14.             LOCK_TYPE: TABLE
  15.             LOCK_MODE: IX
  16.           LOCK_STATUS: GRANTED
  17.             LOCK_DATA: NULL
  18. *************************** 2. row ***************************
  19.                ENGINE: INNODB
  20.        ENGINE_LOCK_ID: 139907486244056:59:4:2:139907418866384
  21. ENGINE_TRANSACTION_ID: 3816000
  22.             THREAD_ID: 52900
  23.              EVENT_ID: 44
  24.         OBJECT_SCHEMA: test
  25.           OBJECT_NAME: siri
  26.        PARTITION_NAME: NULL
  27.     SUBPARTITION_NAME: NULL
  28.            INDEX_NAME: PRIMARY
  29. OBJECT_INSTANCE_BEGIN: 139907418866384
  30.             LOCK_TYPE: RECORD
  31.             LOCK_MODE: X,REC_NOT_GAP
  32.           LOCK_STATUS: GRANTED
  33.             LOCK_DATA: 1
  34. 2 rows in set (0.00 sec)
复制代码
  1. mysql> select * from sys.innodb_lock_waits\G
  2. *************************** 1. row ***************************
  3.                 wait_started: 2023-11-16 14:23:49
  4.                     wait_age: 00:00:02
  5.                wait_age_secs: 2
  6.                 locked_table: `test`.`siri`
  7.          locked_table_schema: test
  8.            locked_table_name: siri
  9.       locked_table_partition: NULL
  10.    locked_table_subpartition: NULL
  11.                 locked_index: PRIMARY
  12.                  locked_type: RECORD
  13.               waiting_trx_id: 3816028
  14.          waiting_trx_started: 2023-11-16 14:23:49
  15.              waiting_trx_age: 00:00:02
  16.      waiting_trx_rows_locked: 1
  17.    waiting_trx_rows_modified: 0
  18.                  waiting_pid: 54820
  19.                waiting_query: select * from siri where b=4 for update
  20.              waiting_lock_id: 139907486245672:59:4:2:139907418878432
  21.            waiting_lock_mode: X,REC_NOT_GAP
  22.              blocking_trx_id: 3816020
  23.                 blocking_pid: 54783
  24.               blocking_query: NULL
  25.             blocking_lock_id: 139907486244056:59:4:2:139907418866384
  26.           blocking_lock_mode: X,REC_NOT_GAP
  27.         blocking_trx_started: 2023-11-16 14:16:49
  28.             blocking_trx_age: 00:07:02
  29.     blocking_trx_rows_locked: 1
  30.   blocking_trx_rows_modified: 0
  31.      sql_kill_blocking_query: KILL QUERY 54783
  32. sql_kill_blocking_connection: KILL 54783
  33. 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是不一样的。可以推测一下这两种加锁流程有什么区别：session2中update进行更新时也会扫描全表，但是遇到第一个锁等待时会做一个判断，发现锁住的行不是需要update的行时，则会跳过这个锁，这样就不会影响真正需要update的行，而select for update则不会做这个跳过，会一直等待锁。二、解读源码
为了验证我的猜想，深究背后的原理，还是得在实际场景下调试一下源码，阅读源码才能更好的了解为什么是这样的。
在mysql源码中，负责给行加锁的函数是sel_set_rec_lock，我们可以在该函数处打下断点，看看select for update和update这两种sql在申请锁的流程上面有什么区别。
  1. /** Sets a lock on a record.
  2. mostly due to we cannot reposition a record in R-Tree (with the
  3. nature of splitting)
  4. @param[in]      pcur            cursor
  5. @param[in]      rec             record
  6. @param[in]      index           index
  7. @param[in]      offsets         rec_get_offsets(rec, index)
  8. @param[in]      sel_mode        select mode: SELECT_ORDINARY,
  9.                                 SELECT_SKIP_LOKCED, or SELECT_NO_WAIT
  10. @param[in]      mode            lock mode
  11. @param[in]      type            LOCK_ORDINARY, LOCK_GAP, or LOC_REC_NOT_GAP
  12. @param[in]      thr             query thread
  13. @param[in]      mtr             mtr
  14. @return DB_SUCCESS, DB_SUCCESS_LOCKED_REC, or error code */
  15. static inline dberr_t sel_set_rec_lock(btr_pcur_t *pcur, const rec_t *rec,
  16.                                        dict_index_t *index,
  17.                                        const ulint *offsets,
  18.                                        select_mode sel_mode, ulint mode,
  19.                                        ulint type, que_thr_t *thr, mtr_t *mtr) {
  20.   trx_t *trx;
  21.   dberr_t err = DB_SUCCESS;
  22.   const buf_block_t *block;
  24.   block = pcur->get_block();
  26.   trx = thr_get_trx(thr);
  27.   ut_ad(trx_can_be_handled_by_current_thread(trx));
  29.   if (UT_LIST_GET_LEN(trx->lock.trx_locks) > 10000) {
  30.     if (buf_LRU_buf_pool_running_out()) {
  31.       return (DB_LOCK_TABLE_FULL);
  32.     }
  33.   }
  35.   if (index->is_clustered()) {
  36.     err = lock_clust_rec_read_check_and_lock(
  37.         lock_duration_t::REGULAR, block, rec, index, offsets, sel_mode,
  38.         static_cast<lock_mode>(mode), type, thr);
  39.   } else {
  40.     if (dict_index_is_spatial(index)) {
  41.       if (type == LOCK_GAP || type == LOCK_ORDINARY) {
  42.         ib::error(ER_IB_MSG_1026) << "Incorrectly request GAP lock "
  43.                                      "on RTree";
  44.         ut_d(ut_error);
  45.         ut_o(return (DB_SUCCESS));
  46.       }
  47.       err = sel_set_rtr_rec_lock(pcur, rec, index, offsets, sel_mode, mode,
  48.                                  type, thr, mtr);
  49.     } else {
  50.       err = lock_sec_rec_read_check_and_lock(
  51.           lock_duration_t::REGULAR, block, rec, index, offsets, sel_mode,
  52.           static_cast<lock_mode>(mode), type, thr);
  53.     }
  54.   }
  56.   return (err);
  57. }
复制代码
update语句是半一致性读，因此use_semi_consistent为true，select_mode为SELECT_SKIP_LOCKED，这表示会话不会浪费时间在创建锁等待上，可以跳过持有锁的行。而对于select for update语句，use_semi_consistent为false，select_mode为SELECT_ORDINARY，表示会话会创建一个锁等待，直到锁等待超时。
因此，对于实验四中的现象update不会被堵塞的原因已经比较清楚了，update在mysql内部被定义成了半一致性读（SELECT_SKIP_LOCKED），因此实验四的session2中update进行全表扫描读取主键时，读取到b=1这一列时，会跳过session1所持有的位于b=1行上的行锁，所以也就不会发生锁等待的现象。相反，实验二中select for update在mysql内部定义为普通读（SELECT_ORDINARY），读取到b=1这一列时，会被session1所持有的位于b=1行上的行锁堵塞，发生锁等待的现象。

来源:https://www.cnblogs.com/coygfly/p/18026342
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: mysql access denied for root ... mysqld –skip-grant-tables 命令失效 .

下一篇: MySQL如何查看添加修改表以及字段注释信息

发表于 2024-2-28 07:25:45

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4