8. Глобальные переменные. Отладка хранимых процедур

В SQL Server предусмотрено несколько глобальных пере­менных, которые можно использовать при написании хранимых процедур. Глобальная переменная имеет в начале имени @@:

1. @@ERROR – используется для проверки кода ошибки оператора, выполняемого SQL Server. Эта переменная содержит 0, если оператор выполняется кор­ректно. Для каждого сеанса работы создается отдельная копия переменной @@ERROR, поэтому её значение не может быть изменено операторами, выполняемыми в дру­гих сеансах.

2. @@FETCH_STATUS – используются для анализа результатов выполнения команды FETCH, для каждого сеанса работы создается отдельная копия этой переменной.

3. @@ROWCOUNT – содержит число строк, подвергнувшихся воздействию последнего оператора SQL, для каждого сеанса создается отдельная копия переменной.

4. @@SERVERNAME – определяет имя ло­кального SQL Server.

5. @@TRANCOUNT – содержит число теку­щих активных транзакций для текущего пользователя.

6. @@VERSION – содержит номер версии SQL Server.

Отладить хранимую процедуру можно:

1. средствами Transact SQL;

2. другими средствами отладки.

При отладка средствами Transact SQL используют:

1. оператор PRINT – служит для отображения содержимого только строковых переменных. Помогает ус­тановить, выполнялась ли определенная строка кода

2. оператор SELECT – используется для ото­бражения содержимого переменных любого типа.

Другие средства отладки:

1. SQL Server Debugger (отладчик SQL Server) – входит в состав Microsoft Visual C++ 6.0 Enterprise Edi­tion и Microsoft Visual Basic 6.0. Среда отладки схожа со средой отладчика MS VB: точки останова, отображение нужных переменных и т.д.

2. Трассировка средствами ODBC. Чтобы акти­визировать это средство необходимо открыть панель управления Windows и щелкнуть на элементе ODBC Data Source (32 bit), затем вкладка Tracing (трассировка) и кнопка Start Tracing Now (начать трассировку).

3. Утилита SQL Server Profiler – выполняет протоколирование активности SQL Server, позволяет от­следить параметры, передаваемые в хранимую процедуру.

Тема X. Транзакции и блокировки

1. Определение транзакции. Ограничения для транзакций. Уровни изоляции транзакций

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

Следует всегда стремиться к минимизации количе­ства блокировок, этим уменьшается вероятность того, что пользова­тели будут мешать друг другу.

Основные требования ACID (Atomicity, Consistency, Iso­lation, Durability — Атомарность, Постоянство, Изолирован­ность, Устой­чивость) к транзакциям не зависят от того, чем они вызваны — непосред­ственно системой SQL Server или косвенно вашим приложением.

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

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

Транзакции должны быть изолированными, т.е. не должны влиять на другие транзакции и зависеть от них. Зависи­мость от других транзакций вызывает тупиковую ситуации (deadlocks), что ведет к откату (rollback) транзакций.

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

Транзакция (transaction) — это логический блок опера­ций, которые меняются на сервере как единое целое. Этот блок может содержать одну или несколько инструкций SQL, которые должны быть определены в качестве части транзакции.

С помощью ISQL можно выполнять транзакции, состоя­щие из одной инструкции. Для этого вводится инструкция, и за­тем команда go. Например, приведенная ниже инструкции воз­вращает определенный пользователем список таблиц текущей базы данных:

SELECT *

FROM SYSOBJECTS

WHERE type ='U' /*таблицы, определенные пользователем */

ORDER BY name

Транзакции из нескольких инструкций объединяют две или более инструкций SQL, которые посылаются для обработки на сервер. Ниже показан пример транзакции из нескольких ин­струкций:

CREATE TABLE Table_A(

x smallint NULL,

y smallint NULL)

GO

CREATE TABLE Table B(

z smallint NULL

GO

BEGIN TRAN

UPDATE Table_A

SET x= x + 1

WHERE y=100

UPDATE Table_B

SETz = z+ 1

IF @@ROWCOUNT = 0 OR @@ERROR !=0

/* He была обновлена ни одна строка*/

BEGIN

ROLLBACK TRAN

PRINT 'Произошла ошибка, не была обновлена ни одна строка'

RETURN

END

COMMIT TRAN

Имеется возможность так настроить SQL Server, чтобы транзакции запускались автоматически при выполнении опреде­ленных операций. Неявное задание транзакций может быть по­лезно при разработке и тестировании. Эту опцию можно устано­вить с помощью инструкции SET:

SET IMPLICIT TRANSACTIONS ON | OFF

После установки в SQL Server опции IMPLICIT TRANS­ACTIONS следующие операции приведут к автоматическому выполнению транзакций:

ALTER TABLE DROP INSERT SELECT

CREATE FETCH OPEN TRUNCATE TABLE

DELETE GRANT REVOKE UPDATE

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

Внутри транзакции запрещены следующие действия:

• Изменение базы данных.

• Создание базы данных.

• Создание индекса.

• Создание процедуры.

• Создание таблицы.

• Создание вида.

• Инициализация диска.

• Удаление (dropping).

• Запись транзакции в дамп.

• Предоставление прав (granting).

• Загрузка базы данных.

• Загрузка транзакции.

• Переконфигурация.

• Лишение прав (revoking).

• Выполнение инструкции SELECT INTO.

• Выполнение инструкции TRUNCATE TABLE.

• Обновление статистик.

Выполнение процедуры sp_DBOPTION и других проце­дур модифицирующих основную базу данных

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

Уровень 0: No trashing of data (запрещение одновремен­ной модификации данных). Двум процессам запрещается изме­нять одни и те же данные.

Уровень 1: No dirty reads (запрещение "грязного" чте­ния). Запрещается считывание данных, которые в этот момент моди­фицируются.

Уровень 2: No nonrepeatable reads (запрещение неповто­ряемого чтения). В промежутках между чтениями данных внутри одной транзакции запрещается их обновление в других транзакциях, т е. запрещена операция UPDATE.

Уровень 3: No phantoms (запрещение появления фанто­мов). В добавление к требованиям уровня 2 запрещаются также вставка и удаление записей, т.е. помимо UPDATE запрещаются операции INSERT и DELETE.

В SQL Server 2000 поддерживаются все четыре уровня изоляции транзакций.