Управление транзакциями и обеспечение целостности баз данных

1.

1
ЛЕКЦИЯ
УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ И
ОБЕСПЕЧЕНИЕ ЦЕЛОСТНОСТИ БАЗ
ДАННЫХ
Понятие транзакции и целостности баз данных.
2. Уровни изолированности пользователей.
3. Понятие сериализации транзакций.
1.

2.

2
Особенности СУБД промышленного уровня
«SQL Server работал бы идеально, если бы не было
пользователей»
• Поддержка параллельной работы многих пользователей.
• Запросы пользователей на доступ и изменение информации в БД
поступают асинхронно, без взаимной согласованности действий.
• Базовое требование к СУБД — поддержка целостности данных, состояния, когда в произвольный момент времени данные адекватно
отображают состояние моделируемых объектов.
• Транзакция переводит БД из одного целостного состояния в другое.
Концепция транзакций – неотъемлемая часть любой клиентсерверной базы данных.
• Одна из основных задач СУБД – обеспечение изолированности, т.е.
создание такого режима функционирования, при котором каждому
пользователю казалось бы, что БД доступна только ему. Такую задачу
СУБД принято называть параллелизмом транзакций.
• Для предотвращения взаимовлияния транзакций в СУБД
используется механизм блокировок, многоверсионность.

3.

3
Цель проектирования транзакций
В
определении
и
документировании
транзакций БД учитывают :
• данные, которые используются транзакцией;
• функциональные характеристики транзакции;
• выходные данные, формируемые транзакцией;
• степень важности транзакции для пользователей;
• предполагаемая интенсивность использования
данных.

4.

4
Определение транзакции
Транзакция (transaction) — это
группа SQL-операторов, которые
выполняются
(успешно
или
неуспешно) как единое целое.
Транзакция - логическая единица
работы в базе данных, а так же
единица
восстановления
информации при сбое СУБД.

5.

5
Характеристики транзакций в терминах
ACID
• Транзакция
неделима (Atomicity)
- представляет собой
единое целое. Не бывает частичной транзакции. Если не может
быть выполнена часть транзакции или вся, она отклоняется (изза отключения электропитания).
• Транзакция является согласованной (Consistency)- не
нарушает бизнес-логику и отношения между элементами
данных. Результат запроса должен быть согласован с состоянием
базы данных на момент старта работы запроса.
• Транзакция всегда изолирована (Isolation) - ее результаты не
зависят от предыдущих или последующих транзакций.
Транзакции в последовательности независимы (свойство
сериализуемости).
Результаты
транзакции
становятся
доступны для других транзакций только после ее фиксации.
• Транзакция
устойчива
(Durability).
После
фиксации
транзакции изменения становятся постоянными.

6.

