Comments 22
Возможно, было бы лучше переименовать FOR UPDATE в FOR KEY UPDATE и переназначить команду FOR UPDATE на более слабый режим FOR NO KEY UPDATE.
Или ввести необязательное слово KEY таким образом:
FOR [KEY] UPDATE
те, кому важно не путаться, начнут его использовать
Чтоб ничего не блокировать - из документации:
To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option.
Это подходит не всегда:
С NOWAIT оператор выдаёт ошибку, а не ждёт, если выбранную строку нельзя заблокировать немедленно. С указанием SKIP LOCKED выбранные строки, которые нельзя заблокировать немедленно, пропускаются.
FOR UPDATE этим и хорош - точечная блокировка, позволяющая получить максимально возможную скорость. Расплатой за это выступает сложность кода.
есть третий, более лучший вариант. В Oracle Database есть третья опция "WAIT n" - ждать n секунд, кроме NOWAIT. В PostgreSQL у команды нет опции, но есть более функциональный аналог в виде параметра lock_timeout:
/* первая сессия */
START TRANSACTION;
SELECT p_val FROM parent WHERE p_id = 1 FOR UPDATE;
/* вторая сессия подвисает до снятия блокировки или таймаута */
set lock_timeout = '1s';
delete from parent where p_id=1;
ERROR: canceling statement due to lock timeout
CONTEXT: while deleting tuple (0,1) in relation "parent"Его не указали на странице документации к команде SELECT, где описаны NOWAIT и SKIP LOCKED, поэтому он малоизвестен.
Смысл статьи в том, что вместо FOR UPDATE в PostgreSQL почти всегда стоит использовать FOR NO KEY UPDATE. То есть при переносе кода с других баз данных или при аудите древнего кода, если есть FOR UPDATE, то стоит проверить - можно ли заменить на FOR NO KEY UPDATE. Обычно такое и делают после переноса кода, так как если FOR UPDATE создаёт излишние блокировки другим сессиям, то обычно возникают взаимоблокировки: в начале статьи поводом к разбирательству были жалобы на взаимоблокировки.
for update без этих опций - будет ждать разблокировки. Это то, о чем в статье сказали "плохо". С ними - не будет. Значит все будет "хорошо".
Часто встречается использование этой конструкции для блокировки select в получении некоторого somefield, когда это поле меняется в параллельной транзакции, чтобы получить актуальное значение, когда поле используется как сквозной счетчик например (всякие кейсы со счетчиком лайков в соц сети или тому подобное) . Т.е. для конкурентного доступа к счетчику.
Ну вот, сделали функционал, а он вредным оказался. И так у нас всегда.
немного не так. Сначала сделали автомобили (FOR UPDATE), потом обнаружили, что они загрязняют окружающую среду, сделали катлизатор (FOR NO KEY UPDATE), но заставлять ставить катализаторы не стали - не переименовали FOR UPDATE в FOR KEY UPDATE, а FOR NO KEY UPDATE в FOR [NO KEY] UPDATE, так как реляционные базы консервативны и в них без спросу не переводят на "лучший тариф".
В постгресе еще есть такие же места - катализаторы, когда не стали менять старое поведение для сохранения обратной совместимости, но в новом коде лучше применять их, как, например отсутствие принудительного создания индекса на колонке, на которую навешен foreign key constraint. Можно буквально по руке с двумя пальцами пересчитать ситуации, когда этот индекс не нужен. Но postgresql не создает индекс, и нужно не забыть его создать, и если колонка nullable, то вполне применим и условный индекс с where column_name is not null
Ну это же история про оптимистичные и пессимистичные блокировки. И про то, что foreign key приводит к каскадным блокировкам в рамках SQL стандарта (вот про стандарт могу приврать).
Правильный подход - foreign key не использовать, select for update использовать для того, чтобы проверить, что с момента последнего чтения запись (иногда несколько да ещё и в связанных сущностях) не изменилась и если не изменилась, то записать. Если блокировать на время проверки не будете, то между чтением и записью может кто-нибудь вклиниться. Этот кейс особенно актуален для программ, связанным с финансами, материальными ценностями, ключевыми справочниками. Мало актуален для большинства транзакций в e-commerce, но и там тоже обязательно встречается.
констрейнты всегда лучше создавать. Чтобы не было каскадных блокировок, нужно создавать индекс по столбцам FK, выше об этом написали. Оптимистичные блокировки по большей части - зло, их можно использовать в случае, если они помогают не держать открытой простаивающую транзакцию. FOR NO KEY UPDATE - хорошо и может использоваться, где надо
"Напрасно ты винишь в непостоянстве рок;
Что не внакладе ты, тебе и невдомек.
Когда б он в милостях своих был постоянен,
Ты б очереди ждать своей до смерти мог."
Омар Хайям
Такая цитата была на стартовой страницы диалоговой программы Примус задолго до появления сколько-нибудь вменяемых программ на ПК.
Очереди, это в системах с многими пользователями неизбежное явление к которому просто надо относится как к данности.
Любые попытки что-либо выкруживать заканчиваются еще большими проблемами чем просто ждать и уметь реагировать на то что ожидание может закончится отказом, timeout-ом.
Не знаю что там в PostgreSQL, но в DB2 есть только один исход кроме просто успешного завершения транзакции после возможного ожидания в очереди - это системная авария после установленного периода ожидания ресурса - timeout.
Это SQLCODE -911
Кстати такой же код имеется и в Оракл. Да и многоие другие тоже совпадают. А почему? Да потому что Лари Элистон списывал их с DB2. Вот он из Оракле дока (https://www.databasestar.com/oracle-sqlcode-list/):
-911 The current unit of work has been rolled back due to deadlock or timeout. reason reason-code, type of resource resource-type, and resource name resource-name
А вот из DB2 (https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=codes-sql):
-911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR
TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.
Берём еще пару кодов из DB2:
-901 UNSUCCESSFUL EXECUTION CAUSED BY A SYSTEM ERROR THAT DOES NOT PRECLUDE THE SUCCESSFUL EXECUTION OF SUBSEQUENT SQL STATEMENTS
-206 object-name IS NOT VALID IN THE CONTEXT WHERE IT IS USED
Из Оракл:
-901 Unsuccessful execution caused by a system error that does not preclude the successful execution of subsequent SQL statements
-206 column-name is not a column of an inserted table, updated table, or any table identified in a from clause
А вот интересный код -805. В DB2 это:
-805 PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason-code
В Оракл:
-805 Dbrm or package name location-name.collection-id.dbrm-name.consistency-token not found in plan plan-name. reason reason
И там и там есть некий dbrm. Но если поискать что это такое, то в DB2 это будет:
The main output from the precompiler is a database request module (DBRM). A DBRM is a data set that contains SQL statements and host variable information that is extracted from the source program during program preparation. The purpose of a DBRM is to communicate your SQL requests to DB2 during the bind process.
А в Орал (гугл дает) это:
.... Oracle database resource manager (DBRM)
И это никак не матчится с объяснением SQLCODE -805 в Оракл. Плагиат.
Потому что Оракл скопировал эти коды с DB2 без осмысления. Некоторым он придал смысл совпадающий с смыслом в DB2, а другие (-805) просто оставил (ссылка на Оракл коды датирована Jan 27, 2016. Да это не официальная документация, но откуда это взялось?).
когда Эллисон списывал DB2 ещё не было 🫢
Списывал с System R, что не скрывают. Oracle released the world's first commercial SQL database in 1979 based on the System R specs. Стандартов тогда не было, сделали совместимым с System R насколько могли
Ну да, это конечно очень важно что и как называлось изначально и кто первым прибежал на рынок продавать свои пирожки.
z/OS появилоась в начале 2000-х, а перед этим называлась OS/390, еще раньше MVS, и даже OS/360. Когда по вашему появилась z/OS?
Тем не менее:
System R's first customer was Pratt & Whitney in 1977.
И да, первая ИБМ БД коммерциализирована была позже:
SQL/DS[1] (Structured Query Language/Data System),[2] released in 1981, was IBM's first commercial relational-database management system.[3] It implemented the SQL database-query language.
А DB2 еще позже:
The name DB2 (IBM Database 2), was first given to the database management system in 1983 when IBM released DB2 on its MVS mainframe platform.[14] IBM's endorsement of SQL in Db2 caused the industry to move to it from alternatives like Ingres's QUEL.[15] Db2 became generally available to customers in 1985,[16] and by 1989 revenue of about $1 billion had grown to equal IMS's.
Но в истории DB2 на Вики также говорится:
DB2 traces its roots back to the beginning of the 1970s, when Edgar F. Codd, a researcher working for IBM, described the theory of relational databases, and in June 1970, published the model for data manipulation.[10]
In 1974, the IBM San Jose Research Center developed a related database management system (DBMS) called System R, to implement Codd's concepts.[11] A key development of the System R project was the Structured Query Language (SQL).
Посмотрите (уверен Вы и так знаете) чем была тогда IBM в ИТ и чем Оракл. В 70-е по рыночным показателям в ИТ ИБМ, стоявшую на первом месте, сравнивили с следущими 10 компаниями суммарно. А Оракл лишь только появился:
Oracle Corporation is an American multinational computer technology company headquartered in Austin, Texas.[5] Co-founded in 1977 in Santa Clara, California, by Larry Ellison, who remains executive chairman .....
Larry Ellison, Bob Miner, and Ed Oates co-founded Oracle in 1977 in Santa Clara, California, as Software Development Laboratories (SDL).[2][10] Beginning as consultants with a background in large-scale memory after a project for Ampex,[11] Ellison took inspiration[12] from the 1970 paper written by Edgar F. Codd on relational database management systems (RDBMS) named "A Relational Model of Data for Large Shared Data Banks."[13] He heard about the IBM System R database[11] from an article in the IBM Research Journal provided by Oates. Ellison wanted to make Oracle's product compatible with System R, but failed to do so as IBM kept the error codes for their DBMS a secret.
Как оказалось не в таком уж и секрете деражались эти коды.
Спросите себя как можно создать что-то раньше того что берется как образец для создания? Это все равно как сказать что ребенок появился раньше матери. Да, мать не продавалась на рынке, но она не перестала из-за этого быть матерью.
Ну а что сама БД Оракл представляла из себя когда вдруг первой коммерциализирована оказалась:
First commercially available SQL RDBMS. Basic SQL queries, simple joins[35] and
CONNECT BYjoins. Atomic role-level SQL statements. Rudimentary concurrency control and database integrity. No query optimizer. Written in assembly language for the PDP-11[8] to run in 128KB of RAM.[36] Ran on PDP-11 and VAX/VMS in PDP-11 compatibility mode.[8]
В тоже время System R:
History of System R can be divided into three phases. "Phase
Zero" of the project, which occurred during 1974 and-most of 1975, involved the development of the SQL
user interface [14] and a quick implementation of a subset of SQL for
one user at a time. The Phase Zero prototype, described in [2], provided
valuable insight in several areas, but its code was eventually abandoned.
"Phase One" of the project, which took place throughout most of 1976
and 1977, involved the design and construction of the full-function,
multiuser version of System R. An initial system architecture was presented in [4]
and subsequent updates to the design were described in [10].
"Phase Two" was the evaluation of System R in actual use. This occurred during
1978 and 1979 and involved experiments at the San Jose
Research Laboratory and several other user sites. The results of some
of these experiments and user experiences are described in [19-21]. At
each user site, System R was installed for experimental purposes only, and
not as a supported commercial product.......
Phase One: Construction of a Multiuser Prototype
The Optimizer
Building on our Phase Zero experience, we designed the System R optimizer to minimize the weighted sum of the predicted number of I/Os and RSS calls in processing an SQL statement (the relative weights of these two terms are adjustable according to system configuration).
Я понимаю что Вы в теме баз данных, много знаете и имеете большой опыт. Но нельзя же так безалаберно относиться к истории того с чем Вы наверное провели много времени. Повторять тухлые заклинания не значит демонстрировать погружение в суть процессов на сегодня уже ставших историей.
Датой рождения Оракл уместнее считать вот эту версию:
Oracle v3 3.1.3. 1983 год. Concurrency control, data distribution, and scalability. Re-written in C for portability to other operating systems, including UNIX.
А правильнее вот эту:
Oracle v4 4.1.4.0 1984 .4 Multiversion read consistency. Halloween Problem solved. Improved concurrency.[8] First version available for MS-DOS[32][33] and IBM mainframe.
Надо иметь в виду что в 70-е, да и в 80-е в области баз данных превалировала БД ИБМ IMS:
The IBM Information Management System (IMS) is a joint hierarchical database and information management system that supports transaction processing.[3] Development began in 1966 to keep track of the bill of materials for the Saturn V rocket of the Apollo program, and the first version on the IBM System/360 Model 65 was completed in 1967 as ICS/DL/I and officially installed in August 1968.
IBM rebranded it IMS/360 in 1969, and ported it to new platforms as they emerged. In 1988, the company claimed that there were 7,000 IMS sites active worldwide. and went on to see extensive use and continual improvement to this day. IMS's most successful year in terms of sales was in 2003, 35 years after it was released. It was in use by over 95% of the Fortune 1000.[4]
Альтернативой IMS была БД ADABAS фирмы Software AG (весьма интересная БД в свете появившейся тенденции к No-SQL):
The company was founded in 1969 by six young employees at the consulting firm AIV (Institut für Angewandte Informationsverarbeitung). One of the founders was the mathematician Peter Schnell, who later became chairman of the board for many years.
ADABAS was launched in 1971 as a high-performance transactional database management system. In 1979, Natural, a 4GL application development English-like language, that was mainly developed by Peter Pagé, was launched.
Также имела успех БД IDMS. Я завтра буду участвовать в миграции БД IDMS в MS SQL.
Вот (без комментариев) эта конструкция (FOR UPDATE) из документации DB2 for z/OS:
update-clause
Last Updated: 2025-07-22
The optional FOR UPDATE clause identifies the columns that can appear as targets in an assignment clause in a later positioned UPDATE statement.
update-clause
FOR UPDATE [OF column-name]
Each column name must be unqualified and must identify a column of the table or view identified in the first FROM clause of the fullselect. The clause must not be specified if the result table of the fullselect is read-only.
If FOR UPDATE clause is specified with a column-name list, and extended indicators are not enabled, column-name must be an updatable column.
If the FOR UPDATE clause is specified without a column-name list, the implicit list of column names consists of all updatable columns of the table or view that is identified in the first FROM clause of the fullselect.
If a dynamically prepared select-statement does not contain a FOR UPDATE clause, the cursor that is associated with the select statement cannot be referenced in a positioned UPDATE statement.
If a statically prepared select-statement does not contain a FOR UPDATE clause, and its result table is not read-only, an implicit UPDATE clause will result. The implicit column name list is determine as follows:
If extended indicators are enabled, all columns of the table or view that is identified in the first FROM clause of the fullselect are included.
Otherwise, all updatable columns of the table or view that is identified in the first FROM clause of the fullselect are included.
The declaration of a cursor referenced in a positioned UPDATE statement need not include an UPDATE clause if the STDSQL(YES) or NOFOR SQL processing option is specified when the program is prepared. For more information, see Positioned updates of columns.
When FOR UPDATE is used, FETCH operations referencing the cursor acquire U or X locks rather than S locks when:
The isolation level of the statement is cursor stability.
The isolation level of the statement is repeatable read or read stability and the RRULOCK subsystem parameter is set to YES.
The isolation level of the statement is repeatable read or read stability and USE AND KEEP EXCLUSIVE LOCKS or USE AND KEEP UPDATE LOCKS is specified in the SQL statement, an X lock or a U lock, respectively, is acquired at fetch time.
No locks are acquired on declared temporary tables. For a discussion of U locks and S locks, see Lock modes and compatibility of locks.
Notes
Referencing columns that will be updated:
If a cursor uses FETCH statements to retrieve columns that will be updated later, specify FOR UPDATE OF when you select the columns. Then specify WHERE CURRENT OF in the subsequent UPDATE or DELETE statements. These clauses prevent Db2 from selecting access through an index on the columns that are being updated, which might otherwise cause Db2 to read the same row more than once.
Перевод:
Имя каждого столбца должно быть неквалифицированным и должно идентифицировать столбец таблицы или представления, указанного в первом предложении FROM полной выборки. Это предложение не должно быть указано, если результирующая таблица полной выборки доступна только для чтения. Если предложение FOR UPDATE указано со списком имён столбцов и расширенные индикаторы не включены, имя столбца должно быть обновляемым столбцом. Если предложение FOR UPDATE указано без списка имён столбцов, неявный список имён столбцов состоит из всех обновляемых столбцов таблицы или представления, указанного в первом предложении FROM полной выборки. Если динамически подготовленный оператор выбора не содержит предложения FOR UPDATE, на курсор, связанный с оператором выбора, нельзя ссылаться в позиционированном операторе UPDATE. Если статически подготовленный оператор SELECT не содержит предложения FOR UPDATE, а его результирующая таблица не доступна только для чтения, будет получено неявное предложение UPDATE. Неявный список имён столбцов определяется следующим образом:
Если включены расширенные индикаторы, включаются все столбцы таблицы или представления, указанные в первом предложении FROM полной выборки.
В противном случае включаются все обновляемые столбцы таблицы или представления, указанные в первом предложении FROM полной выборки.
Объявление курсора, на который ссылается позиционируемый оператор UPDATE, не обязательно должно включать предложение UPDATE, если при подготовке программы указан параметр обработки SQL STDSQL(YES) или NOFOR. Подробнее см. в разделе «Позиционированные обновления столбцов».
При использовании FOR UPDATE операции FETCH, ссылающиеся на курсор, получают блокировки U или X, а не S, если:
Уровень изоляции оператора — устойчивость курсора.
Уровень изоляции оператора — повторяющееся чтение или устойчивость чтения, а параметр подсистемы RRULOCK установлен в значение YES.
Уровень изоляции оператора — повторяемое чтение или стабильность чтения, и если в операторе SQL указано USE AND KEEP EXCLUSIVE LOCKS или USE AND KEEP UPDATE LOCKS, во время выборки устанавливается блокировка X или U соответственно.
На объявленные временные таблицы блокировки не устанавливаются. Подробнее о блокировках U и S см. в разделе «Режимы блокировок и совместимость блокировок».
Примечания Ссылки на обновляемые столбцы: Если курсор использует операторы FETCH для извлечения столбцов, которые будут обновлены позже, укажите FOR UPDATE OF при выборе столбцов. Затем укажите WHERE CURRENT OF в последующих операторах UPDATE или DELETE. Эти условия не позволяют DB2 выбирать доступ через индекс к обновляемым столбцам, что в противном случае может привести к многократному чтению одной и той же строки.
FOR UPDATE OF есть и в PostgreSQL и есть FOR NO KEY UPDATE OF и др.
В Oracle указываются имена столбцов, но они нужны всего лишь, чтобы определить строки в каких таблицах заблокировать. Использовпние столбцов всегда удивляло, так как уровень блокировки - строка. В PostgreSQL вместо столбцов после OF указывается имя таблицы (или псевдоним), что проще и понятнее.
OF используется, если в запросе соединяются таблицы, с одной таблицей не используется.
Решение: выкинуть дедовские fk и забыть как мертворожденную херню для пхп и джанги, типа хранимок. Sfusl база и используется постоянно, проблемы обычно наоборот, когда кто то про них забывает и начинает юзать лок страницы/таблицы вместо лока строки
Не понимаю, почему задизлайкали. Реально, в бигтехе уже практически никто не использует бесполезные fk и переносит всю логику обработки в код. И ладно fk, но на них ведь ещё некоторые разработчики накидывают constraints по типу on delete cascade. Извращение, да и только
Не понимаю, почему задизлайкали.
Потому что это:
в бигтехе уже практически никто не использует бесполезные fk и переносит всю логику обработки в код
выдаёт непонимание самой сути предмета обсуждения, самых основ, а также неуместный максимализм (в БД или снаружи держать логику - это предмет отдельной дискуссии, в статье же речь про непротиворечивость и нормализацию данных)
Почему SELECT FOR UPDATE считается вредным в PostgreSQL