Изучение средств обеспечения целостности данных в SQL Server 2005. Транзакции и блокировки (Лабораторная работа № 5)

Лабораторная работа №5

Изучение средств обеспечения целостности данных в SQL Server 2005. Транзакции и блокировки

1. Цель работы

Изучить основные способы управления параллельностью работы транзакций в Microsoft SQL Server 2005, уровни изолированности транзакций и блокирования данных.

2. Основные сведения из теории

2.1. Использование транзакций

Транзакция – это действие или серия действий, выполняемых одним пользователем или прикладной программой и осуществляющих чтение или изменение данных в БД. Обработка транзакций, состояние данных во время ее работы и после завершения должны соответствовать требованиям ACID (Atomicity, Consistency, Isolation and Durability):

·  Атомарность(Atomicity). Вся совокупность операций обработки данных, выполняемых в транзакции, рассматривается как единый минимальный блок. При условии, что все операции завершились удачно, их результаты фиксируются в БД после завершения транзакции. Иначе данные восстанавливаются в том состоянии, которое было до начала транзакции. Говорят, что в первом случае происходит фиксация транзакции, а во втором – откат транзакции.

·  Согласованность(Consistency). Каждая транзакция должна переводить БД из одного согласованного состояния в другое согласованное состояние. В частности, к моменту фиксации все данные должны удовлетворять существующим в БД ограничениям целостности.

·  Изолированность(Isolation). Если одна транзакция выполняет изменение данных в БД, то результат этих изменений не должен сказываться на работе других транзакций до тех пор, пока данная транзакция не будет зафиксирована или отменена.

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

Самый простой способ запустить транзакцию в SQL Server 2005 (и других реляционных СУБД) – написать и выполнить SQL-скрипт. По умолчанию в SQL Server 2005 каждая отдельная команда Transact-SQL воспринимается как отдельная транзакция. Ее фиксация и откат выполняются автоматически, поэтому вмешательства пользователя не требуется.

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

1. begin tran

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

2. commit

Завершает транзакцию, фиксируя все внесенные изменения.

3. rollback

Выполняет откат транзакции, т. е. завершает ее, восстанавливая БД в исходном согласованном состоянии (в котором она находилась до начала транзакции).

4. save tran

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

Упрощенный синтаксис команды savetran имеет вид:

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

Чтобы вернуть транзакцию в состояние, которое было на момент создания точки сохранения, используется команда rollbacktran с указанием имени точки сохранения.

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

1. Транзакция выполняет вставку новых и модификацию существующих строк в таблице a_Table.

-- Начало транзакции

begin tran

      insert into a_Table (a, b, c) values (5, 5, 5)

      …

      -- Фрагмент кода, в котором устанавливается значение переменной @x

      …

-- Если @x равен 0, выполняется откат транзакции,

-- в противном случае выполнение продолжается

if @x = 0

                        rollback

update a_Table set a = 5 where a <> 5

…

-- Фиксация транзакции

commit

Если переменная @x примет значение 0, то вставка строки (первый вызов после begintran) будет отменена, следующий же оператор update не будет выполняться совсем, поскольку откат – это завершение транзакции. Если же в @x попадет значение, отличное от нуля, транзакция будет выполняться, пока не встретится команда commit, которая зафиксирует и вставку строки, и модификацию.

2. Пример использования точки сохранения

begin tran

      insert into a_Table values (5, 5, 5)

save tran point_one

insert into a_Table values (0, 0, 0)

rollback tran point_one

commit tran

В результате строка (5, 5, 5) будет добавлена в таблицу, а строка (0, 0, 0) – нет.

В Transact-SQL имеется набор команд, которые запрещено использовать внутри явно определенных транзакций. К ним относятся команды создания, настройки и удаления баз данных, резервного копирования БД и журналов транзакций, их восстановления из резервных копий и некоторые другие.

2.2.  Блокировки

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

1. Последнее изменение.

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

2. Чтение после записи («грязное» чтение).