Уровни изоляции транзакций в PostgreSQL, MySQL, MSQL, Oracle с примерами на Go

[Akina]

В Mysql мы по сути разделили один запрос на два. Можно попробовать построить запрос по – другому запрос, к примеру, применить Update + JOIN

Я бы ещё посмотрел, как себя поведёт такой запрос:

UPDATE accounts
CROSS JOIN ( SELECT 1
             FROM accounts
             WHERE user_id = 1 
             HAVING SUM(amount) >= 2000 ) criteria
SET amount = amount - 1000
WHERE id = 1
  AND amount >= 1000;

[CentariumV]

BEGIN;
UPDATE accounts
CROSS JOIN ( SELECT 1
                 FROM accounts
                 WHERE user_id = 1
                 HAVING SUM(amount) >= 2000 ) as criteria
SET amount = amount - 1000
WHERE id = 10
  AND amount >= 1000;

Точно такие же блокировки захватывает:

С таким же планом запроса

[VVitaly]

Хорошая статья, но если "копать глубже"... Вопрос о блокировках и целостности данных при многопоточной работе с ними возник еще на "уровне данных хранящихся в памяти", и на уровне работы многоядерных процессоров и OS, и "развился" в последствии на "уровень БД" (которые "по факту" работают "поверх" механизмов CPU и OS).... :-)

[CentariumV]

Да, если смотреть более обще) Этот момент как раз показан во втором пункте статьи

[poxvuibr]

Mysql, SQLServer ведут себя согласно Стандарту [в них будет грязное чтение на уровне изоляции READ UNCOMMITED]

Насколько я себе представляю, Стандарт требует, чтобы феноменов не было, но не требует, чтобы феномены обязательно воспроизводились на каких-то уровнях изоляции. Поэтому Postgresql тоже ведёт себя согласно Стандарту

[CentariumV]

Интересный момент. 1) Стандарт определяет, каких аномалий на каком уровне точно быть не должно(not possible). И если они присутствуют - значит, реализация уровня не соответствует стандарту, как в Oracle
2) Стандарт определяет, какие аномалии могут присутствовать на уровне изоляции (possible). Значит ли это по аналогии, что если на соответствующем уровне изоляции аномалия присутствовать не может - то реализация также не соответствует Стандарту? (-:
Стандарт:
The isolation level specifies the kind of phenomena that can occur during the execution of concurrent SQL-transactions

Надо заметить, я нигде в тексте не сказал, что Postgresql ведёт себя не согласно Стандарту. Я только сказал, что Mysql и SQLServer ведут себя согласно Стандарту, а в PostgreSQL этот уровень соответствует уровню Read Commited.

[CentariumV]

Вообще, если исходить из жадной логики, то кажется, что если аномалий/феноменов будет меньше, то надежность/консистентность будет выше и это лучше. Но тогда можно было просто оставить один уровень - 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" во всем параграфе Стандарта про уровни изоляции. Но если смотреть в общем, дедуктивно, Стандарт предполагает некий Tradeoff между производительностью и надежностью на каждом уровне.
А защита от каждой аномалии явно предполагает какие - то доп. механизмы, может и незначительно, но снижающие производительность. Слабо верится, что PostgreSQL смогли отказаться от уровня read uncommitted не потеряв при этом совсем в производительности.

Так что вполне возможно, что Стандарт как раз - таки подразумевает, чтобы феномены были на определенных уровнях изоляции. И вполне возможно, что кто - нибудь придет в PostgreSQL и скажет - "Мне для задачи нужен именно уровень Read Uncommitted. Так, его тут нет. И это не есть хорошо"

[poxvuibr]

Так что вполне возможно, что Стандарт как раз - таки подразумевает, чтобы феномены были на определенных уровнях изоляции.

Насколько я понимаю, не подразумевает. Possible в английском означает, что что-то может произойти. но не обязательно.

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

Из-за MVCC в Постгресе как раз получается, что грязные чтения вообще никак производительность не увеличивают. Возможно даже уменьшают, не уверен.

[onets]

Спасибо, на самом деле огромный труд. Делал нечто подобное, когда решал локальную проблему с транзакциями, пока не понимал почему mysql внутри хранимки не работает согласно документации.

[vanxant]

Хабраторт!