Comments 47
Правило согласованности может звучать так: перевод никогда не меняет общей суммы денег на счетах (такое правило довольно трудно записать на 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 не соответствует стандарту.
Если так трактовать стандарт, то получается, что ни одна СУБД не соответствует Стандарту ISO/IEC 9075 - по крайней мере, SQL SERVER, MySQL, Oracle и PostgreSQL, кстати, тоже.
Вообще удивительно, как одно предложение может по - разному трактоваться.
Здесь буду отталкиваться от статьи A Critique of ANSI SQL Isolation Levels https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf. Согласно которой - Lost Update - new phenomena, не упоминаемая в Стандарте, т.е. данное предложение
and that no updates will be lost
Было трактовано неправильно и в статье неточность
Еще один момент: у вас в табличке написано, что 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. Почему так происходит, мы рассмотрим позже, когда будем говорить о реализации.
Тогда непонятно зачем можно в отдельной транзакции указать этот уровень, или может в последней версии уже починили это?
Статья классная, но я не понял, как в примере с переменчивой функцией получился результат 100 / 800, выглядит так, что из транзакции выполнился второй UPDATE, но не первый (при стартовых 100 / 900).
Стандарт определяет и еще один уровень — Serializable, — на котором не допускаются никакие аномалии. И это совсем не то же самое, что запрет на потерянное обновление и на грязное, неповторяющееся и фантомное чтения.
А так ли это? Можно цитату из стандарта, где это прямо говорится? Четкое указание и домыслы - разные вещи.
Я думаю, что согласно Стандарту, Serializable - это как раз - таки уровень, на котором не допускаются грязное, неповторяющееся и фантомное чтение. И не более того. Т.е. все как раз - таки vice versa, наоборот.
Надо быть осторожным в этом плане с домыслами. Приведу пример: Как - то встретил утверждение, что "Serializable - это уровень изоляции, на котором результат выполнения операций, согласно Стандарту ISO/IEC 9075, должен быть такой, как будто операции выполнялись последовательно. Поэтому де-факто Oracle не соответствует стандарту". Но на поверку говоривший не смог найти подобное утверждение в Стандарте, т.е. это утверждение оказалось не более, чем домысл
А так ли это? Можно цитату из стандарта, где это прямо говорится?
Конечно можно цитату:
The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.
Я думаю, что согласно Стандарту, Serializable - это как раз - таки уровень, на котором не допускаются грязное, неповторяющееся и фантомное чтение. И не более того.
Ни в коем случае.
До реализации таких блокировок в какой-либо системе дело, насколько мне известно, не дошло
А как насчет gap lock и Next-Key Locks в Mysql? Не являются ли они по сути теми же предикатными блокировками?
К примеру, установим уровень изоляции Repeatable Read.
И возьмем две транзакции в разных сессиях:
1) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
Select amount from invoices WHERE id BETWEEN 10 and 30 FOR UPDATE;
COMMIT;
2) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
INSERT INTO invoices(id, user_id, amount) VALUES (15, 2, 15);
COMMIT;
Выполним Select в первой транзакции, затем - Insert во второй
В PostgreSQL никакой блокировки не будет.
В Mysql же Insert заблокируется.
Хотя опять же непонятно, если Mysql реализует честный MVCC, то зачем ему вообще такого рода блокировки. Насчет Serializable не проверял, но на уровне Read Committed уже Insert отработает
По определению, данному в этой статье вот очень похоже на предикатную блокировку.
А как насчет gap lock и Next-Key Locks в Mysql? Не являются ли они по сути теми же предикатными блокировками?
Возможно я неточно выразился. Похожие по смыслу блокировки есть, но их реализация радикально отличается от предложенного в статье. В PostgreSQL (и, насколько я представляю, в MySQL тоже) они опираются на индекс. Только в PostgreSQL это даже и не блокировка, а способ отследить зависимость.
Хотя опять же непонятно, если Mysql реализует честный MVCC, то зачем ему вообще такого рода блокировки.
PostgreSQL в такой ситуации оборвет одну из транзакций при попытке фиксации. Возможно, в MySQL этого пытаются избежать с помощью блокировки. Но тогда после снятия блокировки транзакция должна перечитывать новое состояние; не знаю, как с этим в MySQL, не исследовал.
Только в PostgreSQL это даже и не блокировка, а способ отследить зависимость
Да, это хорошо показано в вашей статье
PostgreSQL в такой ситуации оборвет одну из транзакций при попытке фиксации
Не совсем понял, про какую ситуацию речь. Мы же про INSERT|SELECT транзакции говорили в рамках Repeatable Read. Когда PostgreSQL оборвет одну из транзакций при попытке фиксации?
Мм, да, я имел в виду ситуацию, когда две транзакции действительно пересеклись по данным. PostgreSQL на уровне Repeatable Read перечитает строку, которая была заблокирована, и, если она изменилась, оборвет транзакцию.
А с MySQL я бы начал разбираться с изучения https://jepsen.io/analyses/mysql-8.0.34. Там много интересного.
MVCC-1. Изоляция