Re: auto_increment behaviour

[Rick James]

If you need some behavior for the AUTO_INCREMENT to be more than UNIQUE and ascending, then you need to do it yourself.

You are using MyISAM, right? Here's the algorithm: When you INSERT a new row, it effectively calculates MAX(id)+1 to derive the value to use. "3" was deleted, so MAX(id) = 2, thereby giving you 3 again.

In InnoDB, the algorithm is different, and not necessarily to your liking either. In particular, id=N might be COMMITted (hence visible) after id N+1.

Ouch! That got me to thinking. Actually InnoDB really can show a gap, then fill it in later. So, if you are watching the AUTO_INCREMENT to see if something new has arrived, you can miss row(s). Here's a test case:

Setup:

CREATE TABLE iid (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    vc VARCHAR(99),
    PRIMARY KEY(id)
) ENGINE = InnoDB;

Run this from "first" client:

TRUNCATE TABLE iid;
BEGIN;
INSERT INTO iid (vc) VALUES ('insert, pause, commit');
SELECT NOW() as 'BEGIN;INSERT time', SLEEP(11);
COMMIT;
SELECT NOW() as 'COMMIT for 1st INSERT';
SELECT id, vc FROM iid;

Then immediately (before the 1st finishes) run this from "second" client:

BEGIN;
INSERT INTO iid (vc) VALUES ('insert 2nd; commit 1st');
SELECT NOW() as 'BEGIN;INSERT time', SLEEP(1);
COMMIT;
SELECT NOW() as 'COMMIT for 2nd INSERT';
SELECT id, vc FROM iid;

Output -- watch timestamps to note overlap:
1st client:

mysql> BEGIN;
mysql> INSERT INTO iid (vc) VALUES ('insert, pause, commit');
mysql> SELECT NOW() as 'BEGIN;INSERT time', SLEEP(11);
| 2009-08-24 21:27:58 |         0 |
mysql> COMMIT;
mysql> SELECT NOW() as 'COMMIT for 1st INSERT';
| 2009-08-24 21:28:09   |
mysql> SELECT id, vc FROM iid;
|  1 | insert, pause, commit  |
|  2 | insert 2nd; commit 1st |

2nd client:

mysql> BEGIN;
mysql> INSERT INTO iid (vc) VALUES ('insert 2nd; commit 1st');
mysql> SELECT NOW() as 'BEGIN;INSERT time', SLEEP(1);
| 2009-08-24 21:28:05 |        0 |
mysql> COMMIT;
mysql> SELECT NOW() as 'COMMIT for 2nd INSERT';
| 2009-08-24 21:28:06   |
mysql> SELECT id, vc FROM iid;
|  2 | insert 2nd; commit 1st |

My point is that there is a time (28:06-28:09) when id=2 is visible in the table, but id=1 is missing, only to be later 'inserted'.