Что происходит при обновлении нескольких строк в одном запросе в базе данных

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

Понимаете ли вы, что такое атомарность одного оператора, и знаете ли, что происходит с блокировками, пока ваш UPDATE идёт. В связке с Go смотрят ещё и на обращение с транзакцией и пулом соединений.

Как отвечать

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

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

Со стороны Go:

tx, err := db.BeginTx(ctx, nil)
if err != nil { return err }
defer tx.Rollback() // после Commit вернёт ErrTxDone, это не ошибка

res, err := tx.ExecContext(ctx, `update items set status = $1 where id = any($2)`, st, ids)
if err != nil { return err }
n, err := res.RowsAffected()

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

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

Заворачивают одиночный UPDATE в транзакцию «для надёжности». Надёжности это не прибавляет, зато транзакция в Go занимает соединение целиком: забытый Rollback при раннем возврате держит его бесконечно, пул вычерпывается за минуты, и встаёт всё приложение.

Второе: не смотрят на число обновлённых строк. Запрос отработал без ошибки, условие не подошло ни к одной строке, а логика идёт дальше, считая обновление выполненным. RowsAffected стоит сравнивать с ожиданием.

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