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,但發現此時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 | +----+-------------+-------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------------+
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29863023/viewspace-2658602/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL加鎖處理分析MySql
- MySQL 加鎖處理分析MySql
- oracle鎖阻塞的分析Oracle
- 【Mysql】MySQL加鎖處理分析--何登成MySql
- 驚!史上最全的select加鎖分析(Mysql)MySql
- MySQL死鎖系列-常見加鎖場景分析MySql
- 超全面 MySQL 語句加鎖分析(上篇)MySql
- 超全面 MySQL 語句加鎖分析(中篇)MySql
- 超全面 MySQL 語句加鎖分析(下篇)MySql
- mysql加鎖讀MySql
- For Update 加鎖分析
- MySQL鎖(四)行鎖的加鎖規則和案例MySql
- MySQL的共享鎖阻塞會話案例淺析MySql會話
- MySql 中有 select … for update 來加讀鎖,那麼對應地在 DocumentDB中 如何加讀鎖MySql
- Mysql加鎖與實踐MySql
- Mysql加鎖過程詳解(7)-初步理解MySQL的gap鎖MySql
- InnoDB 事務加鎖分析
- 故障分析 | MySQL鎖等待超時一例分析MySql
- MySQL鎖分析MySql
- MySQL如何加鎖控制併發MySql
- redis對hash欄位加鎖Redis
- 從ReentrantLock加鎖解鎖角度分析AQSReentrantLockAQS
- mysql innodb的行鎖(6) --不安全語句加鎖MySql
- Oracle對錶、索引和簇的分析Oracle索引
- 聊聊MySQL的加鎖規則《死磕MySQL系列 十五》MySql
- MySQL 由於MDL讀鎖select被阻塞MySql
- MYSQL引擎的鎖對比MySql
- Mysql事務以及加鎖機制MySql
- 檢視ORACLE的使用者對錶的鎖的使用Oracle
- MySQL:一個死鎖分析 (未分析出來的死鎖)MySql
- mysql DDL時鎖表的排查MySql
- 勿對不可變物件做同步/加鎖物件
- Mysql鎖機制分析MySql
- 故障分析 | MySQL死鎖案例分析MySql
- 通過11G的V$SESSION來分析鎖阻塞關係Session
- 使用logminer來分析對錶所做的修改
- 谷歌搶在蘋果手錶開售前升級Android Wear谷歌蘋果Android
- RR與RC隔離級別下MySQL不同的加鎖解鎖方式MySql