Транзакции

Лабораторная работа 10 — Транзакции и блокировки

Варианты заданий

Содержание отчета

Выполнение лабораторной работы

Анализ использования индексов

Решение об использовании индексов принимает оптимизатор запросов СУБД, проанализировать используется ли индекс и каким образом происходит его сканирование можно из планов выполнения запросов. Один из способов просмотра планов выполнения запросов — использование директивы:

set showplan_text { on | off }

go

или более подробно с помощью директивы:

set showplan_all { on | off }

go

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

Для графического отображения плана выполнения запроса используются команды меню Management Studio Запрос\Показать предполагаемый план выполнения и Запрос\Включить действительный план выполнения. В планах выполнения запросов отображается довольно много пиктограмм (http://msdn.microsoft.com/ru-ru/library/ms175913(SQL.105).aspx) и видов сообщений (http://msdn.microsoft.com/ru-ru/library/ms191158(SQL.105).aspx), об использовании индексов говорят сообщения { clustered | nonclustered } index { seek | scan }, seek соответствует префиксному сканированию, а scan — сплошному.

Повлиять на решения, принимаемые планировщиком запросов относительно порядка соединения таблиц, использования индексов, алгоритмов выполнения соединений и др. можно с помощью подсказок — hints (http://msdn.microsoft.com/ru-ru/library/ms173815%28v=sql.105%29.aspx) (http://msdn.microsoft.com/ru-ru/library/ms173815%28v=sql.105%29.aspx).

Произвести генерацию и вставку тестовых данных в БД, выполнить запросы из ЛР № 4..6, зафиксировать планы и время их выполнения, создать систему индексов для ускорения выполнения запросов, повторно выполнить запросы из ЛР № 4..6, зафиксировать планы и время их выполнения.

Содержание отчета:

— операторы для создания индексов;

— планы и время выполнения запросов до и после индексации, пояснение произошедших изменений.

Варианты заданий приведены в ПРИЛОЖЕНИИ.

Транзакция — совокупность действий в БД, которая должна быть или полностью успешно выполнена или полностью отклонена. Типовым примером транзакции является перевод средств в банковской системе:

(i) сумма списывается со счета A;

(ii) средства зачисляются на счет B.

Если между (i) и (ii) происходит программно-аппаратный сбой, БД оказывается в несогласованном состоянии. Механизм транзакций гарантирует, что либо оба действия будут успешно выполнены, и БД окажется в согласованном состоянии, либо не будет выполнено ни одно из них, и БД окажется в согласованном состоянии, в котором она находилась до начала транзакции. Говоря о транзакциях, обычно отмечают ряд их свойств сокращенно обозначаемых как ACID (http://msdn.microsoft.com/ru-ru/library/ms190612%28SQL.105%29.aspx).

По умолчанию SQL-сервера рассматривают каждый оператор insert, update, delete, select и др. как отдельную транзакцию. Для оформления в виде транзакции группы операторов используются директивы:

(i) begin { tran | transaction } [ <имя транзакции> ] — начинает новую транзакцию (http://msdn.microsoft.com/ru-ru/library/ms188929(SQL.105).aspx);

(ii) save { tran | transaction } <имя точки сохранения> — сохраняет текущее состояние транзакции (http://msdn.microsoft.com/ru-ru/library/ms188378(SQL.105).aspx);

(iii) commit { tran | transaction } [ <имя транзакции > ] — фиксирует в БД изменения, произведенные транзакцией, транзакция завершается успешно (http://msdn.microsoft.com/ru-ru/library/ms190295(SQL.105).aspx);

(iv) rollback { tran | transaction } [ { <имя транзакции > | <имя точки сохранения> } ] — отменяет изменения, произведенные транзакцией, либо до первого begin transaction (транзакция завершается с откатом), либо до указанной точки сохранения (http://msdn.microsoft.com/ru-ru/library/ms181299(SQL.105).aspx).

Пусть в БД существует таблица Счет:

create table Счет (

Номер int primary key,

ФИО varchar(50) not null,

Сумма money not null)

go

оформим с помощью транзакций перевод денег с одного счета на другой:

begin tran

update Счет set Сумма = Сумма + 100 where Номер = 33

if (select Сумма from Счет where Номер = 44) >= 100

begin

update Счет set Сумма = Сумма - 100 where Номер = 44

commit tran

end

else

rollback tran

go

Таким образом, если на счете с номером 44 недостаточно средств, транзакция будет отклонена.

Транзакции могут использоваться в теле ХП и Т, рекомендации по оформлению транзакций приведены в (http://msdn.microsoft.com/ru-ru/library/ms187844(v=sql.105).aspx) и (http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1550/html/sqlug/X16153.htm).

Транзакция может находиться в одном из четырех возможных состояний, текущее состояние хранится в глобальной системной переменной @@transtate (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1550/html/sqlug/X27470.htm), в MS SQL не поддерживается.

Транзакции могут быть вложенными, текущий уровень вложенности содержится в глобальной системной переменной @@trancount (http://msdn.microsoft.com/ru-ru/library/ms189336(v=sql.105).aspx).

SQL-сервера могут поддерживать два режима начала транзакций — неявные транзакции (транзакция начинается автоматически при выполнении очередного оператора DML) и явные(транзакция начинается после begin transaction), переключение режимов осуществляет директива set implicit_transactions { on | off } (http://msdn.microsoft.com/ru-ru/library/ms190230%28v=sql.105%29.aspx)[17].

Так как транзакции устанавливают и удерживают блокировки, при их разработке следует придерживаться ряда рекомендаций (http://msdn.microsoft.com/ru-ru/library/ms187484%28v=sql.105%29.aspx).

Дата добавления: 2015-05-09