|
Задавайте вопросы, мы ответим
Вы не зашли.
Приветствую, Коллеги.
Помогите разобраться плиз.
Имеется три таблицы - таблица ОИВ (oiv) (органы исполнительной власти).
На нее ссылается таблица услуг (service) - каждая услуга закреплена за одним и только одним ОИВ.
И есть таблица талонов (request) - талон выбивается на одну и только одну услугу.
В каждом талоне есть такой параметр - hr_id - он должен быть уникальным в рамках ОИВ - и в этом самая большая беда, ибо будь он уникальным в рамках услуги все проблемы решил бы уникальный ключ на (hr_id, service_id).
В несколько потоков выбиваются талоны. Задача - чтобы у каждого нового талона был hr_id, на один больше, чем у предыдущего талона (в рамках того ОИВ, к которому косвенно относятся талоны).
Неактивен
Как-то очень хитро сочетаются подзапросы и JOIN. Наверное запрос будет более понятным, если его переписать просто через JOIN.
Мне кажется, что то, что требуется сделать - не реляционная структура. Представь себе, что услуга будет передана в другой oiv путем update services - что случится с базой (в плане уникальности для каждого oiv)? Ну раз так, то можно не изображать реляционную структуру - таблица oiv вообще не нужна, services - константная таблица, а request можно денормализовать, добавив туда поле oiv.
Интересно, что READ UNCOMMITTED дает неправильный результат. Мне казалось, что в случае неправильного результата, транзакция не должна закоммититься. Насчет SERIALIZABLE - это тоже выглядит как странное поведение (оно воспроизводится на более простой конструкции?)
Насчет deadlock, в этом случае лучше заблокировать таблицы явно внутри транзакции, тогда deadlock не будет и выполняться будут исключительно последовательно: http://webew.ru/articles/1383.webew
Неактивен
rgbeast написал:
Как-то очень хитро сочетаются подзапросы и JOIN. Наверное запрос будет более понятным, если его переписать просто через JOIN.
В виде JOIN без подзапросов я такой запрос составить не сумел.
Если без JOIN'ов, то это так:
SELECT coalesce( max(hr_id), 0 ) + 1 FROM request r WHERE r.service_id IN (SELECT id from service where oiv_id = (SELECT oiv_id from service where id = 1));
То есть выбрать максимальный hr_id среди тех услуг, который относятся к тому ОИВ, к которому относится первая услуга.
rgbeast написал:
Мне кажется, что то, что требуется сделать - не реляционная структура. Представь себе, что услуга будет передана в другой oiv путем update services - что случится с базой (в плане уникальности для каждого oiv)? Ну раз так, то можно не изображать реляционную структуру - таблица oiv вообще не нужна, services - константная таблица, а request можно денормализовать, добавив туда поле oiv.
Насчет денормализации request - да, была такая мысль, но хочется оставить это как крайний вариант ибо избыточность - раз, плюс дополнительные проверки придётся делать - два. По остальному - что oiv, что services - это не константные таблицы и связи между ними нужны обязательно. То есть талон всегда привязан к конкретной услуге, а услуга к ОИВ. Поясни пожалуйста свою идею насчет того, как не изображать реляционную структуру.
В случае переноса услуги в другой оив - вроде нет криминала в таком - продолжаем нумерацию hr в соответствии с изменившимся набором услуг в рамках ОИВ.
rgbeast написал:
Интересно, что READ UNCOMMITTED дает неправильный результат. Мне казалось, что в случае неправильного результата, транзакция не должна закоммититься. Насчет SERIALIZABLE - это тоже выглядит как странное поведение (оно воспроизводится на более простой конструкции?)
Тот же самый результат, пробовал последовательность
rgbeast написал:
Насчет deadlock, в этом случае лучше заблокировать таблицы явно внутри транзакции, тогда deadlock не будет и выполняться будут исключительно последовательно: http://webew.ru/articles/1383.webew
Через блокировки получилось, да.
Но тогда возникает вопрос - а по какому принципу надо отделять случаи, когда стоит использовать serializable-транзакцию от тех случаев, когда стоит использовать блокировки?
Неактивен
oiv_id в талонах - это вроде бы нормально. Например, в ВТБ24 талоны содержат oiv как часть номера (например, A122, B126, буква - это oiv). Кстати, ВТБ24 меня натолкнул на еще одну мысль - почему бы не сделать id вообще уникальным. Идея номеров подряд в рамках группы мне кажется как-то ортогональна реляционной модели.
Относительно реляционной структуры я имел в виду, что если реляционная модель не работает, то нужно обращаться только к одной таблице талонов. Таблицу services использовать как справочник вне транзакции. oiv придется добавить к таблонам, конечно.
deadka написал:
В случае переноса услуги в другой оив - вроде нет криминала в таком - продолжаем нумерацию hr в соответствии с изменившимся набором услуг в рамках ОИВ.
Здесь я вижу проблему. Пусть услуга 1 была в ОИВ 1 и для нее были талоны 1,2,3,4,5, а услуга 2 была в ОИВ 2 и для нее существовали талоны 1,2,3. Делаем UPDATE services SET oiv=2 WHERE id=1 и получаем для ОИВ 2 талоны 1,1,2,2,3,3,4,5 - с очевидными повторами.
Про блокировки см. специальное правило для блокировки таблиц, которые используются несколько раз:
http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html
You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:
mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t AS t1;
Неактивен
rgbeast написал:
oiv_id в талонах - это вроде бы нормально. Например, в ВТБ24 талоны содержат oiv как часть номера (например, A122, B126, буква - это oiv). Кстати, ВТБ24 меня натолкнул на еще одну мысль - почему бы не сделать id вообще уникальным. Идея номеров подряд в рамках группы мне кажется как-то ортогональна реляционной модели.
Спасибо, об этом надо хорошо поразмыслить...
rgbeast написал:
Относительно реляционной структуры я имел в виду, что если реляционная модель не работает, то нужно обращаться только к одной таблице талонов. Таблицу services использовать как справочник вне транзакции. oiv придется добавить к таблонам, конечно.
Понял. То есть создать поле oiv_id в таблице request и создать уникальный ключ на (oiv_id, hr_id)?
rgbeast написал:
deadka написал:
В случае переноса услуги в другой оив - вроде нет криминала в таком - продолжаем нумерацию hr в соответствии с изменившимся набором услуг в рамках ОИВ.
Здесь я вижу проблему. Пусть услуга 1 была в ОИВ 1 и для нее были талоны 1,2,3,4,5, а услуга 2 была в ОИВ 2 и для нее существовали талоны 1,2,3. Делаем UPDATE services SET oiv=2 WHERE id=1 и получаем для ОИВ 2 талоны 1,1,2,2,3,3,4,5 - с очевидными повторами.
Да, есть такая проблема.
Про блокировки я подредактировал сообщение уже после твоего ответа, в итоге так и сделал - при указании блокировок указывал одну таблицу подряд два раза с разными алиасами. Но вопрос про непонятно поведение транзакций (даже в рамках упрощенной модели с двумя таблицами - request и service) - все равно остаётся.
Неактивен
Про транзакции SERIALIZABLE - приведенный пример похож на багу. После того, как дедлок разрешается и вторая транзакция отваливается, первая должна продолжаться, а не выпадать по таймауту. Это бага дедлок-резолвера - одну транзакцию убил, но ситуацию почему-то не разлочил.
С другой стороны понятно почему явные блокировки здесь лучше, чем автоматические. SERIALIZABLE накладывает автоматические блокировки на таблицы, но делает это не сразу на все, которые нужны, а по мере выполнения запросов (так как он не знает какой запрос будет следующим).
Неактивен
SELECT id from service where oiv_id = (SELECT oiv_id from service where id = 1)
Вот эта штука и является причиной DEADLOCK. Транзакция сама себя держит. Если
уж используешь SERIALIZABLE — ожидай блокировок на строки при чтении.
Что касается обеспечения уникальности — ты пытаешься сделать генератор странным
способом через объединение таблиц. Может, сделать отдельную таблицу-генератор?
Неактивен
paulus, спасибо за ответ. Не вполне понял про отдельную таблицу генератор - поясни пожалуйста, какую именно создать таблицу и как ей пользоваться (при том, что одни и те же действия могут идти с разных рабочих мест операторов).
Неактивен
paulus написал:
Вот эта штука и является причиной DEADLOCK. Транзакция сама себя держит.
Не совсем понял этот момент - в случае отсутствия второй транзакции deadlock не возникает. То есть все таки deadlock между двумя транзакциями, а не внутри одной.
Неактивен
Что-нибудь такое:
CREATE TABLE generator (selector INT NOT NULL, id INT NOT NULL, PRIMARY KEY(selector, id)); INSERT INTO generator VALUES (@oiv_id, 0); UPDATE generator SET id = id + 1, @next_id := id WHERE selector = @oid_id;
Неактивен
ok, с самоблокировкой и правда погорячился 
Попробовал создать таблички, как написано сверху. У меня не воспроизводится
бага. Т.е. первая транзакция дорабатывает как положено.
Неактивен
У меня тоже не воспроизводится. Deadlock резолвится, вторая транзакция откатывается и а первая транзакция сразу завершается.
Версия: 5.5.22-log MySQL Community Server (GPL)
Сам deadlock связан с тем, что первый select блокирует IN SHARED MODE таблицу request. Это происходит в обоих транзакциях - shared mode блокировка может быть наложена параллельно, но при этом update невозможен и ждет освобождения блокировки второй транзакцией. Но во второй транзакции тоже идет update, который ждет освобождения shared lock первой транзакцией - это классический deadlock.
Неактивен
