Глушаков С.В., Ломотько Д.В. Базы данных

Подождите немного. Документ загружается.

220

Часть II. Структурированный язык запросов SQL

SELECT STUDENTS.SFAM, STUDENTS.SIMA,

STUDENTS.SOTCH, USP.OCENKA

FROM STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM

AND STUDENTS.SNUM =3412;

будет неясно, какому именно студенту эта оценка принадле-

жит. С другой стороны, если в таблице успеваемости USP поя-

вится запись со значением поля SNUM = 3417, которое отсутст-

вует в таблице STUDENTS, то в результате запроса

SELECT STUDENTS.SFAM, 'STUDENTS.SIMA,

STUDENTS.SOTCH, USP.OCENKA

FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS.SNUM

AND USP.SNUM = 3417;

можно получить список оценок, которые не принадлежат ни

одному студенту. Очевидно, что такие ситуации будут исключе-

ны, если каждое значение во внешнем ключе будет представлено

только один раз в родительском ключе, и это значение должно

существовать.

SQL автоматически поддерживает справочную целостность

данных с ограничением FOREIGN KEY. Эта функция ограничи-

вает значения, которые можно ввести в БД так, чтобы заставить

внешний ключ и родительский ключ соответствовать принципу

справочной целостности. Одно из действий этого ограничения -

отбрасывание значений для полей, ограниченных как внешний

ключ, которые отсутствуют в родительском ключе. К тому же

FOREIGN KEY также воздействует на возможность пользователя

изменять или удалять значения родительского ключа.

Ограничение FOREIGN KEY используется в командах

CREATE TABLE или ALTER TABLE, которые объявляют внеш-

ним ключом соответствующее поле таблицы, при этом дается

имя родительскому ключу, на которое предусматривается ссылка

внутри этого ограничения. FOREIGN KEY используется анало-

гично тому, как это реализовывалось для других типов ограниче-

ний, рассмотренных выше: оно может быть ограничением поля

или таблицы, причем в последнем случае - позволяет использо-

вать несколько полей в качестве одного внешнего ключ.

Синтаксис ограничения таблицы FOREIGN KEY следующий:

FOREIGN KEY <COLUMN LIST> REFERENCES

<PKTABLE> [<COLUMN LIST>]

Глава 2.5. Специальные аспекты работы с базами данных 221

Здесь первый список полей - это перечень разделенных запя-

тыми из одного или более полей создаваемой или модифицируе-

мой таблицы. PKTABLE - это таблица, содержащая родительский

ключ. Второй список полей - это поля, которые будут составлять

родительский ключ.

Списки двух полей должны быть совместимы, т. е. должны

выполняться следующие условия:

• списки должны содержать одинаковое количество полей;

• в той последовательности, в которой идет перечисление,

должна соблюдаться идентичность типа данных и размер

каждого поля.

Для примера создадим таблицу успеваемости USP с полем

SNUM, определенным в качестве внешнего ключа, ссылающего-

ся на таблицу студентов STUDENTS:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

FOREIGN KEY (SNUM)

REFERENCES STUDENTS (SNUM));

Обратите внимание на то, что при использовании ALTER

TABLE вместо CREATE TABLE для реализации ограничения

FOREIGN KEY значения, которые указываются во внешнем и

родительском ключах, должны быть в состоянии справочной це-

лостности. В противном случае команда будет отклонена. Отсю-

да следует, что употребление команды ALTER TABLE приводит

к необходимости каждый раз следить за соблюдением структур-

ных принципов справочной целостности.

Вариант ограничения поля FOREIGN KEY часто называют

ссылочным ограничением REFERENCES, поскольку оно факти-

чески не содержит в себе ключевых слов FOREIGN KEY, а про-

сто использует слово REFERENCES, как это показано в следую-

щем примере:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL

222

Часть II. Структурированный язык запросов SQL

REFERENCES STUDENTS (SNUM),

PNUM INTEGER NOT NULL);

В данном случае USP.SNUM определяется как внешний ключ.

у которого родительским является STUDENTS.SNUM.

Вообще говоря, используя ограничение FOREIGN KEY таб-

лицы или поля, можно не указывать список полей родительского

ключа, если родительский ключ имеет ограничение PRIMARY

KEY. При этом, в случае употребления ключей со многими поля-

ми, обязательно выполнение условия, чтобы порядок полей во

внешних и первичных ключах совпадал.

Например, если в таблице STUDENTS поле SNUM является

PRIMARY KEY, то его можно использовать как внешний ключ в

таблице успеваемости аналогично предыдущему примеру сле-

дующим образом:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES

STUDENTS,

PNUM INTEGER NOT NULL);

Поддержание справочной целостности требует ограничений и

на значения, которые могут присутствовать в полях, объявленных

как внешний ключ и родительский ключ. Это означает, в частно-

сти, что родительский ключ должен быть уникальным и не со-

держать NULL значений. С учетом этого, при объявлении

