MYSQL对表的最大ID抢加锁时的阻塞分析

2025-03-19 技术教程

本篇内容介绍了“MYSQL对表的最大ID抢加锁时的阻塞分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

示例SQL:

SELECTq.queueidFROMrender.queuesqWHEREq.queueidin(SELECTmax(queueid)FROM(SELECTt.queueidFROMqueuestWHERE1=1ANDSTATUS=0ANDqueuetype<>1。。。ORDERBYqueuetypeASC,createdateASC)alimit1)FORUPDATE;

需求：

从ORACLE转化到MYSQL的SQL，目的是多个会话轮询取表中满足条件最大ID的一行数据，然后第一个抢到的会话需要对这一行数据加锁，以免其他会话读到该行数据，在这期间，表是会不断有新进数据插入的。

问题过程：

A会话执行 select for update;

queues11:03:13>setautocommit=0->;QueryOK,0rowsaffected(0.00sec)->SELECT->q.queueid->FROM->queues.queuesq->WHERE->q.queueidIN(->SELECT->max(queueid)->FROM->(->SELECT->t.queueid->FROM->queuest->WHERE->1=1->ANDSTATUS=0->ANDqueuetype<>1。。。->ORDERBY->queuetypeASC,->createdateASC->)a->)FORUPDATE;+-----------+|queueid|+-----------+|278082656|+-----------+1rowinset(24.46sec)

此时 B 会话继续往表中插入数据，让ID不断增长，例如当前满足条件的最大 QUEUEID 是278082665

接着 C 会话重新执行以上 SELECT for update语句，理论上，当再次查询时需要加锁的ID 应该是 278082665，但发现此时Ｃ会话在等待锁。

分析：

于是，查了下锁看看：

oot@(none)01:52:24>select*frominformation_schema.INNODB_LOCKS\G;***************************1.row***************************lock_id:133781546:45140:18:178lock_trx_id:133781546lock_mode:Xlock_type:RECORDlock_table:`queues`.`queues`lock_index:INDEX_QUEUE_QUEUETYPElock_space:45140lock_page:18lock_rec:178lock_data:0,0,'1000505419',0x99A438AAF5,1280,960,278082656***************************2.row***************************lock_id:133777540:45140:18:178lock_trx_id:133777540lock_mode:Xlock_type:RECORDlock_table:`queues`.`queues`lock_index:INDEX_QUEUE_QUEUETYPElock_space:45140lock_page:18lock_rec:178lock_data:0,0,'1000505419',0x99A438AAF5,1280,960,2780826562rowsinset,1warning(0.00sec)ERROR:Noqueryspecifiedroot@(none)01:52:24>select*frominformation_schema.INNODB_LOCK_waits\G;***************************1.row***************************requesting_trx_id:133781546requested_lock_id:133781546:45140:18:178blocking_trx_id:133777540blocking_lock_id:133777540:45140:18:1781rowinset,1warning(0.00sec)

从上面结果发现lock_index是 INDEX_QUEUE_QUEUETYPE，而从SQL，我们继续查看执行计划。

+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+|1|PRIMARY|q|NULL|index|NULL|INDEX_QUEUE_QUEUETYPE|285|NULL|2067|100.00|Usingwhere;Usingindex||2|SUBQUERY|t|NULL|ALL|INDEX_QUEUE_QUEUETYPE|NULL|NULL|NULL|2067|0.05|Usingwhere|+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+

很明显，主查询使用了子查询的索引，而该索引是非唯一性索引，MYSQL 的加锁是基于索引加锁的，同时从以上锁情况可以看出此存在对包含278082656在内的多条数据进行加锁。

怎么解决：

于是我们再重新看SQL，其实这里应该是要让主查询走具有唯一性的主键索引即可，这样便不会存在以上问题了，只需将主查询的WHERE q.queueid in ()改成 WHERE q.queueid =() ,这是开发规范问题。

SELECTq.queueidFROMqueues.queuesqWHEREq.queueid=(SELECTmax(queueid)FROM(SELECTt.queueidFROMqueuestWHERE1=1ANDSTATUS=0ANDqueuetype<>1。。。ORDERBYqueuetypeASC,createdateASC)alimit1)FORUPDATE

修改之后的执行计划：

+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+|1|PRIMARY|q|NULL|const|PRIMARY|PRIMARY|8|const|1|100.00|Usingindex||2|SUBQUERY|t|NULL|ALL|INDEX_QUEUE_QUEUETYPE|NULL|NULL|NULL|2067|0.05|Usingwhere|+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+

“MYSQL对表的最大ID抢加锁时的阻塞分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！