示例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,但发现此时C会话在等待锁。
分析:
于是,查了下锁看看:
oot@(none) 01:52:24>select * from information_schema.INNODB_LOCKS\G;
*************************** 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_waits\G;
*************************** 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 |
+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+