FOREIGN KEY, SQL должен быть уверенным, что двойные или

NULL значения не присутствуют в родительском ключе. Следо-

вательно, пользователь должен убедиться в том, что все поля,

которые используются в качестве родительских ключей, имеют

ограничение PRIMARY KEY или UNIQUE и NOT NULL.

Рекомендуемой стратегией при создании структуры БД явля-

ется ссылка внешних ключей таблиц только на первичные ключи.

Когда используются внешние ключи, происходит связь не просто

с родительскими ключами, на которые они ссылаются - связыва-

ются определенные записи таблиц. Действительно, сам по себе

родительский ключ не обеспечивает никакой информации, кото-

рая бы не была бы уже представлена во внешнем ключе. Напри-

мер, смысл поля SNUM в роли внешнего ключа состоит в том.

что он обеспечивает связь, но не к значениям полей SNUM. на

Глава 2.5. Специальные аспекты работы с базами данных

223

которые он ссылается, а к другой информации, находящейся в

той же записи, например, к фамилии студента.

Так как основной задачей первичного ключа является иденти-

фикация уникальной записи таблицы, то это - самый логичный и

менее неоднозначный выбор для внешнего ключа. Внешний

ключ, который не имеет никакой другой цели, кроме связывания

строк таблиц, в этом смысле напоминает первичный ключ, ис-

пользуемый исключительно для идентификации строк, и является

хорошим средством для сохранения грамотной и простой струк-

туры БД.

Из вышеизложенного логично вытекает, что внешний ключ,

вообще говоря, должен содержать только те значения, которые

фактически имеются в родительском ключе, либо NULL значе-

ния. Попытка ввести другие данные в этот ключ будет отклонена

системой.

Если объявить внешний ключ, как NOT NULL, то от неопре-

деленных значений в нем можно избавиться, однако это необяза-

тельно, а иногда нежелательно. Это может произойти в ситуации,

когда, например, вводится оценка, а номер студенческого билета

неизвестен заранее.

Напомним, что в качестве внешнего ключа может высту-

пать комбинация из нескольких полей. Чтобы создать такой

внешний ключ, необходимо помнить о том, что сочетание этих

полей в родительской таблице должно отвечать требованию

ограничения UNIQUE. Создание внешнего ключа таким спо-

собом поддерживает целостность БД, даже если при этом

пользователю будет запрещено внутреннее прерывание (ис-

ключения) целостности БД.

С точки зрения реализации целостности БД внутренние пре-

рывания крайне нежелательны, и. если они в системе допускают-

ся, то зачастую целостность данных придется поддерживать

вручную.

Рассмотренные ограничения воздействуют на возможность

(или невозможность) выполнения команд модификации данных.

Для полей, определенных как внешние ключи, любые значения,

помещаемые командами INSERT или UPDATE, должны быть

уже представлены в их родительских ключах. Более того, допус-

кается вставлять в эти поля NULL значения, несмотря на то, что

они не разрешены в родительских ключах. Помимо этого, можно

удалять командой DELETE любые записи с внешними ключами,

не используя родительские ключи вообще.

В соответствии со стандартом, изменение или удаление зна-

чений родительского ключа вообще не допускается. Это, напри-

224

Часть II, Структурированный язык запросов SQL

мер, означает, что нельзя удалить данные о студенте из таблицы

STUDENTS до тех пор, пока в таблице успеваемости USP для

него имеется какая-либо информация. С одной стороны, это явля-

ется положительным моментом - невозможна ситуация, когда

появятся оценки, не принадлежащие ни одному студенту. С дру-

гой стороны, достаточно часто возникает необходимость в пол-

ном удалении информации, например, в случае отчисления сту-

дента из вуза. Об этих возможностях мы уже упоминали выше,

говоря о каскадировании и ограничении действий.

Итак, поведение системы при изменении родительского ключа

может отличаться от предусмотренного стандартом. Если необ-

ходимо изменить или удалить текущее ссылочное значение роди-

тельского ключа, имеются три возможности:

• запретить изменения, оговорив, что изменения в роди-

тельском ключе ограничены;

• сделав изменения в родительском ключе, произвести из-

менения и во внешнем ключе автоматически, что факти-

чески является каскадным изменением;

• сделать изменение в родительском ключе и установить

внешний ключ в NULL значения автоматически, что на-

зывается пустым изменением внешнего ключа.

В пределах этих возможностей можно обрабатывать все коман-

ды модификации. С командой INSERT, правда, в этом случае си-

туация несколько сложнее, т. к. при ее использовании помещаются

новые значения родительского ключа в таблицу. Таким образом,

то, что случится в родительском ключе, можно разделить на огра-

ниченные (RESTRICTED), каскадируемые (CASCADES) и пустые

(NULL) изменения.

Для примера рассмотрим то, как эти возможности применя-

ются в таблицах. Предположим, что имеется необходимость в

изменении номера студенческого билета, причем оценки должны

