Why sometimes there is one of more gap(s) in the value of auto increment column?

Question

I have a table like this:

// colors
+----+-------+
| id | color |
+----+-------+
| 1  | red   |
| 2  | blue  |
+----+-------+
-- id column is auto increment (PK)

When I insert some new values into that table, sometimes there is some gaps in the id column. Something like this:

INSERT INTO colors (color)
            SELECT  'gray'
              UNION ALL
            SELECT  'black'
              UNION ALL
            SELECT  'green'
              UNION ALL
            SELECT  'pink';

/* output:
+----+-------+
| id | color |
+----+-------+
| 1  | red   |
| 2  | blue  |
| 6  | gray  | -- expected id is 3, but it is 6 
| 7  | black |
| 8  | green |
| 9  | pink  |
+----+-------+
*/

A repro of this on SQL Fiddle and RexTester

Well what's wrong? Why sometimes the value of id goes out of the normal order? How can I prevent it?

EDIT: I've created the table like this:

CREATE TABLE colors (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
color VARCHAR(30) NOT NULL
)

When I paste this query twice in two different steps:

INSERT INTO colors (color)
            SELECT  'gray'
              UNION ALL
            SELECT  'black'
              UNION ALL
            SELECT  'green'
              UNION ALL
            SELECT  'pink';

And here is the result:

As you see the order of id column isn't continuous. Why?

Note1: Engine is InnoDB. Note2: Here is the number of auto increment for next row:

