Дмитрий Артемов (dimaa@microsoft)

Надежное программирование, как избежать проблем при разработке приложений на TSQL/для SQL Server Дмитрий Артемов (dimaa@microsoft.com)

Предупреждение • Примеры кода и решения являются иллюстрациями (возможными, но не единственными способами программирования) • Все примеры предельно упрощены

Надежное программирование для снижения уязвимости кода • Если говорить о надежном программировании БД, существует четыре ключевых элемента, которые позволяют избавиться от ошибок и сделают код менее уязвимым, защищая его от возможных проблем при непреднамеренном использовании • Определитесь в предположениях. • Тестируйте как можно больше вариантов использования. • Используйте короткие, удобные для тестирования и полностью протестированные модули. • При необходимости используйте код повторно, но будьте осторожны

Определитесь в предположениях • Четкое определение предположений – одна из наиболее разрушительных ошибок при разработке ПО, в результате код используется не так как планировалось • Необходимо: • В явном виде определиться с предположениями • Убедиться, что они остаются актуальными на всех этапах разработки • Систематически избавляться от предположений,котрые не важны или более не актуальны

Тщательное тестирование • По мере разработки кода, необходимо включить все наше воображение для определения возможных сценариев незапланированного использования, в попытках «сломать» собственную разработку. • Разработанные сценарии следует включить в программу тестирования. • В процессе тестирования мы должны выяснить как различные изменения могут повлиять на на то,как исполняется код, и научиться как разрабатывать код такми образом, чтобы разрабатывать надежный код, который не «ломается» при изменении, например SET LANGUAGE или ROWCOUNT

Защищайте код от незапланированного использования • Слишком часто мы считаем код готовым, после того как он пройдет несколько простых проверок • Мы не посвящаем достаточно времени на поиск и идентификацию всех возможных, не выходящих за разумные рамки, способов использования нашего кода • Когда наступает неизбежное и наш код используется не так, как мы предполагали, он «ломается»

Защита от незапланированного использования (1/2) CREATE TABLE dbo.Messages ( MessageID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Subject VARCHAR(30) NOT NULL , Body VARCHAR(100) NOT NULL ) ; CREATE PROCEDURE dbo.SelectMessagesBySubjectBeginning AS SELECT Subject , Body FROM dbo.Messages WHERE Subject LIKE @SubjectBeginning + '%’ • EXEC dbo.SelectMessagesBySubjectBeginning @SubjectBeginning='Next‘ – OK • EXEC dbo.SelectMessagesBySubjectBeginning @SubjectBeginning = '[OT]‘ – Не работает, [] – для LIKE определяют группы символов [a-d] или [abcd] • EXEC dbo.SelectMessagesBySubjectBeginning @SubjectBeginning = '50%‘ – аналогично, % воспринимается как заменитель символа

Защита от незапланированного использования (2/2) ALTER PROCEDURE dbo.SelectMessagesBySubjectBeginning AS SET @ModifiedSubjectBeginning = REPLACE(REPLACE(@SubjectBeginning, '[', '[[]'), '%', '[%]') SELECT @SubjectBeginning AS [@SubjectBeginning] , @ModifiedSubjectBeginning AS [@ModifiedSubjectBeginning] ; SELECT Subject , Body FROM dbo.Messages WHERE Subject LIKE @ModifiedSubjectBeginning + '%'

Защита от изменений в настройках SQL Server • Часто встречающаяся ошибка заключается в разработке кода, который не учитывает возможных изменений настроек самого сервера/сессии • В результате, код не способен адаптироваться к изменению свойств и настроек при работе на другом экземпляре SQL Server или в ситуации/ когда настройки меняются в рамках сессии

Как SET ROWCOUNT «ломает» триггер (1/2) • Необходимо фиксировать все обновления в таблице • Мы создаем таблицу ObjectsChangeLog, куда будем записывать изменения и триггер,который будет записывать изменения в таблицу ObjectsChangeLog • По умолчанию ROWCOUNT = 0 – возвращает все записи • Если в какой-то момент код меняет значение умолчания для ROWCOUNT и не восстанавливает его, триггер пропустит часть записей • Возможно не сразу, пока число записей < ROWCOUNT • Тем труднее будет отловить эту проблему

Как SET ROWCOUNT «ломает» триггер (2/2) • Не стоит полагаться на судьбу • Мы в явном виде устанавливаем нужное значение и SQL Server восстанвливает то, что было до нас, после завершения триггера ALTER TRIGGER dbo.Objects_UpdTrigger ON dbo.Objects FOR UPDATEAS BEGIN; -- the scope of this setting is the body of the trigger SET ROWCOUNT 0 -- Тело триггера -- На выходе установка ROWCOUNT будет восстановлена • !! Эта настройка считается устаревшей (deprecated) для SQL Server 2008 • !! Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server

