Вопросы по статьям на хабре и википедии про уровни изоляции транзаций — почему так написано?

Question

[footballer]

1) статья на википедии:
https://ru.wikipedia.org/wiki/%D0%A3%D1%80%D0%BE%D...

Потерянное обновление (lost update):
2 запроса одновременно меняют значение поля в строке таблицы
UPDATE tbl1 SET f2=f2+20 WHERE f1=1; UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

В результате значение поля f2 по завершении обеих транзакций может увеличиться не на 45, а на 20 или 25, то есть одна из изменяющих данные транзакций «пропадёт».

Вопрос: учитывая, что каждый индивидуальный запрос в ms sql (и я читал, что не только в нем, а во-всех СУБД, которые поддерживают транзакции) выполняется в неявной автоматической транзакции, то с чего вдруг станет возможно "потерянное обновление"? Т.е. второй update же не сможет прочитать значение f2, пока автоматическая транзакция первого update не завершится.
Даже если предположим, что автор писал текст, предполагая отсутствие автоматических транзакций, то не понятно следующее: поскольку в ms sql автоматические транзакции точно есть, то для чего в ms sql в явных транзакциях есть поддержка уровня изоляции read uncommitted, который защищает только от "потерянных обновлений"? Ведь даже без явных транзакций итак должна быть защита от них, т.к. работают автоматические транзакции?

2) статья на хабре:
https://habrahabr.ru/company/infopulse/blog/261097/ - раздел Потерянное обновление – Интерпретация №2
он там приводит таблицу, в которой 2 параллельных update апдейтят одно и то же поле в БД аналогично тому, как было в статье в википедии. Но у автора на хабре оба запроса заключены в транзакции BEGIN TRAN; COMMIT TRAN; , но в результате он пишет, что произойдет "потерянное обновление".

Вопрос: как может произойти потерянное обновление, если оба его запроса выполняются каждый внутри своей транзакции?

Answer 1

[terrier]

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

Comments

[footballer]

Нет, я спрашивал именно про "уровни изоляции". Разъяснение по первому вопросу: если считать, что в ms sql существуют автотранзакции, а минимальный уровень изоляции транзакций = read uncommitted, то даже если у автотранзакций стоит минимальный уровень изоляции, то любые одиночные запросы (которые по умолчанию оборачиваются в автотранзакции) уже будут гарантировать отсутствие "потерянного обновления". Либо, если я ничего не понимаю, то объясните, плз, чтобы стало понятно.

Разъяснение по второму вопросу: на хабре 2 апдейта одновременно меняют одно и то же поля и оба запроса внутри транзакций. Опять таки, учитывая, что минимальный уровень изоляции транзакции гарантирует отсутствие "потерянного обновления", то откуда у него оно появляется? Вот у него результат:
Результат: Value = 6 Value = 8
но с транзакциями явно должно быть
Результат: Value = 6 Value = 13

[terrier]

У термина "потерянное обновление" есть несколько интерпретаций.
Простейший случай ( c двумя параллельными апдейтами ), который указан в википедии, и который проходит в статье на хабре под заголовком Интерпретация №1 практически невозможен даже при уровне изоляции READ UNCOMMITED ( см. табличку внизу в вики или под спойлер в статье на хабре ).
Однако возможны различные сценарии с вычитыванием во временную переменную и т.д., когда потерянное обновление будет даже при READ COMMITED. В вики про это не пишут, на хабре это проходит как Интерпретация №2. От такого READ COMMITED не защищает, надо защищаться самому. Сценариев потери апдейта много, вот еще, пример от оракла
docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT621
( Table 9-2 Conflicting Writes and Lost Updates in a READ COMMITTED Transaction
)

[footballer]

terrier:
На счет статьи на хабре понял, я каким-то образом не заметил, что там во временную переменную сохраняется.
Но все-равно не понятно это:

Интерпретация №1 практически невозможен даже при уровне изоляции READ UNCOMMITED ( см. табличку внизу в вики или под спойлер в статье на хабре ).

Я понимаю, что он невозможен при уровне изоляции READ UNCOMMITED. Вопрос был в том, что тогда когда он возможен? Когда транзакции вообще нет? Но ведь при отсутствии явной транзакции любые одиночные запросы неявно оборачиваются в автотранзакции, так? Т.е. даже если я выполняю параллельно 2 апдейта, не обернутые явно в транзакции и изменяющие одно и то же поле, то "потерянное обновление" все-равно невозможно, разве не так? а раз оно никогда не возможно, тогда зачем нужно создавать явно транзакцию с уровнем изоляции READ UNCOMMITED?

[terrier]

Я понимаю, что он невозможен при уровне изоляции READ UNCOMMITED. Вопрос был в том, что тогда когда он возможен? Когда транзакции вообще нет?

Когда изоляции транзакций нет. Отвлекаясь от баз данных, в любом языке программирования один поток делает a = a + 1 и другой поток делает a = a + 1. Если синхронизации ( т.е. изоляции ) нет, то на сколько увеличится значение переменной a? Вполне возможно, что на 1 - у нас потерянный апдейт. Однако, в системах, удовлетворяющих стандарту ANSI/ISO SQL такая ситуация запрещена, то есть хотя какая-нибудь изоляция есть всегда. Таким образом именно этот сценарий потери апдейта - чисто теоретический.

[footballer]

terrier:

Однако, в системах, удовлетворяющих стандарту ANSI/ISO SQL такая ситуация запрещена, то есть хотя какая-нибудь изоляция есть всегда

