Тема: ИЗУЧЕНИЕ СРЕДСТВ ПОДДЕРЖКИ ССЫЛОЧНОЙ

ЦЕЛОСТНОСТИ И УПРАВЛЕНИЯ ТРАНЗАКЦИЯМИ

В СУБД MYSQL 5

Цель работы:

Ознакомиться с основными функциональными возможностями СУБД

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

для упоавления транзакциями.

Общие сведения

Ссылочная целостность(англ. referential integrity) – необходимое качество реляционной БД, заключающееся в отсутствии в любой её таблице внешних ключей, ссылающихся на несуществующие записи в непосредственно этой или в других таблицах. Правильно спроектированная и поддерживаемая БД не допускает возможности нарушения ссылочной целостности.

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

0 — Read Uncommited (Dirty Read) — чтение незафиксированных изменений своей транзакции и конкурирующих транзакций, возможны некорректные, неповторяемые чтения и фантомы

1 — Read Commited — чтение всех изменений своей транзакции и зафиксированных изменений конкурирующих транзакций, некорректные чтения невозможны, возможны неповторяемые чтения и фантомы

2 — Repeatable Read (Snapshot) — чтение всех изменений своей транзакции, любые изменения, внесённые конкурирующими транзакциями после начала своей недоступны, некорректные и неповторяемые чтения невозможны, возможны фантомы

3 — Serializable — cериализуемые транзакции, то есть транзакции, результат действия которых не зависит от порядка выполнения шагов транзакции (запрещено чтение всех данных изменённых с начала транзакции, в том числе и своей транзакцией).

 Чем выше уровень изоляции, тем больше требуется ресурсов, чтобы их поддерживать. Чаще всего используется уровень 1 (Read Commited). Уровень 0 используется в основном для отслеживания изменений длительных транзакций. Уровень 2 —соответствует теоретическому пониманию изоляции транзакций. Уровень 3 удобен для оптимизаторов СУБД. Несмотря на то, что наиболее безопасными являются второй и третий уровни изоляции транзакций, в различных ситуациях (когда гарантировано известно, что те или иные проблемы не возникнут) можно использовать и более низкие уровни изоляции с тем, чтобы снизить количество используемых в ходе работы транзакций ресурсов.

Часть СУБД, в которой хранятся записи о планирующихся транзакциях называется журнал транзакций. Работа с ним состоит из трёх шагов:

1. Сделать запись о намерение произвести некоторые операции.

2. Выполнить операции.

3. Внести запись, что операции выполнены безошибочно.

Журнал транзакций в сочетании с сегментом отката (область, в которой хранится копия всех изменяемых в ходе транзакции данных) гарантирует целостность данных. В случае сбоя запускается процедура восстановления, которая просматривает отдельные записи журнала транзакция следующим образом:

● Если повреждена запись, то сбой произошёл во время проставления отметки в журнале. Значит, ничего важного не потерялось, игнорируем эту ошибку.

● Если все записи помечены как успешно выполненные, то сбой произошёл между транзакциями, здесь также нет потерь.

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

Особенности разработки правил контроля ссылочной целостности

И работы с транзакциями в СУБД MySQL 5

При работе с транзакциями и средствами контроля целостности данных, необходимо помнить, что в MySQL 5 поддерживаются только таблицами типа InnoDB (в пятой версии это тип таблицы по умолчанию).

Ссылочная целостность

Правила по контролю ссылочной целостностью задаются при создании внешнего ключа (при выполнении команд CREATE TABLE и ALTER TABLE) следующим образом (приведена лишь часть запроса):

KEY

название ключа в дочерней таблице (поле в дочерней таблице)

CONSTRAINT

Название внешнего ключа

FOREIGN KEY (поле в дочерней таблице)

REFERENCES имя родительской таблицы (поле в родительской таблице)

[ON DELETE reference_option]

[ON UPDATE reference_option]

Здесь:

поле в дочерней таблице – имя поля содержащего внешний ключ в дочерней таблице, по данному полю должен быть создан индекс

имя родительской таблицы – имя внешней (родительской) таблицы на данные из которой ссылается внешней ключ

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

ON DELETE, ON UPDATE reference_option – правило поведения при (удалении/изменении) родительской записи, может принимать значения:

􀂃 CASCADE – каскадное изменение

􀂃 SET NULL – устанавливать дочернее поле в NULL

􀂃 NO ACTION – игнорировать существование ограничения

􀂃 RESTRICT – в MySQL 5 то же что и NO ACTION

Как правило, задается только ON DELETE правило, так как при синхронизации по первичному реляционному ключу (авто-инкрементому) обновления значения ключа не происходит.

Перед заданием правила необходимо создать ключевые поля в дочерней и родительской таблицах. Также правило может быть задано и после создания таблиц, например (здесь и далее рассматриваются таблицы из ЛР № 4):

ALTER TABLE `students`

ADD CONSTRAINT `students_fk`

FOREIGN KEY (`group_id`)

REFERENCES `groups` (`group_id`) ON DELETE CASCADE;