6
Характеристики транзакций
• Классический
пример транзакции — банковская операция
(перевод некоторой суммы с одного банковского счета на
другой.
UPDATE accounts SET balance = balance - transaction_amount
WHERE account_no = from_acct;
UPDATE accounts SET balance = balance+ transaction_amount
WHERE account_no = to_acct;
• Логически транзакция должна объединять только выполнение
взаимосвязанных операций.
• Если транзакции «очень большие», то сбой, автоматически
выполняющий откат транзакции, повлияет на отмену действий,
которые могли бы быть успешно завершены при более
"коротких" транзакциях.
• Транзакция должна быть минимальной длины, поскольку
длинные транзакции обычно уменьшают уровень доступности
данных.

7.

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

8.

Управление транзакциями
• Оптимистическая модель основана на
• предположении
Процесс может модифицировать
без
маловероятностиданные
изменения
каких-либо
ограничений,
поскольку
все другиес
данных
одной
транзакцией
одновременно
процессы, которые считывают эти же данные,
другой.
используют свою
собственную
• Компонент
Database
Engine сохраненную
применяет
версию.
оптимистический
одновременный
• конкурентный
Конфликтная ситуация
возможна
при
доступ,
при только
котором
попытке двух операций записи использовать
сохраняются старые версии строк, и любой
одни и те же данные.
процесс при чтении данных использует ту
• В таком случае система выдает ошибку,
версию строки, которая была активной, когда
которая
обрабатывается
клиентским
он начал чтение.
приложением.

9.

Управление транзакциями
• В
модели
пессимистического
одновременного
конкурентного
доступа
для
предотвращения
одновременного
доступа
к
данным,
которые
используются
другим
процессом,
применяются
блокировки.
• Эта
модель
устанавливает
блокировку
с
обеспечением
разделяемого
доступа
немонопольную блокировку (shared lock) на
считываемые данные, чтобы никакой другой процесс
не мог ИЗМЕНИТЬ эти данные.
• И устанавливает монопольную блокировку (exclusive
lock) на изменяемые данные, чтобы никакой другой
процесс
не
мог
их
СЧИТЫВАТЬ
ИЛИ
МОДИФИЦИРОВАТЬ.

10.

10
Многоверсионность
• Полное ограничение одновременного доступа: и
изменений, и чтений - неэффективно.
• При эффективной реализации – как в Постгресе
и Оракле – читающая транзакция никогда не
будет заблокирована другими транзакциями,
читающими или изменяющими те же данные –
каждая из них будет независимо работать со
своей версией.
• Блокироваться
будут
только
попытки
изменить данные, которые уже изменены
другой
транзакцией,
но
еще
не
зафиксированы.

11.

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

12.

12
Команды управления транзакциями
Oracle, по умолчанию, предоставляет наивысший
уровень параллелизма: чтение данных не блокирует
запись, запись не блокирует изменение, если одна
сессия пытается считать данные, которые другая
сессия изменяет и наоборот.
Но иногда вам может понадобиться изменить такое
поведение и предотвратить возможность изменения
данных которые считаны сессией:
select * from regions for update;

13.

13
Блокировки select for update
запрос читает таблицу, находит
желаемые пользователем строки, а затем пытается
наложить на них построчную блокировку. Если
обнаруживается, что на какой-то строке уже имеется
блокировка от другой сессии, то текущая сессия будет
ожидать пока блокировка не будет снята, и только после
этого продолжит выполнение.
• select for update nowait - при встрече со строкой,
заблокированной другой сессией, текущая сессия не будет
ждать, а сразу сгенерирует ошибку "ORA-00054 resource
busy and NOWAIT specified" и откатит всё, что успела
сделать.
• select for update -

14.

14
Блокировки select for update
• select for update wait n - при обнаружении лока сессия
ждёт указанное время; если лок за это время будет снят выполнение продолжится. Если же по истечении n секунд
хотя
бы
одна
строка
продолжит
оставаться
заблокированной, сгенерируется ошибка "ORA-30006
resource busy; acquire with WAIT timeout expired" с
последующим откатом.
• select for update skip locked - появившйся вариант в
oracle 11g (версия 11.1.0) - позволяет пропускать строки,
которые уже заблокированы, запрос блокирует то, что
может, и идёт дальше.
• Это удобное решение для многопоточной работы, когда
важна скорость, а реакцию на пропущенные строки можно
реализовать в коде обработчика данных.

15.

15
Многоверсионность
• Если один процесс хочет изменить строку, а второй
её прочитать, можно было бы
применять
блокировки, но был придуман вариант получше.
• Этот вариант - многоверсионность.
• Многоверсионность – это наличие нескольких
версий одной и той же строки.
• Механизм состоит в поддержке на низком уровне
одновременно
нескольких
версий
данных.
Транзакции работают со снимком, который из
многих версий составляет согласованную на
определенный момент времени картину данных.

16.

16
Многоверсионность
• Из
четырех требований ACID этот механизм
имеет
непосредственное
отношение
к атомарности согласованности и изоляци.
• В зависимости от уровня изоляции, снимок
может определяться в момент начала транзакции
(repeatable read, serializable) или отдельно для
каждой операции (read committed).
• Снимок не является полной физической копией
всех
данных:
это
только
логическое
представление, и его можно организовать поразному.

17.

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

18.

18
Многоверсионность
for x in (select * from t)
loop
insert into t values (x.username, x.user_id, x.created);
end loop;
Оператор SELECT не будет видеть никакие новые
данные, вставленные оператором INSERT. В противном
случае выполнялся бы бесконечный цикл. Такое
согласованное чтение обеспечивается для всех
операторов.

19.

Многоверсионность в Postgre

20.

Многоверсионность в Postgre

21.

21
Управление транзакциями
• Под управлением транзакциями понимается способность управлять
операциями над данными внутри реляционной СУБД (INSERT, UPDATE и
DELETE).
• Cоздание таблицы фиксируется в базе данных автоматически. Отмена
транзакции не восстановит удаленную оператором DROP TABLE таблицу
в Oracle.
• Существует 2 подхода к указанию границ транзакций в потоке команд явные и неявные транзакции.
Явные транзакции. По умолчанию, каждая команда выполняется как
отдельная транзакция. Но пользователь может объединить несколько
команд в одну транзакцию, явно указав ее начало и конец.
Неявные транзакции. Транзакция неявно начинается с началом сеанса
или при выполнении первой команды SQL после последней команды
COMMIT или ROLLBACK. Транзакция заканчивается при выполнении
команды COMMIT или ROLLBACK.

22.

Управление транзакциями MS SQL
Когда для соединения установлен режим неявных
транзакций и соединение в данный момент не
используется
в
транзакции,
то
запускает
транзакцию выполнение любой из следующих
инструкций :
ALTER TABLE
FETCH
REVOKE
CREATE TABLE
GRANT
SELECT
DELETE
INSERT
TRUNCATE
DROP TABLE
OPEN
UPDATE

23.

23
Управление транзакциями
Т.о., транзакция начинается с началом сеанса работы с
БД.
• Завершается транзакция при следующих событиях:
1. Явно
выполненный
оператор
завершения
транзакции – rollback (без savepoint) или commit.
2. Оператор DDL, DCL(Grant, Revoke).
3. Завершение сеанса (транзакция или фиксируется
или откатывается в случае аварийного завершения
сеанса).
После окончания транзакции в SQL Server и Oracle
сразу неявно начинается новая транзакция.

24.

Управление транзакциями
• Явные транзакции можно вкладывать друг в
друга.
• Вложенные транзакции обычно используются
в хранимых процедурах, которые сами
содержат транзакции и вызываются внутри
другой транзакции.
• Глобальная переменная @@trancount в MS
SQL содержит число активных транзакций
для текущего пользователя.

25.

25
MS Sql Server
•Явные транзакции. Каждый оператор выполняется в своей
транзакции, если он не находится в блоке
begin tran
…
commit (rollback)
•Существуют вложенные транзакции.
begin tran
...........
begin tran
..............
commit(rollback)
..............
commit (rollback)
• Подтверждение вложенной транзакции ни на что не влияет.
• Откат вложенной транзакции откатывает самую внешнюю
транзакцию.

26.

26
Автономные транзакции Oracle
СУБД Oracle поддерживает расширенные транзакции –
автономные транзакции.
• Транзакция, помеченная как автономная не разделяет
ресурсы главной транзакции (блокировки и др.), не зависит
от главной транзакции.
• Если главная транзакция откатывается, то изменения,
внесенные автономной транзакцией, не откатываются.
• Транзакция,
помеченная
как
автономная,
делает
видимыми внесенные изменения для других транзакций
немедленно после своей фиксации.

27.

27
Пример
• Автономную транзакцию можно выполнить только внутри блока PL/SQL.
• Блок помечается как автономный с помощью прагмы в разделе объявлений
CREATE OR REPLACE PROCEDURE Autonomous AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO temp_table VALUES (-10, 'Hello from Autonomous!');
COMMIT;
END Autonomous;
BEGIN
INSERT INTO temp_table VALUES (-10, 'Hello from the parent! ');
Autonomous;
ROLLBACK;
END;
SOL> SELECT * FROM temp_table WHERE num_col = -10;
-10 Hello from Autonomous!

28.

28
Пример
• Автономная транзакция начинается с первой инструкции SQL в
автономном блоке и заканчивается оператором COMMIT или
ROLLBACK.
• Любой
оператор
управления
транзакцией
может
использоваться в автономной транзакции, включая COMMIT,
ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT и SET
TRANSACTION. Точки сохранения являются локальными для
текущей транзакции.
• Инструкция
SAVE
TRANSATION
устанавливает
точку
сохранения внутри транзакции, так что все последующие
изменения данных могут быть отменены без отмены всей
транзакции.
• Инструкция SAVE TRANSACTION в сочетании с инструкцией IF
или WHILE позволяет выполнять отдельные части транзакции.

29.

Управление транзакциями
Для работы с транзакциями язык Transact-SQL
предоставляет следующие шесть инструкций:
• BEGIN TRANSACTION;
• BEGIN DISTRIBUTED TRANSACTION;
• COMMIT [WORK];
• ROLLBACK [WORK];
• SAVE TRANSACTION;
• SET IMPLICIT_TRANSACTIONS.

30.

30
Команды управления транзакциями
завершает транзакцию и делает любые
выполненные
в
ней
изменения
постоянными
(продолжительными).
• Одним из свойств СУБД Oracle является ее способность
прозрачно
обрабатывать
распределенные
транзакции
(обновление данных во множестве разных БД за одну
транзакцию, когда фиксируются все обновления во всех
экземплярах, либо не фиксируется ни одно из них (все они
откатываются)).
• Ключом к распределенным транзакциям в Oracle является
связь БД.
• ROLLBACK - оператор отката завершает транзакцию и
отменяет все выполненные в ней и незафиксированные
изменения. Для этого он читает информацию из сегментов
отката и восстанавливает блоки данных в состояние, в котором
они находились до начала транзакции.
• COMMIT
-

31.

31
Команды управления транзакциями
• SAVEPOINT - позволяет создать в транзакции "метку", или точку
сохранения. В одной транзакции можно выполнять оператор
SAVEPOINT несколько раз, устанавливая несколько точек
сохранения.
• C помощью команды SAVEPOINT крупное число транзакций
может быть разбито на меньшие и поэтому более управляемые
группы, ее применение является одним из способов управления
транзакциями.
• При выполнении оператора ROLLBACK TO SAVEPOINT
происходит следующее:
Вся работа, произведенная после этой точки сохранения,
отменяется. Точка сохранения остается активной. При желании
откат можно повторить.
• Все блокировки, установленные SQL-операторами после точки
сохранения, снимаются, и освобождаются ресурсы, запрошенные
SQL-операторами после этой точки.
• Транзакция не заканчивается, так как SQL-операторы находятся
в состоянии ожидания.

32.

Управление транзакциями
• Инструкция BEGIN TRANSACTION запускает
транзакцию.
• Синтаксис:
BEGIN TRANSACTION [{transaction_name |
@trans_var} [WITH MARK ['description']]]
• Если используется параметр WITH MARK, имя
транзакции помещается в журнал транзакций. При
восстановлении базы данных из копии до прежнего
состояния вместо даты и времени может
использоваться метка транзакции.

33.

33
Управление транзакциями
• Внимание! Команда TRUNCATE в Oracle не записывает
в журнал событий удаление отдельных строк.
Вследствие чего не может активировать триггеры DML.
• Инструкция TRUNCATE TABLE всегда блокирует
таблицу.
• После операции TRUNCATE TABLE
для некоторых
СУБД (например, Oracle) следует неявная операция
COMMIT. Удаленные в таблице записи нельзя
восстановить операцией ROLLBACK.
• В Microsoft SQL Server операция TRUNCATE может
участвовать в транзакциях.

34.

34
Команды управления транзакциями
TRANSACTION - позволяет устанавливать атрибуты
транзакции, такие как уровень изолированности и то, будет
ли она использоваться только для чтения данных или для
чтения и записи. Этот оператор также позволяет привязать
транзакцию к определенному сегменту отката.
• SET
• В Oracle нет необходимости в операторе “начала транзакции”.
• Транзакция начинается неявно с первым же оператором, который
модифицирует данные (первый оператор, получающий блокировку
TX).
• В Oracle каждая транзакция должна быть зафиксирована, когда
нужно. Транзакции могут быть настолько крупными, насколько это
необходимо.
• Можно
явно
начать
транзакцию,
используя
команду
SET
TRANSACTION
или
пакет
DBMS_TRANSACTION,
но
этот
шаг
в
Oracle не обязателен, в отличие от различных прочих СУБД.

35.

35
Фиксация транзакции в SQL Server и
Oracle
• В неявном случае применяется режим автокоммита
• Для установки режима автоматического определения транзакций в SQL Server
используется команда:
• SET IMPLICIT_TRANSACTIONS OFF
• SET IMPLICIT_TRANSACTIONS ON
SET IMPLICIT_TRANSACTIONS ON установит режим неявных транзакций для
текущего сеанса.
• Когда сеанс работает в режиме неявных транзакций, выполняемые инструкции
неявно выдают инструкции BEGIN TRANSACTION.
• Но каждую неявную транзакцию нужно или явно зафиксировать или явно
отменить. Если транзакцию явно не зафиксировать, то все изменения,
выполненные в ней, откатываются при отключении пользователя.
В Oracle
• Sql> set autocommit on ;
• Sql> set autocommit off ;
• Как
правило, выполнение SQL-операторов в интерактивном режиме всегда
использует автокоммит.

36.

Управление транзакциями

37.

37
Управление транзакциями
• То,
как
фиксируются
транзакции,
зависит
от
использования
программных
интерфейсов
—
ODBS и JDBC, ...
• API выполняют автоматическую фиксацию (“autocommit”) по
умолчанию.
• Открытие соединения в JDBC вернет управление транзакциями
разработчику:
connection
conn
=
DriverManager.getConnection
("jdbc:oracle:oci:@database","scott","tiger");
conn.setAutoCommit (false);

38.

38
Управление транзакциями
• Если вы используете SQL Plus в Windows и выполняете
команду DML а затем команду EXIT (EXIT это команду
SQL *Plus а не SQL), ваша транзакция будет
подтверждена. Это потому что разработчики SQL *Plus
встроили вызов команды COMMIT в команду EXIT.
• Если же вы нажмёте на красный крест в правом
верхнем углу – то произойдёт вызов команды
ROLLBACK. Так происходит потому что разработчики
SQL *Plus запрограммировали такое поведение
программы.
• В другой операционной системе поведение программы
SQL Plus может быть другим.

39.

39
Фиксация транзакции
• Во
время транзакции вся информация о
произведенных изменениях хранится либо в
специально выделенной оперативной памяти,
либо во временной области отката в самой БД
до тех пор, пока не будет выполнена одна из
команд управления транзакциями.
• Затем все изменения или фиксируются в БД,
или отбрасываются, а временная область
отката освобождается.

40.

Журнал транзакций
• Для каждой базы данных MS SQL ведется ее
собственный журнал транзакций.
• Журнал транзакций применяется для отката или
восстановления транзакции.
• Если в процессе выполнения транзакции еще до ее
завершения возникает ошибка, то система использует
все существующие в журнале транзакций исходные
значения записей (которые называются исходными
образами записей (before image)), чтобы выполнить
откат всех изменений, выполненных после начала
транзакции.
• Процесс, в котором исходные образы записей из
журнала транзакций используются для отката всех
изменений, называется операцией отмены записей
(undo activity).

41.

Журнал транзакций
• В
журналах
транзакций
также
сохраняются
преобразованные образы записей (after image).
Преобразованные образы — это модифицированные
значения, которые применяются для отмены отката всех
изменений, выполненных после старта транзакции.
• Этот процесс называется операцией повторного
выполнения действий (redo activity) и применяется
при восстановлении базы данных.
• Каждой записи в журнале транзакций присваивается
однозначный идентификатор, называемый порядковым
номером журнала транзакции (log sequence number или
LSN). Все записи журнала, являющиеся частью
определенной транзакции, связаны друг с другом, чтобы
можно было найти все части этой транзакции для
операции отмены или повтора.

42.

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

43.

Уровни изоляции
• Теоретически,
все
транзакции
должны
быть
изолированы друг от друга.
• Но в таком случае доступность данных значительно бы
понизилась, поскольку операции чтения транзакции
блокировали бы операции записи в других транзакциях,
и наоборот.
• Если
доступность
данных
является
важным
требованием, то это свойство можно ослабить,
используя уровни изоляции.

44.

44
Уровни изоляции в SQL Server
• SQL Server поддерживает все четыре уровня
изоляции, определенные стандартом ANSI.
• Уровень изоляции устанавливается командой:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED |
REPEATABLE READ | SERIALIZABLE |
SNAPSHOT | READ COMMITTED SNAPSHOT }
• Уровень изоляции READ COMMITTED установлен
в SQL Server по умолчанию.