сохраниться у этого студента с новым номером. Если же данные

о студенте удаляются, то необходимо, чтобы его оценки в табли-

це успеваемости оставались, скажем, для дальнейшего отчета.

Чтобы это реализовать, необходимо указать условие UPDATE с

каскадируемым, a DELETE с ограниченным эффектом, т. е.:

CREATE TABLE USP

(TJNUM INTEGER NOT NULL PRIMARY KEY,

. OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES

STUDENTS,

Глава 2.5. Специальные аспекты работы с базами данных

225

PNUM INTEGER NOT NULL,

UPDATE OF STUDENTS CASCADES,

DELETE OF STUDENTS RESTRICTED);

После этого, при удалении данных о студенте из таблицы

STUDENTS, команда не будет выполнена до тех пор, пока не бу-

дут удалены его оценки или не изменится значение поля SNUM.

С другой стороны, если номер студенческого билета будет изме-

нен, то для соответствующих оценок этого студента значение

поля SNUM также автоматически поменяется.

Для демонстрации NULL изменений воспользуемся таким

примером:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER REFERENCES STUDENTS,

PNUM INTEGER NOT NULL,

DELETE OF STUDENTS NULLS);

В этом случае удаление информации о студенте из таблицы

STUDENTS пройдет успешно, а записи, соответствующие его

оценкам, будут иметь в поле SNUM NULL значения, что может

пригодиться, скажем, для расчета средней оценки по учебным

предметам за семестр. Разумеется, при наличии в таблице

DELETE с NULL эффектом ограничения NOT NULL не должно

быть в поле SNUM.

Говоря о внешних ключах, мы уже упоминали о возможности

их ссылки на ту же таблицу. Иначе говоря, родительская и внеш-

няя таблица в данном случае - одно и то же.

Эта особенность ограничения FOREIGN KEY может приго-

диться, например, в следующем случае. Пусть структура таблицы

STUDENTS будет несколько изменена (см. табл. 2.7). Назовем

полученную таблицу STUDENTS2.

Таблица 2.7

Модифицированная таблица STUDENTS

SNUM

3412

SFAM

Поляков

STUDENTS2

SIMA

Анатолий

SOTCH

Алексеевич

STAR

3414

8 «Базы данных:

226

Часть II. Структурированный язык запросов SQL

SNUM

3413

3414

3415

3416

SFAM

Старова

Гриценко

Котенко

Нагорный

STUDENTS2

SIMA

Любовь

Владимир

Анатолий

Евгений

SOTCH

Михайловна

Николаевич

Васильевич

STAR

3414

3416

3414

NULL

В таблице STUDENTS2 появилось поле STAR, которое со-

держит номер студенческого билета старосты. В нашем случае

студент Нагорный является старостой всего потока, ему подчиня-

ется староста Гриценко, которому, в свою очередь, подчиняются

все остальные. Очевидно, поскольку староста сам является сту-

дентом, то он тоже представлен в этой таблице.

Попробуем создать такую таблицу, причем номер студенче-

ского билета в поле SNUM будет первичным ключом, а на него

будет ссылаться внешний ключ, в качестве которого объявим

поле STAR:

CREATE TABLE STUDENTS2

(SNUM INTEGER NOT NULL PRIMARY KEY,

SFAM CHAR (20) NOT NULL,

SIMA CHAR (10),

SOTCH CHAR (15)

STAR INTEGER REFERENCES STUDENTS2);

Из этого примера видно, что каждый из студентов ссылает-

ся на другого как на своего старосту, однако Нагорный, как

самый старший, должен иметь в поле STAR NULL значение, т.

е. это поле должно допускать неопределенные значения. Это

вынуждает сделать принцип справочной целостности - в ином

случае в такую таблицу нельзя было бы добавить ни одной

записи. Даже если староста ссылался бы на самого себя, в мо-

мент вставки самой первой записи в таблицу студент с таким

номером должен уже существовать, иначе команда вставки

была бы отклонена. Более того, если внешний ключ таблицы

ссылается на саму себя не напрямую, а через ссылку к другой

таблице (например, внешним ключом у STUDENTS2 была бы

таблица USP. а у нее, в свою очередь, - таблица STUDENTS2),

этот принцип должен быть сохранен.

Глава 2.5. Специальные аспекты работы с базами данных

227

Такой подход называется перекрестной ссыпкой. SQL под-

держивает эту возможность, но практически это может вызвать

трудности, поскольку любая таблица из этих двух, созданная

предыдущей, является ссылочной таблицей, для еще не сущест-

вующей другой таблицы. Рекомендуется быть очень осторожным

и аккуратным при использовании перекрестных ссылок, т. к. ме-

ханизм модификации и удаления данных может повредить ин-

формацию, хранящуюся в БД.

Средства обработки транзакций

Очевидно, что БД - это постоянно изменяющаяся под воздей-

ствием пользователей и их команд структура. До сих пор предпо-

