MYSQL对表的最大ID抢加锁时的阻塞解析
发布时间：2022-01-17 18:57:08 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了MYSQL对表的最大ID抢加锁时的阻塞分析的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 示例SQL: SELECT q.queueid FROM render.queue
|
本篇内容介绍了“MYSQL对表的最大ID抢加锁时的阻塞分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 示例SQL: SELECT q.queueid FROM render.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 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_LOCKSG; *************************** 1. row *************************** lock_id: 133781546:45140:18:178 lock_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:178 lock_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, 278082656 2 rows in set, 1 warning (0.00 sec) ERROR: No query specified root@(none) 01:52:24>select * from information_schema.INNODB_LOCK_waitsG; *************************** 1. row *************************** requesting_trx_id: 133781546 requested_lock_id: 133781546:45140:18:178 blocking_trx_id: 133777540 blocking_lock_id: 133777540:45140:18:178 1 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.queueid FROM queues.queues q WHERE 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抢加锁时的阻塞分析”的内容就介绍到这里了，感谢大家的阅读。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
