И снова здравствуйте.

Имеется три таблицы:


В один прекрасный момент требуется обновить поле value в таблицах MEMBERS и RECORDS для некоторого id_grp. При этом они должны быть обновлены все разом, в одной транзакции. Само обновление выглядит так: считывается текущее значение, обрабатывается, на его место записывается новое, кроме того эти поля в этих таблицах зависимы друг от друга. В силу непреодолимых технических причин процесс обновления может занимать до нескольких [десятков] минут и по его завершению не обновлённые записи становятся невалидными. Соответственно, если в этот момент какой-то пользователь изменит запись или добавит новую в таблицу RECORDS с этим же id_grp (читать можно), то процесс придётся начинать сначала.

Вопрос: как правильно реализовать такое обновление?

pastoralekcvp,
select for update испробован?
Нет ещё, я вот и интересуюсь в каком направлении копать. Спасибо.

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

YuRockalekcvpЭто не помешает добавлению новых записей, как я понимаю?
Нет, конечно не помешают. Но если должно помешать - то ни update, ни select for update не помогут.
Ну в данный момент у меня тупо блокируются все три таблицы на изменение, но мне почему-то кажется что это неверный подход...

А запрос вида: update table set id = id where ... вызывает реально какие-нибудь изменения в базе или нет?

Ещё есть идея поставить в records триггер на добавление записей типа: select id from groups where id = new.id_grp with lock; и тогда добавление будет обламываться, если запись в groups уже заблокирована кем-то.

Мимопроходящий23.04.2018 17:33, alekcvp пишет:
> Ещё есть идея

способов достичь нирваны через анус превеликое множество.
но нужно ли тебе это, при нормальной ориентации?

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

Mikle83alekcvp,
Мало выходных данных:
акт вандализма разовый или периодически планируется?
Влиять на "вне субдшную" логику/алгоритмы есть возможность?
Падение производительности на время обновления критично?

Если Да, то логично для группы ввести флаг активен/нет.
Анализировать его на клиенте (?вьюхах/процедурах).

Тогда задача обновления не будет столь критичной.

Если нет, запрет реализуется через триггер: если на запись (группу?)
выставлен флаг - запрет обновления, вставки данных.

Основная цель: избежать длительной и большой транзакции.
Цена: производительность

1. Разовый, но неоднократный, в смысле изредка возникает необходимость.
2. Есть, но не хотелось бы плодить сущности в виде дополнительных таблиц. К тому же это "псевдотрёхзвенка", т.е. приложение взаимодействует только с ХП.
3. Производительность вообще не критична, желательно чтобы во время обновления можно было получить доступ к незатронутым
обновлением записям.

Флаг - это хорошо, но если во время обновления приложение будет убито по какой-либо причине? Флаг в базе так и останется, группа станет недоступна и придётся лезть в базу "руками".

YuRockalekcvpФлаг - это хорошо, но если во время обновления приложение будет убито по какой-либо причине? Флаг в базе так и останется
Флаг - это запись. Если ее удалось удалить обновить - значит, это "зависший" флаг (создввшая его транзакция его не удалила).
И как такое может быть, чтобы не было коммита, а флаг остался.
А если он создаётся внутри обновляющей транзакции, то другие пользователи его всё равно не увидят до тех пор пока транзакция не завершится - он бесполезен. Поэтому его надо создавать вне основной рабочей транзакции, потом делать commit и только потом начинать обновление данных.

В итоге сделал следующее:

1. Добавил в таблице групп поле grplock;
2. Когда пользователь запрашивает права на запись в эту группу, то это поле проверяется и если оно не null то он посылается;
3. Добавил ХП LOCK_GROUP(gid, locked), которая делает нехорошую вещь:

т.е. даже внутри транзакции она обновляет это поле и изменения видны всем. Если во время обновления данных произойдёт какой-либо exception, то она вызывается ещё раз с locked = 0, после чего транзакции делается rollback.

4. В дисконнект триггер добавил



P.S: Пишущая транзакция nowait.

YuRockalekcvpА если он создаётся внутри обновляющей транзакции, то другие пользователи его всё равно не увидят до тех пор пока транзакция не завершится - он бесполезен.Как раз увидят, если попытаются создать такую же запись. Или обновить существующую. UPDATE OR INSERT, например, упадет.
Ага, только мне нужно чтобы они и в другие таблицы не могли добавлять записи, ссылающиеся на эту.

