Can a row with a larger autoincrement value appear earlier than a row with a smaller one?
Is the following scenario possible?
MySQL version 5.6.15
CREATE TABLE my_table (
id int NOT NULL AUTO_INCREMENT.
data1 int,
data2 timestamp,
PRIMARY KEY (id)
) ENGINE=InnoDB;
innodb_autoinc_lock_mode = 1
AUTO_INCREMENT = 101
- 0 ms: request A is executed:
INSERT INTO my_table (data1, data2) VALUES (101, FROM_TIMESTAMP(1418501101)), (102, FROM_TIMESTAMP(1418501102)),
.. [total 200 values] ..,(300, FROM_TIMESTAMP(1418501300));
- 500ms: Request B is executed:
INSERT INTO my_table (data1, data2) VALUES (301, FROM_TIMESTAMP(1418501301));
- 505 ms: Request B completed. The string gets id = 301.
- 1000ms:
SELECT id FROM my_table WHERE id >= 300;
- will return one line (id = 301). - 1200 ms: Request A completed. Rows get id = 101 to id = 300.
- 1500ms:
SELECT id FROM my_table WHERE id >= 300;
- will return two lines (id = 300, id = 301).
In other words, is it possible that the row with id = 301 can be fetched earlier than the row with id = 300 can be selected?
And how to avoid it, if possible?
source to share
Why does query A take more than a second to run? Ugh! And yes, you see, this is how I expect him to behave.
Primary keys 101 through 300 are reserved immediately when new rows are inserted. This takes a few milliseconds. It then spends over 1 second rebuilding the indexes and halfway through doing whatever you ran another query that inserted a new row using the next available auto_increment: 301.
As Aline Purcaru said, you can avoid this particular problem by changing the blocking mode, but that will cause performance problems (instead of executing in 5 milliseconds, request B will take 700 milliseconds.
In high load situations, the problem gets exponentially worse, and you will see too many connections errors over time, effectively shutting down your entire database.
There are also other rare situations in which auto_increment may produce out-of-order increments that will not be resolved by blocking.
Personally, I think the best option is to use UUID instead of auto_increment for the primary key, and then have a timestamp column (as double with microseconds) to determine which order rows were inserted into the database.
source to share
So ... I read the docs for you.
According to them, when you have two simple inserts in your example (multi-position insertion is still considered simple even if it has multiple lines, because you know how much you are inserting, so the end autoincrement value might be) and when you have innodb_autoinc_lock_mode = 1
, there will be no lock on the table, and you can complete your second query before the first and bring that row up to that.
If you want to avoid this, you need to install innodb_autoinc_lock_mode = 0
, but you can pay for scalability.
Disclaimer: I have not tried and the answer is based on my understanding of the MySQL docs.
source to share