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

MySQL 5.7 MyISAM concurrent insertion feature is what, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The MyISAM storage engine supports concurrent inserts to reduce competition between table reads and writes. If the data file of an MyISAM table has no holes (there are deleted rows in the middle of the table), while the SELECT statement reads rows from the table, an insert statement can add the newly inserted rows to the end of the table when executed. If there are multiple INSERT statements, they are queued and executed in turn. The results of concurrent insertions may not be immediately visible.

The system parameter concurrent_insert can be used to change the handling of concurrent inserts. By default, the value of this parameter is AUTO (or 1), and concurrent insertion is on; when this parameter is set to NEVER (or 0), concurrent insertion is turned off. When this parameter is ALWAYS (or 2), concurrent inserts are allowed to be performed at the back end of the table even if there are deleted rows in the table. If MySQL uses binary logs, concurrent inserts are converted into normal insert statements, such as CREATE...SELECT or INSERT. SELECT statement. This is done to ensure that the accurate data in the table can be rebuilt by applying the log. In addition, for tables with read-only locks, concurrent inserts are blocked and need to wait.

Using the LOAD DATA INFILE statement, if the CONCURRENT option is specified on a MyISAM table that meets the criteria for concurrent insertion (the table ultimately has no free data blocks), other sessions can query data from that table when the LOAD DATA statement is executed. Using the CONCURRENT option has a slight impact on the performance of LOAD DATA, even if this table is not used by other sessions. If the HIGH_PRIORITY option is specified, it overrides the-- low-priority-updates parameter when the MySQL service starts with the-- low-priority-updates parameter. It also causes concurrent inserts not to be used.

Mysql > show variables like'% low%priority%'

+-+ +

| | Variable_name | Value |

+-+ +

| | low_priority_updates | OFF |

+-+ +

1 row in set (0.00 sec)

If the value of this parameter is set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there are no suspended SELECT or LOCK TABLE READ statements on the specified MyISAM table.

This parameter only works for storage engines that use table-level locks (for example, MyISAM, MEMORY, and MERGE).

For LOCK TABLE statements, the difference between READ LOCAL and READ statements is that READ LOCAL allows the execution of insert statements and concurrent insert statements when locked.

Mysql > show variables like'% concurrent%'

+-+ +

| | Variable_name | Value |

+-+ +

| | concurrent_insert | AUTO |

+-+ +

1 row in set (0.00 sec)

Concurrent_insert

The default value for this parameter is AUTO,MySQL to allow concurrent insertions, and if there are no deleted rows in the middle of the MyISAM table, the data will be inserted at the end of the table. If you start mysqld with the-- skip-new parameter, this parameter will be set to NEVER.

NEVER (or 0) turns off concurrent insertion

AUTO (or 1) allows concurrent insertions. If there are no deleted rows in the middle of the MyISAM table, the data will be inserted at the end of the table.

ALWAYS (or 2) allows concurrent insertions and inserts data at the end of the MyISAM table regardless of the rows that have not been deleted in the middle of the table

Mysql > show variables like 'concurrent%'

+-+ +

| | Variable_name | Value |

+-+ +

| | concurrent_insert | AUTO |

+-+ +

1 row in set (0.00 sec)

Session ①

Mysql > lock table test read local

Query OK, 0 rows affected (0.00 sec)

Insert or update operations within the same session will fail

Mysql > insert into test values (90, 'Jimmy')

ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated

Mysql > update test set name='Trinity' where id=80

ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated

Session ②

Allow insert operation

Mysql > insert into test values (90, 'Jimmy')

Query OK, 1 row affected (0.00 sec)

Session ①

Query the newly inserted row of the session ② and the empty set will be queried.

Mysql > select * from test where id=90

Empty set (0.00 sec)

Mysql > show variables like'% isolation%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

Session ②

The update operation waits

Mysql > update test set name='Trinity' where id=80

Query OK, 1 row affected (1 min 10.11 sec)

The session ① does not end waiting until the session ② executes the unlock tables statement

Rows matched: 1 Changed: 1 Warnings: 0

Session ①

After executing the unlock tables statement, you can successfully query the newly inserted data in the session ②

Mysql > select * from test where id=90

+-+ +

| | id | name |

+-+ +

| | 90 | Jimmy |

+-+ +

1 row in set (0.00 sec)

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.