There is no gap in auto increment key. Actually you have deleted the 3 to 5 id. Thats the reason. — Shahbaz Hashmi, Jun 16 '16 at 17:52
Beside @Mr.Llama suggestion, you shouldnt worry about gap in autonumeric field. Deletes will also leave you with gaps and that is something you have to live with. — Juan Carlos Oropeza, Jun 16 '16 at 18:06
@JuanCarlosOropeza Look, I don't delete any row at all .. So why still there is some gaps? btw I'm worry about that because `id` column uses `int` unsigned datatype *(not `bigint`)*, so I'm worry it fills quickly... — stack, Jun 16 '16 at 18:12
@JuanCarlosOropeza It says that's because of *transactions*. But I don't use *transactions*. When I insert multiple rows in one query *(after inserting)*, the number of *AUTO_INCREMENT* will be added more than what it should be. http://i.stack.imgur.com/8LiAy.png — stack, Jun 16 '16 at 18:20
Can you replicate the issue? What is your table definition and steps? Show us something like this http://sqlfiddle.com/#!9/6996a/1 ... here you dont get gaps, so you must be doing something else. Maybe a trigger? — Juan Carlos Oropeza, Jun 16 '16 at 18:24
@JuanCarlosOropeza [There you go](http://sqlfiddle.com/#!9/e3450/1) — stack, Jun 16 '16 at 18:36
You have a delete button but you have never deleted a row? Can you guarantee that no one has deleted a row? — paparazzo, Jun 16 '16 at 18:44
@MartinSmith I don't want to get into a debate on this but a SQL Fiddle is not a guarantee the table the OP is using has never had a delete. — paparazzo, Jun 16 '16 at 18:48
@Paparazzi yes the fiddle reproduce the bahaviour, there is no delete and there is a gap, very very weird, and make this question more interesting. — Juan Carlos Oropeza, Jun 16 '16 at 18:50
Seems to happen with the `SELECT ... UNION ALL` but not the `VALUES`. Maybe it reserves a few values at a time rather than just one by one? — Martin Smith, Jun 16 '16 at 18:57
@JuanCarlosOropeza It is not apparent to me there is a SQL Fiddle that reproduces. I have nothing of value to add. — paparazzo, Jun 16 '16 at 18:57
This get weirder. If you only have 3 element on each insert works ok. If you add the 4th element go crazy. You can check here because sqlFiddle sometime fail. http://rextester.com/SXAI75080 — Juan Carlos Oropeza, Jun 16 '16 at 18:58
@MartinSmith Seems you are [right](http://sqlfiddle.com/#!9/78031/2) — stack, Jun 16 '16 at 19:02
@MartinSmith But actually I cannot use `VALUES` instead of `SELECT .. UNION ALL`. Because in reality I have some conditions on the `WHERE` clause for each `SELECT` statement. Have you any idea? — stack, Jun 16 '16 at 19:14
@stack, you can look at this: http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html may be it helps you — Mahedi Sabuj, Jun 16 '16 at 19:21
I made a more complex testing http://rextester.com/HUU93891 you can see even if put the `UNION ALL` as a subquery still give you a bug. But the last insert with only 3 element produce the right autoid at the end. — Juan Carlos Oropeza, Jun 16 '16 at 19:21
@MahediSabuj specifically `For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible` looks like it explains it. — Martin Smith, Jun 16 '16 at 19:44
@MartinSmith I think `INSERT INTO colors (color) VALUES ('gray'), ('black'), ('green'), (pink);` is also *successive statements* *(or bulk inserts)*. But I don't see any gap when I insert them by `VALUES()`. — stack, Jun 16 '16 at 19:50
@stack possibly that is resolved at compile time so it knows exactly how many rows values will insert. Yep looks like that is a "simple insert" not a "bulk insert". — Martin Smith, Jun 16 '16 at 19:52
It is easy to recreate this in many circumstances (without deletes). It is a commonly known anomaly of the innodb engine. It is why when I wrote this [answer](http://stackoverflow.com/a/33666394) I switched over to MyISAM with a caveat statement. It was merely to show consistency in the min/max/count that would be blown away if innodb was involved. Innodb locking (with its logging) both protects you, and causes some people to get all OCD on id gaps. C'est La Vie. — Drew, Jun 16 '16 at 20:12
@MartinSmith, So for lock mode 0, it will not make gap between auto increment id. And this might make things a bit slower if perform inserts of multiple rows in a single query. — Mahedi Sabuj, Jun 16 '16 at 20:13
@stack your trivial `insert 4 known values` a few comments up is not the issue or realistic example for what you are really doing. The issue is the "uncertainty of rows reservation" due to your unions and the manual page lays it out. It has been referenced to you. — Drew, Jun 16 '16 at 20:38
@JuanCarlosOropeza it is easy to recreate. And with no deletes. There are a lot of examples of it, much on Percona. Here is a screen shot of a quick test, yet another variant: http://i.imgur.com/yTD8WXu.jpg — Drew, Jun 16 '16 at 20:44

nasukkin · Answer 1 · 2016-06-17T18:33:19.277

-1

It appears to have something to do with how your INSERT statements are formed. See this fiddle, which is slightly modified from your example. In this case, you don't get gaps (but you probably will if you append more inserts... in fact, it does.).

EDIT: After a tiny bit more digging, I found that you don't get gaps if you use the MyISAM engine (as opposed to InnoDB). So, perhaps it is a flaw or curious design choice in InnoDB...?

EDIT 2: Further digging has revealed this bug, filed against the InnoDB storage engine. It matches the original question's use-case very closely. No resolution to the bug has been provided, however it appears that one resolution is to set innodb_autoinc_lock_mode to 0 in your my.cnf file prior to mysqld startup.

edited Jun 17 '16 at 18:33

answered Jun 16 '16 at 21:18

nasukkin

2,460
1
12
19

1

This does not answer the question of _why_ this is happening. – jpw Jun 16 '16 at 21:25
@jpw You're right. I don't know the specifics of what it happens, other than it does appear to be an engine implementation detail. However, there are other StackOverflow threads that speculate upon the topic, such as this one: http://dba.stackexchange.com/questions/60295 – nasukkin Jun 16 '16 at 21:34
1

There is no flaw here. It is by design. Well documented. – Drew Jun 16 '16 at 22:18
@Drew It would appear that not everyone in the MySQL community would agree with you. See http://bugs.mysql.com/bug.php?id=61058 – nasukkin Jun 17 '16 at 18:25
Without reading it I already know of a ton of people that consider it a flaw. A flaw that mysql reserves spacing for the unknown or that which needs to be abandoned in a highly performant concurrency model. And they are left with gaps and get all worked up about it. – Drew Jun 17 '16 at 18:28

Why sometimes there is one of more gap(s) in the value of auto increment column?

A repro of this on SQL Fiddle and RexTester

1 Answers1

Linked