DB | Way23

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

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

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

select max(rid) + 1 from table

1	select max(rid) + 1 from table

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

Пользователь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

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

drop table test.emp;

create table test.emp
(
  rid  integer not null,
  name varchar2(100)
);

alter table test.emp
  add constraint table_pk primary key (rid);

insert into test.emp (rid, name) values (1, 'запись 1');
insert into test.emp (rid, name) values (2, 'запись 2');

commit;

drop table test.emp;

create table test.emp

(

rid integer not null,

name varchar2(100)

);

alter table test.emp

add constraint table_pk primary key (rid);

insert into test.emp (rid, name) values (1, 'запись 1');

insert into test.emp (rid, name) values (2, 'запись 2');

commit;

Следующая последовательность запросов 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 то это может привести к неверному поведению.

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

create table test.table1 
  as select value v 
     from table(sys.odcinumberlist(1, 2, 3));

create table test.table2 
  as select value v 
     from table(sys.odcinumberlist(1, 3, null));

create table test.table1

as select value v

from table(sys.odcinumberlist(1, 2, 3));

create table test.table2

as select value v

from table(sys.odcinumberlist(1, 3, null));

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

select 
  t.value 
from 
  test.table1 t 
where 
  t.value in (select t.value from test.table2);

select

t.value

from

test.table1 t

where

t.value in (select t.value from test.table2);

1
3

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

select 
  t.value 
from 
  test.table1 t 
where 
  t.value not in (select t.value from test.table2);

select

t.value

from

test.table1 t

where

t.value not in (select t.value from test.table2);

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

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

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

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

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

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

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

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

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

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

sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

1	sudo dokku plugin:install https://github.com/dokku/dokku-postgres.git postgres

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

Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Фев
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31