Старые песни о главном. Промурыжил много тем и тут и в гугле, но общая картина складывается какая то странная. Чтобы не обсуждать абстрактно, приведу пример (вырожденный, потом обобщу)

Есть таблица остатков (скажем денсредств) и 2 потока каждый из которых читает:
SELECT sum FROM account WHERE key = 5, допустим сумма равна 3-м, на сервере приложений добавляет к 3, свою сумму, скажем первый добавляет 2, второй добавляет 3, и пытается записать результат UPDATE account SET sum = result WHERE key=5. (правильный ответ, если что : 3+2+3 = 8). Предположим они идут параллельно, первый считал - второй считал - первый записал - второй записал.

Итак что будут делать (по умолчанию (!)) с repeatable read каждая из СУБД:

1. MS SQL на сколько я понимаю на первое чтение будет 3 и повесит S блокировку, на второе чтение будет 3 и повесит S блокировку - на первой записи значения 5 попытается конвертить S блокировку в X и повиснет, так как на втором чтении висит S блокировка, на второй записи значения 6 попытается конвертить S в X, также повиснет, после чего один из процессов пометится как deadlock (допустим 2-й), кинет exception, и попросит сервер приложений повторить 2-ю транзакцию, в итоге сначала запишется 5, при повторе запишется 8 все ОК.

2. PostgreSQL и Firebird - первое чтение будет 3, второе чтение будет 3, первая запись будет 5 и пройдет, вторая запись увидит что значение изменилось с начала транзакции и кинет exception, и "попросит" сервер приложений повторить, тот заново начнет транзакцию и запишет 8 - все ОК.

3. Самое веселое в Oracle, который как я понимаю сначала запишет 5, а потом запишет 6 и будет думать что все ОК. Хотя это не так.

Теперь замечания:

a) я так понимаю для 2 и 3 можно повторять поведение 1 при помощи SELECT FOR UPDATE, правда там будет U блокировка, то есть уже второе чтение повиснет и предотвратит deadlock,
ну или SELECT FOR SHARE - тогда поведение будет точь в точь как в пункте 1.
б) у 1 я так понимаю без "ручной" работы с timestamp'ом повторить 2-е поведение автоматически нет возможности. Хотя как я понимаю нет особой разницы какой exception будет deadlock или update conflict (я так понимаю второй все же чаще бывает, но ресурсы на блокировки зато не тратятся)
в) у 3 я так понимаю можно включить TIL SERIALIZABLE и он тогда будет вести себе как 1 или как SELECT FOR UPDATE в пункте а)

Понимаю что где-то заблуждаюсь (а может и везде), поэтому и спрашиваю в каком пункте я не прав.

ЗЫ: Да, я в курсе что UPDATE account SET sum = sum + 2 WHERE key = 5, UPDATE account SET sum = sum + 3 WHERE key = 5, даже при READ COMMITED дадут 8 (кстати во втором случае да или нет?), но тут вопрос что вычисление может быть сложнее, требовать что-то еще чем просто суммирование, поэтому этот пример привел чтобы не усложнять.

И еще такой вопрос:

Если есть SELECT x FROM t1 LEFT JOIN (SELECT SUM(f),j FROM t2 GROUP BY j) Q ON Q.j = t1.x, в блокировочниках (и SELECT FOR UPDATE), S-блокироки повесятся на всю таблицу t2, или только на те у которых j из таблицы t1. Потому как это получается зависит от построенного плана выполнения, если решит сделать predicate push down - блокировки будут только на нужные данные, если нет то на всю таблицу?

ScareCrowстудент учи что такое транзакции,

Очень ценное замечание. Чтобы я без него делал даже не знаю...

Кстати если кто не понял расширю процесс:

первый начал транзакцию - второй начал транзакцию - первый считал - второй считал - первый записал - второй записал - первый коммитнул транзакцию - второй коммитнул транзакцию

Мимо пробегал...Логика железная, кстати. Вспоминаю "Ералаш", там где 13*7=28.