45.

45
Уровни изоляции
• СУБД, обеспечивающие транзакционность, не всегда
поддерживают все четыре уровня,
могут вводить
дополнительные. Возможны также различные нюансы в
обеспечении изоляции.
• Microsoft SQL Server поддерживает все четыре
стандартных
уровня
изоляции
транзакций,
а
дополнительно — уровень SNAPSHOT, находящийся
между Repeatable read и Serialized. Транзакция,
работающая на данном уровне, видит только те
изменения данных, которые были зафиксированы до её
запуска, а также изменения, внесённые ею самой, то
есть ведёт себя так, как будто получила при запуске
моментальный снимок данных БД и работает с ним.

46.

Уровни изоляции
READ UNCOMMITTED;
READ COMMITTED;
REPEATABLE READ;
SERIALIZABLE;
SNAPSHOT.
• Уровни изоляции READ UNCOMMITTED, REPEATABLE
READ
и
SERIALIZABLE
доступны
только
в
пессимистической
модели
одновременного
конкурентного доступа, тогда как уровень SNAPSHOT
доступен
только
в
оптимистической
модели
одновременного конкурентного доступа. Уровень
изоляции READ COMMITTED доступен в обеих
моделях.

47.

47
Уровни изоляции в ORACLE
• Если один пользователь читает данные из строки,
другой может иметь возможность писать в ту же строку.
• Если один пользователь изменяет данные строки,
другие пользователь должны иметь возможность
одновременно читать ту же строку.
• Если два пользователя изменяют одну и ту же строку,
необходимо предотвратить только одновременный
доступ к одной строке.

48.

48
Уровни изоляции в ORACLE

49.

49
Уровни изоляции в ORACLE
Транзакция может быть запущена в одном из двух
режимов: READ-WRITE (по умолчанию) и READ-ONLY.
• В
режиме
READ-WRITE
транзакция
может
модифицировать объекты БД,
видит изменения,
вносимые в БД другими транзакциями, – после
фиксации этих транзакций.
• В режиме READ-ONLY транзакция
не может
модифицировать объекты БД, не видит изменений,
вносимых в базу данных другими транзакциями.

50.

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

51.

51
Проблемы параллельного доступа с
использованием транзакций
• потерянное
обновление
—
несколько
транзакций
одновременно могут считывать и обновлять одни и те же
данные. При этом теряются все обновления данных, за
исключением
обновлений,
выполненных
последней
транзакцией;
• «грязное» чтение— чтение данных, добавленных или
изменённых
транзакцией,
которая
впоследствии
не
подтвердится (откатится);
• неповторяющееся чтение—один процесс считывает данные
несколько раз, а другой процесс изменяет эти данные между
двумя операциями чтения первого процесса. В таком случае
значения двух чтений будут разными;
• фантомное чтение — подобна проблеме неповторяемого
чтения, две последовательные операции чтения могут
возвратить разные значения. Но в данном случае причиной
этому является считывание разного числа строк при каждом
чтении. Дополнительные строки называются фантомами
и вставляются другими транзакциями.

52.

