Правда про MS SQL Server

Виклик неіснуючих процедур і звернення до неіснуючих таблиць

Відомо, що 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.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*