Off topic • Когда готовил презентацию обнаружился интересный побочный эффект от установки ROWCOUNT

Обновление больше записей, чем планировалось 1/2 UPDATE dbo.Employee SET ManagerID = @ManagerID WHERE FirstName = @FirstName AND LastName = @LastName • Очевидно, разработчик предполагал, что в таблице нет сотрудников с одинаковыми именем и фамилией • Если это не так (что вполне возможно), процедура ассоциирует их все с одним и тем же начальником

Обновление больше записей, чем планировалось 2/2 • Обычно, обнаружив следование некоему предположеню мы можем обеспечить его выполнение или устранить. • Обеспечить выполнение возможно созданием UNIQUE constraint на комбинации полей FirstName и LastName. • Но это расходится с реальной жизнью • Поэтому мы строим код. Чтобы исключить неверное предположение UPDATE dbo.Employee SET ManagerID = @ManagerID WHERE EmployeeID = @EmployeeID • Если EmployeeID – первичный ключ на таблице dbo.Employee, измененный код будет работать корректно

Условия в предложении WHERE могут быть обработаны в любом порядке • Есть целый ряд языков, которые гарантируют определенный порядок обработки логических выражений. SQL не из их числа • Нельзя полагаться,что условия WHERE будут обработаны в определенном порядке. Код, который полагается на порядок обработки условий чреват проблемами SELECT …Orders WHERE ISDATE(VarcharColumn) = 1 AND CAST(VarcharColumn AS DATETIME) = '20090707'; • Если CAST будет обработано перед проверкой ISDATE и строка – некорректная дата, мы получим ошибку INSERT orders values (7, '20099999') SELECT… Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. -- Это работает корректно SELECT … FROM Orders WHERE CASE WHEN ISDATE(VarcharColumn) = 1 THEN CAST(VarcharColumn AS DATETIME) END = '20090707' ;

SET, SELECT и пресловутый бесконечный цикл 1/2 • Нельзя рассчитывать что SET иSELECT всегда меняют значения переменных. • Полагаясь на это предположение, мы создаем ненадежный код • SELECT может оставить переменную без изменения,если набор пуст • В такой же ситуации SET присваивает NULL declare @i int set @i = 1 select @i << 1 set @i = (select 1 where 1=2) select @i<< NULL declare @i int select @i =1 select @i << 1 select @i = 1 where 1=2 select @i << 1

SET, SELECT и пресловутый бесконечный цикл 2/2 • Если SET приводит к ошибке, значение переменной также не меняется • Аналогично, если SELECT приводит к ошибке, значение переменной не меняется SET NOCOUNT ON ; DECLARE @i INT ; SELECT @i = -1 ; SELECT @i AS [@i before the assignment] ; SET @i = ( SELECT 1 UNION ALL SELECT 2 ) ; SELECT @i AS [@i after the assignment] ; Т.к. переменная не меняется, мы легко можем попасть в бесконечный цикл SET NOCOUNT ON ; DECLARE @i INT ; SELECT @i = -1 ; SELECT @i AS [@i before the assignment] ; SELECT @i = 1 WHERE ( SELECT 1 AS n UNION ALL SELECT 2 ) = 1 ; SELECT @i AS [@i after the assignment] ; Для ситуации с пустым множеством Используйте явное переназначение Используйте SET вместо SELECT