52
Уровни изоляции стандарта SQL-92
UNCOMMITTED - нефиксированное чтение. Здесь возможно
получение разных результатов для одинаковых запросов без учета фиксации
транзакции. Read uncommitted – не поддерживается PostgreSQL.
• READ COMMITTED - фиксированное чтение. Этот уровень позволяет
получать разные результаты для одинаковых запросов, но только после
фиксации транзакции, повлекшей изменение данных;
• REPEATABLE READ - повторяющееся чтение. Уровень, при котором
читающая транзакция «не видит» изменения данных, которые были ею ранее
прочитаны. При этом никакая другая транзакция не может изменять данные,
читаемые текущей транзакцией, пока та не окончена.
Этот уровень
целесообразно использовать, если на выполняющиеся SQL-операторы не
влияет добавление новых строк;
• SERIALIZABLE - последовательное выполнение. Этот уровень гарантирует
предотвращение всех описанных выше конфликтных ситуаций, но,
соответственно, при нем наблюдается самая низкая степень параллелизма и
самый высокий уровень изолированности: транзакции полностью изолируются
друг от друга, каждая выполняется так, как будто параллельных транзакций не
существует. Только на этом уровне параллельные транзакции не подвержены
эффекту “фантомного чтения”;
• READ

53.

53
Уровни изоляции

54.

54
Потерянное обновление (lost update)
Несколько пользователей изменяют одну и ту же строку,
основываясь на ее начальном значении;
Часть данных будет потеряна, т.к. каждая последующая
транзакция перезапишет изменения, сделанные предыдущей.
Выход из этой ситуации заключается в последовательном
внесении изменений.

55.

55
Черновое чтение или "грязное"
чтение (dirty read)
• Одна
транзакция
считывает
незавершенные
изменения, внесенные другой транзакцией.
• Значение, полученное второй транзакцией, будет
отличаться от значения, хранимого в базе данных.

56.

56
Повторяемое и неповторяемое чтение
Уровень
изоляции
транзакции • Повторяемое чтение (repeatable read)
определяет,
могут
ли
другие происходит, если запрос, повторно
(конкурирующие) транзакции вносить выполняемый в одной транзакции,
постоянно возвращает один и тот же
изменения в данные, измененные
набор данных и игнорирует изменения,
текущей транзакцией, а также может вносимые другими незавершенными и
ли
текущая
транзакция
видеть завершенными транзакциями. Только
изменения,
произведенные после того как текущая транзакция
конкурирующими транзакциями, и заканчивается и начинается другая, в
запросах
становятся
заметны
наоборот.
работы
других
• уровень
0
–
запрещение результаты
завершенных транзакций.
"загрязнения" данных. Этот уровень
требует, чтобы изменять данные • И наоборот, неповторяемое чтение
(non-repeatable
read)
происходит,
могла только одна транзакция; если когда
запрос,
несколько
раз
другой
транзакции
необходимо выполняемый в одной транзакции,
изменить те же данные, она должна выдает несогласованные данные в
ожидать
завершения
первой результате
изменений,
вносимых
другими транзакциями.
транзакции;
• уровень 1 – запрещение "грязного"
чтения. Если транзакция начала
изменение данных, то никакая
другая транзакция не сможет

57.

57
Неповторяемое чтение

58.

58
Фантомы - частный случай неповторяемого
чтения
• Фантом (phantom, фиктивные элементы)— особый тип
неповторяемого чтения. Это строка, которая вводится во
время неоднократного выполнения запроса в одной и той
же транзакции и отмечается этим запросом.
• При этом, если выполняемый SQL-оператор выбирает не
все значения, то выполнение оператора INSERT не
приведет к ситуации фантомной вставки.

59.

Управление транзакциями
• SQL Server использует механизм блокировки для
обеспечения целостности данных при доступе к ним
многих пользователей.
• Внутренний процесс, носящий название Диспетчер
блокировок (Lock Manager) определяет, какую блокировку
применить в том или ином случае, как долго сохранять
блокировку, и определяет, когда позволять процессам
изменять данные так, чтобы результаты операций
чтения были непротиворечивыми.
• Диспетчер блокировок руководствуется правилом FIFO, то
есть первый попавший в очередь обслуживается первым.
• Для принудительного завершения процесса можно
использовать команду KILL.

60.

60
Блокировки
• Транзакции
накладывают блокировки на
данные,
чтобы
обеспечить
выполнение
требований ACID.
• Блокирование происходит, когда один сеанс
удерживает ресурс, запрашиваемый другим
сеансом.
Запрашивающий
сеанс
будет
заблокирован - "повиснет" до тех пор, пока
удерживающий сеанс не завершит работу с
ресурсом.

61.

61
Блокировки
Блокировки (locks) - механизмы, применяемые для
управления параллельными изменениями данных,
предотвращения возникновения конфликтных ситуаций,
связанных
с
одновременным
доступом
к
пересекающимся данным путем временного ограничения
на выполнение некоторых операций обработки данных.
Блокировка может быть наложена как на отдельную
строку таблицы, таблицу, так и на всю базу данных.
Управлением блокировками на сервере занимается
менеджер блокировок, контролирующий их применение и
разрешение конфликтов.

62.

Блокировка
• Режимы блокировки определяют разные типы
блокировок.
• Выбор определенного режима блокировки зависит
от типа ресурса, который требуется заблокировать.
• Для блокировок ресурсов уровня строки и
страницы применяются следующие три типа
блокировок:
разделяемая (shared, S);
монопольная (exclusive, X);
обновления (update, U).

63.

63
Блокировки SQL Server
Разделяемая блокировка (shared lock) резервирует
ресурс (страницу или строку) только для чтения.
Другие процессы не могут изменять заблокированный
таким образом ресурс, но, с другой стороны, несколько
процессов могут одновременно накладывать разделяемую
блокировку на один и тот же ресурс.
Иными словами, чтение ресурса с разделяемой
блокировкой могут одновременно выполнять несколько
процессов.
Shared Lock - разделяемая блокировка, которая
используется при выполнении операции чтения данных.
Позволяется чтение данных другой транзакцией, но
запрещено изменение данных.

64.

64
Блокировки SQL Server
Монопольная блокировка (exclusive lock) резервирует страницу или
строку для монопольного использования одной транзакции. Блокировка
этого типа применяется инструкциями DML (INSERT, UPDATE и DELETE),
которые модифицируют ресурс.
Монопольную блокировку нельзя установить, если на ресурс уже
установлена разделяемая или монопольная блокировка другим процессом,
т. е. на ресурс может быть установлена только одна монопольная
блокировка.
На ресурс (страницу или строку) с установленной монопольной
блокировкой нельзя установить никакую другую блокировку.
Система баз данных автоматически выбирает соответствующий режим
блокировки, в зависимости от типа операции (чтение или запись).
Exclusive Lock - монопольная блокировка, которая применяется при
изменении данных. Эта блокировка полностью запрещает доступ к данных
другими транзакциями.
Update Lock - блокировка обновления, которая является промежуточной
между разделяемой и монопольной блокировкой. Используется, когда
транзакция хочет обновить данные в какой-то ближайший момент времени,
но не сейчас, и, когда этот момент придет, не хочет ожидать другой
транзакции. В этом случае другим транзакциям разрешается устанавливать
разделяемые блокировки, но не позволяет устанавливать монопольные.

65.

Блокировка
• Компонент Database Engine также поддерживает и
другие
типы
блокировок,
такие
как
кратковременные блокировки (latch lock) и
взаимоблокировки (spin lock).
• На уровне таблицы существует пять разных типов
блокировок:
разделяемая (shared, S);
монопольная (exclusive, X);
разделяемая с намерением (intent shared, IS);
монопольная с намерением (intent exclusive, IX);
разделяемая с монопольным намерением (shared
with intent exclusive, SIX).

66.

Блокировка
• Разделяемые и монопольные типы блокировок для
таблицы соответствуют одноименным блокировкам для
строк и страниц.
• Обычно блокировка с намерением (intent lock) означает,
что транзакция намеревается блокировать следующий
нижележащий в иерархии объектов базы данных ресурс.
• Т.е. блокировка с намерением помещаются на уровне
иерархии объектов, который выше того объекта, который
этот процесс намеревается заблокировать.
• Это является действенным способом узнать, возможна
ли подобная блокировка, а также установить запрет
другим процессам блокировать более высокий уровень,
прежде чем процесс может установить требуемую ему
блокировку.