В EMS Manager для задания таких правил существует удобный интерфейс (см рисунок 8.1), данный диалог может быть вызван из закладки Foreign Keys для любой таблицы:

Здесь:

Table fields – задание поля дочерней таблицы;

Foreign table fields – задание поля родительской таблицы;

On delete rule – задает правила поведения при удалении строки в

родительской таблице

On update rule - – задает правила поведения при обновлениее строки в

родительской таблице

Рисунок 8.1 – Утилита EMS SQL Manager Lite for MySQL: диалог в режиме

редактирования внешнего ключа.

Транзакции

По умолчанию MySQL работает в режиме автоматического завершения транзакций (autocommit). Это означает, что как только выполняется оператор обновления данных, который модифицирует таблицу, MySQL тут же сохраняет изменения на диске (данное поведение может быть изменено, рассмотрение изменения поведения завершения транзакций находится за рамками данной лабораторной работы).

Если есть необходимость отключить режим автоматического завершения транзакций только для отдельной последовательности операторов, можно воспользоваться оператором

START TRANSACTION:

START TRANSACTION

SELECT … FROM GROUPS

UPDATE STUDENTS SET …

COMMIT;

После START TRANSACTION режим автоматического завершения транзакций остается выключенным до явного завершения транзакции с помощью COMMIT или отката посредством ROLLBACK. Затем режим автоматического завершения возвращается в свое предыдущее состояние. Уровень изоляции транзакций может быть изменен оператором SET TRANSACTION ISOLATION LEVEL (см. ниже).

Для некоторых операторов нельзя выполнить откат с помощью ROLLBACK. В их число входят операторы языка определения данных (Data Definition Language - DDL), которые создают и уничтожают базы данных, а также создают, удаляют и изменяют таблицы.

Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):

● ALTER TABLE

● BEGIN

● CREATE INDEX

● DROP DATABASE

● DROP INDEX

● DROP TABLE

● LOAD MASTER DATA

● LOCK TABLES

● RENAME TABLE

● SET AUTOCOMMIT=1

● START TRANSACTION

● TRUNCATE TABLE

Оператор UNLOCK TABLES также завершает транзакцию, если какие-либо таблицы были блокированы.

Транзакции в MySQL не могут быть вложенными. Это следствие того, что неявный COMMIT выполняется для любой текущей транзакции, когда выполняется оператор START TRANSACTION или его синонимы. Операторы SAVEPOINTи ROLLBACK TO SAVEPOINT:

SAVEPOINT идентификатор

ROLLBACK TO SAVEPOINT идентификатор

Оператор SAVEPOINT устанавливает именованную точку начала транзакции с именем идентификатор. Если текущая транзакция уже имеет точку сохранения с таким же именем, старая точка удаляется и устанавливается новая.

Оператор ROLLBACK TO SAVEPOINT откатывает транзакцию к именованной точке сохранения. Модификации строк, которые выполнялись текущей транзакцией после этой точки, отменяются откатом. Точки сохранения, установленные в более поздние моменты, чем именованная точка, удаляются.

Операторы LOCK TABLESи UNLOCK TABLES:

LOCK TABLES

имя_таблицы [AS псевдоним] {READ [LOCAL] | [LOW_PRIORITY]

WRITE}

[,имя_таблицы [AS псевдоним] {READ [LOCAL] | [LOW_PRIORITY]

WRITE}] ...

UNLOCK TABLES

LOCK TABLES блокирует таблицы для текущего потока сервера. UNLOCK TABLES снимает любые блокировки, удерживаемые текущим потоком. Все таблицы, заблокированные в текущем потоке, неявно разблокируются, когда поток выполняет другой оператор LOCK TABLES либо когда закрывается соединение с сервером.

LOCK TABLES не является оператором, безопасным в отношении транзакций, и неявно завершает транзакцию перед попыткой блокировать таблицы.

Основная причина необходимости применения LOCK TABLES – эмуляция транзакций или повышение скорости обновления таблиц. Если поток устанавливает блокировку по чтению (READ) на таблице, то этот поток (и все остальные) может только читать данные из таблицы. Если поток устанавливает блокировку записи (WRITE) таблицы, то лишь этот поток может читать и писать в таблицу. Доступ остальных нитей к таблице блокируется.

Разница между READ LOCAL и READ состоит в том, что READ LOCAL позволяет неконфликтующим операторам INSERT (параллельным вставкам) выполняться, пока блокировка удерживается. Однако это не может быть выполнено, если осуществлена попытка манипулировать файлами базы данных извне MySQL в то время, пока удерживается блокировка (т.н. оптимистическая и пессимистическая блокировка).

В случае применения LOCK TABLES необходимо блокировать все таблицы, которые используются в запросах. Если одна и та же таблица используется несколько раз в запросе (через псевдонимы), необходимо получить блокировку на каждый псевдоним. Пока блокировка, полученная от LOCK TABLES, активна, нельзя получить доступ ни к каким таблицам, которые не были блокированы этим оператором.

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

LOCK TABLE students READ;

SELECT * FROM students AS studentstable;

