|
|
Глава 14. Защита данных В этой главе...
Каждый слышал о законе Мерфи, который формулируется обычно так: "Если какая-нибудь неприятность может случиться, она случается". Большую часть времени дела идут хорошо, и мы потешаемся над этим псевдозаконом. Временами нам даже кажется, что мы из тех немногих счастливчиков, над кем не властен один из основных законов мироздания. Даже если неприятности все-таки происходят, то мы обычно легко с ними справляемся. Однако в очень сложной структуре вероятность возникновения проблем резко возрастает (как сказал бы математик, "приближенно описывается квадратичной зависимостью от сложности системы"). Поэтому большие программные разработки почти всегда появляются с опозданием и часто с немалым количеством невыявленных ошибок. Реальное многопользовательское приложение СУБД является большой и сложной структурой. Во время работы этого приложения существует большая вероятность сбоев. И хотя были разработаны специальные методы разработки приложений, направленные на то, чтобы свести последствия этих сбоев к минимуму, полностью исключить их невозможно. Это хорошая новость для профессионалов, занятых поддержкой баз данных, и специалистов по ремонту. Автоматика, скорее всего, никогда их не заменит. Угрозы целостности данных Киберпространство (в том числе ваша собственная сеть) может быть прекрасным местом для посещения, но для размещенных в нем данных это отнюдь не райский уголок. Данные могут быть повреждены или совсем испорчены самыми разными способами. В главе 5 рассказывалось о проблемах, возникающих в результате ввода неправильных данных, ошибок оператора, злонамеренного повреждения данных и одновременного доступа. "Неаккуратные" команды SQL и неправильно спроектированные приложения также могут повредить данные, и не нужно иметь много фантазии, чтобы представить, как это может произойти. Впрочем, данным угрожают и две очевидные опасности: нестабильность платформы и аппаратные сбои. В данном разделе как раз говорится об этих двух опасностях, а также о неприятностях, связанных с одновременным доступом. Нестабильность платформы Нестабильность платформы относится к таким неприятностям, которых даже быть-то не должно, но, увы, они существуют. Чаще всего она проявляется при запуске одного или множества новых и относительно непроверенных компонентов системы. Проблемы могуг "притаиться" в новом выпуске СУБД, в новой версии операционной системы или же в новом оборудовании. Проявляются они обычно тогда, когда вы запускаете очень важное задание. В результате система блокируется, а данные — портятся. И вам больше ничего не остается, кроме как ругать последними словами свой компьютер и тех, кто его сделал, — особенно, если не осталось резервной копии. Внимание: Никогда не выполняйте ответственное задание в системе, имеющей хотя бы один непроверенный компонент. Не поддавайтесь искушению немедленно перейти на только что появившуюся бета-версию СУБД или операционной системы, даже если эта версия предоставляет расширенную функциональность. По необходимости экспериментируйте с новым программным обеспечением на машине, полностью изолированной от рабочей сети. Аппаратный сбой Даже хорошо проверенное, высоконадежное оборудование иногда отказывает, отправляя данные на тот свет. Все материальное со временем изнашивается, даже современные полупроводниковые схемы. Если такой отказ происходит тогда, когда база данных открыта, то данные можно потерять, даже не осознавая этого. Опыт показывает — рано или поздно это произойдет. Уж если закон Мерфи и проявляет себя, так только в самое неподходящее время. Одним из способов защитить данные от отказов оборудования является резервное копирование. Сохраняйте лишние копии всего подряд. Если ваша организация может себе это позволить, для обеспечения максимальной безопасности можно продублировать аппаратные средства вместе с их настройками с таким расчетом, чтобы, если потребуется, можно было быстро установить и запустить резервные копии базы данных и приложений на резервном оборудовании. Ну а если ограниченность в средствах не позволяет дублировать все подряд, включая расходы? Тогда, по крайней мере, делайте достаточно часто резервные копии вашей базы данных и приложений — причем настолько часто, чтобы вам после неожиданного отказа не пришлось заново вводить слишком много данных. Другой способ избежать наихудших последствий аппаратных сбоев — использование транзакций. Это основная тема данной главы. Транзакция — это неделимая единица работы. Или транзакция выполняется целиком, или не выполняется вовсе. Если этот подход "все или ничего" кажется вам слишком радикальным, примите к сведению, что самые большие проблемы возникают в результате неполного выполнения операции с базой данных. Одновременный доступ Представим, что программы и оборудование, с которыми вы работаете, проверены, данные введены правильно, приложения свободны от ошибок, а оборудование абсолютно надежно. Получается, что данным ничего не грозит? К сожалению, нет. Если несколько людей одновременно пытаются использовать одну и ту же таблицу из базы данных, создается ситуация одновременного доступа и их компьютеры соревнуются за право первоочередного доступа. Многопользовательские системы баз данных должны иметь возможность эффективно разрешать возникающие коллизии одновременного доступа. Проблемы взаимодействия транзакций Проблемы, связанные с одновременным доступом, возникают даже в относительно простых приложениях. Представьте, например, такой случай. Вы пишете приложение, которое предназначено для обработки заказов и включает в себя четыре таблицы: ORDER_MASTER (главная таблица заказов), CUSTOMER (таблица клиентов), LINE_ITEM (строка заказа) и INVENTORY (таблица с описанием товаров). Выполняются следующие условия.
Политика вашей компании состоит в том, чтобы каждый заказ или выполнять полностью, или не выполнять вовсе. Частичные выполнения заказов не допускаются. (Спокойно. Это же воображаемая ситуация.) Вы пишете приложение ORDER_PROCESSING (обработка заказа), которое должно обрабатывать в таблице ORDER_MASTER каждый новый заказ, причем делать это следующим образом. Приложение вначале определяет, возможна ли отгрузка всех заказанных товаров. Если да, то приложение оформляет заказ, соответственно уменьшая в таблице INVENTORY значение столбца QuantityOnHand и удаляя из таблиц ORDER JvlASTER и LINE_ITEM записи, относящиеся к этому заказу. Пока все хорошо. Ваше приложение должно обрабатывать заказы одним из двух способов.
Если заказ выполним, большей эффективностью обладает первый способ, если же нет — второй. Таким образом, если большая часть заказов выполнима, необходимо использовать первый способ. В противном случае больше подойдет второй. Предположим, что это приложение установлено и запущено в многопользовательской системе, в которой нет достаточного управления одновременным доступом. Сразу же возникают следующие проблемы.
Последовательное выполнение исключает нежелательные взаимодействии Конфликт транзакций не происходит, если они выполняются последовательно. Главное — побыстрее занять очередь. Если невыполнимая транзакция пользователя 1 заканчивается перед началом транзакции пользователя 2, то функция ROLLBACK (откат) возвращает все товары, заказанные пользователем 1, делая их доступными во время транзакции пользователя 2. Если бы во втором примере транзакции выполнялись последовательно, то у пользователя 2 не было бы возможности изменить количество единиц любого товара, пока не закончится транзакция пользователя 1. Только после окончания транзакции пользователя 1 пользователь 2 сможет увидеть, сколько есть в наличии единиц требуемого товара. Если транзакции выполняются последовательно, одна за другой, они не смогут друг с другом взаимодействовать и нежелательные последствия таких взаимодействий исключаются, Если результат одновременных транзакций будет таким же, как и при последовательном выполнении, то эти транзакции называются упорядочиваемыми (serializable). Внимание: Последовательное выполнение транзакций не является панацеей. Приходится искать компромиссы между производительностью и степенью защиты. Чем больше транзакции изолированы друг от друга, тем больше времени требуется на выполнение какой-либо функции; в киберпространстве, как и в реальной жизни, на ожидание в очереди требуется время. Старайтесь найти такие компромиссы, чтобы настройки вашей системы создавали достаточную защиту, но не большую, чем та, которая вам нужна. Слишком жесткий контроль за одновременным доступом может свести производительность системы на нет. Уменьшение уязвимости данных Чтобы уменьшить шансы потери данных из-за несчастного случая или непредвиденного взаимодействия, можно принимать меры предосторожности на нескольких уровнях. Можно так настроить СУБД, чтобы она без вас принимала некоторые из этих мер. Вы даже иногда не будете знать о них. Кроме того, администратор базы данных может по своему усмотрению обеспечить и другие меры предосторожности. О них вы также можете быть либо осведомлены, либо нет. И наконец, как разработчик вы можете сами принять определенные меры предосторожности при написании кода. Можно избавить себя от большей части неприятностей, если выработать привычку автоматически придерживаться следующих простых принципов и всегда реализовывать их в своем коде или во время работы с базой данных.
Теперь поговорим об этих принципах подробно. Использование транзакций SQL Одним из главных инструментов SQL, предназначенных для сохранения целостности баз данных, является транзакция. Транзакция SQL состоит из любых операторов SQL, которые могут воздействовать на базу данных. Транзакция SQL завершается одним из двух операторов: COMMIT (завершение) или ROLLBACK (откат).
Помни: Под приложением мы сейчас будем подразумевать программу, выполняющуюся на COBOL, С или другом языке программирования, или последовательность команд, вводимых с терминала во время одного сеанса. Приложение может состоять из последовательности транзакций SQL. Первая из них начинается как раз в начале приложения, последняя же транзакция заканчивается в его конце. Каждый выполняемый приложением оператор COMMIT или ROLLBACK завершает одну транзакцию SQL и начинает следующую. Например, приложение с тремя транзакциями должно иметь такой общий вид: Начало приложения Различные операторы SQL (SQL-транзакция-1) COMMIT или ROLLBACK Различные операторы SQL (SQL-транзакция-2) COMMIT или ROLLBACK Различные операторы SQL (SQL-транзакция-З) Конец приложения Помни: Термин "транзакция SQL" (или "SQL-транзакция") применяют из-за того, что приложение может использовать другие возможности ограничения одновременного доступа (например, связанные с сетевым доступом) и выполнять другие виды транзакций. Далее в этой книге под "транзакцией" подразумевается именно "транзакция SQL". Обычная транзакция SQL может выполняться в одном из двух режимов: READ-WRITE (чтение-запись) или READ-ONLY (только чтение). Для нее можно задать один из следующих уровней изоляции: SERIALIZABLE (последовательное выполнение), REPEATABLE READ (повторяющееся чтение), READ COMMITTED (чтение подтвержденных данных) или READ UNCOMMITTED (чтение неподтвержденных данных). (Характеристики транзакций приводятся ниже, в разделе "Уровни изоляции".) Характеристиками по умолчанию являются READ-WRITE и SERIALIZABLE. Если нужно использовать любые другие характеристики, то их следует указать с помощью оператора SET TRANSACTION (задать транзакцию), например, такого: SET TRANSACTION READ ONLY ; ИЛИ SET TRANSACTION READ ONLY REPEATABLE READ ; ИЛИ SET TRANSACTION READ COMMITTED ; В одном приложении может иметься множество операторов SET TRANSACTION, но в каждой транзакции можно указывать только один из них — и он обязательно должен быть первым оператором SQL в транзакции. Если нужно использовать оператор SET TRANSACTION, то его надо выполнять или в начале приложения, или после оператора COMMIT либо ROLLBACK. Этот оператор следует обязательно выполнять в начале каждой транзакции, для которой требуются установки, не совпадающие с предусмотренными по умолчанию. Дело в том, что после оператора COMMIT или ROLLBACK каждая новая транзакция автоматически получает характеристики по умолчанию. Технические подробности: В операторе SET TRANSACTION можно также задать значение параметра DIAGNOSTIC SIZE (размер диагностики), определяющего количество ошибочных условий, информацию о которых должна сохранять реализация. Такое ограничение необходимо, потому что при выполнении оператора SQL может произойти сразу много ошибок. Значение по умолчанию этого параметра определяется реализацией, и, как правило, лучше его не трогать. Транзакция по умолчанию Характеристики транзакции SQL, задаваемые по умолчанию, обычно подходят для большинства пользователей. Впрочем, если необходимо, то с помощью оператора SET TRANSACTION, как уже говорилось в предыдущем разделе, для транзакции можно задать и другие значения параметров. В оставшейся части главы оператору SET TRANSACTION будет уделено много внимания. Транзакция по умолчанию создается исходя из двух неявных допущений:
Эта транзакция задает режим READ-WRITE, и он, как можно ожидать, разрешает отправлять на выполнение операторы, изменяющие базу данных. Транзакция по умолчанию имеет уровень изоляции SERIALIZABLE, который является максимально безопасным. Значение параметра DIAGNOSTIC SIZE зависит от используемой реализации и приведено в ее документации. Уровни изоляции Конечно, транзакция должна быть полностью изолирована от других транзакций, выполняющихся одновременно с вашей. Однако в многопользовательской системе из реального мира полная изоляция не всегда осуществима. Она может обернуться слишком высокой потерей производительности. И тут встает вопрос о компромиссе: какой уровень изоляции нужен вам на самом деле и какое количество производительности вы за него согласны отдать? "Черновое" чтение Самый слабый уровень изоляции называется READ UNCOMMITED и позволяет выполнять так называемое "черновое" чтение. При "черновом" чтении изменение, внесенное первым пользователем, может быть прочитано вторым пользователем еще до того, как первый пользователь подтвердит это изменение с помощью оператора COMMIT. Проблема возникает, если первый пользователь прерывает транзакцию и делает для нее откат. Все последующие действия второго пользователя выполняются теперь на основе неправильного значения. Приведем в качестве примера следующую ситуацию. Пусть имеется приложение, работающее с наличными товарами. Один пользователь уменьшает их количество, а второй читает новое, меньшее значение. Первый пользователь делает откат своей транзакции (восстанавливает первоначальное количество), но второй, думая, что товара осталось мало, заказывает его у поставщика, в результате на складе образуется товарный избыток. И это еще не худший случай. Внимание:Если вам нужны точные результаты, уровнем изоляции READ UNCOMMITED лучше не пользоваться. Уровень READ UNCOMMITED можно использовать тогда, когда нужно статистически обрабатывать такие малоизменяющиеся данные:
Во многих подобных случаях приблизительной информации вполне достаточно; дополнительное снижение производительности, нужное для получения более точного результата, не оправдано. Проблемы неповторяющегося чтения Следующим, более высоким уровнем изоляции является READ COMMITED: изменение, производимое другой транзакцией, невидимо для вашей транзакции до тех пор, пока другой пользователь не завершит ее с помощью оператора COMMIT. Этот уровень обеспечивает лучший результат, чем READ UNCOMMITED, но он все-таки подвержен неповторяющемуся чтению — новой серьезной неприятности. Для пояснения рассмотрим классический пример с наличными товарами. Пользователь 1 отправляет запрос в базу данных, чтобы узнать количество определенного товара, имеющееся на складе. Это количество равно десяти. Почти в то же самое время пользователь 2 начинает, а затем с помощью оператора COMMIT завершает транзакцию, которая записывает заказ на десять единиц того же товара, уменьшая, таким образом, его запасы до нуля. И тут пользователь 1, думая, что в наличии имеется десять единиц товара, пытается оформить заказ на пять единиц. Но и такого количества уже нет. Пользователь 2, по существу, опустошил склад. Первоначальная операция чтения, выполненная пользователем 1 по имеющемуся количеству, является неповторяющейся. Это количество изменили прямо под носом пользователя 1, и все предположения, сделанные на основе полученных им данных, являются неправильными. Риск фиктивного чтения Уровень изоляции REPEATABLE READ дает гарантию, что такой неприятности, как неповторяющееся чтение, уже не будет. Однако на этом уровне все равно часто происходит фиктивное чтение — неприятность, возникающая тогда, когда данные, читаемые пользователем, меняются в результате другой транзакции, причем меняются как раз во время чтения. Предположим, например, что пользователь 1 отправляет на выполнение команду, условие поиска которой (предложение WHERE или HAVING) выбирает какое-либо множество строк. И сразу же после этого пользователь 2 выполняет, а затем с помощью оператора COMMIT завершает операцию, в результате которой данные, хранящиеся в одной из этих строк, становятся другими. Вначале эти данные удовлетворяли условию поиска, заданному пользователем 1, а теперь — нет. Или, возможно, некоторые строки, которые вначале этому условию не соответствовали, теперь вполне для него подходят. А пользователь 1, транзакция которого еще не завершена, и понятия не имеет об этих изменениях; само же приложение ведет себя так, как будто ничего не произошло. И вот несчастный пользователь 1 оправляет на выполнение еще один оператор SQL. В нем условия поиска те же самые, что и в первоначальном операторе, поэтому пользователь надеется, что получит те же строки, что и перед этим. Но вторая операция выполняется уже не с теми строками, что первая. В результате фиктивного чтения надежная информация оказалась негодной. Тише едешь - дальше будешь Уровню изоляции SERIALIZABLE не свойственны неприятности, характерные для остальных трех уровней. Одновременные транзакции с уровнем SERIALIZABLE будут выполняться не параллельно, а последовательно. Если вы задали этот уровень изоляции, то только отказы оборудования и программ могут привести к невыполнению транзакции, и зная, что ваша система работает корректно, можно не беспокоиться о правильности результатов операций с базой данных. Конечно, наивысшая надежность имеет свою цену в виде уменьшения производительности, так что во всем нужно знать меру. В табл. 14.1 приведены четыре уровня изоляции и решаемые ими проблемы. Таблица 14.1. Уровни изоляции и решаемые ими проблемы
Неявный оператор начала транзакции Некоторые реализации требуют, чтобы о начале транзакции сообщалось явным образом с помощью специального оператора, такого как BEGIN (начало) или BEGIN TRAN (начало транзакции). Стандарт SQL:2003 этого не требует. Если еще никакая транзакция не начата и на выполнение отправляется команда SQL, то в системе, совместимой с SQL:20039, создается транзакция по умолчанию. Например, операторы CREATE TABLE, SELECT и UPDATE выполняются только в транзакции. Достаточно запустить один из них на выполнение, и начнется транзакция по умолчанию. Оператор SET TRANSACTION Время от времени для транзакции приходится использовать характеристики, отличные от устанавливаемых по умолчанию. Эти нестандартные характеристики можно задавать с помощью оператора SET TRANSACTION. Этот оператор должен быть первым оператором транзакции. Оператор SET TRANSACTION позволяет задавать режим, уровень изоляции и размер диагностики. Чтобы изменить, например, все три характеристики, можно отправить на выполнение такую команду: SET TRANSACTION READ ONLY, ISOLATION LEVEL READ UNCOMMITED, DIAGNOSTICS SIZE 4 ; Операторы данной транзакции не смогут изменить базу данных (режим READ ONLY). Кроме того, определен самый низкий и, следовательно, наиболее опасный уровень изоляции (READ UNCOMMITED). Для области диагностики выбран размер 4. Как видно, параметры подобраны таким образом, чтобы транзакция использовала поменьше системных ресурсов. Теперь сравните предыдущую команду с этой: SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE, DIAGNOSTICS SIZE 8 ; Эти значения позволяют транзакции изменить базу данных, задают наивысший уровень изоляции и назначают большую область диагностики. Эта транзакция предъявляет гораздо более высокие требования к системным ресурсам. В зависимости от реализации указанные значения могут оказаться значениями по умолчанию. Естественно, в операторе SET TRANSACTION можно использовать и другие значения уровня изоляции и размера диагностики. Совет: Не задавайте слишком высокий уровень изоляции. Может показаться, что для надежности всегда лучше выбирать значение SERILIZABLE. В зависимости от конкретной ситуации это может оказаться неплохой идеей. С другой стороны, не всем транзакциям требуется такой высокий уровень изоляции, очень снижающий общую производительность системы. Если во время транзакции не требуется модифицировать базу данных, смело задавайте режим READ ONLY. В общем, лучше обойтись без фанатизма. Оператор COMMIT Хотя в SQL:2OO3 не предусмотрен явный оператор начала транзакции, но зато есть два опера-юра ее завершения: COMMIT и ROLLBACK. Первый из них используйте тогда, когда вы уже дошли до конца транзакции и собираетесь подтвердить изменения, внесенные в базу данных. В операторе COMMIT может находиться необязательное ключевое слово WORK (СОММТГ WORK означает "завершить работу")- Если при выполнении оператора COMMIT произойдет ошибка или системный сбой, возможно, потребуется выполнить откат транзакции и повторить ее снова. Оператор ROLLBACK В конце транзакции иногда требуется отменить изменения, внесенные во время этой транзакции, т.е. восстановить базу данных в том состоянии, в каком она была перед самым началом транзакции. Для этого можно воспользоваться оператором ROLLBACK, который является отказоустойчивым. Даже если во время его выполнения в системе произойдет аварийный сбой, то после перезагрузки оператор ROLLBACK можно запустить снова. И он должен восстановить базу данных в состоянии, в каком она была перед началом транзакции. Блокирование объектов базы данных Уровень изоляции, который установлен по умолчанию или с помощью оператора SET TRANSACTION, указывает СУБД, сколько усилий ей нужно прилагать, чтобы ваша работа не взаимодействовала с работой других пользователей. Главная защита со стороны СУБД от нежелательных транзакций — это блокировка используемых вами объектов базы данных. Ниже приведено несколько примеров такой блокировки.
В каждой СУБД управление блокировкой реализовано по-своему. И хотя в некоторых реализациях "броня" толще, чем в других, но все равно большинство из нынешних систем в состоянии защитить данные от негативных последствий одновременного доступа. Резервное копирование Резервное копирование — комплекс мер, регулярно выполняемый администратором базы данных. Резервное копирование всех элементов системы должно проводиться через определенные интервалы, которые зависят от того, насколько часто обновляются эти элементы. Если ваша база данных обновляется ежедневно, то ее резервное копирование также следует проводить ежедневно. Приложения, формы и отчеты также могут меняться, хотя и не так часто. Администратор должен успевать создавать и их резервные копии тоже. Совет: Храните несколько версий резервных копий. Иногда ущерб, причиненный базе данных, становится очевидным лишь через некоторое время. Чтобы вернуться к последней работоспособной версии, вам, возможно, придется вернуться на несколько версий назад. Есть много разных способов резервного копирования.
Технические подробности: Возможно, вы слышали от проектировщиков баз данных, что базам данных нужна АСШ (означает "кислота"). Нет, конечно, они не собираются одурманить свои творения психоделией из 1960-годов или растворить в пузырящемся месиве хранящиеся в базах данные. АСШ— это аббревиатура, образованная от слов Atomicity (атомарность), Consistency (согласованность), Isolation (изоляция) и Durability (долговечность). Эти четыре характеристики необходимы для защиты базы данных от искажения данных.
Точки отката и субтранзакции В идеале транзакции должны быть атомарными — такими же неделимыми, как представлялись атомы древним грекам. Однако в действительности даже атомы неделимыми не являются. С появлением реализации SQL: 1999 транзакции в базах данных также перестали быть атомарными. Транзакция теперь может состоять из множества субтранзакций. Субтранзакции отделяются друг от друга точками отката, задаваемыми с помощью оператора SAVEPOINT. Он может использоваться в сочетании с оператором ROLLBACK. До появления точек отката оператор ROLLBACK применялся только для отмены всей транзакции, теперь его можно использовать для отката транзакции до имеющейся в ней точки отката. Вы можете спросить: а для чего это нужно? В основном оператор ROLLBACK применяется для восстановления исходных данных после того, как транзакция была прервана из-за ошибки. Понятно, что если во время транзакции произошла ошибка, то бессмысленно делать откат только к ближайшей точке отката. Ведь для того, чтобы вернуть базу данных в состояние, в котором она была перед началом транзакции, нужен откат всей транзакции. Но это еще не вся правда. Возникают ситуации, в которых нужен откат именно части транзакции. Скажем, со своими данными вы проводите сложную последовательность операций. Где-то во время этого процесса получаются результаты, которые приводят вас к заключению, что вами выбран неверный путь. Если установить точку отката в соответствующем месте этой последовательности, можно выполнить откат к ней и попробовать другой вариант. Если нужно изменить действие лишь части кода, а остальной код является безупречным, то лучше поступить так, чем прекращать всю текущую транзакцию и запускать новую ради небольшого изменения. Чтобы поместить в код SQL точку отката, используйте следующий синтаксис: SAVEPOINT имя_точки_сохранения ; Откат транзакции к этой точке можно выполнить с помощью следующего кода: ROLLBACK TO SAVEPOINT имя_точки_сохранения ; Некоторые реализации SQL не поддерживают оператор SAVEPOINT. Если в вашей реализации его нет, то использовать этот оператор вы не сможете. Ограничения в транзакциях Проверка достоверности данных заключается не только в проверке правильности типа данных. Кроме этого, возможно, потребуется следить, чтобы в некоторых столбцах не было неопределенных значений, а в других — значения не выходили за границы определенного диапазона. Об этих ограничениях см. в главе 5. Говорить о связи ограничений с транзакциями имеет смысл потому, что первые влияют на работу последних. Предположим, например, что нужно добавить данные в таблицу, в которой имеется столбец с ограничением NOT NULL. Как правило, в начале создают пустую строку и затем заполняют ее значениями. Однако ограничение NOT NULL не позволит воспользоваться данным способом, поскольку SQL не даст ввести строку с неопределенным значением, находящимся в столбце с ограничением NOT NULL, даже если данные Ограничения, определенные как NOT DEFERRABLE, применяются немедленно. А те, что определены как DEFERRABLE, первоначально могут быть заданы как DEFERRED (задержанные) или IMMEDIATE (немедленные). Если ограничение типа DEFERRABLE задано как IMMEDIATE, то оно действует так, как и ограничение типа NOT DEFERRABLE, — немедленно. Если же ограничение типа DEFERRABLE задано как DEFERRED, то его действие может быть отсрочено. Чтобы добавлять пустые записи или выполнять другие операции, которые могут нарушить ограничения типа DEFERRABLE, можно использовать следующий оператор: SET CONSTRAINTS ALL DEFERRED ; Он определяет все ограничения типа DEFERRABLE как DEFERRED. На ограничения типа NOT DEFERRABLE этот оператор не действует. После того как выполнены все операции, которые могут нарушить ваши ограничения, и таблица достигла того состояния, когда их нарушать уже нельзя, тогда эти ограничения можно применить заново. Соответствующий оператор выглядит следующим образом: SET CONSTRAINTS ALL IMMEDIATE ; Если вы сделали ошибку и данные не соответствуют каким-либо ограничениям, вы сразу же это увидите после выполнения данного оператора. Если ограничения, ранее заданные как DEFERRED, явно не задаются вами как IMMEDIATE, то, когда вы попытаетесь завершить свою транзакцию с помощью оператора COMMIT, SQL активизирует все задержанные ограничения. Если к этому моменту не все ограничения выполняются, транзакция будет отменена и SQL выдаст сообщение об ошибке. Ограничения в SQL защищают от ввода неправильных данных (или, что так же важно, от недопустимого отсутствия данных), причем у вас есть возможность на время действия транзакции временно отменить имеющиеся ограничения. Чтобы увидеть, насколько важна отсрочка применения ограничений, проанализируйте пример с платежными ведомостями. Предположим, что в таблице EMPLOYEE (сотрудник) имеются столбцы EmpNo (номер сотрудника), EmpName (фамилия сотрудника), DeptNo (номер отдела) и Salary (оклад). DeptNo является внешним ключом, который ссылается на таблицу DEPT (отдел). Предположим также, что в таблице DEPT имеются столбцы DeptNo и DeptName (название отдела), причем DeptNo — это первичный ключ. Представим, что, кроме этого, вы хотите иметь такую же таблицу, как и DEPT, но в которой еще есть столбец Payroll (платежная ведомость), и в нем для каждого отдела имеется сумма значений Salary сотрудников этого отдела. Эквивалент этой таблицы можно создать с помощью следующего представления: CREATE VIEW DEPT2 AS SELECT D.*, SUM(E.Salary) AS Payroll FROM DEPT D, EMPLOYEE E WHERE D.DeptNo = E.DeptNo GROUP BY D.DeptNo ; Точно такое же представление можно определить еще и другим способом: CREATE VIEW DEPT3 AS SELECT D.*, (SELECT SUM(E.Salary) FROM EMPLOYEE E WHERE D.DeptNo = E.DeptNo) AS Payroll FROM DEPT D ; Но предположим, что для большей эффективности вы не собираетесь вычислять значение SUM каждый раз, когда ссылаетесь на столбец DEPT.Payroll. Вместо этого вы хотите, чтобы в таблице DEPT в действительности находился столбец Payroll. Значения в этом столбце вы будете обновлять каждый раз, когда будете вносить изменения в столбце Salary. И, чтобы обеспечить правильность значений в столбце Salary, в определение таблицы можно вставить ограничение: CREATE TABLE DEPT (DeptNo CHAR(5), DeptName CHAR(20), Payroll DECIMAL(15,2), CHECK (Payroll = (SELECT SUM(Salary) FROM EMPLOYEE E WHERE E.DeptNo = DEPT.DeptNo))); Теперь предположим, что вам надо увеличить на 100 значение Salary сотрудника под номером 123. Это можно сделать с помощью следующего обновления: UPDATE EMPLOYEE SET Salary = Salary + 100 WHERE EmpNo = '123' ; Кроме того, следует обновить таблицу DEPT: UPDATE DEPT D SET Payroll = Payroll + 100 WHERE D.DeptNo = (SELECT E.DeptNo FROM EMPLOYEE E WHERE E.EmpNo 423') ; (Подзапрос используется для того, чтобы получить ссылку на значение DeptNo сотрудника с идентификационным номером 123.) Но здесь имеется трудность: после каждого оператора ограничения проверяются. Теоретически должны проверяться все ограничения. Но на практике реализациями проверяются только те из них, которые относятся к значениям, измененным в ходе работы оператора. После первого из двух последних операторов UPDATE реализация проверяет все ограничения, которые ссылаются на измененные им значения. В число этих ограничений входит то, которое определено в таблице DEPT, потому что оно относится к столбцу Salary таблицы EMPLOYEE, а значения в этом столбце изменяются оператором UPDATE. После выполнения первого оператора UPDATE это ограничение оказалось нарушенным. Допустим, что перед выполнением этого оператора база данных находится в полном порядке и каждое значение Payroll в таблице DEPT равно сумме значений Salary из соответствующих строк таблицы EMPLOYEE. Тогда, как только первый оператор UPDATE увеличит значение Salary, это равенство выполняться не будет. Такая ситуация исправляется вторым оператором UPDATE, после выполнения которого значения базы данных соответствуют имеющимся ограничениям. Но в промежутке между этими обновлениями ограничения не выполняются. Оператор SET CONSTRAINTS DEFERRED дает возможность временно отключить все или только указанные вами ограничения. Действие этих ограничений будет задержано до тех пор, пока выполнится или оператор SET CONSTRAINTS IMMEDIATE, или один из двух операторов: COMMIT и ROLLBACK. Следовательно, в нашем случае перед оператором UPDATE и после него необходимо поместить операторы SET CONSTRAINTS: SET CONSTRAINTS DEFERRED ; UPDATE EMPLOYEE SET Salary = Salary + 100 WHERE EmpNo = '123' ; UPDATE DEPT D SET Payroll = Payroll + 100 WHERE D.DeptNo = (SELECT E.DeptNo FROM EMPLOYEE E WHERE E.EmpNo = '123') ; SET CONSTRAINTS IMMEDIATE ; Эта процедура откладывает действие всех ограничений. Например, при вставке в DEPT новых строк первичные ключи проверяться не будут; т.е. вы удалили и ту защиту, которая, возможно, вам нужна. Поэтому следует явно указывать ограничения, которые надо задержать. Для этого при создании ограничений им следует давать имена: CREATE TABLE DEPT (DeptNo CHAR(5), DeptName CHAR(20), Payroll DECIMAL(15,2), CONSTRAINT PayEqSumsal CHECK (Payroll = SELECT SUM(Salary) FROM EMPLOYEE E WHERE E.DeptNo = DEPT.DeptNo)) ; На ограничения с именами можно ссылаться индивидуально: SET CONSTRAINTS PayEqSumsal DEFERRED ; UPDATE EMPLOYEE SET Salary = Salary + 100 WHERE EmpNo = '12 3' ; UPDATE DEPT D SET Payroll = Payroll + 100 WHERE D.DeptNo = (SELECT E.DeptNo FROM EMPLOYEE E WHERE E.EmpNo = 423') ; SET CONSTRAINTS PayEqSumsal IMMEDIATE ; Если для ограничения в операторе CREATE не указано имя, то SQL создает это имя неявно. Оно находится в специальных таблицах — таблицах каталога. Но все-таки лучше явно задавать имена ограничений. Теперь предположим, что во втором операторе UPDATE в качестве значения возрастания вы по ошибке указали 1000. Это значение в операторе UPDATE является разрешенным, потому что имеющееся ограничение было отсрочено. Но при выполнении оператора SET CONSTRAINTS...IMMEDIATE будут проверяться указанные в нем ограничения. Если они не соблюдаются, то будет сгенерировано исключение. Но если вместо оператора SET CONSTRAINTS...IMMEDIATE выполняется оператор COMMIT, а ограничения не соблюдаются, то вместо оператора COMMIT выполняется оператор ROLLBACK. Подведем итог. Временно отменять действие ограничений можно только внутри транзакции. Как только транзакция завершается, ограничения сразу же вступают в силу. Если использовать эту возможность правильно, то транзакция не создаст никаких данных, нарушающих какие-либо ограничения. |
|
|
| На правах рекламы: |