Почему вставка данных без подтверждения изменяет БД?

Question

[weranda]

Приветствую
Познакомился недавно с добавлением данных в БД MySQL через Python.
Вот что делаю:

import mysql.connector

dbconfig = {
	'host': 'XXX',
	'user': 'XXX',
	'password': 'XXX',
	'database': 'XXX',
}

connection = mysql.connector.connect(**dbconfig)
cursor = connection.cursor()

cursor.execute('INSERT INTO site_content (pagetitle) VALUES ("pagetitle")')

cursor.execute('SELECT MAX(id) FROM site_content')
print(cursor.fetchall())

По логике в таблице БД ничего меняться вообще не должно без connection.commit(), но меняется. Подтверждения сохранения данных нет. Открываю таблицу, смотрю данные их нет, но есть одно маленькое "но", сейчас поясню на упрощенном примере.
В таблице есть поле `id` int(10) unsigned NOT NULL AUTO_INCREMENT. Если я выполню описанный выше код 10 раз, то вывод print(cursor.fetchall()) с каждым новым проходом будет выводить ID на единицу больше — если в первом проходе ID был равен 5, то к десятому проходу он будет равен 15 (логично).
Данные в таблицу фактически не добавляются, но где-то в недрах БД код в примере увеличивает поле ID. Посмотрел информацию в БД и заметил, что поле AUTO_INCREMENT с каждым выполнение кода увеличивается на единицу.
Почему так происходит и как этого можно избежать?

Answer 1

[Rsa97]

Представьте: последний id был 100, вы начали транзакцию и добавили строку в таблицу. Должен MySQL зарезервировать ID для этой строки? Очевидно, да. Какой? Видимо, 101.
В этот момент проходит ещё одна команда на вставку строки, в другом потоке. Она тоже хочет получить следующий id. Какой ей выдать? Видимо, 102. Строка записана.
Теперь вы отменяете (или не подтверждаете) первую транзакцию. Куда деть значение 101? Да просто выбросить.
AUTO_INCREMENT гарантирует уникальность и монотонное возрастание, но не непрерывность значений, так что пропуск значений (лакуны) вполне допустимы.

Comments

[weranda]

Такие прерывания нумерации строк в практике допустимы, нежелательны ли?
А что произойдет если таким образом AUTO_INCREMENT был увеличен, к примеру, со 100 до 200 + записи фактически не добавлены в БД, а я потом добавлю строки с пропущенными ID со 100 до 200? По логике вообще ничего не случится, я имею в виду какие-либо сбои, но вам с вашим опытом это должно быть понятнее, чем мне.

[ThunderCat]

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

[weranda]

ThunderCat, Rsa97 Спасибо вам за помощь!

Answer 2

[ThunderCat]

Насколько я понимаю - connection.commit() завершает транзакцию, но если вы сделали инсерт, а затем не сделали комит, то транзакция автоматически откатится, однако так как инсерт "уже был", внутренний курсор автоинкремента сместится на количество (не)добавленных строк.

Почему так происходит и как этого можно избежать?

- Доктор, я когда вот здесь нажимаю - больно!
- Не нажимайте здесь.