Столбцы MySQL Auto Increment при TRANSACTION, COMMIT и ROLLBACK

При использовании MySQL START TRANSACTION и MySQL принимает решение об откате - в случае, если таблица имеет столбец AUTO_INCREMENT - уменьшается ли столбец ... во время отката?

Или надо?

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

# BOTH TABLES START OUT EMPTY // TABLE1 ID is **auto_increment** 

START TRANSACTION;

INSERT INTO `TABLE1` (`ID` ,`NAME`) VALUES (NULL , 'Ted');  # MySQL TABLE1 **ID** is Auto incremented to 1 

INSERT INTO `TABLE2` (`ID` ,`WRONGVALUE`) VALUES (NULL , 'some value');  # error. This TRANSACTION will not succeed 

COMMIT;  # Because of the error - this TRANSACTION is now rolled back and Ted is NOT added


Поскольку MySQL будет автоматически увеличивать идентификатор в первой таблице, независимо от того, завершилась ли транзакция успешно или неудачно, является ли это стандартной практикой для самостоятельного уменьшения размера таблицы?


mysql transactions auto-increment decrement
person Community    schedule 07.02.2013    source источник
comment
Я считаю, что автоинкремент, когда он поднимется, больше не вернется   -  person jcho360    schedule 07.02.2013
comment
насколько мне известно, транзакция не может быть частично завершена ... либо полностью завершена, либо полностью откат (в этом суть транзакции) ... кроме того, я согласен с jcho360   -  person Barranka    schedule 07.02.2013

Ответы (2)


arrow_upward
47
arrow_downward

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

Повторите свой комментарий: если я могу сказать это более четко, любое изменение объема транзакции может быть отменено. Счетчик автоинкремента не откатывается, поэтому он не подчиняется атомарности транзакций. Он также не подчиняется изоляции, потому что другая транзакция получает следующее значение, даже если ваша транзакция еще не зафиксирована.

Принцип работы автоинкремента означает, что иногда, если вы вставляете несколько строк, а затем откатываете транзакцию, значения, выделенные с помощью автоинкремента, теряются навсегда!

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

person Bill Karwin    schedule 07.02.2013
comment
+1 Замечательный ответ ... Я знал, что автоинкременты работают за пределами транзакций, но я никогда не связывал это с тем, что необходимо для параллелизма. - person Michael Fredrickson; 07.02.2013
comment
@ Билл Карвин Привет, Билл? Я просто хотел спросить, считаете ли вы, что моя формулировка в вопросе верна # Из-за ошибки - эта ТРАНЗАКЦИЯ теперь откатывается, а Тед НЕ добавлен - это точно - или это действительно откат - Или даже иди вперед .... Я думаю, что я пытаюсь сказать, это - термин ОТКРЫТЫЙ НАЗАД говорит о том, что часть данных была временно изменена в таблице ... а затем ОТКАТАЛАСЬ НАЗАД, поскольку временные данные были удалены? - person ; 09.02.2013
comment
@KDawg, да, InnoDB регистрирует и сохраняет все изменения, и если это обновление или удаление, копии старых строк также сохраняются. При откате новые версии отбрасываются, а исходные версии восстанавливаются. Это происходит асинхронно в фоновом режиме как своего рода сборка мусора (то есть может произойти через несколько минут или даже часов). Еще до того, как это произойдет, InnoDB может прозрачно вернуть правильную версию любой запрашиваемой вами строки. - person Bill Karwin; 09.02.2013
comment
Обратной стороной, которую я вижу, является то, что в среде разработки / тестирования вы можете очень быстро достичь идентификатора tinyint. - person Rafael Barros; 08.09.2014
comment
@RafaelBarros, это правда, tinyint часто слишком мал для большинства таблиц. Но это не имеет ничего общего с изоляцией автоинкремента. - person Bill Karwin; 09.09.2014

arrow_upward
1
arrow_downward

Раскрытие информации: я относительно новичок в SQL, поэтому кое-что из этого может быть неправильным. Но я так понимаю. Автоинкремент должен работать вне транзакций, иначе вы можете поставить под угрозу данные. Допустим, у вас есть 2 пользователя, которые пытаются подключиться к вашему dB. Оба создают учетные записи. Когда создается Учетная запись 1, она увеличивается до 1. Затем создается Учетная запись 2, и она увеличивается до 2. Пока это будет выглядеть следующим образом:

  • (1, Account1)
  • (2, Account2)
  • Прирост: 2.

Теперь предположим, что Учетная запись 2 фиксируется, но Учетная запись 1 не работает. Теперь ваша таблица выглядит следующим образом:

  • (2, Account2)
  • Прирост: 1.

Поскольку вы уменьшили свой Автоинкремент, у вас не получится получить уникальное значение, когда кто-то попытается зарегистрировать новую учетную запись, потому что пара будет:

  • (2, Account2)
  • Попытка вставить (2, NewAccount)

Что не удастся, потому что 2, первичный ключ, не будет уникальным.

Если вам нужно исправить свою таблицу (в целях разработки), вы всегда можете запустить следующий код:

ALTER TABLE `DB_NAME`.`TABLE_NAME` 
AUTO_INCREMENT = 1 (or some other number);
person Warren Niles    schedule 14.09.2019