лагалось, что эта система правильно разработана и функциониру-

ет без каких-либо сбоев. Однако в реальной жизни, из-за ошибок

людей, взаимодействующих с СУБД, или из-за неустойчивой ра-

боты компьютеров возникают сбои и ошибки и в самой БД. По-

этому в СУБД применяют специальные способы отмены дейст-

вий, вызвавших такие ошибки - это, в первую очередь, транзак-

ции, о которых речь уже шла. Команда SQL, которая оказывает

действие на содержание или структуру БД, не обязательно будет

необратимой - пользователь может установить, что произойдет

после окончания ее действия: останутся ли внесенные изменения

в БД, или они будут полностью проигнорированы.

Транзакция начинается всякий раз. когда происходит сеанс

работы с SQL. Все команды, которые вводятся, будут частью

этой транзакции, пока она не завершится вводом команды

COMMIT WORK или команды ROLLBACK WORK. COMMIT

делает все изменения, произведенные транзакцией, постоянными,

a ROLLBACK может их отменить. Новая транзакция начинается

после каждой команды COMMIT или ROLLBACK.

Синтаксис этих команд очень простой: чтобы оставить все

изменения постоянными используют

COMMIT WORK;

а для отмены изменения

ROLLBACK WORK;

В большинстве случаев можно установить параметр, называе-

мый AUTOCOMMIT. который будет автоматически запоминать

228

Часть II. Структурированный язык запросов SQL

все выполняемые команды, причем действия, которые привели к

ошибке, всегда будут автоматически отменены. Обычно этот ре-

жим устанавливается с помощью команды типа:

SET AUTOCOMMIT ON;

а возврат к обычной диалоговой обработке запросов - с помо-

щью команды

SET AUTOCOMMIT OFF;

Кроме того, имеется возможность установки AUTOCOMMIT.

которую СУБД выполнит автоматически при регистрации. Если

сеанс пользователя завершается аварийно - например, произошел

сбой системы или выполнена перезагрузка пользователя, - то те-

кущая транзакция выполнит автоматический откат изменений.

Это - одна из причин, по которой стоит управлять выполнением

диалоговой обработки запросов, разделив команды на разные

транзакции.

Не рекомендуется организовывать работу так, чтобы одиноч-

ные транзакции содержали много команд, тем более не связанных

между собой. Это может привести к тому, что при отмене изме-

нений будет выполнено много слишком много действий, в том

числе и тех, которые являются нужными и ошибки не вызывали.

Лучшим вариантом является делать транзакции состоящими из

одной команды или нескольких тесно связанных между собой

команд.

Например, возникла необходимость в удалении данных о сту-

денте Полякове из таблицы STUDENTS. Очевидно, что при этом

нужно что-нибудь сделать с его оценками при условии, что огра-

ничение внешнего ключа отсутствует. Логичное решение будет

состоять в том, чтобы установить поле SNUM для записей с его

оценками, в NULL значение и только после этого выполнять уда-

ление. Это можно реализовать следующим образом:

UPDATE USP

SET SNUM = NULL

WHERE SNUM = 3412;

DELETE FROM STUDENTS

' WHERE SNUM = 3412;

Если при этом возникают какие-либо проблемы, то у пользо-

вателя есть возможность отменить все изменения командой

Глава 2,5. Специальные аспекты работы о базами данных

229

ROLLBACK. В случае, если все в порядке, и действие этой груп-

пы команд на БД необходимо запомнить, это реализуется коман-

дой COMMIT.

В стандарте ANSI/ISO определена модель транзакций, а также

указаны задачи операторов COMMIT и ROLLBACK. Согласно

стандарту считается, что транзакция автоматически начинается с

вьшолнения пользователем или программой первого оператора

SQL. Далее происходит последовательное выполнение остальных

операторов SQL до тех пор, пока транзакция не завершится од-

ним из четырех способов:

• команда COMMIT завершает выполнение текущей тран-

закции, причем изменения, внесенные в БД, становятся

постоянными. Новая транзакция начинается непосредст-

венно после COMMIT;

• команда ROLLBACK отменяет выполнение текущей

транзакции, сделанные изменения отменяются, а новая

транзакция начинается непосредственно после

ROLLBACK;

• успешное завершение программы обработки данных

считается успешным окончанием транзакции, как если бы

была выполнена команда COMMIT. Новая транзакция не

начинается, т. к. программа закончилась;

• неуспешное завершение программы считается неуспеш-

ным окончанием транзакции, как если бы был выполнена

команда ROLLBACK. Новая транзакция не начинается,

поскольку программа закончилась.

Обратите внимание на то, что согласно стандартной модели

транзакций, пользователь или программа могут выполнить тран-

закцию всегда. Традиционно, при интерактивном использовании

SQL включен режим AUTOCOMMIT. т. е. каждая команда стано-

вится отдельной транзакцией.

В СУБД SQL Server используется несколько расширенная мо-

дель транзакций, предоставляющая пользователям дополнитель-

