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

[pr1]

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

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"?, из документации к сожалению из-за незнания английского не совсем понимаю, в этом режиме изоляции блокируется вся таблица или только строка из выборки

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

 

[WMix]

не уверен что тебе необходим уровень serializalbe, repeatable read вполне достаточно, но for update ты забыл

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();
}

 

[pr1]

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

 

[WMix]

другая сессия выбирает другого пользователя, но тоже из этой ветки и соответственно обновляет эту ветку.

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

Насчет parent_id, я так понял имеется ввиду подготовленные запросы.

да конечно.

 

[pr1]

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

Возможно я неправ, но ведь это не решает проблему. Пример:

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

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]

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

// 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 расписано подробно и с картинками, но это все, само собой, на английском.

 

[fixxxer]

А, так, стоп, не заметил сразу вот это:

INSERT ... $parent['right_key'] + 1

Тут, кажись, надо бы share lock в селект добавить, ага. Или insert-select сделать вместо +1 со стороны php. Но думать мне лениво, так что это не точно

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

 

[WMix]

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

там нет блокировки

 

[fixxxer]

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

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

 

[WMix]

update/insert это изменения, в транзакции они и так атомарны и сокрыты, вопрос только о уровне чтения, (repeatable read) не изменится ли значение внутри транзакции