Еще одно ценное замечание. Очень полезный форум. Надо почаще писать :)

pkarklinавторб) у 1 я так понимаю без "ручной" работы с timestamp'ом повторить 2-е поведение автоматически нет возможности. Хотя как я понимаю нет особой разницы какой exception будет deadlock или update conflict (я так понимаю второй все же чаще бывает, но ресурсы на блокировки зато не тратятся)

Неправильно понимаете. У 1 версионный режим существует аж с 2005 года.

Это да, я уже тоже заметил... А он как 3 или как 2 работает? В смысле кидает update conflict exception или просто перезаписывает (ну или там минимум \ максимум и т.п. как в oracle strams)...

pkarklinNitro_Junkie,

Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Otherwise, locks are not acquired on data until the data is to be modified. When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated . The update conflict is handled by the Database Engine and there is no way to disable the update conflict detection.

То есть я так понимаю работает как 2? Тогда зачет мелкософту :)

Nitro_Junkie,

Правда как 3 он работать не умеет, судя по фразе
автор there is no way to disable the update conflict detection

Другое дело зачем oracle так резолвит update conflict'ы мне не до конца понятно.

ScareCrowа как вы предлагаете их резолвить при оптимистичной блокировке?

Кидать exception. Ну иметь этот вариант точно, потому как просто всегда записывать последнее это жесть...

kdvScareCrowа как вы предлагаете их резолвить при оптимистичной блокировке?
в Firebird/InterBase, в версионнике, snapshot может попытаться поменять измененные с момента его стартат данные, только если они были отменены по rollback.
То есть,
Tran1 - snapshot start
Tran2 - update X
Tran1 - update X - получаем lock conflict

Если Tran2 сделает Rollback, то Tran1 может повторить update
Если Tran2 сделает Commit, то Tran1 эту запись не обновит в принципе, потому что она "не видит" изменения , сделанного Tran2, в силу своего уровня изолированности. А раз не может видеть (видит только то, что было до действий Tran2), значит не может и изменить.
И перешибать значение 3 на что-то свое, зная, что там уже это значение изменено и закоммичено - как бы моветон.
Snapshot это как бы snapshot и есть. Другое дело, что стандартный RepeatableRead позволяет видеть фантомы...

Кстати да в, описанном мной примере, update conflict кидается не сразу, а по завершении 1-й транзакции, в надежде что эта транзакция rollback'ся хотя это как-то ИМХО чересчур оптимистично (в среднем процент rollback'ых транзакций не сильно большой), можно было бы и сразу кинуть. Я просто опустил этот момент, чтобы не усложнять ситуацию, и считал что транзакция начинается при первом чтении и коммитится сразу после записи.

А что касается фантомов, то, по идее, можно придумать (правда мегаизвращенный случай) когда его и в serializable'е можно получить.

ScareCrowIvan Durakпропущено...

ну постгря же подругому резолвит, причем тоже оптимистически
эм?
http://www.postgresql.org/docs/9.3/static/transaction-iso.html
авторIf the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message

Чего же вы дальше не процитировали?:

But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message

ERROR: could not serialize access due to concurrent update

because a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.

Dimitry Sibiryakov,

Так все таки, а в Оракле что обычно делают?

SERIALIZABLE включают? Ручные блокировки FOR UPDATE используют? Смотрят на системную колонку версии ряда ?

Dimitry SibiryakovNitro_JunkieТак все таки, а в Оракле что обычно делают?
Говорят "фу, что это у вас за update conflict-ы, у нас вот такого дерьма нет".


:) . А если серьезно? Им что жалко было такой режим сделать, для версионника это же не проблема? С учетом :

Oracle has chosen the harder-to-implement but infinitely more concurrent multi-versioning scheme.

ЗЫ: Правда при этом описание repeatable read прерывается, а до этого были только лучи ненависти к deadlock'а. И нигде описание что oracle будет делать в том случае, когда блокировочник кинет deadlock. Пойти что ли на oracle'овски подфорум, или меня там ногами запинают? :)

