(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Правда о MS SQL Server

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.



 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 22.01.2001 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Microsoft Office 365 Бизнес. Подписка на 1 рабочее место на 1 год
Microsoft 365 Business Basic (corporate)
Microsoft Windows Professional 10, Электронный ключ
Microsoft 365 Apps for business (corporate)
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Мир OLAP и Business Intelligence: новости, статьи, обзоры
Delphi - проблемы и решения
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100