Oracle. Как не нужно генерировать первичный ключ

Идея поста возникал при прочтении Получение в программе значения первичного ключа после INSERT.

В Oracle младше 12 версии нет автоинкрементного поля. Стандратный способ генерации первичных ключей — последовательности. Но иногда разработчики генерируют первичный ключ таким способом:

Это приводит к проблемам при многопользовательской работе. Типичная ситуация — пользователи одновременно получают новое значение для ПК, а потом вставляют записи:

Пользователь1Пользователь2
select max(rid) + 1 from table
select max(rid) + 1 from table
insert
insert

В результате второй пользователь зависает из-за блокировки. Если же первый пользователь зафиксирует данные, то у второго сработает ограничение по первичному ключу:

Пользователь1Пользователь2
select max(rid) + 1 from table
select max(rid) + 1 from table
insert
insert
commit
ORA-00001: нарушено ограничение уникальности

Для генерации ПК следует использовать последовательности или специальные механизмы вроде GUID. Иначе будут возникать ошибки.

Пример взаимоблокировки в Oracle

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

Следующая последовательность запросов update приводит к взаимной блокировке.

Сессия 1Сессия 2
update test.emp set name = '11' where rid = 1;
update test.emp set name = '22' where rid = 2;
update test.emp set name = '22' where rid = 2;
Сеанс зависает
update test.emp set name = '11' where rid = 1;
ORA-00060: взаимная блокировка при ожидании ресурса

Ошибка ORA-00060, как написано в документации, может появиться в случайной из этих сессий.

Ссылки

Аудит данных в Oracle на основе пакетных переменных и триггеров

В пакеты Oracle можно добавлять переменные. Эти переменные имеют свои значения для каждой сессии. Такое свойство позволяет организовать аудит — лог записей о том кто и когда менял данные в таблицах. Общий алгоритм такой:

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

Почти все описанные изменения производятся в БД. В клиенте добавляется только код регистрации переменных после коннекта. За счёт этого, такой подход может использоваться даже в наборе старых приложений в которых затруднительно найти и изменить все обращения к таблице в клиентах.

Читать далее Аудит данных в Oracle на основе пакетных переменных и триггеров

Oracle. not in и null

Если в колонке внутри подзапроса not in возможно появление null то это может привести к неверному поведению.

Рассмотрим пример, сделаем две таблицы:

Запрос с in возвращает то что ожидается

1
3

А вот запрос с not in не возвращает вообще ничего

И так будет всегда если в результате подзапроса присутствует null. Таким образом, если делается запрос с not in, всегда нужно проверить что колонка в подзапросе является not null. Или заменить условие, например на not exists.

Подробная статья про поведение null в Oracle, в том числе разобрана ситуация с not in.

Установка Oracle Instant Client в Windows

Instant Client — сокращенная версия клиента Oracle.

Установка состоит из трех этапов:

  1. Загрузка файлов Instant Client
  2. Установка системных переменных
  3. Настройка подключения - файл tnsnames.ora

Читать далее Установка Oracle Instant Client в Windows

Установка PostgreSQL в Dokku

Внутри Dokku можно установить PostgreSQL с помощью плагина. После установки появится возможность создавать базы данных, подключатся к ним, связывать БД с приложениями.

Запускаем команду установки

Читать далее Установка PostgreSQL в Dokku