Dimitry SibiryakovNitro_JunkieА если серьезно?
Отрывают руки тем, кто пишет "а=5" вместо "а=а+2".


А если мне нужно какую то более сложную обработку, которая в один запрос не укладывается сделать? Или по их мнению таких не бывает?

DarkMasterNitro_Junkie,

В процедуру оберни - она тоже внутри транзакции крутится.

Причем тут процедура? если у меня одним запросом читается, а вторым пишется (с учетом считанных данных), как в примере, чем это мне поможет? У меня и так все "в транзакции".

Dimitry SibiryakovNitro_JunkieИли по их мнению таких не бывает?
Именно так. Потому вопрос "как сделать ХХХХ одним запросом" так часто задаётся.


Ясно. "Мы успешно решаем проблемы, которые сами себе создаем".

MasterZivNitro_Junkie,

Итак что будут делать (по умолчанию (!)) с repeatable read каждая из СУБД:

Описанная тобой транзакция должна работать на уровне 0, меньше read uncommitted, поэтому что это модификация данных, и если данные при чтении не блокировать, то будет worm hole на любом уровне изоляции.

То, что ты описал, есть как раз тривиальный случай несериализуемой транзакции, как раз и описывающей минимальный уровень изоляции 0, который все субд должны поддерживать.

Рекомендую кстати почитать про теме статью незабвенного Грея и его коллег, под названием «критика уровней изоляции транзакций ANSI».
Статья есть в интернете и на русском тоже, на citforum точно валяется.

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

Статья неплохая, но непонятно что она меняет. Да я понимаю, что в СУБД, идет компромисс между :
1. Производительностью (параллельностью)
2. Целостностью (последовательностью)
Они предлагают расширить уровни изоляции и виды ошибок, ок не вопрос.

Но по их предлагаемой классификации в таблице 4 у Oracle'а когда ставится REPEATABLE READ на самом деле работает условно SNAPSHOT ISOLATION, остальные же СУБД реально работают в REPEATABLE READ. Вопрос какого хрена они не сделали это хотя бы опционально? Это же не сложно.

А то что скажем термин REPEATABLE READ не удачный, что СУБД его трактуют по разному (точнее одно СУБД называемое Oracle), и что можно ввести еще несколько уровней и точнее определить блокировки это понятно. Просто непонятно нахрена такая детализация нужна?
Сейчас READ UNCOMMITED - NoSQL, READ COMMITED - целостность в рамках транзакции на совести разработчика (крутись как хочешь), REPEATABLE READ - целостность в 95%, SERIALIZABLE - целостность в 99.99 (а может 100), только очень четко должно быть все спроектировано, чтобы по производительности вытянуло.

Уровень SNAPSHOT ISOLATION - видимо нужен специально для Oracle. А CURSOR STABILITY только для курсоров которые далеко не все используют (и вообще немного из другой оперы так как вводит доп абстракцию курсоры, я так могу ввести еще абстракцию сервер приложений и еще с десяток уровней изоляции придумать).

Про уровень 0 не понял. Это же даже по их классификации грязнее чтения чем просто Dirty Read, причем тут он?

mikronNitro_Junkie,

Как бы надуманная проблема для Оракла. Можно написат.
UPDATE account SET sum = 5 WHERE key = 5 AND sum = 3

Что, серьезно поможет? В каком уровне изоляции REPEATABLE READ?

softwarerNitro_JunkieНо по их предлагаемой классификации в таблице 4 у Oracle'а когда ставится REPEATABLE READ на самом деле работает условно SNAPSHOT ISOLATION, остальные же СУБД реально работают в REPEATABLE READ. Вопрос какого хрена они не сделали это хотя бы опционально? Это же не сложно. А то что скажем термин REPEATABLE READ не удачный, что СУБД его трактуют по разному (точнее одно СУБД называемое Oracle),
К сожалению, в мире полно идиотов, и для того, чтобы ими управлять, принимаются "политические" решения. Одним из таких политических вопросов являются стандарты ANSI и следование им. Вопрос "СУБД такая-то поддерживает стандарт ANSI SQL такой-то" является политическим; не меняя ничего важного, он многое меняет в решениях, которые принимают идиоты. Поэтому в СУБД делаются различные дурацкие движения для якобы поддержки стандарта, и когда принимаются стандарты, бушуют войны на тему "как бы сформулировать стандарт так, чтобы мы его поддерживали, а конкуренты - нет". В результате появляются мертворождённые уродцы, согласно которым куча существующих СУБД одинаково хреново, но поддерживает этот бессмысленный "стандарт".

