Управление транзакциями в среде MS SQL Server

SQL Server предлагает множество средств управления поведением транзакций. Пользователи в основном должны указывать только начало и конец транзакции, используя команды SQL или API (прикладного интерфейса программирования). Транзакция определяется на уровне соединения с базой данных и при закрытии соединения автоматически закрывается. Если пользователь попытается установить соединение снова и продолжить выполнение транзакции, то это ему не удастся. Когда транзакция начинается, все команды, выполненные в соединении, считаются телом одной транзакции, пока не будет достигнут ее конец.

SQL Server поддерживает три вида определения транзакций:

- явное;

- автоматическое;

- подразумеваемое.

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

Для установки режима автоматического определения транзакций используется команда:

SET IMPLICIT_TRANSACTIONS OFF

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

SET IMPLICIT_TRANSACTIONS ON

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

Начало транзакции: в журнале транзакций фиксируются первоначальные значения изменяемых данных и момент начала транзакции;

BEGIN TRAN[SACTION]

[имя_транзакции |

@имя_переменной_транзакции

[WITH MARK [‘описание_транзакции’]]]

Конец транзакции: если в теле транзакции не было ошибок, то эта команда предписывает серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакций помечается, что изменения зафиксированы и транзакция завершена;

COMMIT [TRAN[SACTION]

[имя_транзакции |

@имя_переменной_транзакции]]

Создание внутри транзакции точки сохранения: СУБД сохраняет состояние БД в текущей точке и присваивает сохраненному состоянию имя точки сохранения;

SAVE TRAN[SACTION]

{имя_точки_сохранеия |

@имя_переменной_точки_сохранения}

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

ROLLBACK [TRAN[SACTION]

[имя_транзакции |

@имя_переменной_транзакции

| имя_точки_сохранения

|@имя_переменной_точки_сохранения]]

Функция @@TRANCOUNT возвращает количество активных транзакций.

Функция @@NESTLEVEL возвращает уровень вложенности транзакций.

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

Для создания вложенной транзакции пользователю не нужны какие-либо дополнительные команды. Он просто начинает новую транзакцию, не закрыв предыдущую. Завершение транзакции верхнего уровня откладывается до завершения вложенных транзакций. Если транзакция самого нижнего (вложенного) уровня завершена неудачно и отменена, то все транзакции верхнего уровня, включая транзакцию первого уровня, будут отменены. Кроме того, если несколько транзакций нижнего уровня были завершены успешно (но не зафиксированы), однако на среднем уровне (не самая верхняя транзакция) неудачно завершилась другая транзакция, то в соответствии с требованиями ACID произойдет откат всех транзакций всех уровней, включая успешно завершенные. Только когда все транзакции на всех уровнях завершены успешно, происходит фиксация всех сделанных изменений в результате успешного завершения транзакции верхнего уровня.

Каждая команда COMMIT TRANSACTION работает только с последней начатой транзакцией. При завершении вложенной транзакции команда COMMIT применяется к наиболее "глубокой" вложенной транзакции. Даже если в команде COMMIT TRANSACTION указано имя транзакции более высокого уровня, будет завершена транзакция, начатая последней.

Если команда ROLLBACK TRANSACTION используется на любом уровне вложенности без указания имени транзакции, то откатываются все вложенные транзакции, включая транзакцию самого высокого (верхнего) уровня. В команде ROLLBACK TRANSACTION разрешается указывать только имя самой верхней транзакции. Имена любых вложенных транзакций игнорируются, и попытка их указания приведет к ошибке. Таким образом, при откате транзакции любого уровня вложенности всегда происходит откат всех транзакций. Если же требуется откатить лишь часть транзакций, можно использовать команду SAVE TRANSACTION , с помощью которой создается точка сохранения.

Пример:

BEGIN TRAN

SAVE TRANSACTION point1

В точке point1 сохраняется первоначальное состояние таблицы «Товар»

DELETE FROM Товар WHERE КодТовара=2

SAVE TRANSACTION point2

В точке point2 сохраняется состояние таблицы «Товар» без товаров с кодом 2.

DELETE FROM Товар WHERE КодТовара=3

SAVE TRANSACTION point3

В точке point3 сохраняется состояние таблицы «Товар» без товаров с кодом 2 и с кодом 3.

DELETE FROM Товар WHERE КодТовара<>1

ROLLBACK TRANSACTION point3

Происходит возврат в состояние таблицы без товаров с кодами 2 и 3, отменяется последнее удаление.

SELECT * FROM Товар

Оператор SELECT покажет таблицу Товар без товаров с кодами 2 и 3.

ROLLBACK TRANSACTION point1

Происходит возврат в первоначальное состояние таблицы.

SELECT * FROM Товар

COMMIT

Первоначальное состояние сохраняется.

Цель инфологического проектирования базы данных, семантика предметной области, инфологическая модель предметной области, суть семантического подхода к инфологическому проектированию

Проектирование баз данных – процесс создания схемы базы данных и определения необходимых ограничений целостности.

Основные этапы проектирования баз данных

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

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

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