hvladalekcvp1. Добавил в таблице групп поле grplock;Вынеси его в другую таблицу с контролем уникальности.
Смог добавить флаг - можно работать.
Не смог - занято.
Чтобы проверять зомби - добавь туда же CONNECTION ID того, кто добавил флаг.
Дальше рассказывать ?
1. Зачем в таблицу уникальности? Он же к ID группы привязан, на другие группы не влияет, а в этой его обновить не смогут, потому что чтобы обновить надо права получить, а эта функция проверяет наличие этого флага.
2. Ну так и сделал, и в триггере на дисконнект его прибиваю.

YuRockYuRockсделай триггер в каждой такой таблице, который будет делать этот UPDATE OR INSERT флагаУчти, в этом случае обновлять данные в этих таблицах сможет только одна транзакция. Смахивает на монопольное открытие dbf.
Монопольное открытие можно и через lock_write сделать, мне это как раз и не нужно.

hvladКотовасия,
ты предлагаешь удерживать тр-цию, вставившую флаг, на всё время "блокировки".
Не факт, что это приемлемо. Зависит от времени удержания "блокировки".
Вся операция, требующая блокировки, всё равно должна в одной транзакции выполняться, так что почему бы и нет?..

hvladalekcvp,
надеюсь, ты понял, что значение флага == ID той группы записей, которую нужно "блокировать" ?Ну это понятно, идея мне нравится. Но заводить для этого отдельную таблицу...
FireBird 3 поддерживает временные таблицы (которые в базу не пишутся), доступные одновременно всем подключениям?

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

Завтра действительно попробую вариант с отдельной таблицей...

AriochalekcvpНу это понятно, идея мне нравится. Но заводить для этого отдельную таблицу...

Лучше завести ОДНУ лишнюю табличку и для блокировки добавлять ОДНУ строку в неё, чем переписывать ВСЕ строки целевой группы
в твоей больше таблицы, даже если из всей группы ты потом только изменишь 1% записей.Сейчас я завёл ОДНО ПОЛЕ в таблице групп и делаю ДВУКРАТНУЮ запись в него на каждый процесс. А в варианте с отдельной таблицей, в неё будут записи (попытки записей) при КАЖДОМ изменении данных в ТРЁХ таблицах. Плюс в моём варианте [если возникнет необходимость] можно проверить блокировку даже из read_commited транзакции и не дать прочитать данные из обновляемой группы. В случае отдельной таблицы с попыткой записи - так не получится.
Ariochкто есть uid ?ID пользователя в таблице пользователей в базе. Т.е. поставить блокировку может кто угодно из тех кто имеет право писать в эти таблицы, а снять - только тот кто поставил. Ну и при дисконнекте по этому значению снимаются блокировки.
AriochalekcvpFireBird 3 поддерживает временные таблицы (которые в базу не пишутся), доступные одновременно всем подключениям?

а если бы поддерживал?

ты не хочешь создавать ещё одну обычную таблицу (со списоком блокировок), но без хочешь создать ещё одну необычную (временную) таблицу?

а в чем разница?В том, что при [аварийном] выключении сервера и при backup/restore не надо париться об этой таблице.
AriochalekcvpЕсли во время обновления данных произойдёт какой-либо exception, то она вызывается ещё раз с locked = 0, после чего транзакции делается rollback.



зачем?

Анекдот про два стакана на ночь у постели программиста?Я же показал - там внутри автономная транзакция, которая в любом случае снимает блокировку, а rollback основной транзакции отменяет изменения, которые уже успел сделать "вывалившийся" процесс.
AriochalekcvpВся операция, требующая блокировки, всё равно должна в одной транзакции выполняться
Но сама блокировка может выполняться предварительно, в отдельной транзакции (если через флаги, в отдельной таблице или в отдельном столбце). Может и так. Но мне проще всё "завернуть" в одну транзакцию.
AriochalekcvpЕсли в этот момент какой-то пользователь изменит запись или добавит новую в таблицу RECORDS с этим же id_grp (читать можно), то процесс придётся начинать сначала.
Вот это была мотивация или требование?
Что должно произойти, если пока мы рассчитываем группу №1 другое соединение пытается удалить или изменить или добавить строку в этой же группе?Ну представьте что там в записях электронные подписи, а в мемберсах - ключи для проверки. Что будет, если при смене этих подписей, кто-то добавит ещё одну запись, подписанную старым ключём (который считывается непосредственно перед подписью)? Если же обновлять сначала ключ, а потом подписи (в разных транзакциях), то кто-то в этот момент может попытаться проверить старую подпись новым ключём и будет облом.