ные возможности. При этом используются команды:

• BEGIN TRANSACTION - сообщает системе о начале

транзакции. В отличие от стандартной модели, начато

транзакции задается явно с помощью этой команды:

• COMMIT TRANSACTION - сообщает системе об успеш-

ном окончании транзакции. После вьшолнения этой ко-

манды все изменения, сделанные в БД в течение транзак-

ции, становятся постоянными, однако новая транзакция

не начинается;

230

Часть II. Структурированный язык запросов SQL

• SAVE TRANSACTION - создает внутри транзакции точку

сохранения. СУБД сохраняет состояние БД в текущей

точке и присваивает сохраненному состоянию имя точки

сохранения, которое указывается в операторе;

• ROLLBACK TO SAVEPOINT - отменяет изменения, сде-

ланные в БД после точки сохранения, возвращая транзак-

цию к месту, где был выполнен оператор SAVE

TRANSACTION.

• ROLLBACK - отменяет все изменения, сделанные в БД

после оператора BEGIN TRANSACTION.

Введенные точки сохранения особо полезны в сложных тран-

закциях, состоящих из большого количества команд. В процессе

выполнения транзакции программа может периодически сохра-

нять состояние БД, создавая именованные точки сохранения. Ес-

ли во время выполнения возникают какие-либо проблемы, про-

грамма имеет возможность отменить не всю транзакцию цели-

ком, а только ее часть до точки сохранения, возобновив свое вы-

полнение транзакции с этого места. При этом все операторы, вы-

полненные до точки сохранения, остаются в силе, а выполненные

после точки сохранения отменяются. Однако логической едини-

цей работы все-таки является целая транзакция, поэтому, если во

время выполнения транзакции происходит системный или аппа-

ратный сбой, то отменяется целая транзакция.

Реализация механизма транзакций в СУБД основана на ис-

пользовании журнала транзакций. При выполнении пользовате-

лем команды на изменение БД система автоматически вносит в

журнал транзакций информацию о том, что и каким образом бы-

ло модифицировано данной командой. И только после того, как в

журнале будет сделана запись, СУБД изменит физическую запись

на устройстве хранения. После этого, при выполнении команды

COMMIT, в журнале отмечается конец транзакции.

Если пользователь выполняет команду ROLLBACK. СУБД

обращается к журналу и извлекает из него копии модифициро-

ванных во время транзакции данных. Используя их, система воз-

вращает БД в прежнее состояние и. таким образом, отменяет вне-

сенные изменения. В случае системного сбоя администратор БД

восстанавливает данные по журналу транзакций, отыскивая тран-

закции, которые не были завершены к моменту сбоя.

Использование журнала транзакций имеет и недостатки: в ре-

зультате его использования продолжительность операций изме-

нения БД увеличивается, поэтому иногда работу журнала приос-

танавливают. Очевидно, это действие нельзя отнести к разряду

Глава 2.5. Специальные аспекты работы с базами данных

231

желательных, т. к. в случае сбоя или выполнения ROLLBACK

восстановить первоначальное состояние БД нельзя.

Методы блокировки

Как правило, SQL используется в многопользовательских сис-

темах, где очень часто выполняется одновременно сразу много

манипуляций с БД. Это создает потенциальную возможность

конфликта между различными выполняемыми действиями.

Предположим, что первый пользователь выполняет команду:

UPDATE USP

SET OCENKA = 3

WHERE PNUM = 2003;

которая модифицирует данные в таблице успеваемости. В то же

время второй пользователь делает запрос:

SELECT SNUM, MIN (OCENKA)

FROM USP

GROUP BY SNUM;

выбирая из таблицы минимальную оценку по каждому сту-

денту. Очевидно, что во втором случае для изменяемых данных

важно, чтобы в качестве результата был выведен либо конечный

результат, либо не выводилось ничего - ведь любой промежуточ-

ный результат является случайным или непредсказуемым. С дру-

гой стороны, если минимальное значение будет выведено с уче-

том модификации, сделанной первым пользователем, который

позже свои действия отменит, то второй пользователь получит

неточные результаты, т.к. он об отмене может и не знать. Понят-

но, что решение подобных задач СУБД должна обеспечивать на

самом высоком уровне.

Как было сказано выше, обработка системой транзакций, вы-

полняемых одновременно, называется параллелизмом. При этом

могут возникать следующие типы проблем:

• изменение данных может быть осуществлено без учета

модификации, сделанной другим процессом. Такая си-

туация может возникнуть, если в таблице успеваемости

происходит корректировка оценок, а параллельно, другим

пользователем, происходит установление размера сти-

пендии, исходя из полученных студентами оценок:

232

Часть II. Структурированный язык запросов SQL

• изменения данных могут быть отменены одним процес-

сом, в то время как модифицированными данными уже

воспользовались для другого действия. Этот случай уже

проиллюстрирован выше, когда минимальные значения

оценок могли быть уже получены, после чего первый

