Как правильно применить транзакции и какой выбрать уровень изолированности?

Question

[l4m3r]

Есть таблица folder (id, name, files_count). name -- уникальное.
Есть таблица file` (id, filename, folder_id).
Есть операция: Добавить файл в папку. Если папки нет, то создать её. Если есть, то просто инкрементируем у нее files_count.

В своём php так примерно (псевдокод):
1. Получаю запись $record = SELECT * FROM folder WHERE name = :name"
2. Если `$record === null`, то делаю INSERT INTO folder (name) VALUES (:name)
3. Если `$record !== null`, то делаю UPDATE folder SET files_count = files_count + 1 WHERE id = :record_id
4. Делаю INSERT INTO file (filename, folder_id) VALUES (:filename, :folder_id), где `folder_id` это или `last_insert_id` есть запись была только что добавлена, или `$record['id']`.

Как правильно всё это изолировать, чтобы при параллельном выполнении не возникла ситуация, когда 2 скрипта получив `$record === null`, попытались создать две одинаковые папки.
Просто обернуть все пункты одну транзакцию? (begin; select, insert / update, insert; commit;) ? А какой уровень изоляции?

Answer 1

[Дмитрий]

https://dev.mysql.com/doc/refman/8.0/en/insert-on-...

Answer 2

[Stalker_RED]

INSERT INTO files (name, files_count) VALUES ('example', 1)
   ON DUPLICATE KEY UPDATE files_count = files_count + 1

Answer 3

[Иван Шумов]

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

А если очень хочется то есть такая операция как обновление "версии". Храним у записи еще поле, например, timestamp с временем обновления или uuid как идентификатор версии. И обновляем запись в которой и id и это поле совпадают. Разно что поле тоже должно обновляться.

Кстати, files_count + 1 это плохая идея. Сюда стоит заносить агрегацию по родителю. Именно из-за параллелизма

Comments

[l4m3r]

Агрегация по group by медленно работает и придётся считать тонну записей.
Таблица нагружена на чтение и order by files_count

PS: реальные условие вообще не про папки и файлы. Я просто упросил и привёл аналогии.

[l4m3r]

хранить их в кэше.

Ты даёшь советы не зная реальных условий.

[Иван Шумов]

l4m3r, в реальности при такой системе есть кэш и есть такие вещи как инкременты в Redis. Стоит задуматься

[Иван Шумов]

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

[l4m3r]

Ок, спасибо