Объясните для дебилов на пальцах: какая разница между реализацией MVCC внутри Postgres и внутри какого-нибудь Oracle там последнего?

Предположим вы меняете много раз одну строку или несколько строк в одной странице. Она много раз успеет наменяться в памяти, прежде чем пойдёт на диск когда-нибудь потом. То есть, диска пока никакого нет. И в этой ситуации менять что-то inplace сильно выгоднее, чем плодить копии строк, возможно потребляя новые страницы или разбивая старые.

На всякий - тут (в обсуждении) уже несколько раз смешалось «как нечто работает в постгресе/оракле» и «как вообще можно сделать с/без COW».

Если есть REDO/WAL:

• Измененные страницы остается в памяти (как после inplace, так и после COW) и будет записана на диск когда-нибудь (асинхронно, при чекпоинте, при остановке БД и т.д.). Если транзакция очень жирная, то станицы могут spill-ится на диск тактикой вытеснения из активного набора;
• Синхронная (не отложенная) запись идет в REDO/WAL при фиксации каждой верхне-уровневой (не вложенной) транзакции;
• В REDO/WAL при каждой транзакции физически будет писаться логический сектор (4-64K), как при inplace, так и при COW.
• С COW может возникать bloat-проблема если БД не дискардит сразу ненужные копии (постгресе так).
• Без COW необходимо дополнительно лениво/асинхронно дописывать UNDO.

Если REDO/WAL отсутствует:

• Измененные страницы пишутся на диск при фиксации каждой транзакции, вне зависимости от inplace или COW.
• Без COW необходимо дополнительно лениво/асинхронно дописывать UNDO.

Подобие bloat-проблемы, в том или ином виде, есть в любой MVCC БД. В постгресе она в явном виде, но без пенальти для отставших/старых читателей. В оракле «размазана» и со штрафом для для отставших/старых читателей.

Но в принципе, MVCC будет укачивать по-определению, если лить много апдейтов и одновременно требовать старые данные.

при alter table будет экслюзивный ddl lock, чтобы помешать сделать drop table и наоборот, но это не мешает делать dml запросы.

Эксклюзивная блокировка DDL также берет эксклюзивную блокировку DML:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks002.htm#SQLRF5...
С другой стороны, блокировка DML не блокирует SELECT. Ну, то есть, в оракле вообще нет блокировки чтения таблицы. Зато любые операции изменения alter table очень жестко режет.

Страницу на диск записывать никогда не обязательно сразу же. Можно делать запись не чаще чем «раз в 15 минут» при любом сценарии работы.

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

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

Журнал есть. Там всё останется.

Нет, есть журнал, он же бинлог, он же WAL.

Нету его ни в оракле, ни в постгресе. Есть redo log при наличи асинхронной репликации, но и он не гарантирует ACID.

Журнал есть. Там всё останется.

Ну, и какая разница тогда, если ты будешь каждые 10 микросекунд писать в журнал на диск?

Без COW необходимо дополнительно лениво/асинхронно дописывать UNDO.

Я сейчас чешу репу, и у меня есть сомнения по поводу того, что какая-то из no-COW баз асинхронно пишет undo log. Потому что данные нужно писать по месте, и что потом делать после падения сервера? В базу записано незакоммиченное значение, а в undo пусто - вуаля, мы потеряли целостность данных. Для защиты от подобной ситуация СУБД не просто должна записать undo перед запись нового значения - она должна еще и защититься от повреждения блока в из-за незаконченно записи, то есть, сохранить этот блок куда-то. По крайней мере, так делает MySQL - про конкретно детали работы оракла не скажу.

И да, я был бы рад, если бы кто-то мне по яснил, каким образом Oracle/MS SQL/Firebird быстрее постгреса делают вставку записей.

В базу записано незакоммиченное значение, а в undo пусто - вуаля, мы потеряли целостность данных. Для восстановления целостности в оракле есть redo log, но таки да, undo пишется до коммита.

Я сейчас чешу репу, и у меня есть сомнения по поводу того, что какая-то из no-COW баз асинхронно пишет undo log. Потому что данные нужно писать по месте, и что потом делать после падения сервера? В базу записано незакоммиченное значение, а в undo пусто - вуаля, мы потеряли целостность данных.

Undo нужен для получения старых версий данных, которые не нужны после падения сервера (ибо после падения все транзакции стартуют заново).

Другое дело что undo и redo - это одни и тот-же diff, но в разном виде. Поэтому они могут быть совмещены на уровне реализации, т.е. взяв redo с отрицательным знаком ты получишь undo. Но есть существенная разница в деталях = в undo не нужно писать содержимое инсертов, а в redo делитов. Ну и т.д. и т.п.

Без COW необходимо дополнительно лениво/асинхронно дописывать UNDO.

На всякий чуть уточню.

Если в реализации undo не совмещен с redo, т.е. если это физически разные сущности, то «дозапись» undo - достаточно условный процесс, который непосредственно связан с тем, как БД отслеживает использование MVCC-снимков и что предоставляет (например, могут ли хранимки открывать читать/открывать произвольные таблицы с неизвестным во время компиляции именами, или множество этих таблиц как-то фиксируется на старте).