При назначении переменных лучше SET чем SELECT SELECT @CustomerId = CustomerId FROM dbo.Customers WHERE Position = ‘Manager‘ • Если у нас есть два человека на одинаковых должностях, результат выполнения непредсказуем • Мы не знаем и не можем знать, какой CustomerId попадет в переменную (порядок выборки не определен) • Но запрос будет работать! • SET распознает проблему SET @CustomerId = ( SELECT CustomerId FROM dbo.Customers WHERE PhoneNumber = '(123)456-7890’) Msg 512, Level 16, State 1, Line 16Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. • Разработчик должен перехватить ошибку

Как «пережить» изменение сигнатуры процедуры CREATE PROCEDURE dbo.SelectCustomersByName @LastName VARCHAR(50) = NULL , @PhoneNumber VARCHAR(50) = NULL EXEC dbo.SelectCustomersByName 'Hansen', -- @LastName '(234)123-4567‘ -- @PhoneNumber EXEC dbo.SelectCustomersByName @LastName = 'Hansen', @PhoneNumber = '(234)123-4567' ; ALTER PROCEDURE dbo.SelectCustomersByName @FirstName VARCHAR(50) = NULL , @LastName VARCHAR(50) = NULL , @PhoneNumber VARCHAR(50) = NULL EXEC dbo.SelectCustomersByName 'Hansen', -- @FirstName '(234)123-4567' ; -- @LastName EXEC dbo.SelectCustomersByName @LastName = 'Hansen', @PhoneNumber = '(234)123-4567' ;

Как «пережить» изменение схемы таблицы • Одна из наиболее частых причин «поломок» кода – изменение схемы таблицы • Добавление новых полей • Изменение определения существующих полей (например типа или размера)

Квалифицированное указание имен полей CREATE TABLE dbo.Shipments ( Barcode VARCHAR(30) NOT NULL PRIMARY KEY,… CREATE TABLE dbo.ShipmentItems ( ShipmentBarcode VARCHAR(30) NOT NULL, Description VARCHAR(100) NULL ) SELECT Barcode , ( SELECT COUNT(*) FROM dbo.ShipmentItems WHERE ShipmentBarcode = Barcode ) AS NumItems FROM dbo.Shipments ; Barcode NumItems ------------- --------- 123456 2 123654 0 ALTER TABLE dbo.ShipmentItems ADD Barcode VARCHAR(30) NULL SELECT Barcode , ( SELECT COUNT(*) FROM dbo.ShipmentItems WHERE ShipmentBarcode = Barcode ) AS NumItemsFROM dbo.Shipments ; Barcode NumItems --------- -------- 123456 0 123654 0 SELECT s.Barcode , ( SELECT COUNT(*) FROM dbo.ShipmentItems AS i WHERE i.ShipmentBarcode = s.Barcode ) AS NumItemsFROM dbo.Shipments AS s ; Barcode NumItems -------- ---------- 123456 2 123654 0

Изменение типа и/или размера CREATE TABLE dbo.Codes (Code VARCHAR(5) NOT NULL , Description VARCHAR(40) NOT NULL , CONSTRAINT PK_Codes PRIMARY KEY ( Code ) ) ALTER TABLE dbo.Codes ALTER COLUMN Code VARCHAR(10) NOT NULL EXEC dbo.SelectCode @Code = '1234567890' ; Code Description ------ --------------------- 12345 Description for 12345 CREATE PROCEDURE dbo.SelectCode @Code VARCHAR(5) AS SELECT Code , Description FROM dbo.Codes WHERE Code = @Code EXEC dbo.SelectCode @Code = '12345' ; Code Description ---------- --------------------- 12345 Description for 12345 Как быть в этой ситуации? Аккуратная документация и тестирование на граничные значения

Когда Snapshot Isolation «ломает» код 1/3 • Триггер, который выполняет проверку, при включенном режиме SNAPSHOT ISOLATION • ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON; • Может считать данные из “version store” и принять логически неверную запись

Кратко про Snapshot Isolation • Появилась в версии SQL Server 2005 • Позволяет развести “читателей” и “писателей” • За счет размещения последней зафиксированной записи в “хранилище версий” (version store ) в TempDb) • Плюсы • Существенно снижает ожидания при интенсивной конкуренции (читаем из одного места, пишем в другое) • Может существенно снизить число deadlock в ситуации когда блокировка возникает между выборкой и модификацией (те, что обычно лечатся средствами хинта UPDLOCK) • В режиме READ_COMMITTED_SNAPSHOT ON не требует изменения кода (все транзакции, исполняющиеся в режиме READ COMMITTED попадают под раздачу) • Минусы • Может существенно увеличить нагрузку на TempDb • Увеличивает размер самой БД за счет добавления 14 байт к каждой записи • Длинные транзакции могут создавать длинные цепочки версий. Опять нагрузка на TempDb • Обновления могут замедлится из-за необходимости вести версии • Код может работать с ошибками (то, о чем мы говорим сейчас)

Когда Snapshot Isolation «ломает» код 2/3 IF EXISTS (SELECT * FROM inserted AS i INNER JOIN dbo.Developers AS d ON i.AssignedToDeveloperID = d.DeveloperID WHERE d.Status = 'Vacation‘ AND i.Status = 'Active') BEGIN; RAISERROR ('Cannot change ', 16, 1); ROLLBACK; BEGIN TRANSACTION UPDATE Developers --COMMIT Триггер ждет Триггер выдает ошибку Откатывает обновление TICKETS COMMIT BEGIN TRANSACTION UPDATE TICKETS COMMIT

Когда Snapshot Isolation «ломает» код 3/3 • ALTER DATABASE xxx SET READ_COMMITTED_SNAPSHOT ON; BEGIN TRANSACTION UPDATE Developers --COMMIT Мы получаем логически неверные данные Пока транзакция ждет, пытаемся обновить Tickets COMMIT Триггер идет в TempDb, берет данные из Version Store и успешно завершает работу BEGIN TRANSACTION UPDATE TICKETS COMMIT Tempdb Мы получаем логически неверные данные COMMIT READCOMMITTEDLOCK

Тестирование 1/3 • Появление DMV существенно снизило пользу от применения SQL Server Profiler • Однако, пользовательские события могут вдохнуть в него новую жизнь • sp_trace_generateevent @eventid,@userinfo,@userdata • @eventid – int, значение в пределах 82 - 91. Соответствует 10 классам. 82 -UserConfigurable:0, 83 - UserConfigurable:1... • @userinfo - nvarchar(128) для заполнения поля TextData • @userdata - varbinary(8000) для заполнения поля BinaryData

Тестирование 2/3 • Очень полезна в сложной ситуации, когда код падает не всегда • Сбор всех команд за длительный срок создает слишком много данных • Для использования пользовательских событий необходимо иметь права ALTER TRACE • Право редкое, поэтому для его применения можно создать оболочку для вызова sp_trace_generateevent • Оболочке дадим права на базе сертификата

IF @@ROWCOUNT = 0 EXEC ThrowEvent 82, N'No data inserted into MyTable', 0x0000 IF @@ROWCOUNT = 0 EXEC ThrowEvent 82, N'No data inserted into MyTable', 0x0000 Тестирование 3/3 USE master GO CREATE CERTIFICATE ALTER_TRACE_CERT ENCRYPTION BY PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~' WITH SUBJECT='Certificate for ALTER TRACE', START_DATE='20000101', EXPIRY_DATE='99990101' GO CREATE LOGIN ALTER_TRACE_LOGIN FROM CERTIFICATE ALTER_TRACE_CERT GO GRANT ALTER TRACE TO ALTER_TRACE_LOGIN GO GRANT AUTHENTICATE SERVER TO ALTER_TRACE_LOGIN GO CREATE PROCEDURE ThrowEvent @eventid INT, @userinfonvarchar(128), @userdatavarbinary(8000) AS BEGIN EXEC sp_trace_generateevent @eventid = @eventid, @userinfo = @userinfo, @userdata = @userdata END GO ADD SIGNATURE TO ThrowEvent BY CERTIFICATE ALTER_TRACE_CERT WITH PASSWORD='-UsE_a!sTr0Ng_PwD-or-3~' GO GRANT EXEC ON ThrowEvent TO [public] GO -- Отладочный код в основной процедуре IF @@ROWCOUNT = 0 EXEC ThrowEvent 82, N'No data inserted into MyTable', 0x0000

Все, что я только что рассказал, и более того, можно найти в этой книге • Бесплатно главы 1-8 на http://www.simple-talk.com/books/sql-books/defensive-database-programming/

Спасибо! Есть вопросы?

Официальные курсы и сертификация Microsoft • Более 300 официальных курсов Microsoft доступно в России. • Официальные курсы можно прослушать только в авторизованных учебных центрах Microsoft • под руководством опытного сертифицированного инструктора Microsoft • интенсивное обучение с акцентом на практику • более 80-и учебных центров более чем в 20-и городах России (+ дистанционные и выездные курсы) • Сертификат Microsoft - показатель квалификации ИТ-специалиста для работодателя . • Microsoft предлагает гибкую систему сертификаций. • Все курсы, учебные центры и центры тестирования: www.microsoft.com/rus/learning 40 57 % % Доказательство № 75 Доказательство № 119 • рекрутеров считают сертификацию сотрудников одним из критериев для повышения в должности • сертифицированных специалистов считают, что сертификация помогла им получить работу или повышение

Специальные предложения • Сертификационный пакет со вторым шансом • Пакеты экзаменационных ваучеров со скидкой от 15 до 20% и бесплатной пересдачей («вторым шансом»). Все экзамены сдаются одним человеком. • Сэкономьте 15% на сертификации вашей ИТ-команды • Пакет из 10-и экзаменационных ваучеров со скидкой 15% для сотрудников ИТ-отдела. «Второй шанс» включен. Ваучеры можно произвольно распределять между сотрудниками. • Microsoft Certified Career Conference • Первая 24-часовая глобальная виртуальная конференция с 18 ноября с 15.00 (моск. время) по 19 ноября 2010 г. • Сессии по технологиям и построению карьеры • Скидка 50% для сертифицированных специалистов Microsoft и студентов • Бесплатная подписка на TechNet для слушателей официальных курсов • Некоторые курсы по SharePoint, Windows 7; WindowsServer 2008; SQL Server 2008 • Детали: www.microsoft.com/rus/learning С 22 ноября 2010 г. – подписка TechNet бесплатно для слушателей курсов. Количество ограничено!

Дмитрий Артемов (dimaa@microsoft)