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

Question

У меня есть таблица, как: idx (PK) clmn_1

Оба INT. idx не определяется как автоинкремент, но я пытаюсь смоделировать его. Чтобы вставить в эту таблицу, я использую:

"INSERT INTO  my_tbl (idx, clmn_1)   \
 SELECT IFNULL(MAX(idx), 0) + 1, %s  \
 FROM my_tbl", val_clmn_1

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

И как я могу проверить это сам?

Я использую Percona XtraDB server 5.5.

Answer 1

Это не очень хорошее решение, потому что оно создает общую блокировку для 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         

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

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

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

---

Re ваш комментарий:

Даже если 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

---

Re ваш комментарий:

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 или INSERT... ON DUPLICATE KEY UPDATE.