LINUX.ORG.RU
ФорумAdmin

Вопрос по блокировкам MySQL/InnoDB

 ,


0

3

Есть у меня некая таблица, допустим из двух столбцов: id int (primary key, autoincrement), name varchar(255) (unique index).

С этой таблицей работают (пишут и читают) много потоков одновременно.

Задача:

  • Клиент подключается, проверяет есть ли в таблице в поле name некое значение: SELECT `id` FROM `table` WHERE `name` = 'Vasya'
  • Если оно есть, то id возвращается клиенту, тут вопросов нет
  • Если же его нет, то нужно добавить туда строку и опять же вернуть id клиенту (last_insert_id)
  • При этом важно что одну и ту же строку могут искать и пытаться добавить сразу много клиентов. Хочется чтобы этот момент не вызывал deadlock-ов, отката транзакций и подобных бед.
  • То есть, должна быть некая атомарность - кто первый пришёл и спросил о наличии строки в таблице, в случае её отсутствия, и должен иметь возможность её добавить. А остальные уже должны будут получить её id.

Я правильно понимаю, что без блокировки *всей* таблицы на чтение и запись на время транзакции этого реализовать не получится?

В общем, учитывая что SELECT-ов планируется много больше чем INSERT-ов, была придумана такая схема:

  • Проверяем есть ли запись в таблице: SELECT `id` FROM `table` WHERE `name` = 'Vasya'
  • Если есть - всё хорошо, забираем id, уходим
  • Если нет - блокируем таблицу: LOCK TABLES `table` WRITE
  • Ещё раз проверяем есть ли там запись тем же запросом (она могла появиться между селектом и блокировкой)
  • Если появилась - всё хорошо, забираем id, разблокируем таблицу
  • Если нет - вставляем новую запись, забираем id
  • Разблокируем таблицу: UNLOCK TABLES `table`

Под нагрузкой пока не проверял, но выглядит неплохо. Может кому пригодится.

blind_oracle ★★★★★ ()
Последнее исправление: blind_oracle (всего исправлений: 1)
Ответ на: комментарий от blind_oracle

Простите, но глупость. У вас уже name varchar(255) (unique index). Т.е. при попытке добавить запись Vasya если она уже существует, вас просто пошлют с соответствующим сообщением, достаточно обработать исключение.

anc ★★★★★ ()

Если же его нет, то нужно добавить туда строку и опять же вернуть id клиенту (last_insert_id)

Так вернуть last_insert_id или вставить под фиксированным id, если его нет? Это разные задачи. Если нужен просто уникальный id, то это автоинкремент. Если нужно вставить по id при его отсутствии — то вставляем по insert ignore и смотрим результат.

KRoN73 ★★★★★ ()
Ответ на: комментарий от anc

Я не уверен что долбёжка таблицы INSERT-ами, а затем, если INSERT вернул ошибку DUPLICATE KEY, SELECT-ами (чтобы узнать id) - правильное решение. Получается два запроса вместо одного.

Тут фишка именно в том, что в результате INSERT-ов будет крайне мало, а SELECT-ов - много, т.к. эта таблица достаточно быстро придёт к наполнению уникальными записями и расти почти перестанет. По моим прикидкам отношение INSERT:SELECT будет где-то один к 10-100 тысячам, а может и того больше.

blind_oracle ★★★★★ ()
Ответ на: комментарий от KRoN73

Так вернуть last_insert_id или вставить под фиксированным id, если его нет? Это разные задачи.

Тут речь именно про last_insert_id

Если нужен просто уникальный id, то это автоинкремент.

Посмотри вминательно топик, там он и есть.

blind_oracle ★★★★★ ()
Ответ на: комментарий от blind_oracle

Ок, по вашему мнению блокировать таблицу целиком, и оставив за бортом на это время другой народ, это лучший вариант? Когда SELECT-в много так они и отработают шустро.
Вы предлагаете:
1. SELECT - 1шт
2. LOCK TABLES `table` WRITE - тяжелая штука - 1шт
3. SELECT - 2-я по счету
4. INSERT - и все равно проверит (select)
5. UNLOCK TABLES `table`

Вместо:
1. INSERT
2. если пошлет ( а пошлет только один раз и все равно же проверит select) - select

anc ★★★★★ ()
Ответ на: комментарий от anc

Вы предлагаете:

Нет, я предлагаю только пункт 1 в 100 тысячах случаев и пункты 1-5 в одном случае.

вместо

В вашем случае я в 100 тысячах раз буду делать INSERT+SELECT чтобы получить id, так как дубликаты будут встречаться в 99.999% случах.

blind_oracle ★★★★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.