Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Not a Bug
-
10.0.12
-
None
-
None
Description
DROP SCHEMA IF EXISTS `test`;
|
CREATE SCHEMA IF NOT EXISTS `test`;
|
|
|
USE `test`;
|
|
|
DROP TABLE IF EXISTS `test`.`test_name`;
|
|
|
CREATE TABLE IF NOT EXISTS `test`.`test_name` (
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
`name` VARCHAR(64) NOT NULL,
|
PRIMARY KEY (`id`))
|
ENGINE = InnoDB;
|
|
|
CREATE UNIQUE INDEX `name_UNIQUE` ON `test`.`test_name` (`name`(8) ASC);
|
|
|
INSERT IGNORE INTO test_name (name) VALUES ('John');
|
INSERT IGNORE INTO test_name (name) VALUES ('John');
|
INSERT IGNORE INTO test_name (name) VALUES ('John');
|
INSERT IGNORE INTO test_name (name) VALUES ('Abe');
|
INSERT IGNORE INTO test_name (name) VALUES ('Abe');
|
INSERT IGNORE INTO test_name (name) VALUES ('Abe');
|
INSERT IGNORE INTO test_name (name) VALUES ('Zach');
|
|
|
SELECT * FROM test_name;
|
Result:
| ID | Name |
|---|---|
| 1 | John |
| 4 | Abe |
| 7 | Zach |
However, when I set innodb_autoinc_lock_mode = 0
| ID | Name |
|---|---|
| 1 | John |
| 2 | Abe |
| 3 | Zach |
I have a sense, based on the documentation, of the difference between 'traditional' lock mode and "consecutive" lock mode. I understand that if I were rolling-back the transaction, that I would loose IDs, but I would not expect to loose them in this case.