Логика работы SELECT FOR UPDATE
Насколько понимаю логику работы SELECT FOR UPDATE SKIP LOCKED при паральленых вызовах будут взяты не заблокированные записи. Как понимаю проблема будет возникать при получении мин. значение CreateDate,
SELECT MIN("CreateDate") FROM "Document" заблокированные записи будут пропущены и актульное значение не получить. Подскажите пож-ста можно как-то исправить эту проблему?
polin11
05.04.23 02:50:37 MSK
- Ответить на это сообщение
- Ссылка
Не хочется влезать во флуд и холивар, но у тебя явно всё очень плохо с моделью данных. За исключением очень-очень редких случаев, select for update (и уж тем более множественный) это признак ошибки в проектировании. Да и вообще сам оператор update часто намекает на достаточно высокую вероятность проблемы в схеме БД.
no-dashi-v2 ★★
( 05.04.23 08:49:06 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Не совсем ясна задача.
Есть ощущение что возможно вам нужно завернуть всё это дело в функцию и применять pg_try_advisory_xact_lock() внутри неё.
Toxo2 ★★★
( 05.04.23 08:53:09 MSK )
- Ответить на это сообщение
- Ссылка
Select без for update не блокируется и ничего не пропускает.
maxcom ★★★★★
( 05.04.23 08:56:04 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Без понимания, что именно надо сделать в конечном итоге ничего сказать нельзя.
Ты описал пример неправильного подхода, а что считать правильным -неясно.
Psilocybe ★★★★
( 05.04.23 08:56:09 MSK )
Последнее исправление: Psilocybe 05.04.23 08:58:23 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от maxcom 05.04.23 08:56:04 MSK
Select без for update не блокируется
Мне показалось, что товарищ хочет как раз повесить блокировки на те записи, которые он хочет обновить позже в транзакции, но при этом пропустить те, которые уже заблокированы в аналогичном запросе другим процессом.
Вот для таких штук, когда надо быть уверенным, что никто другой кроме текущей транзакции не будет ничего менять — мы обычно используем рекомендательные блокировки.
Toxo2 ★★★
( 05.04.23 09:23:30 MSK )
- Ответить на это сообщение
- Ссылка
А для чего тебе вообще значение min() если у тебя условие where «CreateDate» < NOW()::DATE по сути делает все что нужно?
cobold ★★★★★
( 07.04.23 09:54:26 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от cobold 07.04.23 09:54:26 MSK
Это только если значение LIMIT будет 1, а у него 10.
Anoxemian ★★★★★
( 07.04.23 09:56:42 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от no-dashi-v2 05.04.23 08:49:06 MSK
Да это не холивар. Вот если бы он eav хотел сделать.
crutch_master ★★★★★
( 07.04.23 09:58:08 MSK )
- Ответить на это сообщение
- Ссылка
Нафига тебе двигать дату создания документа — не понятно. Она не должна двигаться по идее, ну да ладно.
Но она всегда будет меньше текущей, т.е. в прошлом.
цикле вешается транзация и пересчитывается поле
Ты зачем-то пытаешься делать императивно то, что императивно делать не надо.
Так можно просто сделать один аптейт с условием вместо всего этого цирка с блокировками. Когда закроешь транзакцию, все и встанет на свои места, пока она идёт, все будут получать то, что было.
crutch_master ★★★★★
( 07.04.23 10:02:01 MSK )
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Anoxemian 07.04.23 09:56:42 MSK
Если я правильно понял описанную логику, он сначала проверяет есть ли записи с датой меньше текущей в принципе(и для этого вычисляет min()) сравнивает в приложении полученную дату с текущей и потом уже выбирает циклом по 10 записей и апдейтит их. Во всем этом непонятно для чего ему эта проверка значения min() в приложении(а если приложение и база на разных хостах, то там еще и разное текущее время может быть)? Ведь можно сразу с ходу делать select for update
cobold ★★★★★
( 07.04.23 10:20:15 MSK )
- Ответить на это сообщение
- Ссылка
SELECT FOR UPDATE ОПЕРАТОР
Команда SELECT FOR UPDATE позволяет заблокировать записи в результирующем наборе курсора. Блокировка записей снимается, когда выполняются следующие команды commit или rollback.
Синтаксис
CURSOR имя_курсора
IS
select_statement
FOR UPDATE [OF column_list] [NOWAIT];
Параметры или аргументы
имя_курсора наименование курсора.
select_statement команда SELECT, которая будет заполнять результирующего набора курсора.
column_list столбцы в курсора результирующего набора, который вы хотите обновить.
NOWAIT не является обязательным. Курсор не будет ждать ресурсов.
Например, вы могли бы использовать оператор SELECT FOR UPDATE следующим образом:
14.2.3.4. Блокировка Чтений ( SELECT . FOR UPDATE и SELECT . LOCK IN SHAREMODE )
Если Вы запрашиваете данные и затем вставляете или обновляете связанные данные в пределах той же самой транзакции, постоянного клиента SELECT оператор не дает достаточную защиту. Другие транзакции могут обновить или удалить те же самые строки, которые Вы только запросили. InnoDB поддерживает два типа блокировки чтений, которые предлагают дополнительную безопасность:
- SELECT . LOCK IN SHARE MODE устанавливает коллективную блокировку на любых строках, которые читаются. Другие сеансы могут считать строки, но не могут изменить их до Ваших фиксаций транзакции. Если какая-либо из этих строк была изменена другой транзакцией, которая еще не фиксировала, Ваш запрос ожидает до той транзакции концы и затем использует последние значения.
- Для индексируют записи, с которыми поиск встречается, SELECT . FOR UPDATE блокирует строки и любые связанные элементы индекса, то же самое, как будто Вы вышли UPDATE оператор для тех строк. Другие транзакции блокируются от обновления тех строк от выполнения SELECT . LOCK IN SHARE MODE , или от чтения данных в определенных уровнях изоляции транзакции. Непротиворечивые чтения игнорируют любой набор блокировок на записях, которые существуют в представлении чтения. (Старые версии записи не могут быть заблокированы; они восстанавливаются, применяя отмену, входит в систему копия в памяти записи.)
Эти пункты прежде всего полезны, имея дело с или структурированными данными графика с древовидной структурой, или в единственной таблице или в разделении через многократные таблицы. Вы пересекаете края или древовидные ответвления от одного места до другого, резервируя право возвратиться и изменить любое из этих значений «указателя» .
Все блокировки, установленные LOCK IN SHARE MODE и FOR UPDATE запросы выпускаются, когда транзакция фиксируется или откатывается.
Блокировка строк для использования обновления SELECT FOR UPDATE только применяется, когда автоматическая фиксация отключается (любой, начиная транзакцию с START TRANSACTION или устанавливая autocommit к 0. Если автоматическая фиксация включается, строки, соответствующие спецификацию, не блокируются.
Примеры использования
Предположите, что Вы хотите вставить новую строку в таблицу child , и удостоверьтесь, что дочерняя строка ссорится в таблице parent . Ваш код программы может гарантировать ссылочную целостность всюду по этой последовательности операций.
Во-первых, используйте непротиворечивое чтение, чтобы запросить таблицу PARENT и проверьте, что родительская строка существует. Можете Вы безопасно вставлять дочернюю строку к таблице CHILD ? Нет, потому что некоторый другой сеанс мог удалить родительскую строку в момент между Вашим SELECT и Ваш INSERT , без Вас являющийся знающим об этом.
Чтобы избежать этой потенциальной проблемы, выполните SELECT использование LOCK IN SHARE MODE :
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
После LOCK IN SHARE MODE запросите возвращает родителя ‘Jones’ , можно безопасно добавить дочернюю запись на CHILD таблица и фиксация транзакция. Любая транзакция, которая пытается читать или записать в применимую строку в PARENT таблица ожидает, пока Вы не заканчиваетесь, то есть, данные во всех таблицах находятся в непротиворечивом состоянии.
Для другого примера рассмотрите целочисленное встречное поле в таблице CHILD_CODES , используемый, чтобы присвоить уникальный идентификатор каждому дочернему элементу, добавленному к таблице CHILD . Не используйте или непротиворечивое чтение или совместно используемое чтение режима, чтобы считать текущую стоимость счетчика, потому что два пользователя базы данных могли видеть то же самое значение для счетчика, и двойная ключевая ошибка происходит, если две транзакции пытаются добавить строки с тем же самым идентификатором к CHILD таблица.
Здесь, LOCK IN SHARE MODE не хорошее решение потому что, если два пользовательских чтения счетчик одновременно, по крайней мере один из них заканчивает мертвой блокировке, когда это пытается обновить счетчик.
Вот два способа реализовать чтение и постепенное увеличение счетчика без интерференции от другой транзакции:
- Сначала обновите счетчик, постепенно увеличивая это 1, затем считайте это и используйте новое значение в CHILD таблица. Любая другая транзакция, которая пытается считать счетчик, ожидает до Ваших фиксаций транзакции. Если другая транзакция находится в середине этой той же самой последовательности, Ваша транзакция ожидает до других фиксаций.
- Сначала выполните чтение блокировки встречного использования FOR UPDATE , и затем постепенно увеличьте счетчик:
SELECT counter_field FROM child_codes FOR UPDATE;UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT . FOR UPDATE читает последние доступные данные, устанавливая монопольные блокировки на каждой строке, которую они читают. Таким образом это устанавливает те же самые блокировки искавший SQL UPDATE установил бы на строках.
Предыдущее описание является просто примером как SELECT . FOR UPDATE работы. В MySQL определенная задача генерирования уникального идентификатора фактически может быть выполнена, используя только одиночное обращение к таблице:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);SELECT LAST_INSERT_ID();
SELECT оператор просто получает информацию об идентификаторе (определенный для текущего соединения). Это не получает доступ ни к какой таблице.
Форум пользователей MySQL
Здравствуйте, объясните мне пожалуйста как работает блокировка SELECT . FOR UPDATE? Читал документацию, но остаются вопросы, ответы которых не могу найти.
Допустим в разных частях приложения есть такой запрос
SELECT * FROM table WHERE status = 1 FOR UPDATE ;
В другой части меняется значение status
UPDATE table SET status = 1 WHERE >COMMIT
.
ROLLBACK
1. Блокируется таблица на любой запрос вида SELECT * FROM table WHERE status = somevalue; или только SELECT * FROM table WHERE status = somevalue FOR UPDATE; ?
2. Блокировка снимается в не зависимости произошел commit или rollback? Т.е на момент запроса если начата транзакция на редактирование, делается блокировка таблицы и снимается как только транзакция завершена?
3. Блокировка скажем так привязана именно к колонке которая используется в WHERE? Что если в WHERE используются несколько колонок?
4. Можно ли такую блокировку применить к insert?
Отредактированно webJunior (18.09.2018 18:12:42)
#2 18.09.2018 18:30:48
klow Старожил Зарегистрирован: 06.12.2014 Сообщений: 411
Re: Вопрос про FOR UPDATE
1. Блокируется не таблица, а только выбранные записи в таблице по FOR UPDATE.
2. Думаю да, иначе нет смысла в блокировке.
3. Колонки не имеют значения, без Where будут заблокированы все записи в таблице, но не сама таблица.
4. Это не имеет смысла так как блокируются только выбранные записи. К ним Инсерт не применишь.
Отредактированно klow (18.09.2018 18:33:54)
#3 18.09.2018 19:08:21
webJunior Завсегдатай Зарегистрирован: 30.03.2017 Сообщений: 30
Re: Вопрос про FOR UPDATE
klow написал:
1. Блокируется не таблица, а только выбранные записи в таблице по FOR UPDATE.
2. Думаю да, иначе нет смысла в блокировке.
3. Колонки не имеют значения, без Where будут заблокированы все записи в таблице, но не сама таблица.
4. Это не имеет смысла так как блокируются только выбранные записи. К ним Инсерт не применишь.
Спасибо за ответ. Хотел бы уточнить некоторые детали
1. Т.е блокировка вступает в силу если происходит изменение строк, которые подходят по условию в запросе FOR UPDATE, правильно? Если никаких транзакций нет на момент запроса FOR UPDATE или изменения происходят в строках, которые не подходят по условию, блокировок не происходит?
2. Допустим в таблице user_requests идет запись каких-то данных, на каждого юзера может быть только одна строка с status равным 0, как только status в этой строке сменился на 1, ему разрешается делать следующую запись.
Сперва делается SELECT на отсутствие записи с status=0 и после этого делается INSERT. Каким образом можно блокировать SELECT если происходит INSERT в эту таблицу?