Основные задачи:

- Обеспечение хранения в БД всей необходимой информации.

- Обеспечение возможности получения данных по всем необходимым запросам.

- Сокращение избыточности и дублирования данных.

- Обеспечение целостности данных (правильности их содержания): исключение противоречий в содержании данных, исключение их потери и т.д.

Концептуальное (инфологическое) проектирование – построение семантической модели предметной области, то есть информационной модели наиболее высокого уровня абстракции. Термины «семантическая модель», «концептуальная модель» и «инфологическая модель» являются синонимами.

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

Инфологическая модель предметной области может быть описана моделью "сущность – связь" (моделью Чена), в основе которой лежит деление реального мира на отдельные различимые сущности, находящиеся в определенных связях друг с другом.

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

Сущность – любой различимый объект (объект, который мы можем отличить от другого), информацию о котором необходимо хранить в базе данных. Сущностями могут быть люди, места, самолеты, рейсы, вкус, цвет и т.д. Необходимо различать такие понятия, как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных личностей, предметов, событий или идей, выступающих как целое. Экземпляр сущности относится к конкретной вещи в наборе. Например, типом сущности может быть ГОРОД, а экземпляром – Москва, Киев и т.д.

Атрибут – поименованная характеристика сущности. Его наименование должно быть уникальным для конкретного типа сущности, но может быть одинаковым для различного типа сущностей (например, ЦВЕТ может быть определен для многих сущностей: СОБАКА, АВТОМОБИЛЬ, ДЫМ и т.д.). Атрибуты используются для определения того, какая информация должна быть собрана о сущности. Примерами атрибутов для сущности АВТОМОБИЛЬ являются ТИП, МАРКА, НОМЕРНОЙ ЗНАК, ЦВЕТ и т.д. Здесь также существует различие между типом и экземпляром. Тип атрибута ЦВЕТ имеет много экземпляров или значений: Красный, Синий, Банановый, Белая ночь и т.д., однако каждому экземпляру сущности присваивается только одно значение атрибута.

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

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

Связь – ассоциирование двух или более сущностей. Если бы назначением базы данных было только хранение отдельных, не связанных между собой данных, то ее структура могла бы быть очень простой. Однако одно из основных требований к организации базы данных – это обеспечение возможности отыскания одних сущностей по значениям других, для чего необходимо установить между ними определенные связи. А так как в реальных базах данных нередко содержатся сотни или даже тысячи сущностей, то теоретически между ними может быть установлено более миллиона связей. Наличие такого множества связей и определяет сложность инфологических моделей.

Семантическая модель (концептуальная, инфологическая модель) – модель предметной области, предназначенная для представления семантики предметной области на самом высоком уровне абстракции. Это означает, что устранена или минимизирована необходимость использовать понятия «низкого уровня», связанные со спецификой физического представления и хранения данных. Наиболее известным представителем класса семантических моделей является модель «сущность-связь» (ER-модель).

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

В реальном проектировании структуры базы данных применяется семантическое моделирование. Семантическое моделирование представляет собой моделирование структуры данных, опираясь на смысл этих данных. В качестве инструмента семантического моделирования используются различные варианты диаграмм сущность-связь (ER - Entity-Relationship).

Требования, предъявляемые к инфологической модели:

- Адекватное, отображение предметной области;

- Недопущение неоднозначной трактовки модели;

- Четкое определение моделируемой предметной области (конечность модели);

- Легкая расширяемость, обеспечивающая ввод новых данных без изменения ранее определенных, то же относят и к удалению данных;

- Возможность композиции и декомпозиции модели в связи с большой размерностью реальных инфологических моделей;

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

- Применимость языка спецификаций модели как при ручном, так и при автоматизированном проектировании информационных систем.

Компоненты инфологической модели:

- Описание объектов и связей между ними, называемой ER-моделью (расшифровывается как модель "Сущность-связь");

- Описание информационных потребностей пользователей;

- Алгоритмические связи атрибутов;

- Лингвистические отношения, обусловленные особенностями отображения предметной области в языковой среде;

- Ограничения целостности.

ER-диаграммы

ER- диаграмма является методом представления логической структуры базы данных в графическом виде для более простого и наглядного отображения основных компонентов конкретного проекта базы данных.

Основной концепцией ЕR-моделирования является тип сущности (entity type), который представляет множество объектов реального мира с одинаковыми свойствами. Тип сущности характеризуются независимым существованием и может быть объектом с физическим (или реальным) существованием или объектом с концептуальным (или абстрактным) существованием.

Каждый тип сущности идентифицируется именем и списком свойств. База данных обычно содержит много разных типов сущностей.

Типы сущностей можно классифицировать как сильные и слабые:

- слабый тип - тип сущности, существование которого зависит от какого-то другого типа сущности;

- сильный тип сущности - Тип сущности, существование которого не зависит от какого-то другого типа сущности.

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

Экземпляр сущности - это конкретный представитель данной сущности.

Атрибут – свойство типа сущности или типа связи. Отдельные свойства сущностей называются атрибутами.