пользователь произведенные изменения оценок отменил;

• одно действие может частично воздействовать на резуль-

тат другого действия. Например, при получении отчета

об успеваемости не исключено, что другим пользовате-

лем произведено удаление из списка одного или несколь-

ких студентов, а данные об их успеваемости уже могут

войти в вывод первого запроса.

• тупиковая ситуация, которая может произойти, если про-

цессы попытаться выполнить конфликтующие друг с

другом действия. Это может возникнуть, например, если

один пользователь пробует изменить значение внешнего

ключа, а другой - родительского ключа, и это происходит

одновременно.

Для исключения подобных ситуаций в SQL присутствует

средство управления параллелизмом, которое указывает

процессам точное место получения результата. Основной

принцип при этом следующий: все одновременно

выполняемые команды не будут завершены до тех пор, пока не

будут завершены предыдущие. Другими словами, система

должна обеспечивать одновременный доступ к таблице не

более чем для одной транзакции в данный момент времени.

Правда, в системах, где требуется обеспечить доступ к БД

очень большому количеству пользователей, управление

параллелизмом осуществляется по несколько видоизмененной

схеме, позволяя администратору БД и пользователям самим

регулировать степень согласованности и доступности данных.

Механизм, используемый SQL для управления параллелизмом

операций, как мы уже знаем, называется блокировкой. Блокиров-

ки задерживают определенные операции в БД, пока другие опе-

рации или транзакции не завершены. Задержанные операции по-

мещаются в очередь и выполняются только тогда, когда блоки-

ровка снята, а иногда не отклоняются системой, выдавая при этом

соответствующее предупреждение.

Блокировки в многопользовательских системах выполняются

по специальным схемам, применяемым ко всем командам в БД, и.

кроме того, снабжены средствами обнаружения блокировок, бло-

кирующих друг друга. В этом случае, одна из команд отменяется,

а следовательно, получает сброс блокировка.

Глава 2.5. Специальные аспекты работы с базами данных

233

Различают два базовых типа блокировок:

• распределяемые (нежесткие) блокировки - могут быть ус-

тановлены более чем одним пользователем в данный мо-

мент времени, что дает возможность любому числу про-

цессов обращаться к данным, но не изменять их;

• специальные (жесткие) блокировки - не позволяют нико-

му вообще, кроме владельца этой блокировки, обращать-

ся к данным. Специальные блокировки используются,

например, для команд, которые изменяют содержание

или структуру таблицы и действуют до конца транзакции.

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

уровня изоляции блокировки, определяющее, сколько таблиц бу-

дет блокировано. Традиционно используют три уровня изоляции,

два из которых можно применить к распределенным и специаль-

ным блокировкам, а третий, так называемый ограниченный уро-

вень, служит для того, чтобы использовать эти блокировки со-

вместно. Уровни изоляции управляются командами, поданными

самим SQL.

Уровень изоляции, называемый повторное чтение, реализует

такую стратегию, что внутри данной транзакции все записи, из-

влеченные с помощью запросов, не могут быть изменены. По-

скольку записи, модифицируемые в транзакции, подвергаются

специальной блокировке, они не могут быть изменены до тех

пор, пока транзакция не завершена. С другой стороны, для запро-

сов повторное чтение означает то, что можно решить заранее,

какие записи будут модифицированы, и заблокировать тот про-

цесс, которому необходимо их выбрать. Таким образом, при вы-

полнении запроса гарантируется, что никакие изменения не будут

сделаны в этих записях до тех пор, пока не завершится текущая

транзакция. Однако повторное чтение, защищающее процесс,

который установил блокировку, в то же время может в значи-

тельной мере снизить производительность работы с данными.

Уровень изоляции, который называют указатель стабильно-

сти, предохраняет каждую запись от изменений на время, когда

она считывается, или от чтения на время ее изменения. Во втором

вариалге это является специальной блокировкой и применяется

до тех пер. пока изменение не будет завершено или отменено.

Следовательно, при модификации группы записей, использую-

щих, указатель стабильности, эти записи будут заблокированы,

пока транзакция не закончится, что аналогично действию, произ-

водимому уровнем повторного чтения. Различие между этими

двумя уровнями в их воздействии на запросы. В с,тучае уровня

234

Часть II. Структурированный язык запросов SQL

указатель стабильности, записи таблицы, которые в данное время

не используются запросом, могут быть изменены.

Третий уровень изоляции называется только чтение. Только

чтение блокирует всю таблицу, а следовательно, не может ис-

пользоваться с командами модификации. Однако любая часть

таблицы в момент выполнения команды может быть отражена в

выводе запроса. Таким образом, только чтение гарантирует, что

вывод запроса будет внутренне согласован с данными таблицы.

По этой причине этот уровень удобен тогда, когда делаются от-

четы по данным таблицы, разрешая одновременный доступ к

большинству или ко всем строкам таблицы сразу нескольким

процессам, не производящим модификации БД.

