MYSQL抓取表的最大ID并锁定时的阻塞分析

示例 SQL：

SELECTq.queueidFROMrender.queues qWHEREq.queueid in (SELECTmax(queueid)FROM(SELECTt.queueidFROMqueues tWHERE1 = 1AND STATUS = 0AND queuetype <> 1...ORDER BYqueuetype ASC,创建 ASC) 一个限制 1) FOR UPDATE ;

需求：

将 SQL 从 ORACLE 转换为 MYSQL，目的是在多个会话中轮询表以获得最大满足一行数据的条件ID的表的个数，那么第一个抓取到的会话需要锁定这行数据，以防止其他会话读取这行数据。在此期间，新的数据会不断插入到表中。

问题流程：

一个session 执行 select for update;

queues 11:03:13>set autocommit=0 -> ;查询正常，0 行受影响（0.00 秒）-> SELECT -> q.queueid -> FROM -> queues.queues q -> WHERE -> q.queueid IN ( -> SELECT -> max(queueid) -> FROM -> ( -> SELECT -> t.queueid -> FROM -> 队列 t -> WHERE -> 1 = 1 -> 和状态 = 0 -> 和队列类型 <> 1... -> 排序 -> 队列类型 ASC, -> 创建 ASC ->) a ->) FOR UPDATE;+-------- ---+|队列ID |+------------+| 278082656 |+------------+1 row in set (24.46 sec)

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

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

分析：

所以，检查锁：

oot@(none) 01:52:24>select * frominformation_schema.INNODB_LOCKS\ G;******************************** 1.行************ ****** ********** lock_id: 133781546:45140:18:178lock_trx_id: 133781546 lock_mode: 8 lock_rec: 178 lock_data: 0, 0, '1000505419', 0x99A438AAF5, 1280, 960, 2780826 56 ************************** 2. 行 ****** ************** ******** lock_id：133777540：45140：18：178lock_trx_id：133777540 lock_mode：00505419'，0x99A438AAF5，1280，960，2780826562行集中，1个警告（0.00秒）错误：没有查询指定root@（无） 01:52:24>从 information_schema.INNODB_LOCK_waits\G 选择 *；******************** ********* 1. 行 ***** **********************requesting_trx_id：133781546requested_lock_id：133781546：45140：18：178blocking_trx_id：133777540blocking_lock_id：133777540：45140：18：1781行集中，1 warning (0.00 sec)

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

+----+-------------+--------+------------+--- ---+------ ------------------+------------------------+-------- -+----- -+------+----------+------------------------ ---+|编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 | +----+-------------+- ------+----------------+----- --+-------------------- -+------------------------ +---------+------+------+ ----------+---------------- ------------+| 1 |小学 |问 |空|索引|空| INDEX_QUEUE_QUEUETYPE | 285 | 285空| 2067 | 2067 100.00 |使用地点；使用索引 || 2 |子查询| t |空|全部 | INDEX_QUEUE_QUEUETYPE |空|空|空| 2067 | 2067 0.05 | 0.05使用 where |+---- +------------+--------+------------+-------- +------ ------------------+------------------------+ -------- -+------+------+----------+---------------- -----------+

显然，主查询使用了子查询的索引，并且该索引是非唯一索引，而MYSQL锁是基于索引锁定的。同时从上面的加锁情况可以看出，多块包括278082656在内的数据被锁定。

如何解决：

那么我们再看一下SQL。事实上，主查询应该使用唯一主键索引，这样就不会存在上述问题。 ，只需要把主查询()中的WHERE q.queueid改为WHERE q.queueid =()即可。这是一个开发规范问题。

SELECTq.queueidFROMqueues.queues qWHEREq.queueid =(SELECTmax(queueid)FROM(SELECTt.queueidFROMqueues tWHERE1 = 1AND STATUS = 0AND queuetype <> 1... ORDER BYqueuetype ASC,createdate ASC) 限制 1) FOR UPDATE

修改后执行计划：

+----+------------+--------+ - -----------+--------+------------------------+----- - ---+---------+--------+------+---------+--------- - ---+|编号 |选择类型 |表|隔断|类型 |可能的键 |关键| key_len |参考|行 |过滤|额外 |+----+-------------+--------+------------+- ------ +---------------------+---------+--------- +----- --+-----+----------+-------------+| 1 |小学 |问 |空|常量 |小学 |小学 | 8 |常量 | 1 | 100.00 |使用索引 || 2 |子查询| t |空|全部 | INDEX_QUEUE_Q UEUETYPE | INDEX_QUEUE_Q UEUETYPE |空|空|空| 2067 | 2067 0.05 | 0.05使用 where |+----+------------+--------+------------+- ----- -+---------------------+---------+--------- +---- ---+--------+----------+-------------+

" 《MYSQL抓取表最大ID时的阻塞分析》的内容就介绍到这里了，感谢您的阅读，如果想了解更多业内资讯，可以关注网站，小编会输出更多高分——为大家提供优质实用的文章！