Проще говоря, БД может отслеживать использование самого старого MVCC-снимка и не писать на диск undo с изменениями раньше него. Например, накапливать в памяти undo для каждого table space, инициировать запись только при нехватке памяти, а перед записью отфильтровывать ненужное (писать iov-вектором).

Хотя в реальности это часто просто append в mmap-енный файл, либо кольцевой буфер в разделе диска.

Нету его ни в оракле, ни в постгресе. Есть redo log при наличи асинхронной репликации, но и он не гарантирует ACID.

Есть в постгре. Незнаю зачем тут употребляется «гарантирует ACID», ведь ACID - это сразу 4 разных требования и говорить наверное надо только про C - consistency или D - durability.

Это просто такой файлик бесконечной длины (физически нарубленный на отрезки по гигу), куда в конец пишутся последовательно все исполняемые на страницах операции вида «в странице N делаю то-то», а так же туда в случае постгри иногда пишутся целые копии страниц ПЕРЕД тем как быть записанными в «файл таблицы» (и то только в процессе выполнения процедуры «checkpoint») - в постгре это такой аналог MySQL-ного doube write buffer, который в мускуле (InnoDB) лежит внутри файла таблицы в виде специальной зоны.

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

Ну хорошо, если база особо ответственная, возврат команды «COMMIT» в которой гарантирует юзерам «точно записалось», то можно на каждый коммит сбрасывать все нужные страницы на диск, а внутри одного коммита какое угодно число изменений в каком угодно числе страниц отражать в WAL и больше нигде.

WAL выгоден тем, что это строго последовательная запись без елозенья головой по диску (или переключения блоков SSD, что тоже жрёт сколько-то времени) и архитектура СУБД обычно устроена с учётом того, что WAL гарантирует сохранение порядка следования операций в нём, но не гарантирует, что в конце что-то не потерялось.

Ну хорошо, если база особо ответственная, возврат команды «COMMIT» в которой гарантирует юзерам «точно записалось», то можно на каждый коммит сбрасывать все нужные страницы на диск, а внутри одного коммита какое угодно число изменений в каком угодно числе страниц отражать в WAL и больше нигде.

Ой, нене, поправлю себя. Даже в этом случае страницы на диск никакие сбрасывать не надо. Надо только flush() на файл WAL-а (журнала) вызвать.

Незнаю зачем тут употребляется «гарантирует ACID», ведь ACID - это сразу 4 разных требования и говорить наверное надо только про C - consistency или D - durability.

ACID - это единое понятие, которое теряет свой смысл с потерей любого составляющего слова. Это примерно как «пёс тузик» - не два отдельных требования к системе, а единый ярлык для конкретного явления.

внутри одного коммита какое угодно число изменений в каком угодно числе страниц отражать в WAL и больше нигде.

Да, я нашел этот момент в доках оракла:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo001.htm#ADMI...

Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

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

ACID - это единое понятие, которое теряет свой смысл с потерей любого составляющего слова. Это примерно как «пёс тузик» - не два отдельных требования к системе, а единый ярлык для конкретного явления.

Я тут подумал, прикинул, и все-таки не согласен со своим утверждением: в ACID свойство I-изоляция независимо и дополняет остальную модель, которая становится ACD. Видимо, кому-то хотелось добавить вторую гласную в слово, потому что ACD читалось плохо.

Подумай ещё, без изоляции не будет консистентности.

Подумай ещё, без изоляции не будет консистентности

На блокирующих СУБД без изоляции вполне норм поддерживается целостность. Это MVCC вас так разбаловало, что вам теперь подавай контроль версии читаемых данных.

Я бы так не сказал.

Ну я же написал - без MVCC:
https://www.ibm.com/support/knowledgecenter/hu/SSEPGG_9.7.0/com.ibm.db2.luw.a...
То есть, фактически здесь идет речь не про степень изоляции, а про степень сериализации, для которой не существует понятия Snapshot, которое, однако, является корректным уровнем изоляции. Хотя некоторые эту сериализацию тоже запихивают в изоляцию - отсюда и возникает проблема определения изоляции, которая слишком много разных вещей в себя вбирает, а потому уже давно является предметом дебатов: https://www.cs.umb.edu/~poneil/iso.pdf

Ты до конца дочитай, там наглядно показано, как эти шареные блокировки «вполне работают». И вообще, крайне занимательная статейка для тех, кто считает что оракл покупают только ради откатов и возможности указать пальцем.

Хотя некоторые эту сериализацию тоже запихивают в изоляцию

Потому что это один из уровней изоляции.

является предметом дебатов

Интересно, завтра на пляже почитаю, данкешон, сегодня уже в гогно, извиняйте.

Ты до конца дочитай, там наглядно показано, как эти шареные блокировки «вполне работают». И вообще, крайне занимательная статейка для тех, кто считает что оракл покупают только ради откатов и возможности указать пальцем.

Статья написана для даунов в стиле «Дядя, что такое read commited? - Детишки, садитесь поудобнее, сейчас я вам расскажу сказку». Чел всю статью рассказывает про несуществующие преимущества оракла по сравнению с воображаемой СУБД, под образ которой не подходит ни постгрес, ни интербэйз, ни MS SQL, ни даже MySQL, который уже в 2003 имел в стабильном релизе движок InnoDB. MyISAM не берем в рассмотрение, потому что не ACID. Из подходящих под образ конкурентов на ум приходит разве что MS Access или SQLite, которые по состоянию на 2005 годы действительно имели описанные в статье проблемы.

