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

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

示例SQL:

SELECT        q.queueidFROM        render.queues qWHERE        q.queueid in (                SELECT                        max(queueid)                FROM                        (                                SELECT                                        t.queueid                                FROM                                        queues t                                WHERE                                        1 = 1                                AND STATUS = 0                                AND queuetype <> 1                                。。。                                ORDER BY                                        queuetype ASC,                                        createdate ASC                        ) a  limit 1        )  FOR UPDATE ;

需求：

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

问题过程：

A会话执行 select for update;

queues 11:03:13>set autocommit=0    -> ;Query OK, 0 rows affected (0.00 sec)    -> SELECT    -> q.queueid    -> FROM    -> queues.queues q    -> WHERE    -> q.queueid IN (    -> SELECT    -> max(queueid)    -> FROM    -> (    -> SELECT    -> t.queueid    -> FROM    -> queues t    -> WHERE    -> 1 = 1    -> AND STATUS = 0    -> AND queuetype <> 1    。。。    -> ORDER BY    -> queuetype ASC,    -> createdate ASC    -> ) a     -> ) FOR UPDATE ;+-----------+| queueid   |+-----------+| 278082656 |+-----------+1 row in set (24.46 sec)

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

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

分析：

于是，查了下锁看看：

oot@(none) 01:52:24>select * from information_schema.INNODB_LOCKS\G;*************************** 1. row ***************************    lock_id: 133781546:45140:18:178lock_trx_id: 133781546  lock_mode: X  lock_type: RECORD lock_table: `queues`.`queues` lock_index: INDEX_QUEUE_QUEUETYPE lock_space: 45140  lock_page: 18   lock_rec: 178  lock_data: 0, 0, '1000505419', 0x99A438AAF5, 1280, 960, 278082656*************************** 2. row ***************************    lock_id: 133777540:45140:18:178lock_trx_id: 133777540  lock_mode: X  lock_type: RECORD lock_table: `queues`.`queues` lock_index: INDEX_QUEUE_QUEUETYPE lock_space: 45140  lock_page: 18   lock_rec: 178  lock_data: 0, 0, '1000505419', 0x99A438AAF5, 1280, 960, 2780826562 rows in set, 1 warning (0.00 sec)ERROR: No query specifiedroot@(none) 01:52:24>select * from information_schema.INNODB_LOCK_waits\G;*************************** 1. row ***************************requesting_trx_id: 133781546requested_lock_id: 133781546:45140:18:178  blocking_trx_id: 133777540 blocking_lock_id: 133777540:45140:18:1781 row in set, 1 warning (0.00 sec)

从上面结果发现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 | Using where; Using index ||  2 | SUBQUERY    | t     | NULL       | ALL   | INDEX_QUEUE_QUEUETYPE | NULL                  | NULL    | NULL | 2067 |     0.05 | Using where              |+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+

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

怎么解决：

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

SELECT        q.queueidFROM        queues.queues qWHERE        q.queueid =(                SELECT                        max(queueid)                FROM                        (                                SELECT                                        t.queueid                                FROM                                        queues t                                WHERE                                        1 = 1                                AND STATUS = 0                                AND queuetype <> 1                                。。。                                ORDER BY                                        queuetype ASC,                                        createdate ASC                        ) a  limit 1        )  FOR UPDATE

修改之后的执行计划：

+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+| 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 | Using index ||  2 | SUBQUERY    | t     | NULL       | ALL   | INDEX_QUEUE_QUEUETYPE | NULL    | NULL    | NULL  | 2067 |     0.05 | Using where |+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+

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