Если UPDATE столкнулся с заблокированной строкой

[Akina]

Думаю, совершенно необходимо дать подробное объяснение, почему для запроса

UPDATE {table} SET {column1} = {value1} WHERE {column2} = {value2}

в случае, когда {column2}="id", выполняется блокировка (сессия 2), тогда как при {column2}="n" блокировки то нет (сессия 1), то есть (сессия 3). Потому что это имхо самый неочевидный в статье момент.

Да и вообще, я вижу "как", но не очень вижу "почему". Я бы даже сказал, что совсем не вижу. А ценность выводов в духе "запишите и запомните, потому что понять это невозможно" мне кажется не очень высокой.

[Mausglov]

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

[OlegIct]

Согласен. Ваши комментарии во всех темах профессиональны. Про блокировки по id и n подводных камней нет - срока блокируется, если WHERE истинно для неё. Первичный ключ роли не играет. Условия update выбраны, чтобы команды выглядели просто.

Строка, на которой заблокировался UPDATE обязана перечитыватся, чтобы не допустить феномена lost update, она перечитывается в обеих базах и они обе соответствуют стандарту SQL. Но Oracle добавляет от себя перечитывание всех незаблокированных строк (заблокированные не играют роли, так как другие сесии их не могут менять). Вкратце, это не дает преимуществ, как написал Mausglov. Это примерно как "что быстрее" shutdown transactional или immediate - без разницы, как повезёт, хотя кажется, что transactional как то "получше". Не вкратце, надо тратить время и текст бы усложнился. Цель статьи - описать просто и коротко, чтобы читатели запомнили важный нюанс про перечитывание. Если удастся просто сформулировать следствия этой разницы, то допишу. Основное: в Oracle, обычно, select for update используют (особо не задумываясь, потому что так принято), в PostgreSQL перечитывание только одной заблокированной строки позволяет быстро работать и без for update, то есть база хорошо работает с любыми командами.

[Mausglov]

запишите и запомните, потому что понять это невозможно

Тут других вариантов не остаётся. Инженеры PostgreSQL выбрали так, инженеры Oracle - этак. Каждый из вариантов по-своему справедлив.

[WaitEvent]

PostgreSQL doesn't have this (because it would require implicit savepoints for each statement, and savepoints are expensive in PostgreSQL) and restarts only the reading of the row. This results in inconsistent snapshots (results with rows from two states), but it still fits the SQL standard definition of read committed (which requires reading only the committed changes, ignoring that in MVCC databases, they can come from different commit times).

[OlegIct]

никаких откатов строк нет в PostgreSQL, точки сохранения не нужны. Это проверяется в PostgreSQL pageinspect и не приведено, чтобы не переусложнять. В Oracle Database блокировки со строк не снимаются.

[WaitEvent]

верно у пг нет микроткатов, там просто inconsistent snapshots. оракл же реализовал микрооткаты

[WaitEvent]

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

https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:11504247549852

[OlegIct]

утрируете :) Там путаница в терминах (введён термин "restart", микрорестарта и микроотката нет), применяется триггер, автономная транзакция, которыми нельзя доказать, что физически был откат строки. В конце концов предъявили Кайту "which seems to be the opposite of what you say in your blog. Could you please clarify". Для защиты чести Кайта был привлечен Стив Адамс, который увел тему в сторону "My oracle-l post was showing another reason why BEFORE ROW triggers should be avoided". Изоборетённый Кайтом "перезапуск" путает (блокировки строк не снимаются), "перечитывание" более корректно.

[WaitEvent]

нет там путаницы, Кайт обозвал это mini-rollback, на то они и мини, что не снимают блокировок. но тут то дело не в жонглировании терминов, а в наличии самого механизма. мсскл (на rc snapshot) блокировки предикатов накладывает, убивая параллельность и перфоменс, оракл mini-rollback устраивает, а пг ничего не делает и просто забивает на консистентность.

пост Кайта тогда открыл портал в ад и несколько лет каждый считал своим долгом хоть что-то ляпнуть на тему этих mini-rollback, т.к. формально они противоречили утверждению в документации (в доке утверждалось, что на RC запрос видит данные на момент старта запроса). автономные триггеры же лишь инструмент, помогающий понять что там под капотом происходит.

но это все лирика, тут важно что у пг без mini-rollback результат не другой, а неконсистентный.

[GerrAlt]

в команде UPDATE нельзя указать порядок просмотра строк (нет выражения ORDER BY)

а если я вначале сделаю select ... for update, а потом уже по полученному списку update то кажется таки смогу управлять порядком просмотра строк (для блокировки)

[OlegIct]

да, так часто и делают. Для PostgreSQL for no key update.

[Tzimie]

A MSSQL просто ждет конца блокировки чтобы проверить WHERE

[OlegIct]

Про другие базы (MSSQL, MySQL) интересно! Только надо поточнее сформулировать, что делает MSSQL, чтобы не запутаться. PostgreSQL и Oracle Database используют блокировки на уровне строк, а с ними (чтобы понять накладывать блокировку или пропустить строку), нужно проверить условие. WaitEvent написал, что MSSQL использует предикатные блокировки, которые менее гранулярны и снижают производительность.