#sql #oracle
Вопрос:
У меня есть это многопоточное java-приложение. Каждый поток вставляет записи в таблицу employee_hist, если идентификатор сотрудника не существует.
Сначала старое приложение с багги
- запускает SQL — запрос, чтобы проверить, существует ли идентификатор сотрудника в таблице employee_hist.
- Если счетчик равен 0, то только он вставляет запись в таблицу истории.
Поскольку это многопоточный процесс, два параллельных потока A и B могут создать проблему. Поток A проверяет, существует ли идентификатор сотрудника, если нет, он вставляет запись. Поток B также выполнял проверку одновременно с потоком A, но затем A первым завершает вставку. Теперь B, когда он попытается вставить тот же идентификатор сотрудника, он пожалуется, что идентификатор сотрудника уже существует, и будет выдана ошибка нарушения ограничений!
Чтобы исправить это, я заменил старый 2-шаговый процесс всего на 1 шаг INSERT INTO employee_hist (emp_id, description) SELECT 1000, 'bush' from dual a where not exists (select 1 from employee_hist b where b.employee_id in(1000));
Это означает, что вставка не произойдет, если запись уже существует. Все это делается в одном-единственном заявлении.
К сожалению, это не работает. Я все еще получаю ошибку о нарушении ограничений. Мне интересно, как это возможно. Что я пропустил?
Комментарии:
1. Предварительная проверка-плохая практика. Если ключ синтетический, вы должны использовать ИДЕНТИФИКАЦИОННЫЙ КЛЮЧ или ПОСЛЕДОВАТЕЛЬНОСТЬ (конфликтов никогда не будет); если ключ естественный… ну, в настоящее время нет веских причин использовать естественные ключи.
2. Я этого совсем не понял. Как я могу не проверять наличие идентификатора сотрудника в таблице hist. Помните, что таблица employee_hist является дочерней таблицей. Сотрудник-это родительская таблица.
3. Говоря простыми словами. Как вы определяетесь со значением «emp_id» при вставке? Вы 1) Позволяете базе данных автоматически выбирать следующее доступное значение или 2) Получаете значение из внешнего мира (в форме, в соответствии с правилом и т. Д.)? Обычно вы должны выполнять #1; столбец «emp_id» должен быть определен как ИДЕНТИФИКАТОР, или вы можете использовать для него старую ПОСЛЕДОВАТЕЛЬНОСТЬ; ваш выбор.
4. идентификатор сотрудника уже существует. В историю перемещаются только те сотрудники, которые были отмечены для архивирования. Так что да,идентификатор сотрудника-это столбец идентификаторов в таблице сотрудников…но в истории сотрудников это внешний ключ. Итак … я все еще в замешательстве от вашего ответа
5. Вы не предоставили подробных сведений в вопросе, чтобы четко указать, какое ограничение нарушено. Это поможет найти решение. 1) Предоставьте
CREATE TABLEинструкции с ограничениями в текстовой форме, которые могут быть выполнены без ошибок. 2) Покажите точноеINSERTутверждение, которое приводит к нарушению ограничений. 3) Укажите точную / полную ошибку (которая должна показывать ограничение), созданную вашей вставкой. Трудно ответить, когда почти нет полезных деталей. Не упускайте из виду детали. Предоставьте весь SQL, необходимый для создания ошибки, даже если ошибка генерируется не всегда.
Ответ №1:
Дело не в количестве шагов, а в последовательности чтения. Если мы представим себе временную шкалу событий (но невероятно замедленную)
9:00 утра: Сессия 1 вставьте EMP=100, если не существует =gt; Строка не найдена, поэтому продолжайте и вставляйте
9:01 утра: Сессия 2 вставьте EMP=100, если не существует =gt; Строка i не найдена (потому что 9:00 утра еще не зафиксирована) =gt;gt; начинается вставка (и будет заблокирована, иначе говоря, подождите)
9:02 утра: Сессия 1 совершает
9:03 утра: Сеанс 2 теперь можно продолжить, но вставка завершится ошибкой с нарушением PK, потому что теперь есть строка с EMP=100
Обычно для обработки этих ситуаций мы блокируем «что-то», например, Это может быть запись родительского отдела и т. Д., Т. Е. Что-то, что находится над историей сотрудников, которое можно использовать для сериализации доступа.
Однако, если это действительно один tbale, то сериализация несуществующих строк нетривиальна, потому что нет «чего-то», что нужно заблокировать. В этом случае вы можете использовать DBMS_LOCK для синтеза блокировки, которую должен получить каждый сеанс, например (psuedocode)
- dbms_lock.запрос(идентификатор=gt; идентификатор сотрудника, режим =gt;gt; эксклюзивный)
- вставка с проверкой «не существует»
- совершить;