Ограничение UNIQUE против проверки перед INSERT

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

Однако я думаю, что обработка исключений имеет несколько небольших преимуществ:

• Обработка исключений позволяет избежать потенциальной гонки. Метод «проверка, затем вставка» может дать сбой, если другой процесс вставляет запись между вашей проверкой и вашей вставкой. Итак, даже если вы выполняете «проверьте, а затем вставьте», вам все равно нужна обработка исключений при вставке, и если вы все равно уже выполняете обработку исключений, вы также можете отказаться от начальной проверки.

• Если ваш код не является хранимой процедурой и должен взаимодействовать с базой данных через сеть (т. Е. Приложение и база данных не находятся в одном ящике), то вы хотите избежать двух отдельных сетевых вызовов (один для проверки и other для вставки) и выполнение этого с помощью обработки исключений обеспечивает простой способ обработки всего этого с помощью одного сетевого вызова. Теперь существует множество способов выполнить метод «проверьте, затем вставьте», избегая при этом второго сетевого вызова, но простой перехват исключения, вероятно, будет самым простым способом решить эту проблему.

С другой стороны, для обработки исключений требуется уникальное ограничение (которое на самом деле является уникальным индексом), которое требует компромисса с производительностью:

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

Я также хотел бы отметить, что если вы находитесь в ситуации, когда на самом деле вам нужно «обновить вставку другого» (т.е. если запись с уникальным значением уже существует, то вы хотите обновить эту запись, иначе вы вставите новую record), то на самом деле вы хотите использовать метод UPSERT вашей конкретной базы данных, если он есть. Для SQL Server и Oracle это будет оператор MERGE.

В зависимости от разумности стоимости первого пункта (поиск) я бы сделал и то, и другое. По крайней мере, в Oracle, с которой у меня больше всего опыта.

Обоснование:

• Unique/primary keys should be a core part of your data model design, I can't see any reason to not implement them - if you have so much data that performance suffers from maintaining the unique index:
  • that's a lot of data
  • разделите его или заархивируйте подальше от вашей OLTP-работы
• Чем больше у вас ограничений, тем безопаснее ваши данные от логических ошибок приложения.
• Если вы сначала убедитесь, что строка существует, вы можете легко извлечь другую информацию из этой строки, чтобы использовать ее как часть сообщения об ошибке, или иным образом разветвите логику приложения, чтобы справиться с дублированием.
• В Oracle откат DML-операторов относительно затратен, потому что Oracle ожидает успеха (т.е. COMMIT изменений, которые были записаны) по умолчанию.

Это не дает прямого ответа на вопрос, но я подумал, что было бы полезно опубликовать его здесь, поскольку он лучше, чем википедия, и ссылка может просто когда-нибудь стать мертвой.

Ссылка - http://www.celticwolf.com/blog/2010/04/27/what-is-a-race-condition/

В Википедии есть хорошее описание состояния гонки, но за ним трудно следовать, если вы не понимаете основ программирования. Я попытаюсь объяснить это менее техническими терминами, используя пример создания идентификатора, как описано выше. Я также буду использовать аналогии с человеческой деятельностью, чтобы попытаться передать идеи.

Состояние гонки - это когда две или более программы (или независимые части одной программы) одновременно пытаются получить некоторый ресурс, что приводит к неправильному ответу или конфликту. Этот ресурс может быть информацией, например, о следующем доступном времени встречи, или это может быть эксклюзивный доступ к чему-либо, например, к электронной таблице. Если вы когда-либо использовали Microsoft Excel для редактирования документа на общем диске, вы, вероятно, слышали от Excel, что кто-то уже редактировал электронную таблицу. Это сообщение об ошибке позволяет Excel корректно обрабатывать потенциальное состояние гонки и предотвращать ошибки.

Обычная задача программ - определить следующее доступное значение некоторого вида и затем присвоить его. Этот метод используется для номеров счетов, студенческих билетов и т. Д. Это старая проблема, которую уже решали раньше. Одно из наиболее распространенных решений - разрешить базе данных, в которой хранятся данные, генерировать число. Есть и другие решения, и все они имеют свои сильные и слабые стороны.

К сожалению, программисты, которые не разбираются в этой области или просто плохо умеют программировать, часто пытаются откатать свои собственные. Умные быстро обнаруживают, что это гораздо более сложная проблема, чем кажется, и ищут существующие решения. Плохие никогда не видят проблемы или, однажды увидев, настаивают на усложнении своего неработающего решения, не исправляя ошибку. Возьмем, к примеру, студенческий билет. Программист-новичок говорит: «Чтобы узнать, каким должен быть следующий номер ученика, мы просто возьмем номер последнего ученика и увеличим его». Вот что происходит под капотом:

