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
По долгу службы мне приходится иногда проводить собеседования на позицию «[старший|младший] разработчик python/django», «тимлид». К моему великому удивлению я обнаружил, что 9 из 10 соискателей, в резюме которых значатся слова » Mysql/Innodb/transactions/triggers/stored proc etc.», абсолютно ничего не могут рассказать о своем прошлом опыте работы с ними. Ни одного описания варианта использования, к сожалению, я так и не получил.
Далее по собеседованию я предлагал попробовать предложить вариант решения для следующей ситуации:
Допустим, мы являемся онлайн сервисом, который в свою очередь пользуется каким-то внешним платным API (активация услуги, платный контент, или что вашей душе угодно), то есть наш сервис сам платит деньги за пользование API. Пользователь в нашей системе создает запрос на активацию услуги, заполняет все поля и на последней странице жмет кнопку «Активировать услугу». То есть на момент отправки HTTP запроса мы имеем в нашей БД запись (запрос на активацию услуги). Каков наш алгоритм?- спрашиваю я и сам продолжаю:
— достаем из базы баланс пользователя;
— если баланса достаточно, то дергаем API;
— если всё хорошо, то списываем с баланса сумму за услугу, делаем UPDATE, коммитим, иначе откатываемся;
— отвечаем пользователю.
Вроде бы всё тривиально, но когда привожу первую и самую очевидную проблему в виде 10 конкурентных запросов (что все они в начале получат одинаковый баланс и начнут звонить в API), решения начинают предлагать самые изощренные, начиная от выполнения 5 селектов (стоит признаться, я ничего не понял в этом варианте), использования автоинкрементных счетчиков, внешних кешей, новых таблиц в бд, слипов и еще не пойми чего.
Как известно (а это знали все кандидаты!), innodb в mysql предоставляет транзакционный механизм и возможность построчной блокировки. Для того, чтобы применить этот самый построчный лок, достаточно добавить в конце SELECT-а выражение FOR UPDATE, например так:
SELECT * FROM requests WHERE FOR UPDATE
Стартанет транзакция и все остальные сессии к базе данных не смогут выполнить аналогичный запрос до завершения нашей транзакции, просто будут ждать. На чтение же запись будет доступна в состоянии, которое зависит от уровня изоляции транзакции.
Также стоит заметить, что использование FOR UPDATE лучше делать с выключенным autocommit-ом, так как вне зависимости от того, что вы залочили, после первого апдейта лок снимется.
Вроде мелочь, вроде очевидно, но 9 из 10…
upd
прежнее название «Транзакции в mysql», не раскрытое в статье было заменено на » Транзакции в mysql и SELECT FOR UPDATE»
ЗЫ
в статье не говорится о том, что АПИ нужно дергать в рамках транзакции и что делать в случае сбоя и как обрабатывать исключительные ситуации.
Зачем нужен SELECT … FOR UPDATE?
Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE / SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. (…)
Я понимаю это так, что если я в транзакции без явного указания уровня изоляции сделаю:
SELECT "id" FROM "table" WHERE /* … */ Потом обработаю результаты в приложении, а потом:
UPDATE "table" SET "flag" = TRUE WHERE "id" IN (/* … */) То получу нужный результат, а именно обновление всех строк с нужными ID, вне зависимости от того, что там удаляют или изменяют другие транзакции. Но судя по тому, что существует такая вещь как SELECT /* … */ FOR UPDATE , это не так? В каких случаях использование SELECT /* … */ FOR UPDATE необходимо? Как на это влияет уровень изоляции транзакций? Нужен ли SELECT /* … */ FOR UPDATE на уровне SERIALIZABLE ?
Отслеживать
задан 31 дек 2019 в 20:55
16k 3 3 золотых знака 25 25 серебряных знаков 41 41 бронзовый знак
Представим, что в первом where у вас flag=false, т.е. вы делаете какую то работу над строками которые false и после этого делаете их true. Так вот, между моментом вашего select и update другая транзакция так же могла поработать с этой записью и уже сделать ее true (вы же ее читали как false). В вашем случае это конечно не страшно. Но вот если бы вы скажем из этой записи перед тем как взводить флаг взяли бы данные, например количество денег на счете и что то с ними сделали и другая транзакция то же что то сделала, то были бы большие проблемы.
31 дек 2019 в 21:00
for update блокирует запись и не позволит другим транзакциям выполнять над ней действия, пока вы не закончите
31 дек 2019 в 21:00
@Mike ваш комментарий ответом надо, я проголосовал бы
14 янв 2020 в 0:10
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Пока у вас транзакции следуют строго одна за другой — у вас всё хорошо и нет проблем. Кроме производительности. А чтобы улучшить производительность — необходимо разрешить транзакции выполнять параллельно. И вот тут начинается богатый и поразительный мир concurrently control . При том, не только в базах данных, а везде где хоть что-то выполняется параллельно.
На деньгах люди обычно лучше понимают, так что будем говорить про деньги. Допустим есть пользователь, у него есть 100 денег на счету. Пользователь может их тратить, вы проверяете баланс select balance . , затем обновляете баланс при покупке update . set balance = ? where . . И вот в счастливый день как-то так вышло, что приходят сразу два запроса на покупки для этого пользователя. Одна на 50 денег, вторая на 70. Одна из них должна быть отклонена, т.к. денег недостаточно. Но в результате получается что обе покупки прошли и у вас проблема, вы продали то что не надо было. И это даже не видно по балансу пользователя. Как же?
Это типичный race condition , обе транзакции сначала данные читают, потом локально что-то делают, потом что-то пишут.
- читать им никто не мешает, потому обе транзакции прочитали что у пользователя 100 денег
- обе транзакции закономерно решили что денег достаточно
- обе транзакции обновили баланс пользователя
На конкурентном доступе к ресурсу подрались только на последнем шаге, транзакция которая начала обновлять данные позже сначала подождала завершение первой транзакции. А затем банально перезаписала баланс на тот который считала правильным сама. Так называемая lost update аномалия.
То есть, в существующем виде эти две транзакции были неверно сериализованы. Для корректного выполнения логики вторая пришедшая транзакция должна была подождать результат первой транзакции до чтения баланса пользователя. Но базу данных об этом не предупредили, и вполне закономерно этот первый select был расценен как не мешающий другим.
Вот как раз для того чтобы предупредить СУБД о том, что мы планируем с данными что-то делать, а потому нам надо сериализовать транзакции иначе, и существует FOR SHARE , FOR UPDATE дополнения. Потому они кстати и задокументированы в разделе Explicit Locking
- Если ничего не указано в select — то из-за сущности MVCC реализации postgresql транзакции смогут читать данные чуть менее чем всегда. Даже если прямо сейчас другая транзакция эту строку уже обновляет — мы получим последнее известное зафиксированное значение этой строк.
- если запросить явно FOR SHARE — то читать мы сможем в много потоков с этой FOR SHARE блокировкой не блокируя друг друга. Но вот если кто-то захочет обновить эту строку — то он встанет в очередь ожидания пока не завершатся все транзакции удерживающие читающую блокировку и вместе с тем задержит все последующие FOR SHARE транзакции.
- если запросить FOR UPDATE — то мы можем быть уверены, что ни одна другая транзакция не сможет обновить эту строку до конца нашей транзакции.
То есть нужны в тех местах, где без этого конкурентныая транзакция может сериализоваться логично с точки зрения СУБД, но некорректно для бизнес логики приложения.
Serializable
Serializable transactions are just Repeatable Read transactions which add nonblocking monitoring for dangerous patterns of read/write conflicts
Реализация Serializable в postgresql отслеживает изменения которые повлекут нарушение изоляции транзакций и все подверженные транзакции получат ошибку сериализации. Само приложение должно быть готово работать на этом уровне изоляции и предполагать получить ошибку сериализации в любой момент времени, быть готовым повторить транзакцию заново.
Нужен ли здесь FOR UPDATE — просто процитирую документацию с советами по уменьшению проседания производительности от Serializable уровня изоляции:
Eliminate explicit locks, SELECT FOR UPDATE, and SELECT FOR SHARE where no longer needed due to the protections automatically provided by Serializable transactions.
На этом уровне изоляции явные блокировки не нужны.
в транзакции без явного указания уровня изоляции
Будет использован уровень указанный в настройке default_transaction_isolation . То есть это может быть и Serializable вместо Read Committed.
получу нужный результат, а именно обновление всех строк с нужными ID, вне зависимости от того, что там удаляют или изменяют другие транзакции.
Вы получите обновление всех строк попадающих под условие. А вот нужен ли именно этот результат — вот ключевой вопрос.
Mysql select for update как работает
В некоторых случаях использовать согласованное чтение нецелесообразно. Приведем пример. Допустим, что необходимо добавить новую строку в таблицу CHILD , предварительно убедившись, что для нее имеется родительская строка в таблице PARENT .
Предположим, что для чтения таблицы PARENT было использовано согласованное чтение, и в таблице была обнаружена родительская строка. Можно ли теперь безопасно добавить дочернюю строку в таблицу CHILD ? Нет, потому что в это время другой пользователь мог без вашего ведома удалить родительскую строку из таблицы PARENT .
В данной ситуации необходимо выполнить операцию SELECT в режиме блокировки, LOCK IN SHARE MODE .
SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Выполнение чтения в режиме совместного использования ( share mode ) означает, что считываются самые новые доступные данные и производится блокировка строк, чтение которых осуществляется. Если последние данные принадлежат еще не зафиксированной транзакции, мы ждем, пока транзакция не будет зафиксирована. Блокировка в режиме совместного использования не позволяет другим пользователям обновить или удалить читаемую строку. После того, как указанный выше запрос вернет родительскую строку ‘Jones’ , мы можем безопасно добавить дочернюю строку в таблицу CHILD и зафиксировать транзакцию. В этом примере показано, как использовать целостность ссылочных данных в своей программе.
Рассмотрим еще один пример. Пусть у нас есть поле целочисленного счетчика в таблице CHILD_CODES , которое мы используем для назначения уникального идентификатора каждой дочерней записи, добавляемой к таблице CHILD . Очевидно, что использование согласованного чтения или чтения в режиме совместного доступа для получения текущего значения счетчика не подходит, так как два пользователя базы данных могут получить одно и то же значение счетчика и создать дублирующиеся ключи при добавлении двух дочерних записей в таблицу.
Для этого случая возможны два способа произвести чтение и увеличить значение счетчика: (1) сначала обновить значение счетчика, увеличив его на 1, и только после этого прочитать его или (2) сначала прочитать счетчик в режиме блокировки FOR UPDATE , а после этого увеличить его значение:
SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE; UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;
Оператор SELECT . FOR UPDATE прочитает последние доступные данные с установкой отдельной блокировки на каждую считываемую строку. Таким образом, блокировка на строки устанавливается точно так же, как и в случае поиска по UPDATE .