Alex V. Malinin

Вызов несуществующих процедур и обращение к несуществующим таблицам.

Известно, что MS SQL Server осуществляет компиляцию сохраненных процедур в момент первого выполнения процедуры. В документации на MS SQL Server можно прочитать:
 

Referencing Objects

SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when it is executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at execution time because referenced objects do not exist.

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

Это не так.

MS SQL Server работает как интерпретатор, то есть осуществляет анализ не всей процедуры, а только тех операторов, которые будут выполнены при данном вызове. Eсли же в процедуре есть условные операторы, то семантические ошибки в операторах, которые не были исполнены, выявлены не будут.

Например:

Create procedure TestSemantic(@Flag integer)  

Begin  

…  

If @Flag = 0  

Begin  

Delete from UndefinedTable  

Exec UndefinedProc  

End  

…  

end  

Если объекты UndefinedTable и UndefinedProc не существуют, то MS SQL Server проинформирует об этом только при выполнении этой процедуры с параметром @Flag = 0

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

Коллизии параметров.

Не лучше обстоят дела с анализом правильности параметров, передаваемых сохраненной процедуре при вызове. Можно опустить все параметры, передать лишние, можно опустить OUT после выходного параметра. Если параметры опущены, или есть лишние параметры, то это будет обнаружено на этапе выполнения. Если же опущен модификатор OUT, то никакого уведомления об этом не будет. Это одна из самых труднообнаружимых ошибок. Ниже приведены примеры подобных вызовов:

exec CalculateProfit @Account_ID, @Profit OUT –- correct  

exec CalculateProfit @Profit OUT -- missing parameter  

exec CalculateProfit @Client_ID, @Account_ID, @Profit OUT –- extra parameter  

exec CalculateProfit @Account_ID, @Profit -- missing OUT  

Пропуск знака @ перед именем переменной.

Следующая очень опасная ситуация связана с пропуском знака @ перед именем переменной в операторе SELECT. Дело в том, что такая конструкция является синтаксически правильной и полезной для целей отладки и создания процедур, которые возвращают набор записей. Например, для целей отладки можно вставить в текст процедуры оператор

select Profit = @Profit  

В результате на системную консоль будет выведено значение переменной @Profit.

Совсем другой результат будет в случае пропуска знака @ в вышеприведенной процедуре расчета прибыли:

Create procedure CalculateProfit(@Account_ID integer, @Profit numeric(28, 2) OUT)  

As  

Begin  

…  

select Profit = @TotalIncom - @TotalTaxes  

-- ^ @ sign missing  

End  

В данном случае пропуск знака @ приведет к тому, что значение выходного параметра (@Profit) останется неопределенным.

Коллизии типов данных и типов переменных

В документации на MS SQL Server можно прочитать следующее:

In the resolution stage, Microsoft® SQL Server™ also performs other validation activities (for example, checking the compatibility of a column data type with variables).

Оставим на совести MS данное высказывание и сделаем контрольный пример:

Пусть в таблице T есть поле F varchar(20). В нижеприведенной процедуре для чтения данного поля ошибочно используется переменная @F varchar(10).

…  

declare @F varchar(10)  

select @F = F from T where …  

select Result = @F  

…

Если значение данного поля в таблице имеет длину более 10 символов (например номер банковского счета), то при выполнении оператора

select @F = F  

в переменной @F будет сохранено урезанное до 10 символов значение данного поля.

MS SQL Server не обнаруживает такие коллизии.

Рекурсии и циклы.

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

Create procedure P1

As  

…  

update T1 set …  

…  

Create trigger Tr1 on T1 for update  

As  

…  

delete from T2 where …  

…  

Create trigger Tr2 on T2 for delete  

As  

…  

exec P1  

…  

В сложных системах подобные цепочки могут быть длинными и труднообнаружимыми. При возникновении такого цикла MS SQL Server выдаст сообщение вида:

Server: Msg 217, Level 16, State 1, Procedure P3, Line 2  

Maximum stored procedure nesting level exceeded (limit 32).  

Из этого сообщения можно узнать только об одной процедуре в цепочке.

Выявить все возможные цепочки путем тестирования вряд ли возможно.

Deadlocks.

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

Create procedure P1 as  

Begin tran  

…  

Update T1 set …  

…  

Update T2 set …  

…  

Commit  

Create procedure P2 as  

Begin tran  

…  

Update T2 set …  

…  

Update T1 set …  

…  

Commit  

Если эти процедуры будут вызваны одновременно (или почти одновременно), то на первом этапе процедура P1 захватит таблицу T1, а процедура P2 захватит таблицу T2. На втором этапе процедура попытается захватить таблицу T2, но она уже захвачена процедурой P2. P2 в свою очередь, попытается захватить таблицу T1, но она уже захвачена процедурой P1. В этой ситуации MS SQL Server выбирает один процесс жертвой конфликта и прекращает его выполнение путем отката транзакции.

Даже две копии одной и той же процедуры могут вызвать Deadlock. Это может произойти по причине того, что в одной процедуре может быть более одной транзакции и по причине наличия условных модификаций внутри одной транзакции. Например:

Create procedure P1 as  

Begin tran  

…  

if … Update T1 set …  

…  

if … Update T2 set …  

…  

if … Update T1 set …  

Commit  

В первой копии такой процедуры могут выполниться первый и второй update, а во второй копии – второй и третий операторы update.