Оракл покупают в основном люди, которые не разбираются в IT, но имеют деньги и хотят гарантировано работающее решение, проверенное временем. А менеджеры оракла уже объяснят заказчику, почему его решение, действительно, самое правильное, и лучше было придумать нельзя. То, насколько гнилые аргументы при этом они приводят, будет ясно даже средненькому разрабу БД. Зато у какого-то промежуточного менеджера всегда будет будет оправдание по поводу того, почему куча денег потрачено, а система работает кое как: мы всё сделали правильно, мы не виноваты.

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

Хотя некоторые эту сериализацию тоже запихивают в изоляцию

Потому что это один из уровней изоляции.

Изоляция «serializable» и сериализация - это две разные вещи, что хорошо показано в последнем примере в статье, где два запроса в изоляции serializable выдают результат параллельного, а не последовательного выполнения. А Firebird/Interbase, например, имеет настоящую сериализацию, которая бы в последнем примере вместо двух нулей вставила 0 и 1.

например, читать таблицу, структура которой изменилась.

Нет, в PostgreSQL нет такой возможности. ALTER TABLE накладывает эксклюзивную блокировку, и конец.

Далее:

Он сделан на copy-on-write, что адово плодит версии

Но потом:

Самым главным следствием для пользователя в PostgreSQL является то, что в операциях записи он всегда будет проигрывать Oracle или Firebird, по факту примерно в два раза

Скорее всего, не будет (на практике). Это всё не так просто — т.е. стоило бы рассмотреть конкретные случаи (например, наличие/отсутствие обновления ключевых полей, и т.д.).

«Считать содержимое записи; записать измененную копию в новое месте; считать все индекса для этой записи; записать копию индексов с измененным указателем на новую запись»

ИндексЫ же. И Вы забыли про очень важную на практике оптимизацию в PostgreSQL – HOT. Когда он используется, PostgreSQL пишет по крайней мере не больше (если не меньше, потому что отсутствует «записать разницу в лог отмены», а всё остальное – аналогично), чем модель ниже.

«Считать запись; записать в нее новое значение; записать разницу в лог отмены»

Про изменения индексированных полей забыли. Каждый такой индекс также будет считан и изменён. Т.е. чем больше индексов затрагивается, тем более результат приближается к MVCC в PostgreSQL.

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

Опять повторяется необоснованное утверждение.

Только в редкой базе за время долгой транзакции более 10% записей будет изменено. Таким образом, оптимизация чтения старых данных в большинстве случаев не играет роли - это и есть «реализация заточенная под эффективность»

А статистика есть для обоснования этого утверждения? Кроме того, есть не только вариант изменения записи только один раз, и целиком забыты откаты транзакций (во всех других реализациях гораздо более дорогие), а их тоже бывает немало.

Да, в этих СУБД хорошо оптимизированы сложные джоины - но простые операции введь намного проще оптимизировать, и здесь нет оправдания у постгреса.

Откуда следует вывод, что постгресу нужны какие-то «оправдания»? Постоянно упоминаемой «потрясающей» разницы на практике что-то никто не показал.

но вот беда - такого сценария нигде не происходит.

И ещё одно необоснованное утверждение.

Я могу предположить, что Equinix просто от балды нарисовало графики.

А я могу предположить, что Oracle… это не аргумент, в общем.

Ну я же написал уже: изменение, вставка - в два раза медленее у постгреса;

И снова… надоело даже читать – см. выше.

я могу привести пример полной построчной выборки таблицы, и там постгрес в разы обгоняет оракл, но такой режим в клиент-серверных СУБД не имеет смысла

Сложные DWH-запросы (которые обрабатывают таблицы / их части / partitions целиком) мне встречались очень часто (в БД с таким назначением).

И да, я был бы рад, если бы кто-то мне по яснил, каким образом Oracle/MS SQL/Firebird быстрее постгреса делают вставку записей.

Невозможно объяснить явление, которого не существует. Я понимаю, что benchmark-ов с Oracle и MS SQL тут не будет, но неплохо бы показать хоть Firebird / MySQL.

в ACID свойство I-изоляция независимо и дополняет остальную модель, которая становится ACD.

Я извиняюсь, но не думайте так больше, а почитайте учебник по теории СУБД – это должно быть почти в любом. Нет, изоляция не независима, и является одним из основных свойств СУБД, без которого ACID превращается, максимум, в AD.

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

Опять-таки, не нужно путать тёплое с мягким. Определению изоляции (максимально кратко: isolation = serializability) уже более 50 лет, наверное, и никаких дебатов по его поводу нет (и оно же в неизменном виде входит в стандарт SQL ещё с начала 1990-х). А статья совсем не об этом.

Изоляция «serializable» и сериализация - это две разные вещи,

Нет. Если какая-то СУБД на том уровне изоляции, который в ней называется «serializable», не предоставляет сериализуемости, она грубо нарушает стандарт (и, с учётом того, что определению serializable полвека, и компетентные разработчики СУБД не могут его не знать, ещё и нагло лжёт).