вызовет ошибку: Table 'studentstable' was not locked with LOCK TABLES, для блокировки таблицы через псевдоним необходимо было использовать конструкцию LOCK TABLE students AS studentstable READ.

Блокировки по записи (WRITE) обычно имеют более высокий приоритет, чем блокировки по чтению (READ), чтобы гарантировать, что обновления данных пройдут как можно быстрее. Это означает, что если один поток получает блокировку по чтению, а затем другой поток запрашивает блокировку по записи, то последующие запросы на блокировку по чтению будут ожидать, пока не будет установлена и снята блокировка по записи (приоритет операций может быть изменен, рассмотрение изменения приоритета операций находится за рамками данной лабораторной работы). LOCK TABLES работает следующим образом:

1. В определенном внутреннем порядке сортируются все таблицы, подлежащие блокировке. С точки зрения пользователя, этот порядок не определен.

2. Если таблица блокируется и по чтению и по записи, устанавливается блокировка записи перед блокировкой чтения.

3. Блокируется по одной таблице за раз до тех пор, пока поток не получит все блокировки.

Эта политика гарантирует, что при этом не случится взаимных блокировок (deadlocks).

Оператор SET TRANSACTION

SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ |

SERIALIZABLE }

Этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса. Поведение SET TRANSACTION по умолчанию заключается в том, что он устанавливает уровень изоляции для следующей (еще не стартовавшей) транзакции. Если используется ключевое слово GLOBAL, оператор устанавливает глобальный уровень изоляции транзакций по умолчанию для всех новых соединений, которые будут установлены с этого момента. Существующие соединения не затрагиваются. Уровни изоляции транзакций и их смысл соответствуют рассмотренным в разделе «Общие сведения».

LOCK TABLE (блокировка) и SET TRANSACTION (установка уровня изоляции транзакций) представляют собой различные подходы к изоляции транзакций.

Рассмотрим примеры, связанные с выполнением транзакций:

Запросы:

START TRANSACTION;

insert into students (student_id, firstname, lastname, group_id)

values (21, 'Степанов', 'Георгий', 3);

insert into students (student_id, firstname, lastname, group_id)

values (22, 'Дуравкин', 'Петр', 3);

ROLLBACK;

не внесут изменения в БД.

Запрос

START TRANSACTION;

insert into students (student_id, firstname, lastname, group_id)

values (21, 'Степанов', 'Георгий', 3);

SAVEPOINT onestud;

insert into students (student_id, firstname, lastname, group_id)

values (22, 'Дуравкин', 'Петр', 3);

ROLLBACK TO SAVEPOINT onestud;

добавит в таблицу students только одну запись.

В качестве примера использования транзакций в прикладных программах при взаимодействии с БД рассмотрим следующею ситуацию:

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

Тогда схематически алгоритм добавления данных можно описать следующим образом:

Шаг 1. START TRANSACTION

Шаг 2. если остались не добавленные записи выполнить запрос INSERT для текущей записи, иначе Шаг 4

Шаг 3. если запрос был выполнен удачно, перейти к Шагу 2, иначе к Шагу 5

Шаг 4. COMMIT, Выход из процедуры добавления

Шаг 5. ROLLBACK, Выход из процедуры добавления

Таким образом, либо все записи будут добавлены удачно и команда COMMIT подтвердит добавление данных, либо команда ROLLBACK вернет таблицы БД к состоянию до начала транзакции.

Порядок выполнения работы

В соответствии с моделью данных, разработанной в ходе выполнения предыдущих лабораторных работ и пользуясь приведенным выше описанием правил контроля ссылочной целостности и управления транзакциями необходимо:

(1) создать правила обеспечения ссылочной целостности;

(2) привести примеры операций, которые могли бы быть определены в транзакции, изобразить в графической нотации алгоритм работы этих транзакций.

Контрольные вопросы

1) Знать, что такое правила обеспечения ссылочной целостности, что такое транзакция.

2) Знать синтаксис команд задания правил обеспечения ссылочной целостности. Уметь задавать эти правила как во время создания таблиц, так и в процессе использования данных таблиц.

3) Знать уровни изоляции транзакций, описать на примерах их отличия.

ЛАБОРАТОРНАЯ РАБОТА № 7

Тема:ИЗУЧЕНИЕ ВОЗМОЖНОСТЕЙ ОПТИМИЗАЦИИ БД СРЕДСТВАМИ

СУБД MYSQL 5.0

Цель работы:

Ознакомиться основными возможностями оптимизации работы с БД, поддерживаемыми в СУБД MySQL 5.0. Выполнить оптимизацию структуры разработанной ранее БД, а также оптимизацию запросов.

Общие сведения

Оптимизация – это процесс изменения системы с целью повышения ее быстродействия.

Оптимизацию работы с БД можно разделить на 3 типа:

• оптимизация запросов

• оптимизация структуры

• оптимизация сервера.

Оптимизация запросов.

Оптимизация запросов - наиболее простой и приводящий к наиболее высоким результатам тип оптимизации. Запросами, которые чаще всего поддаются оптимизации, являются запросы на выборку.