InnoDB: настраиваемое автоматическое приращение с использованием выбора вставки. Может ли быть ошибка дублирования ключа?

Это не лучшее решение, потому что оно создает разделяемую блокировку для my_tbl во время выполнения SELECT. У любого количества потоков может быть общая блокировка одновременно, но она блокирует одновременные блокировки записи. Таким образом, это приводит к сериализации вставок в ожидании завершения SELECT.

Вы можете наблюдать этот замок. Запустите этот запрос за один сеанс:

INSERT INTO  my_tbl (idx, clmn_1) 
 SELECT IFNULL(MAX(idx), 0) + 1, 1234+SLEEP(60) 
 FROM my_tbl;

Затем перейдите к другому сеансу, запустите innotop и просмотрите экран блокировки (нажмите клавишу «L»). Вы увидите такой вывод:

___________________________________ InnoDB Locks ___________________________________
ID  Type    Waiting  Wait   Active  Mode  DB    Table   Index    Ins Intent  Special
61  TABLE         0  00:00   00:00  IS    test  my_tbl                    0         
61  RECORD        0  00:00   00:00  S     test  my_tbl  PRIMARY           0         

Вот почему механизм автоинкремента работает именно так. Независимо от изоляции транзакции, поток вставки на короткое время блокирует таблицу только для увеличения числа auto-inc. Это очень быстро. Затем блокировка снимается, позволяя другим потокам продолжить работу немедленно. Тем временем первый поток пытается завершить вставку.

См. http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html для получения дополнительных сведений о блокировке автоматического увеличения.

Я не уверен, почему вы хотите имитировать поведение автоматического приращения, а не просто определять столбец как столбец с автоматическим приращением. Вы можете изменить существующую таблицу, чтобы автоинкремент.

Повторите свой комментарий:

Даже если PK объявлен как автоинкремент, вы все равно можете указать значение. Автоинкремент срабатывает только в том случае, если вы не укажете столбец PK в INSERT или укажете NULL или DEFAULT в качестве его значения.

CREATE TABLE foo (id INT AUTO_INCREMENT PRIMARY KEY, c CHAR(1));
INSERT INTO foo (id, c) VALUES (123, 'x'); -- inserts value 123
INSERT INTO foo (id, c) VALUES (DEFAULT, 'y'); -- inserts value 124
INSERT INTO foo (id, c) VALUES (42, 'n'); -- inserts specified value 42
INSERT INTO foo (c) VALUES ('Z'); -- inserts value 125
REPLACE INTO foo (id, c) VALUES (125, 'z'); -- changes existing row with id=125

Повторите свой комментарий:

START TRANSACTION; 
SELECT IFNULL(MAX(idx), 0)+1 FROM my_tbl FOR UPDATE; 
INSERT INTO my_tbl (idx, clmn_1) VALUES (new_idx_val, some_val); 
COMMIT; 

На самом деле это хуже, чем ваша первая идея, потому что теперь SELECT...FOR UPDATE создает X-блокировку вместо S-блокировки.

Вам действительно не следует пытаться заново изобретать поведение AUTO-INCREMENT, потому что любое решение SQL ограничено свойствами ACID. Auto-inc обязательно работает вне ACID.

Если вам нужно исправить существующие строки атомарно, используйте либо REPLACE или ВСТАВИТЬ ... ПРИ ДВОЙНОМ ОБНОВЛЕНИИ КЛЮЧА.