If you process a lot of database queries in a cycle, how correct will it be to wrap each iteration in a transaction?

Question

PHP

Hello.

There is a task:
To do selection from base on a condition. Sampling should be done in chunks.
We choose together with connections.
We loop through the results. We make the necessary changes to the models, including related ones.
We save.
We use transactions.

Implementation on Laravel, but in this case it is not particularly important.

I concluded work with the entire chunk in a transaction. But I was told that this is not correct and that the processing of each record should be wrapped in a transaction.

Below is the corrected code.
I will not give a full fragment, I will describe more schematically.
This I mean, let them not be confused by the actions taken. The logic itself is important.

// Получаем данные с разбивкой на чанки.
MyModel::query()
    ->where('active', true)
    ->with('rel1', 'rel2')
    ->chunkById(200, function ($chunk) {
    // Обрабатываем чанк.
    foreach ($chunk as $record) {
        try {
            // На каждой итерации обрабатываем один элемент чанка в отдельной транзакции.
            // Если что-то идёт неправильно, ловим исключение по каждому случаю.
            $record::resolveConnection()->transaction(function () use ($record) {
                $record->rel1->value = $record->rel1->value / 100 * 5;
                $record->rel2->value = Str::random(10);
                $record->step += 1;
                $record->active = $record->step < 5;
                $record->push();
            }, 3);
        } catch (Throwable $e) {
            // Что-то делаем...
        }
    }
});

In general, this approach makes sense.
But wouldn't constant begin and commit transactions cause performance problems?

Or maybe there are some other advantages and, or disadvantages of this or that approach?

Thank you.

Answer 1

Logic depends on logic.
You have to ask yourself: if something went wrong while processing one record, should you roll back the changes associated with just that record, or with the whole chunk?
And you'll get an answer to your question
. If it doesn't matter, then the whole chunk is better.