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

This article mainly introduces "how to understand mysql innodb_autoinc_lock_mode and database behavior". In daily operation, I believe many people have doubts about how to understand mysql innodb_autoinc_lock_mode and database behavior. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "how to understand mysql innodb_autoinc_lock_mode and database behavior". Next, please follow the editor to study!

Mysql's auto_incremet knows all about it.

There is also a parameter of a lock type of this behavior, which will affect the behavior of the database.

Innodb_autoinc_lock_mode has three values 0, 1, 2 corresponding to

"traditional", "consecutive", or "interleaved" lock mode

For 3 actions:

1. Insert_like statements include: insert, insert... Select, replace, replace... Select has load data statement.

2.simple insert statement: insert, replace like this.

3.bulk insert sentence: insert... select, replace...select, load data.

4.mixed insert sentences: for example, INSERT INTO T1 (c1memc2) VALUES (1djina'), (NULL,'b'), (5djc'), (NULL,'d')

And INSERT... A statement like ON DUPLICATE KEY UPDATE.

The innodb_autoinc_lock_mode value is 0:

This parameter is introduced from 5.1 and corresponds to the traditional pattern. For the insert like statement, the auto_incremet acquires a table-level lock.

The lock is held until the end of the current statement. Make sure that the writing of the statement is predictable, predictable and reproducible, that is, the order of the data stored in the statement.

For replication in binlog mode using statement format, the value obtained by slave is the same as that obtained by the main database on the same line.

For example:

CREATE TABLE T1 (C1 INT (11) NOT NULL AUTO_INCREMENT, c2 VARCHAR (10) DEFAULT NULL, PRIMARY KEY (C1)) ENGINE=InnoDB

Execute the statement:

Tx1: INSERT INTO T1 (c2) SELECT 1000 rows from another table...

Tx2: INSERT INTO T1 (c2) VALUES ('xxx')

In statement replication, the log repeats the sql execution to ensure that the values obtained by the master and slave are consistent.

When tx1 executes, it can guarantee that these 1000 records get the value of autoinc field continuously, and tx2 will not get the value of autoinc until the execution of tx1 is finished.

This limits concurrency.

Value of innodb_autoinc_lock_mode is 1:

A value of 1 is the default value.

For bulk insert statements. Each statement has only this lock until the end of the statement, but each statement holding the lock can only be executed once.

For simple inset statements, the number of values that can be expected to be inserted, then the statement does not need to hold the table-level lock until the end of the statement. It is a higher-level mutex lock.

For unpredictable insertions, the behavior is the same as bulk insert, holding the lock until the end of the statement, only once at a time.

For mixed inset database, more than the number of inserted autoinc values will be dispatched. At the end of the statement, the excess values will be discarded. At this time, the values of the autoinc field may not be contiguous.

Value of innodb_autoinc_lock_mode is 2:

The insert like statement can hold the table-level lock and execute it for the same time.

In this way, for statement based replication, when restoring, the result becomes uncertain. That is, it is possible that the value of the autoinc field of the master-slave library is inconsistent.

This will trouble the business logic agent.

In this mode, each statement can generate autoincs almost at the same time, so that the value of the autoinc field obtained by each statement is uncertain. It is also unpredictable.

For simple insert, if the number of values to be inserted can be predicted before execution, the autoinc values obtained by the sql data are continuous. Otherwise, it may not be continuous.

Getting the value of the autoinc field for a mixed insert statement is unpredictable.

There may be a gap for the value of autoinc obtained by the bulk insert statement, that is, the value obtained may not be contiguous.

The relationship between the value of innodb_autoinc_lock_mode and replication.

The values of 0 and 1 are safe for replication, that is, the values of the master and slave databases are consistent.

If it is only 2 for statement based-based replication, the data from the library is uncertain. That is, it is not safe.

It is safe to copy the data of the slave database corresponding to row based or mixed based in accordance with the master database.

You can see that when you take a value of 2, you can get greater performance.

In addition, if you specify the value of the autoinc field, insert may trigger an key conflict error.

If the value specified by insert is greater than the maximum value of the autoinc field, the result is uncertain.

When innodb_autoinc_lock_mode is 2, there will be a gap for bulk insert. The values written to the database by the same sql are not necessarily contiguous.

At this point, the study on "how to understand mysql innodb_autoinc_lock_mode and database behavior" 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.