67.

Блокировка
• Блокировка обновления (update lock) может быть
установлена на ресурс только при отсутствии на нем другой
блокировки обновления или монопольной блокировки. У
объекта может быть только одна блокировка обновления.
• Этот тип блокировки можно устанавливать на объекты с
установленной разделяемой блокировкой. В таком случае
блокировка обновления накладывает на объект другую
разделяемую блокировку.
• Если
транзакция,
которая
модифицирует
объект,
подтверждается, и у объекта нет никаких других блокировок,
блокировка обновления преобразовывается в монопольную
блокировку.
• Блокировка обновления применяется для предотвращения
распространенных типов взаимоблокировок.

68.

Матрица совместимости разных
типов блокировок

69.

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

70.

Блокировка
Блокировка имеет несколько разных свойств:
длительность блокировки;
режим блокировки;
гранулярность блокировки.
• Длительность блокировки — это период
времени, в течение которого ресурс удерживает
определенную
блокировку.
Длительность
блокировки зависит, среди прочего, от режима
блокировки и выбора уровня изоляции.

71.

Блокировка
Гранулярность блокировки определяет, какой
ресурс блокируется в одной попытке блокировки.
Компонент Database Engine может блокировать
следующие ресурсы:
• строки;
• страницы;
• индексный ключ или диапазон индексных ключей;
• таблицы;
• экстент;
• саму базу данных.

72.

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

73.

Укрупнение блокировок
• Если в процессе транзакции имеется большое количество
блокировок одного уровня, то Database Engine
автоматически объединяет эти блокировки в одну уровня
таблицы.
• Этот процесс преобразования большого числа блокировок
уровня строки, страницы или индекса в одну блокировку
уровня таблицы называется укрупнением блокировок
(lock escalation).
• Пороги
укрупнения
устанавливаются
динамически
системой и не требуют настройки (5000 блокировок).
• Проблемой укрупнения блокировок является то, что когда
сервер баз данных принимает решение об укрупнении,
оно
может не быть оптимальным для приложений,
имеющих различные требования.

74.

74
О использовании блокировок
• Для
большинства приложений приемлемы алгоритмы
блокирования СУБД, устанавливаемые по умолчанию.
• Иногда транзакции выполняются быстрее, если они явно
блокируют данные, предвидя, какая операция будет
выполнена над БД.
• Если
таблица велика, целесообразнее перед ее
обновлением заблокировать всю таблицу ( алгоритмы
блокирования по умолчанию устанавливают строчные
блокировки по мере обновления каждой строки таблицы).
Пример: Если во время выполнения оператора UPDATE,
примененного ко всем строкам таблицы, обновятся почти все
строки, а затем встретится заблокированная строка, может
понадобится откат обновления всех предыдущих строк – это
займет много времени.

75.

Укрупнение блокировок
• Механизм укрупнения блокировок можно модифицировать с
помощью инструкции ALTER TABLE. Эта инструкция
поддерживает параметр TABLE и имеет следующий
синтаксис:
SET (LOCK_ESCALATION = {TABLE | AUTO | DISABLE})
• Параметр TABLE является значением по умолчанию и задает
укрупнение блокировок на уровне таблиц.
• Параметр AUTO позволяет Database Engine выбирать
уровень гранулярности, который соответствует схеме
таблицы.
• Параметр DISABLE отключает укрупнение блокировок в
большинстве случаев.
Пример . Отмена возможности укрупнения блокировок для
таблицы
USE sample;
ALTER TABLE employee SET (LOCK_ESCALATION = DISABLE);

76.

Настройка блокировок
• Настройку блокировок можно осуществлять, используя подсказки
блокировок (locking hints) или параметр LOCK_TIMEOUT инструкции
SET.
• Подсказки блокировок задают тип блокировки, используемой Database
Engine для блокировки табличных данных.
• Все подсказки блокировок указываются в предложении FROM инструкции
SELECT.
• Типы доступных подсказок блокировок:
• UPDLOCK — устанавливается блокировка обновления для каждой строки
таблицы при операции чтения. Все блокировки обновления удерживаются
до окончания транзакции;
• TABLOCK
(TABLOCKX)
—
устанавливается
разделяемая
(или
монопольная) блокировка для таблицы. Все блокировки удерживаются до окончания транзакции;
• ROWLOCK — существующая разделяемая блокировка таблицы
заменяется разделяемой блокировкой строк для каждой отвечающей
требованиям строки таблицы;
• PAGLOCK — разделяемая блокировка таблицы заменяется разделяемой
блокировкой страницы для каждой страницы, содержащей указанные
строки;

77.

