Shulou(Shulou.com)06/01 Report--

I. background note

Recently, a friend asked why so many threads are in Searching rows for update. At that time, the first reaction when I saw this state was the lock. Let's put aside the lock and talk about why there is Searching rows for update.

2. Experimental environment:

Root@mysqldb 10:15: [xucl] > show create table test1\ G

* * 1. Row *

Table: test1

Create Table: CREATE TABLE `test1` (

`a`int (11) NOT NULL

`b` varchar (20) DEFAULT NULL

PRIMARY KEY (`a`)

KEY `b` (`b`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

Root@mysqldb 10:15: [xucl] > select * from test1

+-+ +

| | a | b | |

+-+ +

| | 2 | b | |

| | 3 | c |

| | 1 | ccc |

+-+ +

3 rows in set (0.00 sec)

The approximate state is as follows:

III. Preliminary process

Simply do pstack, where the thread stack of id=2 is as follows:

It is obvious from the stack that the thread is in a state of lock waiting, but why it is in Searching rows for update does not give a clear hint. You can see that the order of calls is

Mysql_parse

Mysql_execute_command

Sql_cmd_update::execute_command

Mysql_update

...

Ha_innobase::index_read

...

Lock_wait_suspend_thread

...

Needless to say, let's cut directly from mysql_update, and the entry function is located in sql_update.cc

Bool mysql_update (THD * thd

List & fields

List & values

Ha_rows limit

Enum enum_duplicates handle_duplicates

Ha_rows * found_return, ha_rows * updated_return)

{

...

If (used_key_is_modified | | order)

{

/ *

When we get here, we have one of the following options:

A. used_index = = MAX_KEY

This means we should use full table scan, and start it with

Init_read_record call

B. used_index! = MAX_KEY

B.1 quick select is used, start the scan with init_read_record

B.2 quick select is not used, this is full index scan (with LIMIT)

Full index scan must be started with init_read_record_idx

* /

If (used_index = = MAX_KEY | | (qep_tab.quick ()

Error= init_read_record (& info, thd, NULL, & qep_tab, 0,1, FALSE)

Else

Error= init_read_record_idx (& info, thd, table, 1, used_index, reverse)

If (error)

Goto exit_without_my_ok

THD_STAGE_INFO (thd, stage_searching_rows_for_update)

Ha_rows tmp_limit= limit

}

...

The debug results are as follows:

Here, the condition is whether used_index is equal to MAX_KEY, where MAX_KEY is constant and used_index is defined as follows:

Used_index= get_index_for_order (order, & qep_tab, limit, & need_sort, & reverse)

The judgment here is more complicated. My level is limited. I do not have an in-depth understanding of the optimizer for the time being, and I will not explain it. The source code is located in sql_select.cc. Interested students can study it in depth.

The definition of the function is_key_used is as follows: http://www.ytsgfk120.com/ of Wuxi Gynecology Hospital

Bool is_key_used (TABLE * table, uint idx, const MY_BITMAP * fields)

{

Bitmap_clear_all (& table- > tmp_set); / / clear tmp_set bitmap

Table- > mark_columns_used_by_index_no_reset (idx, & table- > tmp_set); / / set the bitmap here

Const bool overlapping= bitmap_is_overlapping (& table- > tmp_set, fields); / / compare whether the index location and the modified location coincide

/ / Clear tmp_set so it can be used elsewhere

Bitmap_clear_all (& table- > tmp_set)

If (overlapping)

Return 1; / / return 1 if coincident

...

If you see the result variable of debug, used_key_is_modified is true, then go to the following judgment

And then we get into the stage_searching_rows_for_update state, which is the state we saw in show processlist at the beginning.

If we modify other fields, then the state we enter is Updating, and the corresponding source code is

If (used_key_is_modified | | order)

{

...

}

...

Thd- > count_cuted_fields= CHECK_FIELD_WARN

Thd- > cuted_fields=0L

THD_STAGE_INFO (thd, stage_updating)

...

Needless to say, let's test and verify THD_STAGE_INFO (thd, stage_updating);, and then update the data.

The experimental results are in line with expectations.

Other test results:

Case result

The update primary key goes directly to stage_updating

The update unique index goes directly into stage_updating

Update ordinary secondary index + limit enters stage_searching_rows_for_update, and when completed, enters stage_updating

IV. Summary

Finally, let's sum up:

The requirement for the emergence of Searching rows for update status is relatively strict. When data is updated, if the updated field is the index used in the current execution plan, and the index belongs to the ordinary secondary index (neither primary key nor unique index), then Searching rows for update state will appear, which can be seen because of lock waiting.

If not, then go directly to the Updating state, which is the state we often see.

The occurrence of the above two states and the long duration is not the root cause of database performance degradation, but other reasons should be considered, such as the lock waiting problem in this case.

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.