Как заблокировать строку InnoDB, которая еще не существует?

Как я могу гарантировать, что я могу искать, если имя пользователя существует в моей базе данных, а затем вставить это имя пользователя в базу данных в виде новой строки без какого-либо перехвата между SELECT а также INSERT заявления?

Как будто я блокирую строку, которой не существует. Я хочу заблокировать несуществующую строку с именем пользователя "Foo", чтобы теперь я мог проверить, существует ли она в базе данных, и вставить ее в базу данных, если она еще не существует без прерывания.

Я знаю что используя LOCK IN SHARE MODE а также FOR UPDATE существуют, но, насколько я знаю, это работает только на строки, которые уже существуют. Я не уверен, что делать в этой ситуации.

Источник

4 ответа

Решение

Если есть индекс на username (что должно быть, если нет, добавьте один, и, как правило, UNIQUE один), то выдача SELECT * FROM user_table WHERE username = 'foo' FOR UPDATE; предотвратит любое одновременное преобразование от создания этого пользователя (а также возможное значение "предыдущий" и "следующий" в случае неуникального индекса).

Если подходящий индекс не найден (для WHERE условие), тогда эффективная блокировка записи невозможна, и вся таблица становится заблокированной *.

Эта блокировка будет удерживаться до конца транзакции, выдавшей SELECT ... FOR UPDATE,

Некоторая очень интересная информация по этой теме может быть найдена на этих страницах руководства.

* Я говорю " эффективный", потому что фактически блокировка записей - это блокировка индексных записей . Если подходящий индекс не найден, можно использовать только кластерный индекс по умолчанию, и он будет заблокирован полностью.

Источник

Хотя приведенный выше ответ верен в том смысле, что SELECT ... FOR UPDATE предотвратит одновременную сессию / транзакцию от вставки одной и той же записи, это не полная правда. В настоящее время я борюсь с той же проблемой и пришел к выводу, что SELECT ... FOR UPDATE почти бесполезен в этой ситуации по следующей причине:

Параллельная транзакция / сеанс также может выполнить SELECT ... FOR UPDATE для того же значения записи / индекса, и MySQL с радостью примет это немедленно (без блокировки) и без выдачи ошибок. Конечно, как только другой сеанс сделает это, ваш сеанс также не сможет больше вставить запись. Ни ваш, ни другой сеанс / транзакция не получают никакой информации о ситуации и думают, что они могут безопасно вставить запись, пока они на самом деле не попытаются это сделать. Попытка вставить затем приводит либо к взаимоблокировке, либо к ошибке дублированного ключа, в зависимости от обстоятельств.

Другими словами, SELECT ... FOR UPDATE запрещает другим сессиям вставлять соответствующие записи, НО, даже если вы делаете SELECT ... FOR UPDATE и соответствующая запись не найдена, есть вероятность, что вы не сможете на самом деле вставить эту запись. ИМХО, это делает метод "сначала запрос, затем вставка" бесполезным.

Причиной проблемы является то, что MySQL не предлагает какого-либо метода для реальной блокировки несуществующих записей. Две одновременных сессии / транзакции могут блокировать несуществующие записи "FOR UPDATE" одновременно, что на самом деле не должно быть возможным и которое значительно усложняет разработку.

Кажется, что единственный способ обойти это - использовать таблицы семафоров или блокировать всю таблицу при вставке. Пожалуйста, обратитесь к документации MySQL для дальнейшей ссылки на блокировку целых таблиц или использование таблиц семафоров.

Просто мои 2 цента...

Источник

Блокировка несуществующей записи не работает в MySQL. Есть несколько сообщений об ошибках об этом:

Одним из обходных путей является использование таблицы мьютекса, где существующая запись будет заблокирована до вставки новой записи. Например, есть две таблицы: продавцы и продукты. У продавца много товаров, но не должно быть дубликатов товаров. В этом случае таблица продавцов может использоваться как таблица мьютексов. Перед тем, как вставить новый продукт, будет создана блокировка в записи продавца. С помощью этого дополнительного запроса гарантируется, что только один поток может выполнить действие в любой момент времени. Нет дубликатов. Нет тупика.

Источник

Вы "нормализуете"? То есть таблица - это список пар идентификаторов и имен? И вы вставляете новое "имя" (и, вероятно, хотите, чтобыid для использования в других таблицах)?

Тогда имейте UNIQUE(name) и делать

INSERT IGNORE INTO tbl (name) VALUES ($name);

Это не объясняет, как id только что создал, но вы об этом не спрашивали.

Имейте в виду, что "тритон" idвыделяется до того, как выясняется, нужен ли он. Так что это может привести к быстрому увеличениюAUTO_INCREMENT ценности.

Смотрите также

 INSERT ... ON DUPLICATE KEY UPDATE ...

и хитрости для использования с VALUES() а также LAST_INSERT_ID(id). Но, опять же, вы не указали реальную цель в Вопросе, поэтому я не хочу без надобности вдаваться в подробности.

Примечание. Вышеупомянутое не волнует, какое значение autocommit или находится ли оператор внутри явной транзакции.

Для одновременной нормализации пакета "имен" два приведенных здесь SQL-запроса весьма эффективны: http://mysql.rjweb.org/doc.php/staging_table. Этот метод позволяет избежать "сжигания" идентификаторов и избежать любого времени выполнения. ошибки.

Источник

Не отвечая на вопрос напрямую, но разве конечная цель не может быть достигнута с использованием уровня изоляции Serializable? Предполагая, что конечной целью является избежать дублирования имен. Из Эрмитажа:

MySQL "сериализуемый" предотвращает циклы анти-зависимости (G2):

set session transaction isolation level serializable; begin; -- T1
set session transaction isolation level serializable; begin; -- T2
select * from test where value % 3 = 0; -- T1
select * from test where value % 3 = 0; -- T2
insert into test (id, value) values(3, 30); -- T1, BLOCKS
insert into test (id, value) values(4, 42); -- T2, prints "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction"
commit;   -- T1
rollback; -- T2
Источник
Другие вопросы по тегам