Firebird/Interbase, например, имеет настоящую сериализацию, которая бы в последнем примере вместо двух нулей вставила 0 и 1.

Тоже не имеет, кстати. См. http://www.bailis.org/blog/when-is-acid-acid-rarely/ про реальные «достижения» современных СУБД на этом поприще.

Понятно всё с тобой, а на момент даже показалось адекватный человек, понимаешь о чём говоришь, но нет.

Нет, в PostgreSQL нет такой возможности. ALTER TABLE накладывает эксклюзивную блокировку, и конец.

Да, действительно, нету. Хз, откуда я взял про то, что есть. Наверное, в мозгу перепуталась инфа про неблокирующее создание индексов и отсутствие обновления таблицы при alter table... add column - я как-то сделал ложный вывод, что «неизменение записей» значит «отсутствие блокировки». Add/drop column, хоть и гарантировано короткое без default, но все равно требует ACCESS EXCLUSIVE, то есть, завершения всех читающих/пишущих/почесывающих транзакций.

Скорее всего, не будет (на практике). Это всё не так просто — т.е. стоило бы рассмотреть конкретные случаи (например, наличие/отсутствие обновления ключевых полей, и т.д.).

Абсолютно все бенчмарки, которые я видел и которые приводил в треде, говорят про то, что вставка и обновление в Oracle и MS SQL довольно стабильно в два раза быстрее, чем в PostgreSQL. Я могу объяснить это для update - не обновляются индексы, но я никак не могу объяснить это для вставок, где индексы должны меняться в обоих СУБД, и в обоих для ускорения этого процесса нужно удалять/отключать индексы на время вставки, иначе скорость вставки падает по логарифму кол-ва записей. Да, постгрес создает копию версий индекса вместо обновления по месту, ну и чо? Это же не должно давать какой-то серьезной разницы, но как-то дает.

И Вы забыли про очень важную на практике оптимизацию в PostgreSQL – HOT. Когда он используется, PostgreSQL пишет по крайней мере не больше (если не меньше, потому что отсутствует «записать разницу в лог отмены», а всё остальное – аналогично), чем модель ниже.

Да, HOT приводит механизм подобным ораклу. Тогда тайна покрывается еще больим мраком.

Про изменения индексированных полей забыли. Каждый такой индекс также будет считан и изменён. Т.е. чем больше индексов затрагивается, тем более результат приближается к MVCC в PostgreSQL.

В постгресе HOT отваливается при обновлении индексированных полей.

Только в редкой базе за время долгой транзакции более 10% записей будет изменено. Таким образом, оптимизация чтения старых данных в большинстве случаев не играет роли - это и есть «реализация заточенная под эффективность»

А статистика есть для обоснования этого утверждения? Кроме того, есть не только вариант изменения записи только один раз, и целиком забыты откаты транзакций (во всех других реализациях гораздо более дорогие), а их тоже бывает немало.

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

Откуда следует вывод, что постгресу нужны какие-то «оправдания»? Постоянно упоминаемой «потрясающей» разницы на практике что-то никто не показал.

Тред читай. Все ссылки дали, но каждый раз будет приходить человек и писать «где разница? Никто ее не показал».

Я могу предположить, что Equinix просто от балды нарисовало графики.

А я могу предположить, что Oracle… это не аргумент, в общем.

Можно так предположить. Но тогда надо предполагать, что оракл договорился с Microsoft создавать якобы независимые бенчмарки, которые показывали бы их СУБД, как превосходящие. Это уже намного менее вероятно, чем индусская контора, которая приводит графики без описания процедуры тестирования.

Сложные DWH-запросы (которые обрабатывают таблицы / их части / partitions целиком) мне встречались очень часто (в БД с таким назначением).

Такой запрос делается на стороне процесса, который имеет прямой доступ к базе, то есть, либо серверный язык, либо просто локальная СУБД. А SQL нужен для того, чтобы задать ограниченную операцию процессу, имеющему прямой доступ к базе. Если же нужно выбрать всю таблицу, то SQL не нужен.

Я извиняюсь, но не думайте так больше, а почитайте учебник по теории СУБД – это должно быть почти в любом. Нет, изоляция не независима, и является одним из основных свойств СУБД, без которого ACID превращается, максимум, в AD.

Что делать с SQLite, который до реализации WAL вообще не имел изоляции? Я напомню, что в состав ACID не входит понятия «concurrency», потому полностью упорядоченный по изменениям доступ к базе вполне удовлетворяет ACD до тех пор, пока соблюдена атомарность изменений, целостность и сохранность данных. Собсна, сама изоляция - это именно требование к параллельному доступу, но если параллелизации нет - изоляция отваливается.

Определению изоляции (максимально кратко: isolation = serializability) уже более 50 лет, наверное, и никаких дебатов по его поводу нет (и оно же в неизменном виде входит в стандарт SQL ещё с начала 1990-х). А статья совсем не об этом.
Если какая-то СУБД на том уровне изоляции, который в ней называется «serializable», не предоставляет сериализуемости, она грубо нарушает стандарт (и, с учётом того, что определению serializable полвека, и компетентные разработчики СУБД не могут его не знать, ещё и нагло лжёт).

