Как устроена операция UPDATE в базе данных

Что проверяют

Понимаете ли вы, что происходит в базе, когда ваш код на Python выполняет UPDATE: блокировки, версии строк, видимость для других транзакций. Синтаксис никого не интересует. Следом почти всегда дают задачу про списание баланса двумя параллельными запросами.

Как отвечать

Покажите механику на PostgreSQL, раз это самая частая база в Python-стеке:

• поиск строк: по WHERE, через индекс или полный проход, как у SELECT;
• блокировка: на каждую изменяемую строку ставится строковая блокировка до конца транзакции. Второй UPDATE той же строки ждёт. В READ COMMITTED после снятия блокировки он перепроверяет условие на новой версии строки и работает уже с ней;
• MVCC: строка не перезаписывается на месте. Создаётся новая версия, старая помечается устаревшей и остаётся видна тем, кто начал раньше. Мусор убирает VACUUM, отсюда разбухание таблиц при частых обновлениях;
• HOT-обновление: если не менялись индексированные колонки и на странице есть место, индексы не трогаются. Помогает fillfactor ниже 100;
• WAL: изменение пишется в журнал и становится надёжным на COMMIT.

В InnoDB (MySQL) иначе: строка меняется на месте, старое значение уходит в undo log.

Со стороны Python назовите главную ловушку, lost update: прочитали значение, посчитали в коде, записали, а параллельный запрос сделал то же самое. Лечится вычислением в самом запросе (SET views = views + 1, для списания условие остатка в WHERE), в Django через F(), либо select_for_update(), либо оптимистичной блокировкой по колонке version. Сколько строк затронуто, смотрят в cursor.rowcount, а RETURNING отдаёт новые значения без второго запроса.

Где спотыкаются

Забывают про транзакции в драйвере. В psycopg по умолчанию автокоммит выключен, и без commit() изменения пропадают при закрытии соединения.

Используют QuerySet.update() в Django и удивляются, что не вызвались save() и сигналы pre_save/post_save, а поле с auto_now не обновилось.

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