SERIALIZABLE режим

Чалышев Максим Михайлович

SQL. 5 дней которые изменят вашу жизнь.

В СУБД ORACLE есть возможность, чтобы пользователь всегда видел только те данные в таблицах, которые были доступны с начала его сессии
Такой режим (уровень изоляции) называется SERIALIZABLE. Для того чтобы включить этот режим используется команда

Alter session set isolation_level=serializable;

Следующий пример показывает отличие режима SERIALIZABLE от стандартного режима эксплуатации СУБД. Уровня изоляции READ COMMITTED
Откроем в двух разных окнах программу SQL Developer (или создадим новый Worksheet) подключимся к схеме SYS как администратор.
создадим таблицу man5

Create table Man5(prt number, name varchar2(50));

INSERT INTO man5 VALUES(20, 'Олег');
INSERT INTO man5 VALUES(21, 'Влад');
INSERT INTO man5 VALUES(22, 'Саша');
Commit;

Напишем запрос

SELECT * FROM man5 WHERE id>20; 

Запрос вернул 3 строки.

Теперь перейдем во второе окно
Напишем запрос

SELECT * FROM man5 WHERE id>20;

Очистим нашу таблицу с помощью команды DELETE
Рассмотрим как работает режим SERIALIZABLE;
Во втором окне перейди в режим изоляции данных SERIALIZABLE

Alter session set isolation_level=serializable;

NSERT INTO man5 VALUES(30, 'Таня');
INSERT INTO man5 VALUES(31, 'Лена');
INSERT INTO man5 VALUES(32, 'Вадим');
INSERT INTO man5 VALUES(33, 'Ира');
Commit;
SELECT * FROM man5 WHERE id>20;

Запрос возвращает 7 строк
Во втором окне, там, где включен режим (уровень изоляции) SERIALIZABLE
Пишем запрос

SELECT * FROM man5 WHERE id>20;

Запрос возвращает 3 строки
В первом окне пишем команду удаления данных

DELETE man5 WHERE id>20;
SELECT * FROM man5 WHERE id>20;
COMMIT;

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

SELECT * FROM man5 WHERE id>20;

По прежнему возвращает три строки.
Данный режим обеспечивает изолированный собственный набор данных, на который не могут повлиять внешние сессии.
Важные замечания
Интересен и обратный пример, закроем и заново откроем два окна SQL Developer.
Очистим таблицу man5;

Delete man5;
Commit;

В первом окне

INSERT INTO man5 VALUES(20, 'Олег');
INSERT INTO man5 VALUES(21, 'Влад');
INSERT INTO man5 VALUES(22, 'Саша');
Commit;

Во втором окне перейдем в режим SERIALIZABLE;
Введем команду

INSERT INTO man5 VALUES(32, 'Вадим');
INSERT INTO man5 VALUES(33, 'Ира');
Commit;
SELECT * FROM man5 WHERE id>20;

Вернёт 5 записей
Переходим в первое окно

SELECT * FROM man5 WHERE id>20;

3 записи
То есть, при уровне изоляции SERIALIZABLE, так же справедливо утверждение, что данные во внешних сессиях, так же изолированы от изменений данных в сессии SERIALIZABLE

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

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

При работе с уровнем я получаю ошибку ORA-8177, почему так происходит и как избежать данной ошибки?
Данная ошибка возникает при изменении данных одно таблицы разными сессиями в режиме SERIALIZABLE
Уровень изоляции SERIALIZABLE захватывает слот в списке заинтересованных транзакций. Если Oracle слот не доступен, то возникает ошибка ORA-8177. Доступные слоты ITL контролируется параметрами INITRANS и MAXTRANS, которые задаются при создании таблицы.

Попробуйте увеличить параметры INITRANS и MAXTRANS и пересоздать таблицы с которыми работает сессия SERIALIZABLE