Mysql Вопрос про конкурентные запросы

pr1

Новичок
Здравствуйте, просьба объяснить один момент. Допустим есть код, который добавляет новую категорию.

PHP:
try {

    $pdo->query('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE');
  
    $pdo->beginTransaction();

    $parent = $pdo->query('SELECT right_key FROM categories WHERE id =  '.$parent_id)->fetch();

    $pdo->query('UPDATE categories SET left_key = left_key + 2, right_key = right_key + 2 WHERE left_key > '.$parent['right_key']);

    $pdo->query('UPDATE categories SET right_key = right_key + 2 WHERE right_key >= '.$parent['right_key'].' AND left_key < '.$parent['right_key']);
 
    $pdo->query('INSERT INTO categories (left_key, right_key) VALUES ('.$parent['right_key'].', '.$parent['right_key'] + 1 .')');

    $pdo->commit();

    return true;

} catch (PDOException $e) {

    $pdo->rollBack();

}
Меня интересует момент, когда одновременно несколько сессий, пытаются создать новую категорию. Я правильно понимаю, что при включении уровня изоляции SERIALIZABLE, таблица на время будет заблокирована на изменение и остальные сессии будут в очереди дожидаться commit или roolback, т.е таким образом решается проблема с параллельными транзакциями?
 
Последнее редактирование:

AnrDaemon

Продвинутый новичок
Вас не затруднит уточнить, какую надуманную проблему вы пытаетесь таким странным способом решить?
 

pr1

Новичок
Вас не затруднит уточнить, какую надуманную проблему вы пытаетесь таким странным способом решить?
Это не решение существующей проблемы. Хотел бы узнать как решаются проблемы с race condition. В данном случае при вставке новой строки, как гарантированно избежать повреждения данных, если несколько сессий одновременно попытаются обновлять ветки.
 

AnrDaemon

Продвинутый новичок
Какой ещё race condition, о чём вы вообще?…
Я задал простой вопрос. Ответьте на него.
Если ответа нет, то вывод очевиден - вы маетесь дурью и вам в детский сад.
 

AnrDaemon

Продвинутый новичок
В данном случае при вставке новой строки, как гарантированно избежать повреждения данных
Чо?… Что за бредятина? Какое повреждение данных при вставке новой строки?
Вы вообще теорию реляционных БД курили или учили?
 

pr1

Новичок
AnrDaemon ты че такой злой? Успокойся. Я ведь написал, вопрос не в существовании какой-то реальной проблемы. В первом посте привел простой пример. Таблица хранится в виде nested sets, поэтому при вставке новой записи, нужно обновлять ветки. Вопрос в том, как в таких запросах решаются проблемы, когда несколько сессий одновременно пытаются обновлять ветки в этой таблице?

Например является ли решением, включать "TRANSACTION ISOLATION LEVEL SERIALIZABLE"?, из документации к сожалению из-за незнания английского не совсем понимаю, в этом режиме изоляции блокируется вся таблица или только строка из выборки
PHP:
$parent = $pdo->query('SELECT right_key FROM categories WHERE id =  '.$parent_id)->fetch();
 

WMix

герр M:)ller
Партнер клуба
не уверен что тебе необходим уровень serializalbe, repeatable read вполне достаточно, но for update ты забыл
PHP:
try {

//    $pdo->query('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE');
    $pdo->beginTransaction();
// '.$parent_id.' говорит мне о том что pdo ты не понял
    $parent = $pdo->query('SELECT right_key FROM categories WHERE id =  '.$parent_id.' FOR UPDATE')->fetch();
    $pdo->query('UPDATE categories SET left_key = left_key + 2, right_key = right_key + 2 WHERE left_key > '.$parent['right_key']);
    $pdo->query('UPDATE categories SET right_key = right_key + 2 WHERE right_key >= '.$parent['right_key'].' AND left_key < '.$parent['right_key']);
    $pdo->query('INSERT INTO categories (left_key, right_key) VALUES ('.$parent['right_key'].', '.$parent['right_key'] + 1 .')');
    $pdo->commit();
    return true;
} catch (PDOException $e) {
    $pdo->rollBack();
}
 
  • Like
Реакции: pr1

pr1