Я это понимаю, и в чем то согласен. Но млин Oracle там настроек делает вагон и маленькую тележку, что им мешает просто при update'е сверить версию записи с версией начала транзакции и если она больше кинуть exception. Я же не автоматический блокировочник от них хочу, а одну маленькую штуку которая с очень большой вероятностью обеспечит мне поддержку одного из самых популярных конфликтов.

MasterZivNitro_Junkie,

Про уровень 0 не понял. Это же даже по их классификации грязнее чтения чем просто Dirty Read, причем тут он?


А, значит главное ты так и не понял.
0 - это согласованная запись, минимальный уровень изоляции, который только возможен, если хочешь соблюдать транзакционность.

Я также не понял, что ты в итоге хочешь, понять уровни изоляции, или разобраться , как решать проблему в том конкретном случае, который ты привел.

И как мне минимальный уровень изоляции поможет решить проблему, которую я привел?

SELECT FOR UPDATE расставлять? Так это я с любым уровнем изоляции могу делать (даже READ COMMITED).

mikronNitro_JunkieЧто, серьезно поможет? В каком уровне изоляции REPEATABLE READ?
для READ COMMITTED а для SERIALIZABLE уже не нужно. А других уровней у оракла (10.2) я не знаю.

Так в приведенном случае с READ COMMITED она сначала запишет 5, а во втором ничего не запишет, и останется 5, что тоже неправильно. Или предполагается ловить rows updated и если там 0 то самому "кидать" exception. Так это ручной режим, я его могу и timestamp'ами (как и у блокировочников) решить, просто можно заколебаться это делать в каждом случае.

MasterZivЭта проблема, описанная тобой, никакими другими уровнями изоляции не решается.


Разве она не решается в 95% (а то и 100) установкой REPEATABLE READ в MSSQL (в обоих режимах), Postgres, Firebird и т.п. и перепроведением транзакции при deadlock \ update conflict'е ?

Leonid KudryavtsevНО большинство средств разработки так и поступают (например Oracle Forms, ряд ООП оберток над БД). Т.ч. обычно проблем с этим нет.

Там только в ORM проблем нет, как только начинаешь чистый SQL юзать, те же яйца. Только не надо говорит не юзайте SQL, я даже это обсуждать не буду :)

Leonid KudryavtsevТо, что сервер выдал exception, в любом случае никак не спасет - этот exception еще как-то *вменяемо* обработать нужно.
Очень даже спасет во многих случаях, я просто перезапущу транзакцию (причем могу это даже сделать невидимо для пользователя).

Позвоните Ларри, предложите ему денег. Он попросит программистов реализовать ))). Все зависит от Вас (точнее Ваших финансовых возможностей) )))

Спасибо, но я лучше тогда вместо оракла, mssql юзать (предлагать) буду, переплачивать хрен знает сколько и получать такую проблему на ровном месте. :(

MasterZivNitro_JunkieЯ это понимаю, и в чем то согласен. Но млин Oracle там настроек делает вагон и маленькую тележку, что им мешает просто при update'е сверить версию записи с версией начала транзакции и если она больше кинуть exception. Я же не автоматический блокировочник от них хочу, а одну маленькую штуку которая с очень большой вероятностью обеспечит мне поддержку одного из самых популярных конфликтов.

Э.. ммм... А если вы всё же РАЗНЫЕ поля данной записи будете менять ?
Версии будут разные, получишь ты exception, транзакцию откатишь, и всё зря ?

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