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

This article mainly introduces "mysql auto_increment lock brought table lock example analysis", in daily operation, I believe many people in mysql auto_increment lock brought table lock example analysis problems have doubts, Xiaobian consulted all kinds of data, sorted out simple and easy to use operation methods, hope to answer "mysql auto_increment lock brought table lock example analysis" doubts helpful! Next, please follow the small series to learn together!

Case description:

Online a table has about 200 million, about 50 G size data, business re-planning, most of the data need to be archived historically. At that time, for convenience, we created a new table with the same structure, and then quickly renamed it into an online table, and then inserted the backup table data that needs to be imported into the online table into the select operation. As a result, the online table is locked, and all services are blocked. When the situation is found, there is no way to undo the insert operation (because a lot of inserts have been inserted. In the rollback, the estimated cost is higher), so we can only wait for the insert to complete.

create table new_table like old_table; --Create a new table with the same structure as the online table;

alter table new_table auto_increase =xxxx --Set the value of the new table to be larger, in order to leave space and difference with the old table data;

rename table old_tale to old_table_bak;

renmae table new_table to online_table;

--These two rows are executed together to reduce the time to switch tables and minimize the impact on the data on the line;

insert into online_table select * from old_table_bak where xxxxxxx; --Import the required data from historical tables into new tables;

Case Study:

Also be oneself careless, think innodb insert will only add row lock, did not consider auto_increment self lock. A table lock was created, affecting the entire business.

Here is some information about auto_increment self-locking.

The innodb_autoinc_lock_mode parameter is:

Prior to mysql 5.1.22, mysql's "INSERT-like" statements (packages INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA) locked the table with an AUTO-INC lock throughout the execution of the statement until the end of the statement (rather than the end of the transaction).

Therefore, when using INSERT…SELECT, INSERT…values(…),values(…), LOAD DATA and other time-consuming operations, the entire table will be locked, while blocking other statements such as "INSERT-like" and Update. It is recommended to use programs to divide these statements into multiple statements and insert them one by one to reduce the locking time of a single time table.

After mysql 5.1.22, mysql was improved by introducing the parameter innodb_autoinc_lock_mode, which controls mysql lock table logic.

Before introducing this, let's introduce a few terms to facilitate innodb_autoinc_lock_mode.

1. "INSERT-like"：

INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

2. "Simple inserts"：

insert statement, INSERT, INSERT … VALUES(),VALUES()

3. "Bulk inserts"：

insert statements that cannot determine the number of inserts by parsing insert statements, INSERT … SELECT, REPLACE … SELECT, LOAD DATA

4. "Mixed-mode inserts"：

Not sure if auto_increment id needs to be allocated, generally the following two cases

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT … ON DUPLICATE KEY UPDATE

innodb_autoinc_lock_mode = 0 ("traditional" lock mod).

This pattern is retained for backward compatibility, as before mysql 5.1.22, and is characterized by "table-level locking" and poor concurrency, as described earlier.

innodb_autoinc_lock_mode = 1 ("consecutive" lock mode).

This mode is the default mode in the new version, recommended to use, relatively high concurrency, characterized by "consecutive", that is, to ensure that the newly inserted auto_increment id in the same insert statement is consecutive.

In this mode:

"Simple inserts": Directly parse the statement to get the number to insert, and then allocate enough auto_increment ids at once, only locking the entire allocation process.

"Bulk inserts": Because the number of inserts cannot be determined, table-level locking is used as in the previous schema.

"Mixed-mode inserts": Parse the statement directly to get the worst-case number of inserts, and then allocate enough auto_increment ids at once to lock the entire allocation process.

It should be noted that in this way, too many ids will be assigned, resulting in "waste."

For example INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,' b'), (5,'c'), (NULL,' d'); assigns 5 ids at once, regardless of whether the user specifies a partial id;

INSERT... ON DUPLICATE KEY UPDATE A one-time allocation regardless of whether future insertions will only perform update operations due to duplicate key.

Note: When master mysql version =5.1.22, slave needs to set innodb_autoinc_lock_mode to 0, because the default innodb_autoinc_lock_mode is 1. The execution results of INSERT … ON DUPLICATE KEY UPDATE and INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,' b'), (5,'c'), (NULL,' d'); are different. In real life, INSERT … ON DUPLICATE KEY UPDATE is generally used.

innodb_autoinc_lock_mode = 2 ("interleaved" lock mode)

This mode is to allocate one by one, without locking the table, only locking the process of allocating ids. The difference between innodb_autoinc_lock_mode = 1 is that multiple will not be pre-allocated. This mode has the highest concurrency.

However, when binlog_format is statement-based in replication (SBR statement-based replication for short), there is a problem, because one is allocated one by one, so that when concurrent execution is performed,"Bulk inserts" will be allocated to other INSERT at the same time, and master-slave inconsistency will occur (the execution result of the slave library is different from that of the master library), because binlog will only record the initial insert id.

Test SBR, execute begin;insert values(),();insert values(),();commit; will add SET INSERT_ID=18/*!*/; before each insert value (),(); in binlog.

But there is no problem with row-based replication RBR.

In addition, the main disadvantage of RBR is that when the number of logs includes a large number of update delete (update multiple statements, delete multiple statements), the log will be much larger than SBR; if there are not many such statements in the actual statement (there are many such cases in reality), it is recommended to use RBR with innodb_autoinc_lock_mode, but then again,"Bulk inserts" are very few in real production, so innodb_autoinc_lock_mode = 1 should be enough.

At this point, the study of "example analysis of table lock brought by mysql auto_increment lock" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.