Транзакции с инсертами и агрегирующими функциями: блокировки и изоляция не помогут?

 
 
 
Сообщения:511
Представим ситуацию:
  • есть таблица с целочисленное полем value
  • есть код, выполняющий внутри транзакции агрегирующую функцию sum по полю value
  • в коде выполняется определенная логика в зависимости от суммы: на вход подается аргумент для поля value, если сумма с аргументом меньше 450, то добавить новую строку, иначе бросить эксепшн

Допустим:
  • в таблице две строки со значениями 100 и 200 в поле value
  • стартуют две транзакции, на вход каждой подаем аргумент 100
  • обе транзакции параллельно считают сумму 300 и отдают в код
  • в обоих транзакциях условие выполнено (сумма + аргумент < 450) и каждая пишет новую строку со значением value 100

В итоге получаем в таблице 5 строк с суммой 500 в поле value, т.е. условие чтобы сумма была меньше 450 нарушено.

Блокировки тут не помогут, как на уровне базы, так и с помощью доп. поля version, т.к. в данном случае мы не апдейтим записи, а инсертим новые. Уровень иозоляции serializable тут тоже не поможет, т.к. он не будет лочить вычисление суммы в начале каждой транзакции. Хоть обе транзакции и не увидят заинсерченные строки в других транзакциях, в БД они все-же будут.

Решения:
1. средствами синхронизации в самом приложении
2. путем добавления некой технической таблицы в которой в обязательном порядке в каждой транзакции апдейтить одну и ту же строку, с применением блокировки, что-то вроде мьютекса
3. в начале транзакции лочить конкретную существующую запись в таблице, тот же мьютекс, только без технической таблицы

Все решения похожи на воркэраунды, может я упустил еще какие-то более правильные варианты решения проблемы?

Изменен:21 дек 2016 11:48
 
 
Сообщения:2361
Уровни изоляции как раз таки должны помочь, зависит от вашей базы конечно, Вы кстате не написали что используете. Вот например для PostgreSQL рассмотрен именно ваш случай 13.2. Transaction Isolation в разделе 13.2.3. Serializable Isolation Level.

3-й вариант не является костылём, вполне логично сделать SELECT FOR UPDATE для сущности более высокого уровня для соблюдения иварианта при модификации подчиненных сущностей, такой подход понятен и очевиден любому программисту.

Ну и как 4-й вариант всегда есть денормализация, тобишь хренение суммы в явном виде.
 
 
Сообщения:511
PostgreSQL обозначили данную проблему как Serialization Anomaly. Они же видимо и единственные кто данную проблему решили в своей реализации Serializable. Сделали некий мониторинг на возможность возникновения аномалии и бросают эксепшн если есть вероятность. У меня к сожалению Oracle, он такого не умеет.
Ну чтож, тогда либо буду лочить что-то строковое в транзакциях, либо вообще в JVM засинхронизую.

Изменен:22 дек 2016 13:58
 
 
Сообщения:142
почему оракл не умеет? lock table отменили?

 
 
Сообщения:511
Потому что на уровне изоляции serializable он не будет следить за тем выполняются ли транзакции конкурентно или нет. True-serializable транзакции получается только в постгресе, в остальных БД псевдо-serializable.
Про LOCK TABLE была мысля, но смущает что LOCK TABLE не запрещает читать из таблицы, даже для exclusive mode.
Quote:
A lock never prevents other users from querying the table.

Можно попробовать поставить LOCK TABLE в начале каждой конкурентной транзакции. Вопрос в том будет ли транзакция стопится сразу в момент вызова LOCK TABLE, если другая уже выполнила лок таблицы или же она застопится только в момент вызова первого insert/update/delete. Надо будет проверить.

Update: проверил, стопится сразу, это будет попроще чем добавлять таблицы или выискивать строки для блокировки из существующих.

Изменен:23 дек 2016 13:17
 
 
Сообщения:142
frymock:
Про LOCK TABLE была мысля, но смущает что LOCK TABLE не запрещает читать из таблицы

lock table запрещает другой lock table, остальное не имеет значения

 
 
Сообщения:511
Хотя если транзакция длинная, то предпочтительнее все-таки создать/найти верхнеуровневую сущность и сделать оптимистическую блокировку на ней. Долго держать LOCK TABLE не лучшая идея.

 
Модераторы:Нет
Сейчас эту тему просматривают:Нет