Нахождение всех ситуаций, в которых возможен deadlock путем тестирования, вряд ли возможно. К сожалению, MS SQL Server не предоставляет возможность семантического анализа и поиска подобных ситуаций.

ПРИМЕЧАНИЕ: Для гарантированного исключения deadlocks можно рекомендовать следующий простой прием: в начале каждой транзакции захватывать некоторую специальную таблицу. Тогда все конкурирующие транзакции будут вынуждены ожидать окончания транзакции, которая стартовала первой. Захват таблицы можно осуществить с помощью оператора SELECT:

Select @var1 = Field2 from T1 where Field1 = 1 with (UPDLOCK)  

Выбор несуществующих записей

Пусть имеется таблица T1 со следующим набором записей

T1:  

Field1 Field2  

1 10  

2 NULL  

Рассмотрим процедуру P1  

Create procedure P1 (@Param1 integer)  

Declare  

@Var1 integer  

begin  

Select @Var1 = 0  

Select @Var1 = Field2 from T1 where Field1 = @Param1  

Select Var1 = @Var1  

end  

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

@Param1 @Var1  

1 10  

2 NULL  

3 0  

Наиболее важен третий результат. Если оператор SELECT не возвращает ни одной строки, то значение переменной @Var1 остается неизменным!

Эту особенность MS SQL Server чрезвычайно важно учитывать при программировании курсоров и циклов. Если цикл содержит оператор SELECT и на какой-либо итерации возвращает пустой набор записей, то значение переменной не изменяется и не становится равным NULL, что может служить причиной ошибки в вычислениях. При организации циклов с оператором SELECT следует обязательно инициализировать значения переменных, используемых в операторе SELECT, например:

…  

while …  

begin  

select @Var1 = NULL  

select @Var1 = Field1 from T1 where …  

if @Var1 is NULL …  

…  

end  

Использование неинициализированных переменных

MS SQL Server не сообщает об использовании неинициализированных переменных. Процедура, представленная ниже, будет сохранена и выполнена без всяких сообщений и предупреждений.

Create procedure P1  

Declare  

@V1 integer,  

@R1 integer  

begin  

if @V1 > 5  

…  

select @R1 = @V1*10  

…  

select @R1 = Field2 from T1 where Field1 = @Var1  

end 
   
    

Документация содержит описания только самых простых ситуаций

В документации говорится о том, что значение системной переменной @@TranCount увеличивается на единицу после выполнения каждого оператора BEGIN TRANSACTION, уменьшается на единицу при выполнении оператора COMMIT, и СБРАСЫВАЕТСЯ в нуль при выполнении каждого оператора ROLLBACK. В той же документации говорится, что вложенные транзакции игнорируются и если даже вложенная транзакция завершилась оператором COMMIT, то внешняя транзакция может закончится оператором ROLLBACK, что приведет к откату вложенной транзакции. Все это верно, но только для описанного случая – когда вложенная транзакция закончена оператором COMMIT. Если же вложенная транзакция закончена оператором ROLLBACK, то отменяются все операции, начиная с оператора BEGIN TRANSACTION самой внешней транзакции. При этом системная переменная @@TranCount устанавливается в нуль. Дальнейшие операторы выполняются вне транзакции, хотя формально они находятся до оператора COMMIT/ROLLBACK внешней транзакции. При выполнении оператора окончания внешней транзакции (все равно COMMIT или ROLLBACK) MS SQL Server выдает сообщение о том, что для данного оператора завершения транзакции нет соответствующего оператора, открывающего транзакцию. Для корректной отработки таких ситуаций MS рекомендует перед каждым оператором завершения внешней транзакции вставлять проверку на значение переменной @@TranCount:

…  

begin tran -- outer transaction: @@TranCount = 1  

…  

begin tran -- inner transaction: @@TranCount = 2  

…  

if …  

commit -- commit of inner transaction @@TranCount = 1  

else  

rollback -- rollback of inner transaction @@TranCount = 0  

…  

update T1 set … -- if nested transaction was rolled back  

delete from T2 … -- those statements  

insert into T3 … -- will be committed anyway  

…  

if @@TranCount > 0 -- inner transaction have been committed  

begin  

if …  

commit -- commit of outer transaction  

else  

rollback  

end  

Этого недостаточно. Для корректной откатки внешней транзакции (в случае откатки вложенной транзакции) следует выполнять все операторы между операторами окончания вложенной и внешней транзакций только при условии подтверждения вложенной транзакции.

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

Автору пришлось написать программу SQL Semantic Analyzer, которая успешно обнаруживает обращения к несуществующим объектам, коллизии параметров, пропуск знака @ перед именами переменных, циклы, deadlocks и служит навигационным средством, позволяя определить какие именно процедуры модифицируют данное поле данной таблицы, какие процедуры вызывают данную процедуру, и т.п. Программа доступна с www.mssqlproducts.com

Автор будет благодарен всем, кто дополнит приведенный выше список скрытых опасностей MS SQL Server.

• SQL Server Version 7.0: Краткий обзор характеристик
• Microsoft SQL Server 7.0 – Механизм хранения данных.
• Развертывание систем Windows 2000 Professional
• Настройка SQL Server 6.5 на обработку запросов c высокой производительностью

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Microsoft
Отправить ссылку на страницу по e-mail