Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the blocking analysis of the maximum ID lock of the table by MYSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Example 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

Demand:

The purpose of the conversion from ORACLE to MYSQL's SQL is that multiple sessions poll a row of data in the table that satisfies the maximum ID of the condition, and then the first session needs to lock this row of data to prevent other sessions from reading it. During this period, the table will continue to have new data inserted.

Problem process:

A session executes 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)

At this point, session B continues to insert data into the table, allowing the ID to grow. For example, the maximum QUEUEID that meets the condition is 278082665.

Then the C session re-executes the above SELECT for update statement. In theory, the ID that needs to be locked when querying again should be 278082665, but it is found that the C session is waiting for the lock.

Analysis:

So I checked the lock and took a look:

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: 178lock _ data: 0,0, '1000505419' 0x99A438AAF5, 1280, 960,278082656 lock_page * 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 blocking_lock_id 45140 blocking_lock_id: 133777540 blocking_lock_id 1881 row in set, 1 warning (0.00 sec)

From the above results, we find that lock_index is INDEX_QUEUE_QUEUETYPE, while from SQL, we continue to look at the execution plan.

+- +-+-+ | 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 | 2067 | Using where | + -+-+-+

Obviously, the main query uses the index of the subquery, which is a non-unique index, and the locking of MYSQL is based on the index. At the same time, it can be seen from the above lock that multiple pieces of data, including 278082656, are locked.

How to solve the problem:

So we look at SQL again, in fact, it should be to let the main query take the unique primary key index, so that there will not be the above problems, just change the WHERE q.queueid in () of the main query to WHERE q.queueid = (), which is a problem of development specification.

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

Revised execution plan:

+-+- -+ | 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 | 2067 | Using where | + This is the end of the content of "blocking Analysis of the maximum ID of the Table by MYSQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.