Статья в том числе затрагивает проблему разницы между изоляцией и сериализацией. Сам стандарт, к которому я советую отсылаться [1], дает два дополняющих определения serializable - одно из них определяет изоляцию для этого режима в терминах феномена P3 («Phantom»), второе - определяет совершенно иное требование сериализуемости, ортогональное феноменам нарушения изоляции P1, P2, P3. Oracle в своем «serializable» выполняет отсутствие феноменов P1, P2, P3, как того требует стандарт, но не выполняет требование сериализуемости, таким образом соответствуя режиму «ANOMALY SERIALIZABLE» из статьи.
Таким образом, здесь можно увидеть два независимых требования к системе: изоляция и сериализация - которые с легкой ноги коммитета ANSI были запихнуты в одно понятие «изоляция».
[1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Firebird/Interbase, например, имеет настоящую сериализацию, которая бы в последнем примере вместо двух нулей вставила 0 и 1.

Тоже не имеет, кстати. См. http://www.bailis.org/blog/when-is-acid-acid-rarely/ про реальные «достижения» современных СУБД на этом поприще.

Вот я тут сижу теперь и думаю «то ли я верблюд, то ли...». Пошел и проверил взаимные insert...select:

> Insert into T2 (ID) select ID from T1

> Insert into T1 (ID) select ID from T2
lock conflict on no wait transaction
Acquire lock for relation (t2) failed

Понятно всё с тобой, а на момент даже показалось адекватный человек, понимаешь о чём говоришь, но нет.

Что такое, котик, в книжке по другому написано? А мозги мама тебе зачем подарила?

в книжке по другому написано?

тебе-то откуда знать, ты же их не читаешь, а если и читаешь, то не понимаешь.

котик

котик нассал тебе за шиворот - проверяй

Абсолютно все бенчмарки, которые я видел и которые приводил в треде, говорят про то, что вставка и обновление в Oracle и MS SQL довольно стабильно в два раза быстрее, чем в PostgreSQL.

Это приведённые здесь? Объясните для дебилов на пальцах: какая разница между реализацией MVCC внутри Postgres и внутри какого-нибудь Oracle там последнего? (комментарий) Нет, не говорят. Они измеряют производительность транзакций, имитирующих определённые (по идее, типичные для «бизнеса») операции. Т.е. ничего конкретного («чистых» benchmarks UPDATE и INSERT) приведено не было, и

Я могу объяснить это для update

«объяснять» просто нечего.

Тогда тайна покрывается еще больим мраком.

Это оттого, что «тайны» никакой нет.

В постгресе HOT отваливается при обновлении индексированных полей.

Совершенно верно. Но писал-то я не об этом, а о… прочитайте цитату.

Я явно указал, что не исключаю вариант маленькой интенсивно обновляемой базы.

Вот и не нужно говорить за всех. И вы опять пропустили важную часть цитаты.

Тред читай. Все ссылки дали, но каждый раз будет приходить человек и писать «где разница? Никто ее не показал».

Давайте-давайте, «отмахиваться» не нужно (см. выше).

Если же нужно выбрать всю таблицу, то SQL не нужен.

То есть весь раздел DWH, специальное предназначенные для этого СУБД (вроде exasol и teradata) – не нужны, теста TPC-H в реальности не существует, и т.д. и т.п.!? Тут уж вы хватили через край, мне кажется.

Сколько умных слов в треде, ничего не знаю, знаю только что в Oracle есть такое понятие как Rollback Segment, который фактически определяет размер данных которые можно вставить в одной транзакции и из-за того что он конечен бывали ошибки его нехватки, его просто увеличивали (все меняется, нагрузки постепенно растут). У postgres такого нет, я так понимаю все данные пишутся сразу в структуры конечных таблиц, просто данные не видны до успешного завершения транзакции, и если что-то пошло не так, то невидимые данные останутся до вызова VACUUM.

Что делать с SQLite, который до реализации WAL вообще не имел изоляции?

Конечно, имел. Вы путаете метод и результат. До реализации WAL методом обеспечения изоляции в sqlite был однопользовательский доступ, и он вполне был ACID СУБД.

Я напомню, что в состав ACID не входит понятия «concurrency»,

А вот учебники и ISO SQL считают строго наоброт. Зря не читаете.

но если параллелизации нет - изоляция отваливается.

Как раз, соблюдается. Это просто вырожденный случай.

Статья в том числе затрагивает проблему разницы между изоляцией и сериализацией.

Нет, не затрагивает. В разделе «определение терминов» приводится всё та же классика.

Сам стандарт, к которому я советую отсылаться [1], дает два дополняющих определения serializable

Нет, ничего подобного! Как вы читаете вообще, я извиняюсь? Вам недвусмысленные куски стандарта тут поцитировать?!

одно из них определяет изоляцию для этого режима в терминах феномена P3 («Phantom»),

Нет, не определяет. Определение в стандарте только одно, и оно взято прямо «из учебника».

Oracle в своем «serializable» выполняет отсутствие феноменов P1, P2, P3, как того требует стандарт,

Нет, стандарт требует гораздо большего – сериализуемости, т.е. Oracle тупо лжёт, когда они пишут, что соответствуют стандарту в этом части (опять-таки, см. ссылку про «достижения»).

Вот я тут сижу теперь и думаю «то ли я верблюд, то ли…». Пошел и проверил взаимные insert…select:

Дело не в том, что «я пошёл и проверил случайный тест, и вроде оно ничего», а в том, можно ли в принципе найти для данной СУБД такое расписание конкурентных транзакций на SERIALIZABLE, когда требование сериализуемости не выполняется. Если можно (как для firebird) – СУБД нарушает стандарт, и изоляции не обеспечивает.

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

Это приведённые здесь? Объясните для дебилов на пальцах: какая разница между реализацией MVCC внутри Postgres и внутри какого-нибудь Oracle там последнего? (комментарий) Нет, не говорят. Они измеряют производительность транзакций, имитирующих определённые (по идее, типичные для «бизнеса») операции. Т.е. ничего конкретного («чистых» benchmarks UPDATE и INSERT) приведено не было

А так?
http://phpdao.com/mysql_postgres_oracle_mssql/

Про изменения индексированных полей забыли. Каждый такой индекс также будет считан и изменён. Т.е. чем больше индексов затрагивается, тем более результат приближается к MVCC в PostgreSQL.

В постгресе HOT отваливается при обновлении индексированных полей.

Совершенно верно. Но писал-то я не об этом, а о… прочитайте цитату.

И? Что я не так понял? Можешь считать мою фразу чем-то вроде «да, приближается».

А статистика есть для обоснования этого утверждения? Кроме того, есть не только вариант изменения записи только один раз, и целиком забыты откаты транзакций (во всех других реализациях гораздо более дорогие), а их тоже бывает немало.

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

Вот и не нужно говорить за всех. И вы опять пропустили важную часть цитаты.

Какую из? Про откаты транзакций? Где-то кто-то эксплуатирует базу с высоким процентом откатов?

То есть весь раздел DWH, специальное предназначенные для этого СУБД (вроде exasol и teradata) – не нужны, теста TPC-H в реальности не существует, и т.д. и т.п.!? Тут уж вы хватили через край, мне кажется.

Ты хочешь сказать, что кто-то в RAC или Teradata гоняет взад-вперед сырые данные?

Oracle тупо лжёт, когда они пишут, что соответствуют стандарту в этом части Можно конкретики? Если это со слов поциента, то лучше перечитай первоисточник(документацию), иначе поломанный телефон получается.

У postgres такого нет, я так понимаю все данные пишутся сразу в структуры конечных таблиц, просто данные не видны до успешного завершения транзакции, и если что-то пошло не так, то невидимые данные останутся до вызова VACUUM.

Все базы подобного типа имеют тот или иной механизм буферизации записи и защиты от ошибочной записи: WAL у постгреса, redo log у оракла. Они отыгрывают важную роль в производительности коротких быстрых транзакций, поскольку собирают кучу изменений в один блок вместо того, чтобы растыкивать каждую правку в свой блок. При этом в самом процессе сервера правильный вид таблиц восстанавливается через кэши.

Это - промежуточный механизм, а есть формат конечного хранения данных, в который данные превращаются, проходя через WAL/redo log. Например, вставка в оракле создает в redo логе запись о вставленном кортеже, запись об undo логе для этого кортежа, запись об изменении индекса, запись об undo логе изменения кортежа. Всё это плотно умещается в redo логе, потому транзакция может завершиться быстро.

Что делать с SQLite, который до реализации WAL вообще не имел изоляции?

Конечно, имел. Вы путаете метод и результат. До реализации WAL методом обеспечения изоляции в sqlite был однопользовательский доступ, и он вполне был ACID СУБД.

Я напомню, что в состав ACID не входит понятия «concurrency»,

А вот учебники и ISO SQL считают строго наоброт. Зря не читаете.

Цитироваю:
«The isolation level of an SQL-transaction defines the degree to which the operations on SQL-data or schemas in that SQL-transaction are affected by the effects of and can affect operations on SQL-data or schemas in concurrent SQL-transactions.»
Какое-то слово непонятно? Параллельные SQL-транзакции. Точка. Нет параллельных транзакций - нет изоляции.

Как раз, соблюдается. Это просто вырожденный случай.

А хвост - это вырожденный случай коня.

Oracle тупо лжёт, когда они пишут, что соответствуют стандарту в этом части

Можно конкретики? Если это со слов поциента, то лучше перечитай первоисточник(документацию), иначе поломанный телефон получается.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32331914...
«Serializable does not mean that all transactions executed by the users are the same as if they were executed one right after another in a serial fashion. It does not imply that there is some serial ordering of the transactions that would result in the same outcome. This last point is a frequently misunderstood concept and a small demonstration will clear it up»
Это цитата из книги «Expert Oracle Database Architecture», Thomas Kyte, Darl Kuhn.

То, что использует Oracle под названием Serializable, у людей зовется Snapshot, но ANSI SQL 92 не вводит такого понятия. Да, оракл слукавил, но я не могу его винить в том, что ANSI принял двусмысленное определение уровней изоляции и не отделил изоляцию от сериализации.

А так? http://phpdao.com/mysql_postgres_oracle_mssql/

«Так» было бы лучше, если бы там было точное описание тестов, настроек баз, железа и т.д. и т.п. Т.е. это очередная чушь, которую никто не может воспроизвести. Нормальное что-то есть?

Какую из? Про откаты транзакций?

Да.

Где-то кто-то эксплуатирует базу с высоким процентом откатов?

И тоже да. Не раз видел, представляете?

Ты хочешь сказать, что кто-то в RAC или Teradata гоняет взад-вперед сырые данные?

Ничего не знаю про RAC, но он тут, кажется, ни при чём? Я хочу сказать, что зачастую аналитические запросы читают таблицы (или partitions, или индексы) целиком.

Если это со слов поциента, то лучше перечитай первоисточник(документацию)

Т.е. статью не читали, я так понял? Или не поняли, о чём это? Документация тут не имеет значения!

Соответствие требованиям ACID – не вопрос мнения, или утверждений Oracle, или авторитета. Это – проверяемое, тестируемое свойство (как 2+2=4?). Человек проверил. Реальные «успехи» СУБД представлены в статье.

Мораль: документация Oracle (да и некоторых других СУБД) лжёт.

Какое-то слово непонятно?

Мне непонятно, почему цитируется определение isolation level, а не isolation. У вас какие-то проблемы с чтением, или что?

Нет параллельных транзакций - нет изоляции.

Наоборот – значит, они полностью изолированы.

А хвост - это вырожденный случай коня.

Прочитайте же, наконец, стандарт внимательно, а? Подумайте над прочитанным, что ли…

«Serializable does not mean that all transactions executed by the users are the same as if they were executed one right after another in a serial fashion.

It does. This «all transactions executed by the users are the same as if they were executed one right after another in a serial fashion» is the almost word-by-word the serializability definition in ISO SQL!

It does not imply that there is some serial ordering of the transactions that would result in the same outcome.

It does! Attempt to cite the exact definition from ISO SQL here, and then claim that it is not true is particularly hilarious.

Это цитата из книги «Expert Oracle Database Architecture», Thomas Kyte, Darl Kuhn.

С каких пор эта дрянь стала учебником, а?!

То, что использует Oracle под названием Serializable, у людей зовется Snapshot

Вот именно.

но ANSI SQL 92 не вводит такого понятия.

Вводит, недвусмысленно (как, опять-таки, учебники по теории СУБД).

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

И снова неверно. Так вот я – могу, потому что ранее процитированное – наглая ложь Oracle. Не осилили полноценную реализацию, теперь приходится отвираться (FAIL).

«Так» было бы лучше, если бы там было точное описание тестов, настроек баз, железа и т.д. и т.п. Т.е. это очередная чушь, которую никто не может воспроизвести. Нормальное что-то есть?

Конечно же нет. Любой тест, который я приведу, пойдет в категорию «ненормальный»:
https://infostart.ru/public/962876/
«загрузка DT, 40 с лишним гигабайт, итоговая база почти 2 терабайта.
С явным преимуществом побеждает MS SQL. Он в 2 раза быстрее загрузил данные в DT. Оба загружали очень долго, Postgres – почти двое суток, а MS SQL – почти сутки»
Ой, что такое, очередной некошерный тест показал двухкратное преимущество в скорости вставки? Да не может быть. Нужен «нормальный» тест - сделай свой.

Я хочу сказать, что зачастую аналитические запросы читают таблицы (или partitions, или индексы) целиком.

Давай сразу определимся: речь идет о том, что клиент по сетке запрашивают всю таблицу целиком с сервера, или же о том, что клиент отправляет серверу запрос, в котором фигурирует полная выборка? Когда я писал про преимущество в скорости постгреса по обработке сырых данных - я имел в виду именно взаимодействие клиент-сервер, потому что взаимодействие сервер-база имеет совсем другие характеристики.

Мне непонятно, почему цитируется определение isolation level, а не isolation. У вас какие-то проблемы с чтением, или что?

Например, потому что ANSI SQL 92 не определяет понятие «isolation». Совсем. Рекомендую больше читать и меньше писать.

Наоборот – значит, они полностью изолированы.

Это вопрос про цвет кошки в темной комнате: результаты полной изоляции и отсутствия изоляции совпадают.

It does. This «all transactions executed by the users are the same as if they were executed one right after another in a serial fashion» is the almost word-by-word the serializability definition in ISO SQL!

You should notice they don't actually mention ANSI standard in their description. Thus their «serializability» can be anything they want. You can take a look at the original book to make sure there's not a single word about the standard:
https://books.google.com/books?id=3_RUBQAAQBAJ&pg=PA262&lpg=PA262

С каких пор эта дрянь стала учебником, а?!

Да мне пофик, учебник или нет. Важно, что это авторитетное мнение авторитетных петухов от оракла, которые говорят «сериализация будет такой, какую мы захотим. Теперь наше дело - убедить вас в том, что вы именно этого и хотели». Чем, собсна, и занимаются все менеджеры по продажам оракла.

но ANSI SQL 92 не вводит такого понятия.

Вводит, недвусмысленно (как, опять-таки, учебники по теории СУБД).

Так приведи. Заранее предупреждаю: ANSI 92 «READ STABILITY» - это не snapshot, потому что при повторном выполнении запроса в одной транзакции отображает новые записи.

Любой тест, который я приведу, пойдет в категорию «ненормальный»

Прекратите передёргивать. Любой ненормальный тест, который вы покажете, пойдёт в категорию «ненормальный». Критерии нормального теста я уже приводил (а если вы программист, должны и сами знать; если не знаете, зачем вообще ввязались в дискуссию?).

С явным преимуществом побеждает MS SQL. Он в 2 раза быстрее загрузил данные в DT. Оба загружали очень долго, Postgres – почти двое суток, а MS SQL – почти сутки»

Цитирую из статьи «Но что такое есть в Postgres, что он загружает данные в DT в 2 раза дольше? Наши исследования показали, что это «create index». Он создает индексы намного медленнее MS.»

Т.е. данные вставлялись в неиндексированные таблицы, видимо (я не буду указывать на очевидную и разностороннюю бездарность тестирующих, а так же на то, что они грубо нарушили лицензию MS SQL (было бы это в США – на этом их организация (а возможно, и карьера, и личная свобода) бы и кончилась)). Так как этот тест доказывает приведённый тезис о том, что вставка в PostgreSQL медленнее, если тут проблема не в ней?

Нужен «нормальный» тест - сделай свой.

Я не считаю себя компетентным DBA многих СУБД (и это не говоря о трудозатратах). Поэтому заниматься этим не буду (это только авторов подобных статей такие соображения не останавливают). Это вы здесь хотели что-то доказать и «выяснить».

Когда я писал про преимущество в скорости постгреса по обработке сырых данных - я имел в виду именно взаимодействие клиент-сервер, потому что взаимодействие сервер-база имеет совсем другие характеристики.

Внятнее можно писать, а? Пример (псевдокод) запроса, который имелся в виду:

SELECT year, SUM(cost_amount) AS total_cost, SUM(sold) AS total_sales
  FROM all_sales
 GROUP BY year
 ORDER BY year;

Т.е. на диске эта таблица (детализированные продажи), допустим, занимает 1 ТБ, а размер отправляемого клиенту результата ~Кб. Вот здесь вполне вероятно полное сканирование таблицы, и сложных аналитических запросов с примерно такими же характеристками – много.

Например, потому что ANSI SQL 92 не определяет понятие «isolation».

А ещё он не определяет используемые им слова английского языка. То, что вы привели не относящееся к делу определение, и ищете определения не там, где нужно – это только ваши проблемы, не надо с больной головы на здоровую валить.

Рекомендую больше читать и меньше писать.

Рекомендую почитать, наконец, учебники.

Это вопрос про цвет кошки в темной комнате: результаты полной изоляции и отсутствия изоляции совпадают.

А, то есть можно с тем же успехом сказать, что неконкурентные транзакции не изолированы (т.е. влияют друг на друга)?

You should notice they don’t actually mention ANSI standard in their description.

So why are you using it instead of the textbook / to try to prove a point about the standard?!

Thus their «serializability» can be anything they want.

This behavior (of common terms «redefinition» to prove one’s point) has quite a few of appropriate names: demagogy, fraud, shameless lie, etc.

Да мне пофик, учебник или нет.

А, то есть я могу аргументировать (к примеру) и рекламой (в которой «1+1=3»), и это нормально в технической дискуссии, и поможет нам выяснить, что правда, а что – нет?

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

Их мнение мне интересно ровно в той же степени, что и мнения продавцов гомеопатии, вечных двигателей и т.п.

Так приведи. Заранее предупреждаю: ANSI 92 «READ STABILITY» - это не snapshot, потому что при повторном выполнении запроса в одной транзакции отображает новые записи.

Я добавлю ещё контекста:

То, что использует Oracle под названием Serializable, у людей зовется Snapshot, но ANSI SQL 92 не вводит такого понятия.

Вводит, недвусмысленно (как, опять-таки, учебники по теории СУБД).

И вот оно (взято из SQL 2011, «4.36.4 Isolation levels of SQL-transactions»)!

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.

А вот оно же из SQL92:

The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable exe cution 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.

«Найдите десять отличий», как говорится. И это совершенно «классическое» определение «из учебника».

А, то есть можно с тем же успехом сказать, что неконкурентные транзакции не изолированы (т.е. влияют друг на друга)?

Да, неизолированная транзакция влияет на ничто, и видит все вносимые им изменения.

То, что использует Oracle под названием Serializable, у людей зовется Snapshot, но ANSI SQL 92 не вводит такого понятия.

Вводит, недвусмысленно (как, опять-таки, учебники по теории СУБД).

И вот оно (взято из SQL 2011, «4.36.4 Isolation levels of SQL-transactions»)!

Моя фраза состояла из 4 предложений. Обычно в русском языке в сложном предложении логической связью объединены именно соседние предложения. То есть, последнее подчиненное предложение «но ANSI SQL 92 не вводит такого понятия», очевидно, относится к предыдущему предложению «у людей зовется Snapshot». Я не вижу ничего в твоих цитатах про snapshot.

Да, неизолированная транзакция влияет на ничто, и видит все вносимые им изменения.

А некурящий – это тот, кто курит отсутсвие сигареты. Ну ok.