Атрибуты сущности содержат значения, описывающие каждую сущность. Значения атрибутов представляют основную часть сведений, сохраняемых в базе данных. Связь, которая соединяет две сущности, также может иметь атрибуты, аналогичные атрибутам типа сущности. Свойства отображаются на ER-диаграмме в виде эллипсов, содержащих имена этих свойств. Эллипсы соединяются с соответствующей сущностью (или связью) сплошной линией. Контур эллипса изображается штриховой или пунктирной линией, если свойство производное, и двойной линией, если свойство многозначное. Если свойство составное, то составляющие его свойства показаны в виде других эллипсов, соединенных с эллипсом составного свойства с помощью дополнительных сплошных линий. Имена ключевых свойств обычно подчеркиваются, а множества значений не отображаются совсем.

Домен атрибута - набор значений, которые могут быть присвоены атрибуту. Например, количество комнат в объекте недвижимости может варьироваться от одной до пятнадцати для каждого экземпляра этой сущности. Следовательно, набор допустимых значений для атрибута "количество комнат" (Rooms) сущности Property_for_Rent можно определить как набор целых чисел от 1 до 15. Различные атрибуты могут совместно использовать один и тот же домен. Например, атрибуты адреса (Address) сотрудников компании (сущность Staff) и владельцев объектов недвижимости (сущность Owner) могут совместно использовать один и тот же домен всех возможных адресов. Домены также могут представлять собой комбинацию, состоящую из нескольких других доменов.

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

Многозначный атрибут – атрибут, который содержит несколько значений для одной сущности.

Производный атрибут – Атрибут, который представляет значение, производное от значения связанного с ним атрибута или некоторого множества атрибутов, принадлежащих некоторому (не обязательно данному) типу сущности.

Некоторые атрибуты могут быть связаны с определенной сущностью. Например, «возраст» сотрудника является величиной, производной от его «даты рождения», и поэтому атрибуты «возраст» и «дата рождения» являются связанными. Причем атрибут «возраст» является производным атрибутом, значение которого вычисляется на основании значения «даты рождения».

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

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

Первичный ключ – потенциальный ключ, который выбран в качестве первичного ключа.

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

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

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

Связь – это некоторая ассоциация между двумя сущностями. Одна сущность может быть связана с другой сущностью или сама с собою.

Каждая связь изображается в виде ромбика с указанным на нем именем связи. Ромбик имеет двойной контур, если связь соединяет слабую сущность с сильной сущностью, от которой эта слабая сущность зависит.

Степень связи – количество сущностей, которые охвачены данной связью.

Связь со степенью два называется бинарной (binary).

Связь со степенью три называется тернарной (ternary).

Связь со степенью четыре называется кватернарной (quaternary).

Рекурсивная связь – связь, в которой одни и те же сущности участвуют несколько раз и в разных ролях.

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

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

Атрибуты, описанные в разделе, могут также принадлежать связям. Рассмотрим в качестве примера связь views между сущностями Client и Prop-erty_for_Rent. Допустим, что требуется фиксировать дату просмотра объекта недвижимости клиентом, а также записывать комментарии, сделанные клиентом в ходе осмотра этой недвижимости.

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

Показатель кардинальности – описывает количество возможных связей для каждой из сущностей-участниц. Наиболее распространенными являются бинарные связи с показателями кардинальности "один к одному" (1:1), "один ко многим" (l:М) и "многие ко многим" (M:N).

Связь типа один-к-одному означает, что один экземпляр первой сущности (левой) связан с одним экземпляром второй сущности (правой). Связь один-к-одному чаще всего свидетельствует о том, что на самом деле мы имеем всего одну сущность, неправильно разделенную на две.

Связь типа один-ко-многим означает, что один экземпляр первой сущности (левой) связан с несколькими экземплярами второй сущности (правой). Это наиболее часто используемый тип связи. Левая сущность (со стороны "один") называется родительской, правая (со стороны "много") - дочерней.

Связь типа много-ко-многим означает, что каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, и каждый экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности. Тип связи много-ко-многим является временным типом связи, допустимым на ранних этапах разработки модели. В дальнейшем этот тип связи должен быть заменен двумя связями типа один-ко-многим путем создания промежуточной сущности.

Степень участия – определяет, зависит ли существование некоторой сущности от участия в связи некоторой другой сущности.

Существует два варианта участия сущности в связи: полное (total) и частичное (partial). Степень участия является полной, если для существования некоторой сущности требуется существование другой сущности, связанной с ней определенной связью. В противном случае степень участия является частичной. Полную степень участия иногда называют обязательным участием (mandatory), а частичную – необязательным (optional).

Участники связи с полным участием соединяется со значком связи двойной линией, а участники связи с Частичным участием - одинарной линией.

EER-диаграммы

ЕЕR-модель включает все концепции ЕR-модели плюс дополнительны концепции специализации/генерализациии категоризации. Концепции специализации/генерализациии категоризации связаны с близкими им понятиями типов сущностей, называемых суперклассами, подклассами, а также с процессом наследования атрибутов.