MYSQL對表的最大ID搶加鎖時(shí)的阻塞分析

本篇內容介紹了“對表的最大ID搶加鎖時(shí)的阻塞分析”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

示例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，目的是多個(gè)會(huì )話(huà)輪詢(xún)取表中滿(mǎn)足條件最大ID的一行數據，然后第一個(gè)搶到的會(huì )話(huà)需要對這一行數據加鎖，以免其他會(huì )話(huà)讀到該行數據，在這期間，表是會(huì )不斷有新進(jìn)數據插入的。

問(wèn)題過(guò)程：

A會(huì )話(huà)執行 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)

此時(shí) B 會(huì )話(huà)繼續往表中插入數據，讓ID不斷增長(cháng)，例如當前滿(mǎn)足條件的最大 QUEUEID 是278082665

接著(zhù) C 會(huì )話(huà)重新執行以上 SELECT  for update語(yǔ)句，理論上，當再次查詢(xún)時(shí)需要加鎖的ID 應該是 278082665，但發(fā)現此時(shí)Ｃ會(huì )話(huà)在等待鎖。

分析：

于是，查了下鎖看看：

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)

從上面結果發(fā)現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              |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+

很明顯，主查詢(xún)使用了子查詢(xún)的索引，而該索引是非唯一性索引，MYSQL 的加鎖是基于索引加鎖的，同時(shí)從以上鎖情況可以看出此存在對包含278082656在內的多條數據進(jìn)行加鎖。

怎么解決：

于是我們再重新看SQL，其實(shí)這里應該是要讓主查詢(xún)走具有唯一性的主鍵索引即可，這樣便不會(huì )存在以上問(wèn)題了，只需將主查詢(xún)的WHERE q.queueid in ()改成 WHERE q.queueid =() ,這是開(kāi)發(fā)規范問(wèn)題。

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 |
+----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+