если говорить про ms sql , то хоть "какая-нибудь изоляция есть всегда" - это имеется ввиду, что даже одиночные запросы оборачиваются в автотранзакции, поэтому этот сценарий потери апдейта в ms sql - чисто теоретический?
Если так, то для чего тогда в ms sql есть уровень изоляции транзакций READ UNCOMMITTED? Т.е когда я перед запросом могу написать:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
и дальше в транзакции выполнить апдейт.
Для чего устанавливать уровень изоляции READ UNCOMMITTED , если получается, что итак этот сценарий потери апдейта невозможен благодаря автотранзакциям?

Хотя, сейчас появилась мысль: это может быть нужно, когда нужно несколько запросов обернуть в атомарную единицу, но при этом я почему-то хочу, чтобы при выполнении моих запросов существовало «грязное» чтение ? Тогда я вынужден обернуть запросы в транзакцию (для атомарности), но вынужден установить уровень READ UNCOMMITTED для существования грязного чтения. Это единственный вариант, который я нашел, для чего может быть нужен READ UNCOMMITTED. Я угадал?

[terrier]

даже одиночные запросы оборачиваются в автотранзакции, поэтому этот сценарий потери апдейта в ms sql - чисто теоретический?

Этот сценарий потери апдейта чисто теоретический, потому что запрещен стандартом.

но при этом я почему-то хочу, чтобы при выполнении моих запросов существовало «грязное» чтение

Мне сложно представить себе ситуацию, когда вы именно хотите грязное чтение. На более слабых уровнях изоляции применяются меньше локов, а следовательно производительность может быть больше. Именно это может быть мотивацией, чтобы их применять. В реляционных БД как правило ставить ниже READ COMMITED неразумно, а вот, например, в mongodb - READ UNCOMMITED - дефолтный уровень изоляции.

Answer 2

[Алексей Сумин]

Статья на википедии имеет более глубокие исторические корни, она не про то какие сейчас есть клевые СУБД, а о том как они развивались. Т.е. когда-то давно-давно, были простейшие СУБД без транзакций, без изолированности и пользователи при их эксплуатации сталкивались с определенными ошибками: кто-то увидел чужие данные которых не было, кто-увидел фантомные данные, которых больше нет и так далее, в ответ на эти проблемы появлялись более строгие уровни изоляции данных. При этом есть дилемма: чем выше уровень изоляции - тем ниже производительность системы. Т.е. это статья больше о теории хранения данных с совместным доступом, например, с какими проблемами вы столкнетесь, если захотите написать свой транзакционный движок.

Теперь про потерянное обновление, есть таблица Counter с одной строкой и полем count = 0.
Транзакция №1
UPDATE counter SET count = count + 10;
что в это время делает движок:
1. читает текущее значение count равное 0
2. прибавляет к нему 10
3. но транзакция еще не завершена, и новое значение 10 просто держится в памяти
В это время стартует Транзакция №2 с тем же самым запросом
UPDATE counter SET count = count + 10;
что делает движок? То же самое что и в первый раз:
1. читает текущее значение count равное 0, потому что первая транзакция еще не завершена, и вторая не видит её результатов <- это и есть уровень изоляции, мы или видим данные в чужой транзакции или нет.
2. прибавляет к нему 10
3. завершаем Транзакцию №2 и на диск сохраняется count равное 10.
После этого подоспела и первая транзакция, она тоже готова сохранить своё вычисленное значение count равное 10 и на диск снова пишется 10, а не 20, как мы ожидали.

Современные промышленные СУБД такой трюк провести не дадут (строки на время update будут заблокированы), а вот студент, который пишет программу для курсовой работы, может допустить такую не очевидную ошибку.

Comments

[footballer]

Современные промышленные СУБД такой трюк провести не дадут (строки на время update будут заблокированы)

строки будут заблокированы, даже если я явно не оберну update в транзакцию (потому что сработает автотранзакция)? Вот про это я и спрашивал, раз СУБД (ms sql) не дадут провести этот трюк, то для чего в той же ms sql нужен уровень изоляции READ UNCOMMITED ? Раз даже без выставления READ UNCOMMITED в явной транзакции все-равно этот трюк не пройдет. Раз в принципе невозможно потерянное обновление, то какой может быть смысл открывать транзакцию с READ UNCOMMITED ? Я выше предположил, что это может быть нужно, только если мне нужно несколько операций объединить в 1 атомарную единицу, т.е. будет не 1 апдейт, а несколько, и при этом мне нужно грязное чтение, тогда я буду вынужден открыть явную транзакцию с уровнем READ UNCOMMITED. Я угадал? Или не так?

[Алексей Сумин]

SQL стандарт определяет 4 уровня изоляции (от более строгих к менее строгим) : SERIALIZABLE, REPEATABLE READ, READ COMMITTED и READ UNCOMMITTED, чтобы соответствовать стандарту СУБД позволяют выставить любой из них. Но поскольку уровень READ UNCOMMITTED для реальных систем не приемлем и даже опасен, то в некоторых СУБД он включается только формально, например "In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED" https://www.postgresql.org/docs/9.3/static/sql-set...
Та же статья на википедии говорит, что Oracle тоже его не поддерживает фактически. Насчет MSSQL не знаю.

>какой может быть смысл открывать транзакцию с READ UNCOMMITED

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

при этом мне нужно грязное чтение

- Никому не нужно грязное чтение. Что будет, если при грязном чтении вы увидели данные другой транзакции "X" сделали на основании этих данных какой-то апдейт, а после этого транзакция "X" откатилась назад ?

только если мне нужно несколько операций объединить в 1 атомарную единицу, т.е. будет не 1 апдейт, а несколько

- это и есть транзакция, вы объединяете несколько запросов в одну транзакцию, и современные СУБД сделают всё, чтобы они прошли как одна атомарная операция.

я буду вынужден открыть явную транзакцию с уровнем READ UNCOMMITED.

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