Некоторые СУБД выполняют блокировку страницы памяти,

в которой хранится фрагмент БД, вместо блокировки записи.

Страница может состоять из одной или более строк таблицы,

причем там же могут быть индексы и другая служебная ин-

формация. Если блокируются страницы вместо записей, ис-

пользуются те же механизмы, что и описанные выше. Основ-

ным преимуществом такого подхода является его эффектив-

ность: SQL не следит за блокировкой каждой записи индиви-

дуально, т.е. он работает быстрее. Однако при этом могут быть

заблокированы строки таблицы, для которых это делать в дан-

ный момент совсем необязательно.

Итак, средства управления параллелизмом в СУБД определяет

то, в какой степени одновременно поданные команды будут ме-

шать друг другу. В современных СУБД оно является адаптируе-

мым средством, автоматически находящим лучшее решение с

учетом обеспечения максимальной производительности БД и

доступности данных для действующих команд.

Глава 2.6

Представления

Что такое представление

Представление, или VIEW, - некоторое подобие таблицы, со-

держание которого выбирается из других таблиц с помощью вы-

полнения запроса, причем, при изменении значений в этих таб-

лицах, данные автоматически меняются и в представлении. По

Глава 2.6. Представления

235

этой причине зачастую представления более эффективны, чем

обыкновенные запросы.

Рассматриваемые до этого таблицы относились к базовым.

т.е. таким, которые содержат данные и постоянно находятся на

устройствах хранения информации. Поскольку данные для

создания представлений выбираются из других таблиц, то они

не содержат никаких собственных значений, однако часто

представления являются более гибким средством, т.к. с их по-

мощью информация может быть показана пользователю в наи-

более удобном для него варианте (подробно о представлениях

мы уже говорили выше).

Итак, представление - это фактически тот же запрос, который

выполняется всякий раз. когда представление участвует в какой-

либо команде. Вывод этого запроса при этом в каждый момент

времени становится содержанием представления.

Когда СУБД встречает в команде ссылку на представление,

она отыскивает его определение, находящееся в БД. После этого

происходит преобразование пользовательской команды в ее эк-

вивалент с учетом того, какой запрос лежит в основе используе-

мого представления. Следовательно, у пользователя создается

впечатление будто он работает с настоящей, реально сущест-

вующей таблицей.

При этом у СУБД есть две возможности реализации представ-

ления. Если его определение простое, то система формирует каж-

дую запись представления по мере необходимости, постепенно

считывая исходные данные из базовых таблиц. В случае, если

определение сложное, то СУБД приходится сначала выполнить

такую операцию, как материализация представления, т.е. сохра-

нение информации, из которой состоит представление во вре-

менной таблице. Затем система приступает к выполнению поль-

зовательской команды и формированию ее результатов, а после

этого временная таблица удаляется.

Создание, удаление и обновление

представлений

Представление создается командой CREATE VIEW, после ко-

торой указывается его имя. а далее следует запрос, формирую-

щий тело представления. Например, для создания представления

OTLSTUD, которое содержит информацию о студентах, полу-

236

Часть И Структурированный язык запросов SQL

чающих стипендию в размере 25,50, можно воспользоваться сле-

дующей командой:

CREATE VIEW OTLSTUD

AS SELECT *

FROM STUDENTS

WHERE STIP=25.50;

Теперь в БД существует представление с именем OTLSTUD,

которое можно использовать в командах так же, как и любую

другую таблицу: она может быть запрошена, модифицирована, з

нее могут быть вставлены записи, она может быть удалена из БД

или соединена с другими таблицами и представлениями.

При выполнении запроса

SELECT *

FROM OTLSTUD

будет получен следующий вывод:

SNUM SFAM SIMA SOTCH STIP

3412

3416

Поляков

Нагорный

Анатолий

Евгений

Алексеевич

Васильевич

25.50

Очевидно, что к представлению можно сделать запрос, со-

держащий предикат, что, соответственно, приведет к выводу

только удовлетворяющих ему строк.

Преимущество использования представлений по сравнению с

запросами к основной таблице, как уже было сказано, в том, что

представление будет модифицировано автоматически всякий раз,

когда таблица, лежащая в его основе, изменяется. Содержание

представления не фиксировано, и обновляется каждый раз, когда

на него ссылаются в команде. Если, например, в таблице STU-

DENTS появится еще один студент со стипендией 25.50, то он

автоматически отобразится в представлении.

Представления значительно расширяют возможности управ-

ления данными. В частности, это прекрасный способ дать доступ

к информации в таблице, скрыв часть данных. Например, если

необходимо скрыть размер стипендии студента от пользователя,

можно создать представление:

CREATE VIEW STIPOFF

AS SELECT SNUM, SFAM, SIMA, SOTCH

FROM STUDENTS

Глава 2.6. Представления

237

При выполнении запроса

SELECT *

FROM STIPOFF

будет выведено все содержимое созданного представления:

