关于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
中语句的条件字段是否是索引无关，而与
session2
中
select 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
是不一样的，这其实就是MySQL中乐观锁与悲观锁的区别：
session2
中是乐观锁的场景，
update
进行更新时也会扫描全表，但是不会在读取的时候就加锁，它是在进行更新的时候进行判断，如果有行冲突则发生锁等待，如果没有则更新成功。
而
select for update是悲观锁，在数据读取的时候就加锁，所以即使实际需要更新的行没有锁，但因为全表扫描，需要对每一行都加锁，也就发生了实验二中的锁等待现象
。
二、解读源码
通过阅读和调试MySQL源码，可以更好的理解在源码中是怎么实现的。
在mysql源码中，负责给行加锁的函数是sel_set_rec_lock，我们可以在该函数处打下断点，看看select for update和update这两种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); }
在mysql的debug模式中执行select * from testdb.siri where b=4 for update，gdb中命中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 update，sel_mode是SELECT_ORDINARY；对于update，sel_mode是SELECT_SKIP_LOCKED。sel_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_consistent为true，select_mode为SELECT_SKIP_LOCKED，这表示会话不会浪费时间在创建锁等待上，可以跳过持有锁的行。而对于悲观锁，select for update语句，use_semi_consistent为false，select_mode为SELECT_ORDINARY，表示会话会创建一个锁等待，直到锁等待超时。
因此，通过阅读源码，对MySQL内部乐观锁与悲观锁的实现机制也有了一定的认识。对于乐观锁，update在mysql内部被定义成了半一致性读（SELECT_SKIP_LOCKED），因此实验四的session2中update进行全表扫描读取主键时，读取到b=1这一列时，会跳过session1所持有的位于b=1行上的行锁，所以也就不会发生锁等待的现象。对于悲观锁，实验二中select for update在mysql内部定义为普通读（SELECT_ORDINARY），读取到b=1这一列时，会被session1所持有的位于b=1行上的行锁堵塞，发生锁等待的现象。
1.本站内容仅供参考，不作为任何法律依据。用户在使用本站内容时，应自行判断其真实性、准确性和完整性，并承担相应风险。
2.本站部分内容来源于互联网，仅用于交流学习研究知识，若侵犯了您的合法权益，请及时邮件或站内私信与本站联系，我们将尽快予以处理。
3.本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
4.根据《计算机软件保护条例》第十七条规定“为了学习和研究软件内含的设计思想和原理，通过安装、显示、传输或者存储软件等方式使用软件的，可以不经软件著作权人许可，不向其支付报酬。”您需知晓本站所有内容资源均来源于网络，仅供用户交流学习与研究使用，版权归属原版权方所有，版权争议与本站无关，用户本人下载后不能用作商业或非法用途，需在24个小时之内从您的电脑中彻底删除上述内容，否则后果均由用户承担责任；如果您访问和下载此文件，表示您同意只将此文件用于参考、学习而非其他用途，否则一切后果请您自行承担，如果您喜欢该程序，请支持正版软件，购买注册，得到更好的正版服务。
5.本站是非经营性个人站点，所有软件信息均来自网络，所有资源仅供学习参考研究目的，并不贩卖软件，不存在任何商业目的及用途
暂无评论内容