MySQL InnoDB блокирует только затронутые строки?
У меня есть таблица InnoDB, содержащая пользователей, как это:
+--------------+-----------------------+
| user_id | name |
+--------------+-----------------------+
| 1 | Bob |
| 1 | Marry |
| 2 | Bob |
| 1 | John |
| 3 | Bob |
| 2 | Marry |
+--------------+-----------------------+
При каждой вставке я увеличиваю user_id на 1, поэтому, например, следующий user_id для Боба будет 4
Я использую этот запрос, чтобы сделать это:
INSERT INTO users (user_id, name)
SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';
Теперь мне нужно убедиться, что два разных пользователя не добавляют "Боб" одновременно. Я не хочу никаких двух Бобов с user_id 4. Каждый из идентификаторов пользователя Боба должен быть разным.
Можно ли написать и обновить блокировку всех строк Боба при выполнении вышеуказанного запроса на вставку? Я не могу заблокировать всю таблицу, потому что многим другим пользователям все еще нужен полный доступ к их строкам. Кроме того, все строки должны быть доступны для чтения в любое время. Как бы я это сделал?
3 ответа
Предполагая, что здесь InnoDB, вы можете использовать SELECT ... LOCK IN SHARE MODE, чтобы заблокировать строки при их выборе.
Ответ на этот вопрос такой же, как этот. InnoDB заблокирует больше строк, чем вы ожидаете, для безопасности с двоичным журналированием на основе операторов.
Решение: использовать двоичное журналирование на основе строк с уровнем изоляции зафиксированного чтения.
Чтобы решить первую часть вашего вопроса, создайте уникальный индекс user_id и name.
alter table `users` add unique index ak_user_id_name(user_id,name);
Это предотвратит дублирование user_id, имени записи.