SNUM SFAM SIMA SOTCH

3412 Поляков Анатолий Алексеевич

3413 Старова Любовь Михайловна

3414 Гриценко Владимир Николаевич

3415 Котенко Анатолий Николаевич

3416 Нагорный Евгений Васильевич

Иначе говоря, представление STIPOFF фактически та же таб-

лица STUDENTS, но без поля STIP.

Представление может теперь изменяться командами модифи-

кации DML, но модификация не будет воздействовать на само

представление. Фактически команды будут перенаправлены к

базовой таблице. Например, выполнение команды:

UPDATE STIPOFF

SET SIMA = 'Василий

WHERE SNUM = 3415;

аналогично выполнению той же команды для таблицы студен-

тов STUDENTS. Однако следующая команда будет отвергнута

системой:

UPDATE STIPOFF

SET STIP =25.50

WHERE SNUM = 3415;

Это связано с тем, что поле ST1P отсутствует в представлении

STIPOFF. И еще одно важное замечание: не все представления

могут быть модифицированы, о чем пойдет разговор немного

ниже.

В рассмотренных примерах поля представлений имеют свои

имена, полученные непосредственно из имен полей основной

таблицы. Однако иногда возникает необходимость назвать

238

Часть II. Структурированный язык запросов SQL

столбцы новыми именами. Это, например, может потребоваться в

случае, если столбцы являются выводимыми, и поэтому не

имеющими имен, или если два или более столбцов в объединении

имеют те же имена, что в их базовой таблице.

Имена, которые необходимо присвоить полям, записываются

в круглых скобках после имени таблиц. Они могут не указывать-

ся, если совпадают с именами полей запрашиваемой таблицы.

Когда делается запрос к представлению, на самом деле систе-

мой осуществляется обращение к базовым таблицам. Например,

при выполнении команды

SELECT *

FROM OTLSTUD

WHERE SNUM > 3412;

СУБД фактически осуществляет следующее:

SELECT *

FROM STUDENTS

WHERE STIP = 25.50

AND SNUM > 3412;

В данном случае СУБД прекрасно справляется со своей зада-

чей. Однако иногда возникают ситуации, когда появляются про-

блемы с представлением в результате комбинации из двух допус-

т"?'ых предикатов, которые не будут работать.

В качестве иллюстрации этого создадим представление, кото-

рое содержит данные о количестве студентов, получающих ту

или иную стипендию:

CREATE VIEW STIPCOUNT (STIP, COL)

AS SELECT STIP, COUNT (*)

FROM STUDENTS

GROUP BY STIP;

Теперь сделаем запрос к этому представлению для того, что-

бы выяснить, имеется ли какой - либо размер стипендии, назна-

ченный для двух студентов:

SELECT *

FROM STIPCOUNT

WHERE COL = 2;

Если-скомбинировать два предиката, то. скорее всего, будет

получено:

SELECT STIP, COUNT (*)

FROM STUDENTS

Глава 2.6. Представления

239

WHERE COUNT (*) = 2

GROUP BY STIP;

Запрос, приведенный выше, является недопустимым, т.к. аг-

регатные функции не могут использоваться в предикате. Возни-

кает закономерное предположение о том, что СУБД в некоторых

случаях не сможет правильно сформулировать запрос, а значит -

выполнить его. Все зависит от того, каким образом и по каким

алгоритмам система интерпретирует пользовательские команды.

Правильным способом формирования вышеупомянутого запроса

будет следующий:

SELECT STIP, COUNT (*)

FROM STUDENTS

GROUP BY STIP

HAVING COUNT (*) = 2;

но SQL может не выполнить такого превращения. Лучшее, что

можно сделать в этом случае, это самому проверить, справляется

ли используемая СУБД с аналогичными запросами и, при необ-

ходимости, соблюдать соответствующие ограничения при фор-

мировании запросов к представлениям.

В SQL существует понятие групповых представлений - т.е. та-

ких, которые содержат предложение GROUP BY или которые

основаны на других групповых представлениях. Групповые пред-

ставления являются превосходным способом обрабатывать слож-

ную информацию непрерывно. Предыдущий пример представле-

ния как раз относится к числу групповых. Теперь каждый раз,

когда требуется определить количество студентов, получающих

ту или иную стипендию, достаточно просто выбрать все записи

из рассматриваемого представления вместо того, чтобы создавать

достаточно сложный запрос.

Представления могут быть основаны сразу на нескольких ба-

зовых таблицах. Примером этому может служить создание пред-

ставления, которое показывало бы оценки студента по учебному

предмету, причем содержало бы не коды, а полные названия:

CREATE VIEW STUDOCEN

AS SELECT THIRD.UNUM, FIRST.SFAM,

SECOND.PNAME, THIRD.OCENKA

FROM STUDENTS FIRST, PREDMET SECOND,

USP THIRD

WHERE FIRST.SNUM = THIRD.SNUM

AND SECOND.PNUM = THIRD.PNUM;