Новичок
@WMix Разве FOR UPDATE не блокирует только строку из выборки? Т.е есть вероятность, что другая сессия выбирает другого пользователя, но тоже из этой ветки и соответственно обновляет эту ветку.
Насчет parent_id, я так понял имеется ввиду подготовленные запросы. Я это знаю, просто код написал для примера, без всяких prepare, bindValue и т.д.
 

WMix

герр M:)ller
Партнер клуба
другая сессия выбирает другого пользователя, но тоже из этой ветки и соответственно обновляет эту ветку.
ну поэтому repeatable read и FOR UPDATE, да, заблокирует сточку до commit, но acid будет соблюден, ну или deadlock!.. и там нужно внимательней думать
Насчет parent_id, я так понял имеется ввиду подготовленные запросы.
да конечно.
 
  • Like
Реакции: pr1

pr1

Новичок
ну поэтому repeatable read и FOR UPDATE, да заблокирует сточку до commit, но acid будет соблюден, ну или deadlock!.. и там нужно внимательней думать
Возможно я неправ, но ведь это не решает проблему. Пример:

PHP:
// Две сессии одновременно добавляют новую категорию

SELECT right_key FROM categories WHERE id = 1 FOR UPDATE; /* Сессия 1 блокирует строку id 1 */

SELECT right_key FROM categories WHERE id = 2 FOR UPDATE; /* Сессия 2 блокирует строку id 2 */

// id 1 и id 2 находятся в одной ветке, соответственно эта ветка будет обновляться одновременно
 

WMix

герр M:)ller
Партнер клуба
в данной сессии (подразумевается сессия базы данных от connect до disconnect, вероятнее всего для одного http запроса) взяты две записи с пожеланием изменить (for update), все остальные сессии ждут пока запись изменится, чтобы если прочитать, это было повторяемым (repeatable).
внутри нашей сессии эти записи не заблокированы.

PHP:
// id 1 и id 2 находятся в одной ветке, соответственно эта ветка будет обновляться одновременно
это ничего не значит, кроме что для всех остальных эти записи в обновлении
 

pr1

Новичок
в данной сессии (подразумевается сессия базы данных от connect до disconnect, вероятнее всего для одного http запроса) взяты две записи с пожеланием изменить (for update), все остальные сессии ждут пока запись изменится, чтобы если прочитать, это было повторяемым (repeatable).
внутри нашей сессии эти записи не заблокированы.

это ничего не значит, кроме что для всех остальных эти записи в обновлении
Нет в выше приведенном примере две разные сессии, для добавления новой категории, одновременно запрашивают правый ключ родителя. Сессия 1 запрашивает родителя с id 1, Сессия 2 запрашивает родителя с id 2. Я просто не понимаю, как же Сессия 2 будет ждать завершения транзакции Сессии 1, если Сессия 1 заблокировала строку с id 1, а не id 2.
 

AnrDaemon

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

fixxxer

К.О.
Партнер клуба
Serializable тут не нужен (в mysql serializable это то же самое, что repeatable-read, только все select-ы идут автоматически с шаред локом).
Достаточно дефолтного repeatable-read. Обновляемые строки будут заблокированы, на инсерт будет gap lock.
Последний insert меня смущает только в смысле возможного unique constraint violation, если там есть уникальные индексы где-то. Дедлоков тут вроде не должно быть, если нет других операций записи в ту же таблицу.

из документации к сожалению из-за незнания английского не совсем понимаю
Настоятельно советую учить. Вот я бы мог дать тебе ссылки, где про уровни изоляции в mysql расписано подробно и с картинками, но это все, само собой, на английском.
 
  • Like
Реакции: pr1

fixxxer

К.О.
Партнер клуба
А, так, стоп, не заметил сразу вот это:

INSERT ... $parent['right_key'] + 1
Тут, кажись, надо бы share lock в селект добавить, ага. Или insert-select сделать вместо +1 со стороны php. Но думать мне лениво, так что это не точно :)

Вообще я бы открыл две mysql-консоли параллельно и поигрался.
 

fixxxer

К.О.
Партнер клуба
блокировку сделает update. поскольку там a=a+2, то один хрен в каком порядке

а вот с инсертом вопрос :)
 

WMix

герр M:)ller
Партнер клуба
update/insert это изменения, в транзакции они и так атомарны и сокрыты, вопрос только о уровне чтения, (repeatable read) не изменится ли значение внутри транзакции
 
Сверху