Вызов несуществующих процедур и обращение к несуществующим таблицам
Известно, что 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 = 0BeginDelete from UndefinedTableExec UndefinedProcEnd…end
Если объекты UndefinedTable и UndefinedProc не существуют, то MS SQL Server проинформирует об этом только при выполнении этой процедуры с параметром @Flag = 0
Сложные процедуры могут содержать множество условных вызовов, и тестирование не всегда обеспечивает перебор всех возможных вариантов ветвления. Всегда останется вероятность того, что в какой-то ветке условного оператора есть вызов несуществующей процедуры или обращение к несуществующей таблице. Создание набора тестов, которые обеспечивали бы выполнение всех веток всех условных операторов, задача трудно выполнимая. Цена (время и деньги) создания и прогона такого набора тестовых примеров будет меньше, чем цена выполнения семантического анализа текстов всех процедур на предмет выявления подобных ситуаций.
Коллизии параметров
Не лучше обстоят дела с анализом правильности параметров, передаваемых сохраненной процедуре при вызове. Можно опустить все параметры, передать лишние, можно опустить OUT после выходного параметра. Если параметры опущены, или есть лишние параметры, то это будет обнаружено на этапе выполнения. Если же опущен модификатор OUT, то никакого уведомления об этом не будет. Это одна из самых труднообнаружимых ошибок. Ниже приведены примеры подобных вызовов:
exec CalculateProfit @Account_ID, @Profit OUT -- correctexec CalculateProfit @Profit OUT -- missing parameterexec CalculateProfit @Client_ID, @Account_ID, @Profit OUT -- extra parameterexec CalculateProfit @Account_ID, @Profit -- missing OUT
Пропуск знака @ перед именем переменной
Следующая очень опасная ситуация связана с пропуском знака @ перед именем переменной в операторе SELECT. Дело в том, что такая конструкция является синтаксически правильной и полезной для целей отладки и создания процедур, которые возвращают набор записей. Например, для целей отладки можно вставить в текст процедуры оператор
select Profit = @Profit
В результате на системную консоль будет выведено значение переменной @Profit.
Совсем другой результат будет в случае пропуска знака @ в вышеприведенной процедуре расчета прибыли:
Create procedure CalculateProfit(@Account_ID integer, @Profit numeric(28, 2) OUT)AsBegin…select Profit = @TotalIncom - @TotalTaxes-- ^ @ sign missingEnd
В данном случае пропуск знака @ приведет к тому, что значение выходного параметра (@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 updateAs…delete from T2 where ……Create trigger Tr2 on T2 for deleteAs…exec P1…
В сложных системах подобные цепочки могут быть длинными и труднообнаружимыми. При возникновении такого цикла MS SQL Server выдаст сообщение вида:
Server: Msg 217, Level 16, State 1, Procedure P3, Line 2Maximum stored procedure nesting level exceeded (limit 32).
Из этого сообщения можно узнать только об одной процедуре в цепочке.
Выявить все возможные цепочки путем тестирования вряд ли возможно.
Deadlocks
По определению Deadlock - это ситуация, когда две транзакции накладывают Lock на некоторые таблицы, а потом пытаются наложить Lock на таблицы, уже захваченные конкурирующей транзакцией. Например:
Create procedure P1 asBegin tran…Update T1 set ……Update T2 set ……CommitCreate procedure P2 asBegin tran…Update T2 set ……Update T1 set ……Commit
Если эти процедуры будут вызваны одновременно (или почти одновременно), то на первом этапе процедура P1 захватит таблицу T1, а процедура P2 захватит таблицу T2. На втором этапе процедура попытается захватить таблицу T2, но она уже захвачена процедурой P2. P2 в свою очередь, попытается захватить таблицу T1, но она уже захвачена процедурой P1. В этой ситуации MS SQL Server выбирает один процесс жертвой конфликта и прекращает его выполнение путем отката транзакции.
Даже две копии одной и той же процедуры могут вызвать Deadlock. Это может произойти по причине того, что в одной процедуре может быть более одной транзакции и по причине наличия условных модификаций внутри одной транзакции. Например:
Create procedure P1 asBegin 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 Field21 102 NULLРассмотрим процедуру P1Create procedure P1 (@Param1 integer)Declare@Var1 integerbeginSelect @Var1 = 0Select @Var1 = Field2 from T1 where Field1 = @Param1Select Var1 = @Var1end
Выполнение данной процедуры с разными значениями входного параметра даст разные результаты:
@Param1 @Var11 102 NULL3 0
Наиболее важен третий результат. Если оператор SELECT не возвращает ни одной строки, то значение переменной @Var1 остается неизменным!
Эту особенность MS SQL Server чрезвычайно важно учитывать при программировании курсоров и циклов. Если цикл содержит оператор SELECT и на какой-либо итерации возвращает пустой набор записей, то значение переменной не изменяется и не становится равным NULL, что может служить причиной ошибки в вычислениях. При организации циклов с оператором SELECT следует обязательно инициализировать значения переменных, используемых в операторе SELECT, например:
…while …beginselect @Var1 = NULLselect @Var1 = Field1 from T1 where …if @Var1 is NULL ……end
Использование неинициализированных переменных
MS SQL Server не сообщает об использовании неинициализированных переменных. Процедура, представленная ниже, будет сохранена и выполнена без всяких сообщений и предупреждений.
Create procedure P1Declare@V1 integer,@R1 integerbeginif @V1 > 5…select @R1 = @V1*10…select @R1 = Field2 from T1 where Field1 = @Var1end
Документация содержит описания только самых простых ситуаций
В документации говорится о том, что значение системной переменной @@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 = 1elserollback -- rollback of inner transaction @@TranCount = 0…update T1 set … -- if nested transaction was rolled backdelete from T2 … -- those statementsinsert into T3 … -- will be committed anyway…if @@TranCount > 0 -- inner transaction have been committedbeginif …commit -- commit of outer transactionelserollbackend
Этого недостаточно. Для корректной откатки внешней транзакции (в случае откатки вложенной транзакции) следует выполнять все операторы между операторами окончания вложенной и внешней транзакций только при условии подтверждения вложенной транзакции.
Для обнаружения большинства из описанных ситуаций достаточно провести семантический анализ текстов всех процедур и триггеров Вашей базы данных.
Автору пришлось написать программу SQL Semantic Analyzer, которая успешно обнаруживает обращения к несуществующим объектам, коллизии параметров, пропуск знака @ перед именами переменных, циклы, deadlocks и служит навигационным средством, позволяя определить какие именно процедуры модифицируют данное поле данной таблицы, какие процедуры вызывают данную процедуру, и т.п. Программа доступна с www.mssqlproducts.com
Автор будет благодарен всем, кто дополнит приведенный выше список скрытых опасностей MS SQL Server.