1. Бетти, админ. ассистент приемной комиссии запускает программу управления студентами. Обратите внимание, что на самом деле это просто копия программы, которая работает на ее компьютере. Он общается с сервером базы данных по сети школы, но не имеет возможности общаться с другими копиями программы, работающими на других компьютерах.
2. Бетти создает новую студенческую запись для Боба Смита, вводя всю информацию.
3. Пока Бетти занимается вводом данных, Джордж - еще один администратор. помощник, запускает программу управления студентами на своем ПК и начинает создавать запись для Джины Верде.
4. Джордж быстрее печатает, поэтому заканчивает одновременно с Бетти. Они оба одновременно нажимают кнопку «Сохранить».
5. Программа Бетти подключается к серверу базы данных и получает наибольшее количество учащихся - 5012.
6. Программа Джорджа, в то же время, дает тот же ответ на тот же вопрос.
7. Обе программы решают, что новый идентификатор студента для сохраняемой записи должен быть 5013. Они добавляют эту информацию в запись, а затем сохраняют ее в базе данных.
8. Теперь у Боба Смита (ученица Бетти) и Джины Верде (ученицы Джорджа) один и тот же студенческий билет.

Этот студенческий билет будет прикреплен ко всем другим записям, от оценок до карточек питания в столовой. В конце концов эта проблема обнаружится, и кому-то придется потратить много времени на присвоение одному из них нового идентификатора и сортировку запутанных записей.

Когда я описываю эту проблему людям, обычная реакция: «Но как часто это будет происходить на практике? Никогда, правда? ». Неправильный. Во-первых, когда ввод данных выполняется вашими сотрудниками, обычно это делается всеми в течение относительно небольшого периода времени. Это увеличивает шансы на перекрытие. Если рассматриваемое приложение является веб-приложением, открытым для широкой публики, шансы, что два человека одновременно нажмут кнопку «Сохранить», еще выше. Я недавно видел это в производственной системе. Это было публичное бета-тестирование веб-приложения. Уровень использования был довольно низким, каждый день регистрировалось всего несколько человек. Тем не менее шести парам людей удалось получить идентичные удостоверения личности за несколько месяцев. Если вам интересно, нет, ни я, ни кто-либо из моей команды не писали этот код. Однако мы были весьма удивлены, сколько раз возникала эта проблема. Оглядываясь назад, мы не должны были быть такими. Это действительно простое применение закона Мерфи.

Как можно избежать этой проблемы? Самый простой способ - использовать существующее решение проблемы, которое было хорошо протестировано. Все основные базы данных (MS SQL Server, Oracle, MySQL, PostgreSQL и т. Д.) Имеют способ увеличивать числа без создания дубликатов. Сервер MS SQL называет его столбцом «идентичности», в то время как MySQL называет его столбцом «автоматический номер», но функция остается той же. Каждый раз, когда вы вставляете новую запись, автоматически создается новый идентификатор, который гарантированно будет уникальным. Это изменит описанный выше сценарий следующим образом:

1. Бетти, админ. ассистент приемной комиссии запускает программу управления студентами. Обратите внимание, что на самом деле это просто копия программы, которая работает на ее компьютере. Он общается с сервером базы данных по сети школы, но не имеет возможности общаться с другими копиями программы, работающими на других компьютерах.
2. Бетти создает новую студенческую запись для Боба Смита, вводя всю информацию.
3. Пока Бетти занимается вводом данных, Джордж - еще один администратор. помощник, запускает программу управления студентами на своем ПК и начинает создавать запись для Джины Верде.
4. Джордж быстрее печатает, поэтому заканчивает одновременно с Бетти. Они оба одновременно нажимают кнопку «Сохранить».
5. Программа Бетти подключается к серверу базы данных и передает ему запись для сохранения.
6. Программа Джорджа в то же время передает другую запись для сохранения.
7. Сервер базы данных помещает обе записи в очередь и сохраняет их по одной, присваивая им следующий доступный номер.
8. Теперь Боб Смит (ученица Бетти) получает идентификатор 5013, а Джина Верде (ученица Джорджа) получает идентификатор 5014.

С этим решением проблем с дублированием нет. Код, который делает это для каждого сервера базы данных, неоднократно тестировался на протяжении многих лет как производителем, так и пользователями. Миллионы приложений по всему миру полагаются на него и продолжают ежедневно проводить стресс-тесты. Может ли кто-нибудь сказать то же самое об их отечественном решении?

Существует по крайней мере один хорошо протестированный способ создания идентификаторов в программном обеспечении, а не в базе данных: uuids (Универсальные уникальные идентификаторы). Однако uuid принимает форму xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, где «x» обозначает шестнадцатеричную цифру (0-9 и a-f). Вы хотите использовать это для номера счета, студенческого билета или какого-либо другого идентификатора, который видят публике? Возможно нет.

Подводя итог, можно сказать, что состояние гонки возникает, когда две программы или две независимые части программы пытаются получить доступ к некоторой информации или доступ к ресурсу одновременно, что приводит к ошибке, будь то неправильный расчет, дублированный идентификатор или конфликтующий доступ. к ресурсу. Существует гораздо больше типов условий гонки, чем я здесь описал, и они влияют на многие другие области программного и аппаратного обеспечения.

Описание вашей проблемы - именно то, почему первичные ключи могут быть составными, например, они состоят из нескольких полей. Таким образом, база данных будет обрабатывать уникальность за вас, и вам не нужно об этом заботиться.

В вашем случае определение таблицы может быть примерно таким, например:

 CREATE TABLE `real_estate` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `property` varchar(255) DEFAULT NULL,
   `property_value` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `index_id_property_property_value` (`id`, `property`, `property_value`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;