Настройка блокировок
• NOLOCK — синоним для READ UNCOMMITTED ;
• HOLDLOCK — синоним для REPEATABLE READ (;
• XLOCK — устанавливается монопольная блокировка, удерживаемая
до завершения транзакции. Если подсказка XLOCK указывается с
подсказкой ROWLOCK, PAGLOCK или TABLOCK, монопольные
блокировки
устанавливаются
на
соответствующем
уровне
гранулярности;
• READPAST — указывает, что компонент Database Engine не должен
считывать строки, заблокированные другими транзакциями.
Все эти параметры можно объединять вместе в любом имеющем смысл
порядке. Например, комбинация подсказок TABLOCK с PAGLOCK не
имеет смысла, поскольку каждая из них применяется для разных
ресурсов.

78.

78
Параметр LOCK_TIMEOUT
• Чтобы процесс не ждал освобождения блокируемого объекта
до бесконечности, использовать параметр LOCK_TIMEOUT.
Этот параметр задает период в миллисекундах, в течении
которого транзакция будет ожидать снятия блокировки с
объекта.
• Период ожидания равен 8 секундам:
• SET LOCK_TIMEOUT 8000
• Если данный ресурс не может быть предоставлен процессу в
течение этого периода времени, инструкция завершается
аварийно и выдается сообщение об ошибке.
• Значение LOCK_TIMEOUT равное -1 (значение по
умолчанию) указывает отсутствие периода ожидания, то есть
транзакция не будет ожидать освобождения ресурса совсем.

79.

Отображение информации о блокировках
• Важным средством для отображения информации о
блокировках
является
динамическое
административное
представление
SYS.DM_TRAN_LOCKS.
• Представление возвращает информацию о текущих
активных ресурсах диспетчера блокировок. Каждая
строка представления отображает активный в
настоящий момент запрос на блокировку, которая
была предоставлена или предоставление которой
ожидается.
• Столбцы представления соответствуют двум группам:
ресурсам и запросам.
• Группа ресурсов описывает ресурсы, на блокировку
которых делается запрос, а группа запросов
описывает запрос блокировки.

80.

Отображение информации о
блокировках
Наиболее важными столбцами этого представления
являются :
• resource_type — указывает тип ресурса;
• resource_database_id — задает идентификатор базы
данных, к которой принадлежит данных ресурс;
• request_mode — задает режим запроса;
• request_status — задает текущее состояние запроса.
Пример:
USE AdventureWorks2012;
SELECT resource_type, DB_NAME(resource_database_id) as
db_name, request_session_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT;'

81.

81
Режим блокировки Oracle

82.

82
Классификации блокировок
ORACLE
1. разделяемые блокировки (S-locks);
2. исключительные блокировки (X-locks) или
монопольные.
Существуют логические и физические уровни блокировок:
• блокировка на уровне строк (row-level locking);
• блокировка на уровне элемента таблицы (item-level locking);
• блокировка на уровне таблицы (table-level locking);
• блокировка на уровне страницы или блока (page-level
locking).
• блокировка на уровне табличного пространства (tablespacelevel locking);
• блокировка на уровне БД (dbspace-level locking);

83.

83
Классификации блокировок
• Монопольная блокировка –запрещает разделение
ассоциированного
ресурса.
Первая
транзакция,
получившая
монопольную
блокировку
ресурса,
становится единственной транзакцией, которая может
изменять этот ресурс до снятия монопольной
блокировки.
• Разделяемая блокировка – позволяет совместно
использовать ассоциированный ресурс, в зависимости
от того, какие операции выполняются (например,
несколько пользователей могут читать данные
одновременно). Несколько транзакций могут получить
разделяемые блокировки для одного и того же ресурса.

84.

84
LOCK TABLE
Предложение LOCK TABLE в Oracle позволяет наложить
замок в требуемом режиме на таблицу:
LOCK TABLE имя_таблицы IN режим_блокировки
MODE [NOWAIT | WAIT [ n ]]
Блокировку таблицы в режимах SHARE и EXCLUSIVE можно
специально запретить или же наоборот, разрешить.
ALTER TABLE emp DISABLE TABLE LOCK;
Режим блокировки определяет, какие другие блокировки
могут быть применены к таблице. Например, несколько
пользователей могут одновременно затребовать блокировки
row share для одной и той же таблицы, но лишь один
пользователь за раз может затребовать МОНОПОЛЬНУЮ
(exclusive) блокировку.

85.

85
Режим блокировки LOCK TABLE
разрешает запросы к заблокированной
таблице, но запрещает все остальные действия (запрос
блокировок и insert, delete, update).
• Эта
блокировка автоматически запрашивается для
ТАБЛИЦЫ, модифицируемой следующими предложениями:
• SELECT ... FROM таблица ... FOR UPDATE OF ...;
• LOCK TABLE таблица IN ROW SHARE MODE;
• SHARE разрешает одновременные запросы к таблице, но
запрещает обновлять заблокированную таблицу
и
запрашивать некоторые блокировки, а те, которые могут
быть запрошены (ROW SHARE, SHARE ), выполняться
после фиксации первой.
• EXCLUSIVE

86.

86
Режим блокировки LOCK TABLE
EXCLUSIVE
позволяет другим транзакциям
запрашивать, добавлять строки, а также удалять и
обновлять строки, не затронутые текущей блокировкой.
Позволяет создавать блокировки ROW EXCLUSIVE ,
SHARE UPDATE или ROW SHARE, применяя их к др.
строкам.
ROW
EXCLUSIVE
применяется
СУБД
автоматически при операциях INSERT, UPDATE, DELETE.
• ROW SHARE (SHARE UPDATE ) разрешает конкурирующий
доступ к заблокированной таблице, но запрещает
пользователям
блокировать
всю
таблицу
для
эксклюзивного доступа (EXCLUSIVE).
• SHARE ROW EXCLUSIVE используется для просмотра
всей таблицы и разрешения пользователям просматривать
строки в таблицы, но запрещает другие блокировки в
режиме SHARE и запрещает обновлять строки.
• ROW

87.

87
LOCK TABLE
Режимы с EXCLUSIVE предполагают монопольное овладевание
объектом, а режимы с характеристикой SHARE — долевое,
допускающее одновременное нахождение на объекте нескольких
однотипных замков (для режима SRX первенствует поведение
EXCLUSIVE).
Наличие внешних ключей в схеме обычно приводит к дополнительным
замкам на объектах БД и к дополнительным шансам блокирования
работы транзакций.

88.

Взаимоблокировки
• Взаимоблокировка (deadlock) — это проблема
одновременного конкурентного доступа, в которой две
транзакции блокируют друг друга.
• В примере используется инструкция WAITFOR, чтобы
приостановить обе транзакции на десять секунд, чтобы
эмулировать взаимоблокировку.

89.

Взаимоблокировки
• Если обе транзакции в примере будут выполняться в одно и
то же время, возникнет взаимоблокировка и система
возвратит следующее сообщение об ошибке:
Msg 1205, Level 13, State 45
Transaction (Process id 56) was deadlocked with another process
and has been chosen as deadlock victim. Rerun your command.
• Транзакция (процесс с идентификатором 56) находится во
взаимной блокировке с другим процессом и выбрана в
качестве потерпевшей взаимоблокировки.
• Система баз данных обрабатывает взаимоблокировку,
выбирая одну из транзакций (транзакцию, которая замыкает
цикл в запросах блокировки) в качестве "жертвы" и выполняя
ее откат. После этого выполняется другая транзакция.

90.

Взаимоблокировки
• Дедлок переводится как мёртвая блокировка. То есть
такая, которую нельзя разрешить увеличением
времени ожидания, даже если ждать несколько лет,
она не "рассосётся" сама собой.
• Можно включить логирование дедлоков, чтобы
посмотреть причины и затем начать с ними
разбираться.
• Возможно, изменился либо запрос отчёта, либо
планы выполнения, либо другие пользователи стали
интенсивнее работать. Причин может быть много.

91.

Взаимоблокировки
• В Microsoft SQL Server используется механизм устранения
взаимоблокировок на основе графа ожидания. Граф строится при
каждом запросе блокировки.

92.

Взаимоблокировки

93.

Взаимоблокировки
• По истечении некоего тайм-аута просыпается монитор
блокировок, и если он обнаруживает, что какая-то
транзакция ждет слишком долго, инициируется процесс
нахождения замкнутого цикла в графе ожидания.
• В случае обнаружения мертвой блокировки происходит
откат одной из транзакций, участвующих в цикле.
• «Жертва» вычисляется в зависимости от объема
проделанной работы, которая в свою очередь
определяется по количеству записей в журнале
транзакций, которые необходимо откатить.
• Однако есть возможность указать серверу, какую
транзакцию предпочтительнее видеть в качестве
«жертвы», с помощью команды:
SET DEADLOCK_PRIORITY
LOW | NORMAL |
@deadlock_var

94.

Взаимоблокировки
• Можно повлиять на то, какая транзакция будет выбрана
системой в качестве "жертвы" взаимоблокировки, присвоив в
инструкции SET параметру DEADLOCK_PRIORITY один из
21 (от –10 до 10) разных уровней приоритета
взаимоблокировки.
• Константа LOW соответствует значению –5, NORMAL
(значение по умолчанию) — значению 0, а константа HIGH —
значению 5.
• Сеанс "жертва" выбирается в соответствии с приоритетом
взаимоблокировки сеанса.

95.

Взаимоблокировки
• Если приоритеты взаимоблокировки сеансов различны, то в
качестве жертвы выбирается сеанс с более низким приоритетом
взаимоблокировки.
• Если у обоих сеансов одинаковый приоритет взаимоблокировки, то
экземпляр SQL Server выбирает сеанс, откат которого как откат
жертвы связан с меньшими затратами ресурсов.
• Например, если оба сеанса имеют приоритет взаимоблокировки
HIGH, то экземпляр выберет в качестве жертвы сеанс, откат
которого предположительно связан с меньшими затратами
ресурсов.
• Затраты определяются путем сравнения количества байт журнала,
записанных на данный момент в каждой транзакции. (Это
значение обозначается как Log Used в графе взаимоблокировки).
• При выборе блокировки, которой необходимо пожертвовать,
сервер исходит из соображений минимальной стоимости.

96.

96
"Мертвые" блокировки

97.

97
"Мертвые" блокировки
Разрешение конфликта:
• Заставьте сеанс, содержащий блокировку, сделать фиксацию или откат
• В чрезвычайной ситуации администратор может аварийно завершить
сеанс. Когда сеанс уничтожается, вся работа в пределах текущей
транзакции теряется (откатывается).
ALTER SYSTEM KILL SESSION ‘sid, serial#’,
• где sid – идентификатор сеанса, удерживающего блокировку, а serial# серийный номер сеанса.
• Их значения можно узнать из представлений словаря данных:
V$SESSION,DBA_WAITERS,
V$ACCESS,V$SESSION_WAIT,
V$TRANSACTION.
• Пользователи, сеансы которых были уничтожены, получают следующую
ошибку в следующий раз, когда они пытаются выполнить SQL-оператор:
ORA-03135: connection lost contact

98.

98
"Мертвые" блокировки
PMON может автоматически уничтожать бездействующие сеансы в
соответствии с установленным тайм-аутом, что может быть сконфигурировано
посредством профилей или Менеджера ресурсов.

99.

99
"Мертвые" блокировки

100.

100
Системный номер изменения, или
SCN
• Системный
номер изменения, или SCN (system
change number) –оценочный фактор, используемый
базой данных Oracle для отслеживания состояния в
каждый данный момент времени.
• SCN – это логическая временная метка, используемая
Oracle для упорядочивания событий, происходящих с
базой данных.

101.

101
Системный номер изменения, или
SCN
1.
В начале запроса Oracle анализирует текущий SCN
системы. Для простоты будем называть его SCN запроса
(query SCN).'
2.
При выполнении запроса Oracle считывает блоки
данных и создает из них результирующее множество
запроса. Считывая каждый блок данных, Oracle сравнивает
SCN запроса с SCN, находящимся в заголовке блока
данных:
• Если SCN в блоке данных равен SCN запроса или меньше
его, Oracle может использовать данные этого блока для
построения результирующего множества запроса.
• Если SCN в блоке данных больше SCN запроса, Oracle
считывает информацию из сегментов отката системы,
чтобы воссоздать информацию блока данных в том виде, в
котором она находилась в момент, соответствующий SCN
запроса.

102.

102
Системный номер изменения, или
SCN
• Когда транзакция модифицирует или вставляет данные,
Oracle сначала пишет новый SCN в сегмент отката.
• Процесс-писатель журналов затем немедленно вносит
запись о фиксации транзакции в журнал повторного
выполнения, и эта запись получает уникальный SCN в
сегмент отката.
• Фактически запись этого SCN в журнал повторного
выполнения отмечает зафиксированную транзакцию в
базе данных Oracle.

103.

103
Системный номер изменения (SCN )
помогает
Oracle
определять
необходимость
восстановления после сбоя, после внезапного прерывания
работы экземпляра базы данных или после издания
команды SHUTDONW ABORT.
• Всякий раз, когда база данных выполняет операцию
контрольной точки, Oracle пишет команду START SCN в
заголовки файлов данных.
• Управляющий файл поддерживает значение SCN для
каждого файла данных, называемый STOP SCN, который
обычно устанавливается в бесконечность, и всякий раз,
когда экземпляр останавливается нормально (командой
SHUTDOWN NORMAL или SHUTDOWN IMMEDIATE).
Oracle копирует номер START SCN в заголовках файлов
данных в номера STOP SCN для файлов данных в
управляющем файле.
• SCN

104.

104
Системный номер изменения, или
SCN
• Когда перезапускаете БД после успешного останова,
нет необходимости ни в каком восстановлении, потому
что номера SCN в файлах данных и управляющих
файлах соответствуют.
• Внезапное
прерывание работы экземпляра не
оставляет времени на приведение в соответствие
номеров SCN, и Oracle обнаруживает необходимость
восстановления экземпляра, потому что отличаются
номера SCN в файлах данных с одной стороны, и
управляющем файле - с другой. Они играют ключевую
роль в восстановлении базы данных.
• Oracle определяет, на сколько нужно вернуться,
применяя архивные журналы повторного выполнения
во время восстановления на основе SCN.

105.

105
Блокировки MS Sql Server
Sys.dm_tran_locks
Sys.dm_exec_connections
Sys.dm_exec_sessions
Sys.dm_exec_requests (blocking_session_id)
KILL [SPID]
SPID- уникальный ID серверного процесса

106.

DBCC
• Инструкции Database Consistency Checker (DBCC) – команды,
которые показывают, что происходит внутри SQL Server,
позволяют оперативно определить неисправность и найти
проблемы связанные с падением производительности.
• DBCC выступают в качестве консольных команд базы данных
для SQL Server.
• Флаги трассировки используются для пользовательской настройки
определенных характеристик в целях управления работой SQL
Server.
• Нижележащие команды могут быть использованы для записи
подробностей взаимоблокировки в журнале ошибок SQL Server.

107.

DBCC
Включить трассировку
DBCC TRACEON (1222,-1)
-1 = сбор информации изо всех сессий
Проверить трассировку
DBCC TRACEON (-1) или
DBCC TRACEON(1204)
DBCC TRACEON(3604)
DBCC TRACEON(3605)
DBCC TRACEOFF(1204)
DBCC TRACEOFF(3604)
DBCC TRACEOFF(3605)
Логи о дедлоках будут писаться в errorlog сервера

108.

DBCC
Краткий перечень флагов, которые могут пригодиться при
отлове взаимоблокировок:
1204 – сбор расширенной информации о взаимоблокировке.
1205 -дополняет флаг 1204 (мёртвые блокировки), отображает
стек вызовов в случае возникновения мёртвой блокировки
3605 – выдача информации в EventLog.
3406 – выдача информации в файл errorlog.
1206 – сбор информации не только о блокировках,
участвующих во тупиковой ситуации (что делает флаг 1204), но
и об остальных блокировках, наложенных заблокированными
транзакциями.
1200 – сбор информации о порядке наложения блокировок
(недокументированный).

109.

Мониторинг сервера
Оборудование
ломается,
производительность
приложений падает, транзакции блокируют друг друга,
внесение изменений в среду приводит к отказам.
Необходимо наблюдать за средой SQL Server и
диагностировать неполадки.

110.

Мониторинг сервера
• В состав Microsoft SQL Server и операционной системы
Microsoft Windows входят служебные программы,
позволяющие следить за текущим состоянием БД и
измерять производительность, если это состояние
меняется.
• Для
возможности увеличения производительности
мониторят время ответа для часто используемых
запросов с получением рекомендаций по изменению
текста запроса или индексов таблицы.
• Можно
оценивать
активность
пользователей,
определить, правильно ли настроены параметры
безопасности, проверить работу приложений и систем
разработки.

111.

Средства мониторинга
компонентов SQL Server
• Монитор активности
• Приложение SQL Server Profiler
• Расширенные события
• Трассировка SQL
• Программа распределенного воспроизведения
• SQL Server Management Studio Графическое
отображение инструкции Showplan
• Системные хранимые процедуры
• Консольные команды базы данных (DBCC)
• Динамические административные представления и
функции
• Функции
• Флаги трассировки

112.

Мониторинг активности
Монитор
активности
отображает
сведения
о
процессах SQL Server и о том, как функционирование этих
процессов влияет на текущий экземпляр SQL Server.

113.

Мониторинг сервера
• Монитор
активности
выполняет
запросы
в
отслеживаемом экземпляре.
• Для просмотра фактической активности нужно
разрешение VIEW SERVER STATE.
• Для просмотра раздела ввода-вывода в файл данных
монитора активности, кроме разрешения на VIEW
SERVER STATE, необходимо иметь также разрешения на
CREATE DATABASE, ALTER ANY DATABASE или VIEW
ANY DEFINITION.
• Для вызова инструкции KILL для процесса пользователь
должен быть членом предопределенных ролей сервера
sysadmin или processadmin.

114.

Оптимизация производительности SQL Server с
использованием индексов
• Если
на сервере есть медленно выполняющийся
запрос, первое на что смотрят – статистика IO.
• В SQL Server есть несколько вариантов получения
данных о том, как выполняется запрос (просмотр плана
выполнения запросов).
• В случае с большими и сложными запросами план
выполнения может быть не наглядным. Статистика
получается легко, показывает проблемные места в
читабельном виде.
• По статистике чтений можно увидеть, на какую таблицу
идет нагрузка, по каким полям в запросе происходит
фильтрация данной таблицы и убедиться, что по этим
полям есть индекс.

115.

Оптимизация производительности SQL Server
• Как включить статистику io и время time:

116.

Оптимизация производительности SQL
Server
• Первые четыре строчки
–
это
время
компиляции запроса.
• После идет статистика
выполнения. Тут нужно
смотреть
на
количество
сканирований
и
количество
чтений
(logical reads, physical
reads и др).
• Запрос
простой,
решается
одним
сканированием.
Судя
по количеству чтений,
сканировалась
абсолютно
вся
таблица.

117.

Оптимизация производительности SQL
• Если запрос выполняется быстро и один раз, можно
ничего не делать.
• Если сразу тысяча человек будут выполнять этот запрос
и искать по разному условию или в базе будет миллион,
даже при низкой статистике нагрузка на БД будет
серьезная.
• Для оптимизации нужно создать индекс, который
ускоряет поиск.

118.

Анализ запросов с помощью SQL Profiler
• SQL Server Profiler — предназначен для трассирования
(и/или записи в файл или таблицу SQL Server) всех
событий SQL Server, включая выполняемые на нем
команды Transact-SQL.
• Запустить
это
приложение
можно
из
меню
Start/Programs/ SQL Server /Performance Tools или из
меню Tools двух других приложений— SQL Server
Management Studio и Database Engine Tuning Advisor.

119.

Анализ запросов с помощью SQL
Profiler
SQL Profiler используется администраторами для:
• анализа работы приложения;
•определения оптимальности запросов, направляемых на
сервер;
•выявления команд Transact-SQL, при выполнении
которых возникает ошибка;
•сбора информации о пользовательской активности в
течение продолжительного промежутка времени;
•проведения мониторинга работы сервера в режиме
реального времени.

120.

SQL Profiler
• SQL Server Profiler - это инструмент для создания и
управления
трассировками
и
анализа
полученных
результатов.
• Можно сохранить эти события, записывая подробности в
файл трассировки, который можно проанализировать и / или
использовать позже, чтобы сгенерировать определенную
последовательность шагов при попытке диагностировать
проблему.
• Используя графический интерфейс профилировщика, можно
отслеживать внутреннюю активность в SQL Server.
• Можно фиксировать все или только те события, которые
интересуют.
• SQL Server Profiler используется для захвата деталей и
графиков взаимоблокировок.

121.

SQL Profiler
Запускаем
SQL Profiler Пуск - Все программы Microsoft SQL Server - Средства обеспечения
производительности – SQLProfiler (или из меню Сервис
Management Studio).

122.

SQL Profiler

123.

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

124.

SQL Profiler

125.

Шаблоны трассировки SQL Profiler
• SQLServerProfilerSP_Counts.tdf. Подсчитывает количество
запущенных хранимых процедур. Результаты группируются
по именам хранимых процедур и содержат количество
запусков соответствующей процедуры.
• SQLServerProfilerStandard.tdf.
Собирает
общую
информацию о соединениях, выполненных хранимых
процедурах и пакетах SQL в порядке их выполнения.
• SQLServerProfilerTSQL.tdf. Собирает информацию обо
всех операторах T-SQL в порядке их поступления в SQL
Server от пользователей. Эта трассировка содержит просто
операторы T-SQL и моменты времени их запуска.

126.

Шаблоны трассировки SQL Profiler
Выводит
запущенные операторы T-SQL, а также время (в
миллисекундах), которое потребовалось для выполнения
этих операторов.
• SQLServerProfilerTSQL_Replay.tdf.
Предоставляет
подробную информацию о запускавшихся операторах TSQL.
• SQLServerProfilerTSQL_SPs.tdf . Выводит указанные
хранимые процедуры, а также команды T-SQL внутри этих
процедур. Результаты выводятся в порядке выполнения.
• SQLServerProfilerTuning.tdf. Собирает данные о хранимой
процедуре и выполнении пакета SQL.
• SQLServerProfilerTSQL_Duration.tdf.

127.

SQL Profiler
Перейдите на вкладку «Выбор событий» в левом верхнем углу
диалогового окна «Свойства трассировки» и выберите события.

128.

SQL Profiler
TSQL_Locks -cобирает информацию об обычных и взаимных
блокировках, блокировка процессов, цепочки взаимной блокировки,
графы взаимной блокировки, укрупнение блокировок и времена
ожидания блокировки. Также регистрируются все хранимые
процедуры,
все
команды
хранимых
процедур
и
все
нерегламентированные SQL-запросы

129.

SQL Profiler
Нажмите на кнопку «Выполнить» внизу. SQL Server Profiler начал захватывать
события, выбранные ранее. Нажав кнопку «Выполнить», создайте
искусственный тупик. Посмотрите на вывод Profiler.
Два процесса 57 и 58 заблокированы, а процесс 57 объявлен жертвой
тупика и убит. Можно видеть, что имя объекта, другие детали, такие как DBID,
FileID, PageID, идентификатор связанного объекта отражены на графике.

130.

Взаимоблокировки

131.

ВКЛАДКА ВЫБОРА СОБЫТИЙ
SQL Profiler
• Событие — это действие экземпляра SQL Server Database
Engine. Выбор событий — это основная часть настройки
трассировки, он предполагает работу с матрицей:
«Событие» — «Свойство события».
• Таким образом, в этой матрице надо установить флажки по
тем событиям и их свойствам, которые хотите трассировать.
• Приложение SQL Server Profiler за короткое время может
собрать очень много событий, которые будучи загружены в
сетку интерфейса SQL Server Profiler, могут требовать очень
большого объема памяти.
• Время
остановки
трассировки
позволяет
запускать
трассировку на заданное время.

132.

SQL Profiler

133.

SQL Profiler
• На форме присутствуют флажки: «Показать все
события» (Show all events) и «Показать все столбцы»
(Show all columns).
• При установленном флажке в матрице раскрываются все
события/столбцы,
при
снятом
остаются
только
выбранные.
• Флажок «Показать все столбцы» влияет на отображение
данных в «Фильтры столбцов» — отображаемый список
соответствует отображаемым столбцам в матрице.
• При этом, даже если столбец скрыт (не выбран в
матрице и снят флаг «Показать все столбцы»), но отбор
на него был установлен — отбор сработает.

134.

SQL Profiler
• «Фильтры столбцов» (Column Filters) — открывает список
столбцов, по которым можно установить отборы.
• Если значение события при трассировке не подходит под
значение отбора в столбце, данное событие не будет
отражено в трассировке.
• Таким
образом,
можно
установить
отбор
на
информационную базу, по которой необходимо произвести
трассировку.
• «Упорядочить столбцы» (Organize Columns) — используется
для изменения (организации) порядка следования
выводимых колонок.

135.

SQL Profiler
В трассировке - только запросы к базе данных «TestBase_8_2»
При желании можно поставить фильтр и по другим полям,
наиболее
интересные
из
них:
Duration(Длительность),
TextData(обычно это текст запроса) и RowCounts (количество
строк возвращаемых запросом).

136.

SQL Profiler
Все запросы к таблице _InfoRg4312 длительностью более
3-х секунд в базе данных TestBase_8_2

137.

SQL Profiler
Фильтр по тексту запроса
Если
необходимо
отслеживать запросы,
которые обращаются к
нескольким таблицам,
то создаем несколько
элементов в разделе
«Похоже на». Условия
всех
фильтров
работают вместе.

138.

СОБЫТИЯ ДЛЯ ПОЛУЧЕНИЯ ГРАФА
ВЗАИМОБЛОКИРОВКИ

139.

СОБЫТИЯ ДЛЯ ПОЛУЧЕНИЯ
ИНФОРМАЦИИ ОБ ЭСКАЛАЦИИ
• Для получения информации об эскалации достаточно
добавить событие Locks: Escalation.
• Событие Escalation возникает при эскалации
блокировки, т.е. когда блокировка более мелких
фрагментов преобразуется в блокировку более
крупных фрагментов.
• Также можно ограничить набор выводимых колонок
теми данными, которые требуются для анализа.

140.

СОБЫТИЯ ДЛЯ ПОЛУЧЕНИЯ
ИНФОРМАЦИИ ОБ ЭСКАЛАЦИИ

141.

SQL Profiler
Нажимаем «Запустить», после этого трассировка начинает
работу, и можно видеть те события, отображение которых
настроили и которые попадают под фильтры. Для управления
трассировкой можно использовать кнопки на командной
панели.
Слева на право: Ластик – очищает окно трассировки, Пуск –
запускает трассировку, Пауза – ставит трассировку на паузу,
при нажатии на Пуск трассировка возобновляется, Стоп –
останавливает
трассировку

142.

142
СПАСИБО ЗА ВНИМАНИЕ

143.

143