Комментарии 35
Правило согласованности может звучать так: перевод никогда не меняет общей суммы денег на счетах (такое правило довольно трудно записать на SQL в виде ограничения целостности, так что оно существует на уровне приложения и невидимо для СУБД)
Это не совсем так. Точнее совсем не так. Просто оно будет записано не в виде ограничения (CONSTRAINT), а в виде схемы данных, где сумма фигурирует один раз и к ней так или иначе «подвязаны» два счёта. Такой, классический «транзакционный» (не в смысле СУБД, а в смысле бухгалтерского учёта) вариант. Правда при такой схеме остаток (сальдо) по счёту не хранится, а требует вычисления каждый раз, когда он потребуется.
А в статье пример игрушечный, специально упрощенный. Но на нем все довольно хорошо видно.
Неправда )
Пользовательские блокировки.
Последнее средство — вручную установить исключительную блокировку или на все нужные строки (SELECT FOR UPDATE), или вообще на всю таблицу (LOCK TABLE). Это всегда работает, но сводит на нет преимущества многоверсионности: вместо одновременного выполнения часть операций будет выполняться последовательно.
Не совсем согласен.
Ето минимальное зло. При правильном SELECT мы блокируем только нужние записи.(для примера одного клиента) И операции по другим клиентам проходят паралельно. Но следует учитивать что для всех изменений в даной таблице нужно предварительно использовать
SELECT FOR UPDATE в идеале использовать одну сторедж процедуру.
P.S. Хорошая статтья, На жаль многие разработчики не хотят вникать в такие детали БД.
Превосходно!
Прошу вас, не останавливайтесь, пишите ещё!
Скину коллегам )
В PostgreSQL достаточно средств, чтобы одним SQL-оператором решать сложные задачи. Отметим общие табличные выражения (CTE), в которых в том числе можно использовать операторы INSERT/UPDATE/DELETE, а также оператор INSERT ON CONFLICT, который реализует логику «вставить, а если строка уже есть, то обновить» в одном операторе.
Существует еще конструкция CASE WHEN THEN END, но почему то я очень давно не встречал ее упоминания в статьях (а статей разных читаю много). Я использую периодически эту конструкцию. Почему ее не упоминают? Быть может у нее есть какой-то существенный недостаток?
Владимир, спасибо на добром слове.
Недостатков у CASE я не вижу (ну, кроме многословности), прекрасная и полезная конструкция. Я ее использую совершенно автоматически; наверное, потому и не упоминают — вроде как очевидная вещь. Но в любом учебнике по SQL она есть, например в этом.
К моему удивлению, не удается найти какой-либо авторитетной информации по этим вопросам. Может быть вы прольете свет?
Еще раз спасибо!
Рад, что статья понравилась! Свет попробую пролить.
какой уровень изоляции у одного «SQL-оператора»
Уровень изоляции — это свойство транзакции, а не отдельных операторов. Можете считать, что все операторы одной транзакции имеют один и тот же уровень, установленный для этой транзакции.
являются ли запросы CTE (а также подзапросы) отдельными операторами, в том числе рекурсивные
Нет, это составные части одного и того же запроса.
Да и что будет с банальным JOIN? Например, если мы соединяем тяжелую таблицу с собой по первичному ключу, а параллельно пролетает транзакция на обновление записи в этой таблице, может ли получится так, что значения в левой и правой части результата будут разными?
Ни в коем случае. Это один оператор, он всегда* видит только согласованные данные (на момент начала оператора или на момент начала транзакции, смотря по уровню изоляции).
* Ну, не совсем всегда. В статье я привожу два случая, когда в PostgreSQL внутри одного оператора можно увидеть несогласованные данные на уровне Read Committed:
- если в запросе вызывается volatile-функция, содержащая другой запрос;
- оператор UPDATE, который перечитывает только одну строку, а не весь набор.
Никакие другие ситуации мне неизвестны.
К моему удивлению, не удается найти какой-либо авторитетной информации по этим вопросам.
Зачем вам авторитеты — проверьте сами. Вдруг и я вас обманываю? (:
Вот скажем вас join интересует. Возьмите таблицу, соедините ее саму с собой, параллельно запустите обновление и сравните результат с известным правильным.
Возьмите таблицу, соедините ее саму с собой, параллельно запустите обновление и сравните результат с известным правильным.
Я пробовал. Проблема в том, что таким образом сложно понять является ли результат случайным, особенностью реализации СУБД или поведением определенным в стандарте.
Мне попадалось несколько утверждений о том, что «одиночные» запросы страдают от тех же проблем, что и транзакции, состоящие из нескольких запросов. Правда все эти утверждения относились к SQL Server.
Вот и хочется понять, а что в запросе полностью атомарно (консистентно и изолированно). Ведь, если весь запрос, то таким образом с помощью CTE можно хорошенько «просадить» конкурентность. В то же время об уровнях изоляции думать не придется…
Стандарт не слишком много чего определяет, поэтому ориентироваться всегда надо на особенности конкретной реализации. За MS SQL не скажу, а в Постгресе весь запрос целиком видит согласованные данные.
то таким образом с помощью CTE можно хорошенько «просадить» конкурентность
Не понимаю, почему? Запрос (если мы о SELECT говорим), выполняясь, никому не мешает.
Стандарт не слишком много чего определяет, поэтому ориентироваться всегда надо на особенности конкретной реализации.
Видимо, так. Получается, что глядя на бизнес-транзакцию и уровень изоляции не получится понять будет ли приложение работать корректно. Для меня это открытие.
Не понимаю, почему? Запрос (если мы о SELECT говорим), выполняясь, никому не мешает.
В CTE можно писать запросы на изменение. Они могут быть тяжелыми. Я вижу тут 2 варианта: 1. Запросы из WITH должны интерпретироваться как шаги транзакции со всеми вытекающими. 2. Внутри СУБД нужно что-то заблокировать (причем возможно целыми площадями), чтобы предотвратить возможные коллизии с параллельными запросами.
Получается, что глядя на бизнес-транзакцию и уровень изоляции не получится понять будет ли приложение работать корректно.
Вне контекста конкретной СУБД — не получится. Ну, есть какие-то общие знаменатели, но в общем случае точно нет.
Внутри СУБД нужно что-то заблокировать
Если внутри CTE выполняются изменения, то конечно нужно. Но и если бы CTE выполнялся по шагам, тоже надо было бы блокировать. И в этом смысле разницы никакой нет, ведь блокировки снимаются только в конце транзакции (а не оператора).
Блокировкам отдельный цикл посвящен, кстати.
Но и если бы CTE выполнялся по шагам, тоже надо было бы блокировать.
Да, но по шагам блокироваться будет постепенно и с возможным разрешением коллизии через откат транзакции. А в CTE, подозреваю, все блокировки будут браться сразу и надолго. Т.е. это уже шаг в сторону последовательного выполнения транзакций, чего так не хотят вендоры СУБД, изобретая уровни изоляции.
Вот кстати есть формальный способ записи истории транзакций буквами r1(x), w1(x), c1, a1 (в книге у вас на сайте он тоже используется). Я сейчас подумал, что непонятно как записывать self-join, CTE, "set value = value + 1" с помощью такой нотации. Нет ли тут проблемы с формализацией?
Извините, что так много вопросов. Но раз уж начал спрашивать… Отдельный цикл про блокировки тоже буду читать, спасибо. Я, наверное, еще не скоро разберусь. :)
А в CTE, подозреваю, все блокировки будут браться сразу
Нет, тоже постепенно.
Вот кстати есть формальный способ записи истории транзакций буквами r1(x), w1(x), c1, a1
Есть. Я его сознательно не стал тут приводить, чтобы не уводить разговор совсем в сторону теории. Почему? См. ниже.
непонятно как записывать self-join, CTE, "set value = value + 1" с помощью такой нотации. Нет ли тут проблемы с формализацией?
Есть такая проблема. Теория использует простую модель с элементарными операциями. Как эти операции сгруппированы по SQL-операторам, теорию не интересует.
Вот давайте посмотрим на пример потерянного обновления, про который вы пишите ниже:
r1(x) r2(x) w1(x) w2(x)Пара операций r1(x) w1(x) подразумевает, что вы читаете объект x (допустим, строку таблицы), а потом записываете что-то в этот же x. Что именно — теория никак не определяет.
Если мы реализуем эти операции так, как в том примере (https://github.com/ept/hermitage/blob/master/postgres.md#lost-update-p4):
begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from test where id = 1; -- T1
select * from test where id = 1; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 11 where id = 1; -- T2, BLOCKS
commit; -- T1. This unblocks T2, so T1's update is overwritten
commit; -- T2то мне непонятно, что именно мы тут потеряли? Оба оператора UPDATE записывают в x фиксированное значение — их в принципе не интересует, что в x было до этого.
Но если мы переделаем этот пример вот так:
...
update test set value = value + 11 where id = 1; -- T1
update test set value = value + 11 where id = 1; -- T2, BLOCKS
...то увидим, что потери не случится: второй оператор UPDATE перечитает строку после того, как разблокируется.
Но вообще по-моему лучше по-другому интерпретировать эту запись. Я бы считал, что пара r1(x) w1(x) соответствует одному оператору
update t set x = f(x);потому что такой оператор как раз сначала читает x, а потом записывает в него что-то на основе полученного значения.
Но так или иначе — результат один. Если у нас Read Committed, то мы не теряем обновление, но (поскольку перечитываем только одну строку) можем получить несогласованное чтение. А если Repeatable Read (или Serializable), то транзакция просто обрывается во избежание проблем.
Это два разных подхода.
В случае Read Committed мы предпочитаем получить аномалию, и никогда не обрываем из-за этого транзакции. Задача обеспечения согласованности ложится на плечи разработчика приложения.
В случае Serializable (и отчасть Repeatable Read) мы предотвращаем потенциальные проблемы тем, что обрываем транзакции, и приложение должно их просто повторять.
Ну вот, много написал, но не уверен, что что-то прояснил (:
то мне непонятно, что именно мы тут потеряли? Оба оператора UPDATE записывают в x фиксированное значение — их в принципе не интересует, что в x было до этого.
Почему не интересует? Это могут быть два человека-оператора, сидящих каждый за своим терминалом и вручную обновляющих записи. Как условный пример — инкрементируют счетчик. Сначала оба прочитали 10, записали 11. А должно быть в результате 12.
Я бы считал, что пара r1(x) w1(x) соответствует одному оператору
Мне кажется это уже вольная трактовка. Которая кстати автоматически означает, что операция инкремента "set value = value + 1" не атомарна (не изолирована).
Ну вот, много написал, но не уверен, что что-то прояснил (:
Да, прояснили. :) Пока вывод такой: раз есть проблема с формализованным представлением SQL-запроса в виде элементарных операций чтения и записи, то невозможно понять из скольки операций он состоит и где могут произойти коллизии. А из этого автоматически следует, что пытаться найти в нем аномалии бесполезно. Как и оперировать ими на практике. В принципе у меня было такое подозрение, но я всегда думал, что я что-то пропустил.
Тем не менее остается целых три варианта:
- Использовать Serializable (если СУБД позволяет).
- Использовать Read Committed с ручными блокировками.
- Читать руководство по уровням изоляции конкретной СУБД, где написано что и как видят запросы и что в какой момент блокируется.
(Лучше, наверное, в обратном порядке)
Добавлю еще немного про аномалию Lost Update. Я, конечно, уже запутался, перебирая разные источники в поисках ответов. Но кажется, что lost update — это самая аномальная аномалия — она то появляется то исчезает в зависимости от источника.
Например, в документации PostgreSQL она не упоминается совсем, даже в таблице ссылающейся на стандарт:
The SQL standard and PostgreSQL-implemented transaction isolation levels are described in Table 13.1.
Вот что, собственно, пишет сам стандарт об уровнях изоляции (ISO/IEC 9075-1:2011(E)), в котором транзакциям посвящены целых пол-страницы:
Every isolation level guarantees that no update is lost.
Не уверен, что речь идет именно об аномалии Lost Update, потому что упоминания других аномалий, как и слов anomaly и phenomena в документе найти не удалось.
Зато попалась на глаза статья от Microsoft. Вот выдержка из нее:
A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.
The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.
Думаю, на этом я свои изыскания в области аномалий закончу...
Спасибо вам за ответы!
Ну, первый-то сценарий совсем клинический.
А так мне кажется, что интерпретации зависят от того, считаем мы r и w одной SQL-операцией (UPDATE), или двумя (SELECT + UPDATE). Если одной, то второй сценарий в Постгресе вообще невозможен (транзакция 2 будет заблокирована до окончания 1). А если двумя, то увы, возможен, потому что в этом случае база ничего не знает о связи w с r. Речь про Read Committed, конечно.
Так что заголовок бы поменять, чтоб не путаться.
В моем понимании грязная запись — это когда транзакция 2 перезаписывает изменение, сделанное транзакцией 1, до того, как 1 зафиксируется. В PostgreSQL это очевидно невозможно в силу блокировок.
А потерянное обновление возникает, когда одна транзакция уже завершилась, но вторая может записать новое значение, не учитывая изменений, сделанных и зафиксированных первой.
То есть это все-таки разные вещи, на мой взгляд.
И с определением потерянного обновления согласна. Но случаться то оно может, почему нет? (А в таблице в статье показано, что нет — как будто это самый недопустимый феномен).
На уровне Read Commited возможен Lost Update, а Repeatable Read его предотвращает. vladmihalcea.com/a-beginners-guide-to-database-locking-and-the-lost-update-phenomena
почему нет?
Ну просто в стандарте SQL говорится:
The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost.
То есть они безусловно требуют атомарность и отсутствие потерянных обновлений. Но при этом, что интересно, никак не определяют, что имеют в виду под lost updates.
Ну и получается как-то неловко — Read Committed не соответствует стандарту.
Еще один момент: у вас в табличке написано, что Postgres не допускает аномалии потерянных обновлений ни на на одном из уровней изоляции. В оф. документации я про это почему-то ничего не нашел, что также наводит на мысль, что поведение соответствует стандарту.
Однако вот в этой табличке https://github.com/ept/hermitage указано, что Postgres допускает данную аномалию (P4) на уровне Read Committed, что подтверждают мои собственные эксперименты. Сценарий приведен здесь: https://github.com/ept/hermitage/blob/master/postgres.md#lost-update-p4
Вполне похоже на r1(x) r2(x) w1(x) w2(x).
Великолепная статья! Все простым языком, кратко и без воды, все что нужно знать, огромное спасибо!
Еще одно важное замечание: если используется изоляция Serializable, то все транзакции в приложении должны использовать этот уровень. Нельзя смешивать транзакции Read Committed (или Repeatable Read) с Serializable. То есть смешивать-то можно, но тогда Serializable будет без всяких предупреждений вести себя, как Repeatable Read. Почему так происходит, мы рассмотрим позже, когда будем говорить о реализации.
Тогда непонятно зачем можно в отдельной транзакции указать этот уровень, или может в последней версии уже починили это?
MVCC-1. Изоляция