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

This article mainly introduces "what is the Searching rows for update status of MySQL". In daily operation, I believe many people have doubts about the Searching rows for update status of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what is the Searching rows for update status of MySQL?" Next, please follow the editor to study!

1. Restrictive conditions

Generally speaking, it cannot be a unique key, a primary key, or a full table. The code is as follows:

If (used_index! = MAX_KEY) / / cannot be a unique key (primary key) and full table {/ / Check if we are modifying a key that we are used to search with: used_key_is_modified= is_key_used (table, used_index, table- > write_set); / / confirm that the conditions for query and modification by writing bitmap write_set are the same} 2, enter the state

Enter the stage_searching_rows_for_update state

THD_STAGE_INFO (thd, stage_searching_rows_for_update); ha_rows tmp_limit= limit; IO_CACHE* tempfile= (IO_CACHE*) my_malloc (key_memory_TABLE_sort_io_cache, sizeof (IO_CACHE), MYF (MY_FAE | MY_ZEROFILL)) 3. Use of temporary files

Create a temporary file at the beginning of MY. In the tmp directory, the scan line is added to the temporary file for the later actual update operation. The actual update operation will enter the stage_updating state, as follows:

If (open_cached_file (tempfile, mysql_tmpdir,TEMP_PREFIX, DISK_BUFFER_SIZE, MYF (MY_WME)) / / Open a temporary MY file {my_free (tempfile); goto exit_without_my_ok } while (! (error=info.read_record (& info)) & &! thd- > killed) {thd- > inc_examined_row_count (1); / / scan adds bool skip_record= FALSE; if (qep_tab.skip_record (thd, & skip_record)).. 4. Test summary: mysql > show create table test0820 +- - -+ | Table | Create Table | | +-+- - -+ | test0820 | CREATE TABLE `test0820` (`id` int (11) NOT NULL `name` varchar (20) DEFAULT NULL, `name1` varchar (20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_u_ test` (`name1`) KEY `name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+- - -+ 1 row in set (0.00 sec)

Primary key ID update does not trigger

Unique key idx_u_test update does not trigger

Normal index name update trigger

If Using temporary appears in the update execution plan, stage_searching_rows_for_update is used.

Mysql > desc update test0820 set name1='7' where name1='5' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+-+ | 1 | UPDATE | test0820 | NULL | range | idx_u_test | idx_u_test | 63 | const | 1 | 100.00 | Using where | +- + 1 row in set (2. 58 sec) mysql > desc update test0820 set name='7' where name='5' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | UPDATE | test0820 | NULL | range | name | name | 63 | const | 1 | 100.00 | Using where Using temporary | +-+- -+ 1 row in set (1.91 sec) mysql > desc update test0820 set id=2 where id=1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | UPDATE | test0820 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | + -+ 1 row in set (2.30 sec) 5, Stage_searching_rows_for_update status scan data is locked So it's easy to test this situation.

Stack frame:

# 0 0x00007ffff7bd368c in pthread_cond_wait@@GLIBC_2.3.2 () from / lib64/libpthread.so.0#1 0x0000000001b2f921 in os_event::wait (this=0x7ffee0e418e8) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/include/os0event.h:156#2 0x0000000001b2f269 in os_event::wait_low (this=0x7ffee0e418e8 Reset_sig_count=1) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:131#3 0x0000000001b2f692 in os_event_wait_low (event=0x7ffee0e418e8 Reset_sig_count=0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/os/os0event.cc:328#4 0x0000000001af0c4b in lock_wait_suspend_thread (thr=0x7ffee0e42ed0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0wait.cc:387#5 0x0000000001bb6de8 in row_mysql_handle_errors (new_err=0x7fffec5eb7bc, trx=0x7fffd7804080, thr=0x7ffee0e42ed0) Savept=0x0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:1312#6 0x0000000001bf9ed6 in row_search_mvcc (buf=0x7ffee097fb40 "\ 377", mode=PAGE_CUR_GE, prebuilt=0x7ffee0e42730, match_mode=1, direction=0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:6318#7 0x0000000001a53113 in ha_innobase::index_read (this=0x7ffee0952030, buf=0x7ffee097fb40 "\ 377", key_ptr=0x7ffee0a2f6d0 ", key_len=63) Find_flag=HA_READ_KEY_EXACT) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536#8 0x0000000000f933c2 in handler::index_read_map (this=0x7ffee0952030, buf=0x7ffee097fb40 "\ 377", key=0x7ffee0a2f6d0 ", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at / mysqldata/percona-server-locks-detail-5.7.22/sql/handler.h:2942#9 0x0000000000f83dac in handler::ha_index_read_map (this=0x7ffee0952030, buf=0x7ffee097fb40"\ 377 ") Key=0x7ffee0a2f6d0 ", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at / mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:3248#10 0x0000000000f8e844 in handler::read_range_first (this=0x7ffee0952030, start_key=0x7ffee0952118, end_key=0x7ffee0952138, eq_range_arg=true, sorted=true) at / mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:7750#11 0x0000000000f8c775 in handler::multi_range_read_next (this=0x7ffee0952030) Range_info=0x7fffec5ec370) at / mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:6817#12 0x0000000000f8d68d in DsMrr_impl::dsmrr_next (this=0x7ffee09524a0, range_info=0x7fffec5ec370) at / mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:7204#13 0x0000000001a6689a in ha_innobase::multi_range_read_next (this=0x7ffee0952030 Range_info=0x7fffec5ec370) at / mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:22211#14 0x00000000017bdbd8 in QUICK_RANGE_SELECT::get_next (this=0x7ffee0e40250) at / mysqldata/percona-server-locks-detail-5.7.22/sql/opt_range.cc:11237#15 0x00000000014e27f5 in rr_quick (info=0x7fffec5ec870) at / mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:399#16 0x000000000168c103 in mysql_update (thd=0x7ffee0000c00, fields=...) Values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR, found_return=0x7fffec5ecbd8, updated_return=0x7fffec5ecbd0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:691#17 0x0000000001692e40 in Sql_cmd_update::try_single_table_update (this=0x7ffee0006bc0, thd=0x7ffee0000c00, switch_to_multitable=0x7fffec5ecc7f) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:2896#18 0x000000000169338d in Sql_cmd_update::execute (this=0x7ffee0006bc0 Thd=0x7ffee0000c00) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023#19 0x00000000015cc801 in mysql_execute_command (thd=0x7ffee0000c00, first_level=true) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756#20 0x00000000015d2fde in mysql_parse (thd=0x7ffee0000c00, parser_state=0x7fffec5ee600) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#21 0x00000000015c6b72 in dispatch_command (thd=0x7ffee0000c00, com_data=0x7fffec5eed70) Command=COM_QUERY) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#22 0x00000000015c58ff in do_command (thd=0x7ffee0000c00) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#23 0x000000000170e578 in handle_connection (arg=0x6795460) at / mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#24 0x0000000001945538 in pfs _ spawn_thread (arg=0x6947660) at / mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#25 0x00007ffff7bcfaa1 in start_thread () from / lib64/libpthread.so.0#26 0x00007ffff6b37c4d in clone () from / lib64/libc.so.6T1T2BEGIN

Delete from test0820

Update test0820 set name='100' where name='90'

The display is as follows:

Mysql > show processlist +-+-- + -+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | + -+ -+ | 1 | event_scheduler | localhost | NULL | Daemon | 4771 | Waiting on empty queue | NULL | 0 | 0 | 3 | root | localhost | testmts | Query | 28 | Searching rows for update | | update test0820 set name='100' where name='90' | 0 | 0 | | 7 | root | localhost | testmts | Query | 0 | starting | show processlist | 0 | 0 | +-+-- | -+ 3 rows in set (0.01sec) so far The study on "what is the Searching rows for update status of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.