Например: GRANT SELECT(person_id, lname, fname) ON persons TO PUBLIC Если какой-либо пользователь не имеет права на выборку того или иного поля, то он не может его использовать не только в разделе SELECT, но и в разделе WHERE. Например, если пользователь nick не имеет права на выборку поля address из таблицы persons, то он не имеет права выполнить следующие два запроса: SELECT lname, fname, address FROM persons WHERE person_id = 101 SELECT lname, fname FROM persons WHERE address MATCHES "*Одесса*" Пользователь, получивший то или иное право с помощью оператора GRANT ... ON ... TO ... не может передать это право другому пользователю. Для возможности передачи права другим, пользователь должен получить право на его передачу. Это право указывается в операторе GRANT с помощью ключевых слов WITH GRANT OPTION: GRANT <тип права на таблицу> (<имя поля>, ....) ON <имя таблицы> TO <имя пользователя> WITH GRANT OPTION Например, владелец таблицы tabl может передать право на выборку для пользователей "sales" и "tech", но пользователю "tech" это право передается с правом передачи: GRANT SELECT ON tabl TO salesGRANT SELECT ON tabl TO tech WITH GRANT OPTION После этого пользователь "tech" может выполнить следующие два оператора, а пользователь "sales" не может: GRANT SELECT ON tabl TO jason WITH GRANT OPTIONGRANT SELECT ON tabl TO micky Права на таблицу могут как передаваться, так и отбираться. Отобрать право у пользователя можно только в том случае, если Вы передали ему это право, либо Вы являетесь владельцем таблицы. Если Вы отбираете право у пользователя , который получил его с правом на передачу, то автоматически этого права лишаются и те, кому пользователь это право передал. Отбирание права производится оператором REVOKE <тип права на таблицу> ON <имя таблицы> FROM <имя пользователя> Например: REVOKE UPDATE ON persons FROM georgeREVOKE ALL ON passwords FROM PUBLIC Если какое-либо право отбирается у пользователя, которому оно было дано с правом на передачу (WITH GRANT OPTION), то это право теряют и те пользователи, кому этот пользователь его передал. Предположим, Вы владелец таблицы tab1. Вы передаете право на вставку пользователям user1, user2: GRANT INSERT ON tab1 TO user1 WITH GRANT OPTIONGRANT INSERT ON tab1 TO user2 WITH GRANT OPTION Затем, пользователь "user1" передал это право пользователю "user3": GRANT INSERT ON tab1 TO user3 Теперь, если Вы отберете право на выборку у пользователя "user1", то его автоматически потеряет и пользователь "user3". В операторах GRANT и REVOKE для таблиц и процедур можно использовать несколько прав одновременно и указывать несколько пользователей одновременно. Название прав и имена пользователей в этом случае надо указывать через запятую. Например оператор GRANT SELECT, UPDATE(fname, lname, address) ON persons TO micky, roma Эквивалентен следующим четырем операторам: GRANT UPDATE(fname, lname, address) ON persons TO mickyGRANT SELECT ON persons TO mickyGRANT UPDATE(fname, lname, address) ON persons TO romaGRANT SELECT ON persons TO roma 5.9.4. Права на хранимые процедуры Для хранимых процедур существует единственный тип права доступа - выполнять ее. Если база данных находится не в режиме ANSI, то при создании хранимой процедуры все пользователи автоматически получают право на ее исполнение. Если же база данных находится в режиме ANSI, то надо явно указать, какие пользователи имеют право ее исполнять. Право на исполнение называется EXECUTE. Передача и отбор этого права аналогичны передаче и отбору прав на уровне таблицы. Можно передавать право и с правом на передачу: GRANT EXECUTE ON <имя процелуры> TO <имя пользователя> GRANT EXECUTE ON <имя процелуры> TO <имя пользователя> WITH GRANT OPTION REVOKE EXECUTE ON <имя процелуры> FROM <имя пользователя> 5.9.5. Кто и как следит за соблюдением прав В заключении разговора о правах стоит отметить, что стоит за словами "имеет право" и "не имеет право". Контроль за доступом к информации осуществляет сервер базы данных. Пользователь не имеет доступа непосредственно к файлам с базой данных. Он даже не знает, как и где хранятся эти данные. При выполнении запроса пользователя, SQL-сервер получает его, определяет имя пользователя и по внутренней информации определяет может ли этот пользователь выполнить этот запрос. Если имеет право, то сервер производит обработку запроса, если нет - пользователю посылается сообщение об ошибке. Как хранится информация о привилегиях - это внутреннее дело SQL-сервера. Например, Informix DS использует для этого системные таблицы (каталоги), где и зафиксирована кто и что может делать с данными. 5.9.6. Механизм ролей В реальной информационной системе может быть очень много пользователей. Следить за тем, чтобы каждому пользователю были даны только нужные ему права достаточно сложно, особенно, если в базе данных существует много объектов. В тоже время, как правило, число категорий, типов пользователей, не так велико. В большинстве реальных систем число таких категорий не превышает 10. В SQL есть возможность управлять доступом не путем явного указания каждого конкретного пользователя, а путем создания роли, приписывания того или иного пользователя к конкретной роли, и управлением правами и привилегиями на уровне ролей. Роль можно рассматривать и наоборот, то есть как приписывание тех или иных прав и привилегий конкретной группе пользователей. Итак, роль - это группа пользователей с определенными правами. Для создания роли используется оператор CREATE ROLE <имя> Например, если мы хотим создать три группы - admin (администратор), sales (продавцы) и tech (технический персонал), то надо выполнить следующие операторы: CREATE ROLE adminCREATE ROLE salesCREATE ROLE tech Имя роли должно быть не длиннее 8 символов и не должно совпадать с именем пользователя, которому были переданы права на какой-либо объект в базе данных. Кроме того, имя пользователя не должно совпадать с именами пользователей, работающих в данный момент с базой данных. Если какой-то пользователь имеет имя, совпадающее с именем роли, он не сможет получить доступ к базе данных. То есть, для ролей имеет смысл выбирать имена, отличные от имен пользователей, которые могут работать на данной машине. Для указания пользователей, входящих в ту или иную роль, используется оператор GRANT <имя роли> TO <имя пользователя1>, <имя пользователя2> …. Один пользователь может принадлежать нескольким ролям, также как и не принадлежать ни одной роли (или, что тоже самое, принадлежать только роли по имени NULL). Например: GRANT admin TO andyGRANT tech TO andy, mickyGRANT sales TO nick, kate В этом примере пользователь andy входит сразу в две роли - admin и tech, пользователи micky, nick, kate - входят только в одну роль. Исключение пользователя из роли производится оператором REVOKE <имя роли> FROM <имя пользователя> Например: REVOKE sales FROM nick Для удаления роли используется оператор DROP ROLE <имя> Для приписывания роли или отбирания у роли тех или иных прав и привилегий используются варианты операторов GRANT и REVOKE, рассмотренные в пунктах 5.9.2 и 5.9.3, только вместо имени пользователя необходимо указывать имя роли: GRANT UPDATE(fname, lname, address) ON persons TO adminGRANT SELECT ON persons TO techREVOKE UPDATE(fname, lname, address) ON persons FROM sales Определять новые роли может только пользователь, являющийся администратором данной базы данных, то есть имеющий привилегию DBA на текущую базу данных. Удалить роль, модифицировать список пользователей, входящих в ту или иную роль, а также приписать или отобрать у роли некоторые привилегии, может только адинистратор базы данных, или пользователь, которому данная роль была передана с опцией WITH GRANT OPTION. Итак, пользователь может принадлежать нескольким ролям, кроме того, у него еще могут быть права и привилегии, данные ему лично (как пользователю). Для того, чтобы воспользоваться правами и привилегиями, принадлежащими какой-либо роли, пользователь должен выполнить оператор (или, естественно, внутри его программы должен быть выполнен оператор): SET ROLE <имя роли> например: SET ROLE admin Естественно, если пользователь не принадлежит этой роли, то данный оператор возвратит ошибку. Если оператор SET ROLE не будет выполнен, то права пользователя определяются на основе прав данных ему лично. Если оператор SET ROLE был выполнен, то пользователь в дополнение к своим личным правам, получает права, приписанные указанной роли. Одновременно может использоваться только одна роль, то есть новый оператор SET ROLE отменяет действие предыдущего. Для того, чтобы отказатья от прав, приписанных роли и вернуться только к правам, данных пользователю лично, надо выполнить оператор SET ROLE NULL Использование механизма ролей позволяет упростить ведение списка пользователей, сделать адинистрирование системы более простым, и, как следствие, более надежным. 5.9.7. Псевдотаблицы (VIEW) С помощью механизма прав на уровне таблицы можно управлять доступом разных пользователей на уровне таблицы целиком, либо на уровне полей в таблице. Но иногда возникает ситуация, когда надо управлять доступом к отдельным записям в таблице. Например, на некоторой фирме надо обеспечить такой доступ к информации о сотрудниках, чтобы каждый пользователь мог видеть записи только о тех сотрудниках, которые работают в одном с ним отделе. Как решить эту задачу? Если пользоваться правами на уровне таблиц, то потребуется создавать столько таблиц с одинаковой структурой, сколько отделов на фирме. Очевидно, это усложнит структуру базы данных, сделает ее негибкой и затруднит разработку прикладных программ. В SQL данная проблема может быть решена с помощью псевдотаблиц (VIEW). Псевдотаблица строится на основе уже существующих таблиц или пседотаблиц. В основе псевдотаблицы лежит оператор выборки. Собственно говоря, псевдотаблица и задается оператором выборки: CREATE VIEW <имя псевдотаблицы> AS <оператор выборки> Например: CREATE VIEW cheap_items AS SELECT item_id, company, name, price FROM items WHERE price < 100.00 В этом примере была создана псевдотаблица, содержащая информацию только о дешевых (дешевле 100 единиц) товаров. Создавать псевдотаблицы пользователь может только в том случае, если он имеет право на выборку по всем таблицам, входящим в эту псевдотаблицу и имеет право на управление ресурсами базы данных. Псевдотаблица не существует как самостоятельный набор данных, она динамчески строится из таблиц, указанных в операторе выборки. Во всем остальном псевдотаблица похожа на обычную таблицу. Псевдотаблицу можно использовать внутри других операторов выборки, на нее можно раздавать привилегии, ее можно использовать как основу для построения новых псевдотаблиц и т.д. Так как же решить ту задачу, с которой мы начали этот параграф? А именно, как сделать так, чтобы разные пользователи, просматривая одну и ту же таблицу, видели разные данные, а, говоря точнее, информацию только о тех сотрудниках, которые работают в одном с ним отделе? Для этого надо вначале создать таблицу, в которой будет поле с номером отдела: CREATE TABLE employers ( login_name CHAR(8), { системное имя } depat_num INTEGER, { номер отдела } ..............) Теперь надо написать запрос, который бы выбирал записи с полем depat_num равным значению этого поля у пользователя, выполняющего этот запрос: SELECT login_name, depat_num, ... FROM employers WHERE depat_num = (SELECT depat_num FROM employers WHERE login_name = USER) Если, например, таблица employers имеет следующие записи -------------T-----------T-----¬¦ login_name ¦ depat_num ¦.....¦+------------+-----------+-----+¦ nick ¦ 0 ¦ ¦¦ andy ¦ 4 ¦ ¦¦ kate ¦ 2 ¦ ¦¦ george ¦ 2 ¦ ¦¦ micky ¦ 4 ¦ ¦¦ roma ¦ 4 ¦ ¦L------------+-----------+------ то пользователь "george" при исполнении этого запроса увидит информацию только о себе и о пользователе "kate", пользователь "andy" увидит информацию о себе и пользователях "roma" и "micky", а пользователь "nick" получит данные только о себе. Теперь осталось только на основе данного оператора SELECT создать псевдотаблицу my_collegues: CRETATE VIEW my_collegues AS SELECT login_name, depat_num, ... FROM employers WHERE depat_num = (SELECT depat_num FROM employers WHERE login_name = USER) Теперь запросы вида SELECT login_name FROM my_collegues будут выдавать совершенно разные значения для разных пользователей. Для окончательного решения нашей задачи надо дать всем право на выборку из данной псевдотаблицы и отобрать всякие права на работу с исходной таблицей: GRANT SELECT ON my_collegues TO PUBLIC REVOKE ALL ON employers FROM PUBLIC В псевдотаблицу можно вставлять значения, обновлять данные и удалять записи, но только в том случае, если псевдотаблица образована из одной таблицы. Так, операторы UPDATE, INSERT, DELETE применимы для описанных выше псевдотаблиц my_collegues и cheap_items. Но если для определенния псевдотаблицы использовались поля из двух или более таблиц, то такая псевдотаблица может быть использована только в операторе SELECT. Пример определения псевдотаблицы, построенной из нескольких таблиц: CREATE VIEW pers_comp AS SELECT persons.lname, persons.fname, persons.sname, companies.name, companies.addresss FROM persons, companies WHERE persons.company = companies.company_id При создании VIEW можно дополнительно указать, что эта псевдотаблица будет с проверкой. Это означает, что при вставке, удалении или модификации записи будет проверяться условие WHERE в операторе SELECT, который образует данную псевдотаблицу. Для описания псевдотаблицы с проверкой надо указать ключевые слова WITH CHECK OPTION в операторе CREATE VIEW: CREATE VIEW <имя псевдотаблицы> AS <оператор выборки> WITH CHECK OPTION Например, в определенную выше псевдотаблицу cheap_items можно вставить запись с ценой, равной 500 единиц: INSERT INTO cheap_items (company, name, price) VALUES (101, "смокинг", 500) так как эта псевдотаблица создана без проверки. Правда, сразу после этого оператора вставки в данной псевдотаблице запись о смокинге ценой в 500 единиц будет все равно отсутствовать (при выборке эта запись будет отсечена по условию price<100). А если бы мы создали эту псевдотаблицу с проверкой, то SQL-сервер выдал бы сообщение об ошибке: CREATE VIEW cheap_items ASSELECT item_id, company, name, price FROM items WHERE price < 100.00 WITH CHECK OPTION Использование псевдотаблиц бывает полезным не только в случаях разграничения доступа, но и в случаях изменения структуры базы данных. Если при проектировании базы данных была допущена ошибка, или изменились условия задачи, то очень часто приходится переделывать структуру базы данных - менять структуру таблиц, вводить новые таблицы и т.д. Обычно это требует переписывать прикладную программу. Но если с помощью псевдотаблиц можно можно симитировать старую структуру, то ничего переписывать не надо. Кроме того, использования псевлотаблиц с проыеркой (WITH CHECK OPTION) является еще одним способом поддержания целостности базы данных. Удаляется псевдотаблицы оператором DROP VIEW <имя псевдотаблицы> Например: DROP VIEW cheap_itemsDROP VIEW employers Естественно, для удаления псевдотаблицы надо быть или ее собственником, или иметь привилегии администратора базы данных. 5.9.7. Синонимы Существуют случаи, когда по историческим причинам разным пользователям удобнее работать с одними и теми же таблицами (в том числе и псевдотаблицами), но под разнвми именами. Такое бывает при реорганизации базы данных и желании сохранить работающие программы без каких-либо изменений. SQL позволяет создавать синонимы для таблиц. Синонимы бывают публичными или личными. Публичный синоним доступен после создания всем пользователям. Личный синоним - тольо владельцу, то есть пльзователю, который его создал. Нельзя создать синоним на синоним. Публичный синоним создается оператором CREATE PUBLIC SYNONYM <имя синонима> FOR <имя таблицы> Личный синоним создается аналогично, только вместо ключевого слова PUBLIC используется слово PRIVATE: CREATE PRIVATE SYNONYM <имя синонима> FOR <имя таблицы> Личных синонимов с одинаковым именем может быть несколько, но они будут принадлежать разным пользователям и, соответсвенно, для каждого пользователя обозначать что-то свое. Примеры создания синонимов: CREATE PUBLIC SYNONYM goods FOR items CREATE PRIVATE SYNONYM tovary FOR items Для баз данных в режиме ANSI синонимы бывают только личными и при создании синонима слово PRIVATE указывать не надо. Имена синонимов могут использоваться в операторах GRANT и REVOKE при передаче или лишения права на доступ к таблице.. Удаляется синоним оператором DROP SYNONYM <имя синонима> 5.10. Управление одновременным доступом к данным Проблема доступа разных пользователей к одним и тем же данным заключается не только в том, что надо разграничивать возможность выполнить то или иное действие для разных лиц. Данная проблема имеет и другую сторону, а именно проблему синхронизации попыток прочитать и поменять данные. Здесь мы рассмотрим способы решения этой проблемы с помощью механизма блокировки и уровней изоляции. 5.10.1. Что бывает, когда несколько человек одновременно пытаются обновить одни и теже данные Рассмотрим пример простой базы данных, которая, например, содержит информацию о местах в самолете и используется в программе для бронирования билетов. Этой программой пользуется одновременно несколько кассиров, которые продают эти самые билеты. Когда кассиры, отвечая на вопрос "можно ли купить билет на такой-то рейс" просматривают базу данных, то есть только читают данные, но не обновляют их, проблем не возникает. Но представим себе, что два разных кассира почти одновременно обратились к базе данных и увидели, что на требуемый рейс есть одно свободное место. После этого они делают заказ. Но кто-то из них обязательно опоздает и ему будет сообщено, что "мест уже нет". Но эта ситуация не такая страшная - можно попытаться заказать билет на другой рейс. Гораздо хуже, если будет продано два билета на один и тот же рейс на одно и то же место. Другими словами, для многих задач надо гарантировать, чтобы то, что пользователь видит на экране соответствовало не тому, что было в базе пять минут назад, когда эти данные считывались, а тому, что есть в базе данных сейчас. Другая проблема, с которой приходиться сталкиваться при разработке многопользовательских систем - запрещение доступа к некоторым данным при проведении транзакции. Например, в банковской базе данных счетов программе надо перевести миллион с одного счета на другой. Для этого надо из одного счета вычесть миллион, а к другому прибавить. Каждое из этих действий по-отдельности не имеет смысла. То есть транзакция состоит из двух операторов обновления. Внутри этой транзакции, то есть в промежутке между этими операторами, состояние базы данных некорректное, поэтому нельзя позволять другим видеть промежуточные значения для модифицируемых данных. Если кто-то попытается подсчитать в этот момент баланс, то, как говорят бухгалтеры, дебит с кредитом не сойдется. Итак, рассмотрим те механизмы, которые есть в SQL, и которые используются для предотвращения описанных выше ситуаций. 5.10.2. Открытие базы данных только для себя Первый, самый простой, способ провести модификацию данных в базе, не опасаясь влияния других пользователей - это открыть базу данных для единоличного использования. Такое открытие базы данных еще называют открытием в исключительном режиме доступа. Это достигается оператором DATABASE <имя базы> EXCLUSIVE В случае выполнения этого оператора только Вы можете работать с базой данной и никто другой не может открыть ее. Если другой пользователь попытается получить доступ к данным из этой базы, он получит сообщение об ошибке. Точно так же, если указанная база данных уже открыта другим пользователем (как с указанием EXCLUSIVE, так и в обычном режиме), то Вы получите сообщение об ошибке. Действие данного оператора, так же как и обычного оператора открытия базы данных, завершается выполнением оператора CLOSE DATABASE Оператор DATABASE EXCLUSIVE используется достаточно редко. Он обычно применяется при каком-то существенном перестроении схемы базы данных. Для реализации многопользовательского доступа к данным существуют более мягкие и изящные способы - блокирование таблицы и уровни изоляции. Блокирование означает ограничение доступа других пользователей к некоторым ресурсам в течении некоторого промежутка времени, обычно небольшого. Например, вместо термина "открытие базы данных в эксклюзивном режиме" используют также термин "блокировка базы данных". Блокировка может быть разной. Тип блокировки определяется тем, что можно другим пользователям делать с заблокированным объектом, насколько ограничен доступ к этому объекту. 5.10.3. Блокирование таблицы Выше мы расмотрели способы ограничения доступа ко всей базе данных. При необходимости ограничить доступ других пользователей к некоторой таблице можно заблокировать только эту таблицу. Заблокировать таблицу можно двумя способами. Первый способ разрешает доступ других пользователей к заблокированной таблице на чтение данных. Такая блокировка называется "разделяемой". При втором способе любой доступ к таблице для других пользователей запрещен. Такая блокировка называется эксклюзивной (эксклюзивная блокировка аналогична открытию базы данных в исключительном режиме). Разделяемая блокировка на таблицу ставится оператором LOCK TABLE <имя таблицы> IN SHARE MODE Эксклюзивная блокировка на таблицу ставится оператором LOCK TABLE <имя таблицы> IN EXCLUSIVE MODE Для того, чтобы иметь право заблокировать таблицу, пользователь должен иметь право на выборку данных из этой таблицы. Одновременно можно заблокировать сразу несколько таблиц. Если таблица заблокирована в эксклюзивном режиме, то другой пользователь уже не может ее заблокировать. Если таблица заблокирована в разделяемом режиме, то другой пользователь тоже может заблокировать эту таблицу в разделяемом режиме. В случае двух или более одновременных блокировок обновлять данные может только тот пользователь, который начал это первым. Снятие блокировки производится оператором UNLOCK TABLE <имя таблицы> Блокировка с таблиц также снимается автоматически при закрытии базы данных (оператор CLOSE DATABASE) или при завершении (например, аварийном) пользовательской программы. Сервер автоматически ставит на таблицу эксклюзивную блокировку при модификации структуры данной таблицы (переименовании всей таблицы, переименовании отдельного поля и т.д.). Эта блокировка снимается автоматически при завершении данной операции. 5.10.4. Механизм блокирования записей и уровни изоляции Выше мы расмотрели блокировки на уровне базы данных и таблиц. Блокировка таких крупных объектов используется достаточно редко, так как такая блокировка накладывает серьезные ограничения на многопользовательский доступ. Кому нужна программа резервирования авиабилетов, если только один кассир может вносить изменения а остальные в этот момент времени могут только просматривать данные? Обычно в программах производится блокировка на уровне записей в таблице. Так как достаточно редко разные пользователи меняют одни и теже данные одновременно, то такой способ блокирования и гарантирует непротиворечивость данных, и обеспечивает реальный многопользователький режим работы. Если же все-таки два пользователя захотят обновить одновременно одну и ту же запись, то в SQL предусмотрены специальные средства разрешения подобных конфликтов (см. пункт 5.10.5 этого параграфа). В отличии от блокировки таблицы или всей базы, для блокировки записей нет специальных операторов. Блокировка записей производится сервером автоматически. Какие записи блокируются и как программа пользователя поступает с заблокированными записями определяется уровнем изоляции данной программы. Но в любом случае, измененная (или новая добавленная) запись блокируется до конца транзакции. Уровни изоляции, и это надо отметить, представляют собой очень красивое и изящное решение, сильно упростившее разработку программ. Установка того или иного уровня мзоляции сама по себе явно ничего не блокирует. Сам термин "уровень изоляции" введен потому, что он описывает механизм управления влиянием других процессов на Ваш конкретный процесс, на порядок исполнения Ваших запросов и на то, как Ваш процесс влияет на другие процессы.. Существуют следующие основные уровни изоляции: грязное чтение (DIRTY READ), достоверное чтение (COMMITTED READ), стабильный курсор (CURSOR STABILITY), многократное чтение (REPEATABLE READ). Уровень изоляции DIRTY READ означает отсутствие изоляции Вашего процесса от других. Программа в данном уровне изоляции может прочитать абсолютно все записи в той или иной таблице (естественно, с учетом прав на доступ). Обновить же она может только не заблокированные другим пользователями записи. Сама программа в данном уровне изоляции не накладывает никаких блокировок на прочитанные записи (но, естественно, накладывает блокировку на модифицированные записи). При данном уровне изоляции возможно появление так называемых фантомных рядов, то есть когда другой пользователь начал транзакцию, сделал какие-то изменения (появились фантомные ряды), а затем транзакция откатилась. Если в этот момент Ваша программа, имеющая данный уровень изоляции, будет просматривать базу, то она увидит и фантомные ряды. Например, если некоторая другая программа проводит транзакцию из двух операторов модификации - снятие с одного счета одного миллиона рублей и занесение этой суммы на другой счет, то при попытке подсчитать баланс при уровне изоляции, установленном в грязное чтение, можно получить ошибку в один миллион рублей. Уровень изоляции DIRTY READ является единственно допустимым для баз данных без транзакций, но может применяться и для баз данных с транзакциями. Уровень изоляции "грязное чтение" обеспечивает самую высокую производительность. Его обычно используют для доступа к данным, которые хранятся в малоизменяемых таблицах, например для выборки из таблиц с описанием классификаторов или для чтения таблиц с результатами проведенных экспериментов. Уровень изоляции COMMITTED READ (достоверное чтение) обеспечивает чтение данных, когда они не модифицируются другим процессом. Использование этого уровня изоляции обеспечивает Вам невозможность прочесть так называемые "фантомные" записи, которые появляется в процессе выполнения транзакции другим процессом. То есть если Ваша программа прочитала запись, то эта запись гарантирована существует. Однако, никаких блокировок на прочитанные записи при этом не делается, то есть другой процесс может прочитать эту же запись и удалить или изменить ее. Данный уровень изоляции является уровнем, принимаемым по умолчанию для баз данных с транзакциями. Использование достоверного чтения оправдано в программах аналитического плана, то есть в программах, практически не производящих модификацию базы данных, а занимающихся анализом существующих данных. К таким программам относятся, например, системы принятия решения. Уровень изоляции «Достоверное чтение», как и все более высокие уровни, описываемые ниже, возможен только для баз данных с транзакциями. Уровень изоляции CURSOR STABILITY (стабильный курсор) обеспечивает невозможность другим пользователям изменить прочитанную Вами запись до тех пор, пока Вы не прочтете другую запись. То есть, прочитанный ряд блокируется. Обычно последовательный просмотр записей реализуется с помощью механизма, называющегося "курсор", отсюда и название данного уровня изоляции. Стабильный курсор обычно используется в системах оперативной обработки транзакций для реализации рабочих мест по вводу и модификации данных. Например, естественно использовать этот уровень изоляции для реализации рабочих мест, связанных с перемещением каких-либо товаров, для рабочих мест операционистов в банке и т.д. Самый высший уровень изоляции - это REPETABLE READ (повторяемое чтение). Если программа использует данный уровень изоляции, то SQL-сервер заблокирует все записи, прочитанные данной программой в пределах транзакции. То есть уровень изоляции REPEATABLE READ означает запрещение изменения другими процессами всех записей, задействованных Вами в течении транзакции. Это означает, что Вы можете в течение одной транзакции многократно читать одни и те же записи и они гарантированно не могут быть изменены другими пользователями. Этот уровень изоляции является самым сильным и принимается по умолчанию для баз данных с транзакциями в режиме ANSI. Данный уровень изоляции используется в программах, предназначенных для проведения сложных транзакций, для которых очень существенно целостность, неизменность большого числа данных. Например, для рассмотренного выше примера с переводом денег с одного счета на другой использование данного уровня изоляции позволяет получить абсолютно правильный баланс, в котором никогда не будут отражены промежуточные операции. Оператор, с помощью которого можно установить требуемый уровень изоляции выглядит так: SET ISOLATION TO <уровень изоляции> Например: SET ISOLATION TO REPEATABLE READSET ISOLATION TO DIRTY READ Выбор того или иного уровня изоляции обусловлен необходимостью обеспечения требуемой безопасности Ваших данных. В то же время не следует завышать этот уровень, так как это ведет к дополнительным накладным расходам и, как следствие, может увеличить время реакции системы. Если это необходимо, то можно менять уровень изоляции впроцессе работы Вашей программы: SET ISOLATION TO DIRTY READSELECT ... INTO ... ..........................SET ISOLATION TO CURSOR STABILITYFOREACH my_cursor INTO var, var ..........................END FOREACH 5.10.5. Управление ожиданием снятия блокировок В реальных программах надо как-то уметь обрабатывать ситуации, когда пользовательская программа пытается обратитьться к заблокированным ресурсам (таблице, записи). Помимо стандартного управления ошибками (оператор WHENEVER, мы его рассмотрели в одной из предыдущих статей), существует специальный оператор установки режима ожидания SET LOCK MODE. Его синтакис: SET LOCK MODE TO WAIT <число секунд> SET LOCK MODE TO NOT WAIT Если Вы установили режим ожидание в "NOT WAIT" ("не ждать"), то если Ваша программа пытается обратиться к заблокированным ресурсам, то тут же получает сообщение об ошибке. Этот режим устанавливается по умолчанию. Если Вы установили режим ожидания в "WAIT" ("ждать") и не указали число секунд, то Ваша прогрмма будет ожидать разблокирования ресурсов до бесконечности. Если же при этом указать и число секунд, то Ваша программа будет ожидать разблокирования ресурсов указанное число секунд, и, если за указанное время этого не произошло, то она получает сообщение об ошибке, Например, если Вы хотите, что бы пользователь ждал не более 5 секунд, а затем получал сообщение о недоступности ресурсов, то надо выполнить оператор SET LOCK MODE TO WAIT 5 5.10.6. Тупиковые ситуации Теоретически, особенно при использовании режима ожидания до бесконечности, возможно возникновение тупиковой ситуации, клинча (английский термин deadlock - смертельные объятия). Это означает, что две или более программ заблокировали некоторые данные, но для продолжения работы им нужны данные, которые заблокированы другими программами. То есть программы ждут друг друга. Рассмотрим пример: пользователи А и В исполняют некоторые программы (соответсвенно программы А и В), работающие с одной и той же базой данных. Обе программы находятся в режиме бесконечного ожидания снятия блокировок (был выполнен оператор SET LOCK MODE TO WAIT). Программа А выполнила оператор LOCK TABLE table_a IN EXCLUSIVE MODE заблокировав, таким образом, таблицу table_a. Затем программа В выполняет оператор LOCK TABLE table_b IN EXCLUSIVE MODE и блокирует таблицу. Для дальнейшей работы программе А надо заблокировать таблицу table_b и она выполняет оператор LOCK TABLE table_b IN EXCLUSIVE MODE который не может быть сейчас выполнен (таблица table_b заблокирована программой В), следовательно программа А переходит в режим ожидания. Таким образом, программа А приостановлена и ждет от программы В когда та продолжит работу и разблокирует таблицу table_b. А в программе В исполняется оператор LOCK TABLE table_a IN EXCLUSIVE MODE который так же не может быть выполнен из-за блокироки таблицы table_a. Возникает ситуация, когда программа А ждет программу В, и наоборот. Аналогичная ситуация может возникать при проведении транзакций на уровне блокировки отдельных записей. Причем, не всегда эту ситуацию можно предусмотреть явно. SQL-сервер должен отслеживать эту ситуацию и каким-то образом проводить разблокирование. Например, SQL-сервер Informix при обнаружении подобной ситуации выдаст сообщение об ошибке и откатит транзакцию. 5.11. Повышение скорости обработки запросов. Язык SQL - не процедурный язык. Когда мы обсуждали, ЧТО делает тот или иной оператор, то никогда не обсуждалось, КАК это выполняется. Но SQL используется для написания реальных программ, с которыми работают реальные люди. Поэтому во многих случаях требуется обеспечить быстрое исполнение SQL-операторов. В данном параграфе будут рассмотрены способы управления скоростью исполнения тех или иных SQL-запросов. 5.11.1. Индексы Предположим, в нашей базе данных есть таблица persons, содержащая сведения о людях (ФИО и где работают:) CREATE TABLE persons ( person_id SERIAL UNIQUE, company INTEGER, lname CHAR(40), fname CHAR(30), sname CHAR(30), ); Если мы попытаемся определить, какие люди работают в фирме с уникальным номером 105 (то есть у какх записей в таблице persons поле company равно 105), нам надо будет выполнить запрос SELECT lname, fname, sname FROM persons WHERE company = 105 При исполнении этого запроса сервер базы данных должен будет просмотрть всю таблицу persons и для каждой записи из этой таблицы проверить условие "company = 105". Если в таблице persons несколько миллионов записей, то такой запрос может потребовать для своей обработки длительное время. Совсем не обязательно иметь таблицу в несколько миллионов записей, чтобы нагрузить сервер работой на несколько минут. Предположим, в таблице companies одна тысяча записей, а в таблице persons ­всего десять тысяч записей (в каждой фирме, в среднем, работает десять человек). Таблица compenies имеет следующую структуру: CREATE TABLE companies ( company_id SERIAL UNIQUE, name CHAR(40), address CHAR(40) ); Если мы будем искать всех людей и их рабочие адреса для фирм под названием "АО Рога и Копыта" и "АОЗТ Сделай Сам", то при исполнении запроса SELECT persons.lname, persons.fname, persons.sname, companies.address FROM persons, companies WHERE company.name IN ("АО Рога и Копыта", "АОЗТ Сделай Сам") AND persons.company = company.company_id сервер, в строгом соответствии с правилами обработки запросов, должен будет для каждой записи из таблицы persons просмотреть всю таблицу companies. Итого, для исполнения этого запроса, надо будет просмотреть десять миллионов возможных комбинаций (десять тысяч умножить на одну тысячу). Вернемся к первому примеру. Если бы записи были упорядочены по номеру компании, найти имя человека, работающего в фирме с номером 105 было бы достаточно быстро. Использование алгоритма двоичного поиска (деления пополам) позволяет найти нужную запись в миллионной таблице примерно на двадцатой попытке.) Однако в SQL нельзя предугадать, какие будут запросы к данной таблице, Если мы упорядочим таблицу persons по номерам компаний, скорость поиска по фамилии все равно останется низкой, а упорядочить одну и ту же таблицу по двум полям одновременно невозможно. Но в SQL есть способ повысить скорость исполнения определенных запросов. И этот способ основан на индексах. Например, если бы мы хотели повысить скорость поиска записей в таблице persons по полю company, то следовало бы создать индекс по данному поля в данной таблице: CREATE INDEX pers_comp_index ON persons(company) В общем случае, оператор создания индекса выглядит так: CREATE INDEX <имя индекса> ON <имя таблицы> (<имя поля>,<имя поля> ...) Имя индекса должно быть уникальным среди других имен в базе данных. То есть имя индекса не может совпадать с именем таблицы, с именем другого индекса и т.д. Примеры создания индексов: CREATE index1 ON persons(lname) CREATE comp_indx ON items(company, name) Для того, чтобы четко представлять, как работает индекс и в каких случаях SQL-сервер может использовать индекс для ускорения поиска, рассмотрим один из вариантов внутреннего представления индекса. Пусть у нас есть таблица persons с полями: ------------T---------T----------T---------¬¦ person_id ¦ company ¦ lname ¦ fname ¦+-----------+---------+----------+---------+¦ 1 ¦ 101 ¦ Антонов ¦ Сергей ¦¦ 2 ¦ 105 ¦ Шапокляк ¦ Алексей ¦¦ 3 ¦ 102 ¦ Антонов ¦ Антон ¦¦ 4 ¦ 101 ¦ Бендер ¦ Остап ¦L-----------+---------+----------+---------- Каждая запись в этой таблице реально где-то размещена. С местом ее размещения может быть однозначно связано какое-то значение. Если для хранения используется файл, как в сервере Informix-SE, то в качестве этого уникального значения может выступать смещение записи от начала файла. Если для хранения используется своя собственная файловая ситема, как, например, в Informix DS, то в качестве внутреннего идентификатора записи может выступать составной ключ (номер диска, номер сектора, смещение от начала сектора). Как выглядит это уникальное для каждой записи значение - абсолютно не важно, это внутреннее дело сервера. Очевидно, что такое значение всегда можно построить. Предположим, что это значение всегда присутствует для любой таблицы и выглядит как псевдополе с именем "rowid" (идентификатор записи). С учетом этого поля записи в данной таблице выглядят следующим образом: - - - - T-----------T---------T----------T---------¬ ¦ rowid ¦ person_id ¦ company ¦ lname ¦ fname ¦+ - - - +-----------+---------+----------+---------+¦ 1003 ¦ 1 ¦ 101 ¦ Антонов ¦ Сергей ¦¦ 1023 ¦ 2 ¦ 105 ¦ Шапокляк ¦ Алексей ¦¦ 1063 ¦ 3 ¦ 102 ¦ Антонов ¦ Антон ¦¦ 1053 ¦ 4 ¦ 101 ¦ Бендер ¦ Остап ¦L - - - +-----------+---------+----------+---------- При исполнении оператора создания индекса сервер создает набор записей, собственно и составляющих индекс. Каждая запись из индекса состоит из значений той таблицы, по которой построен этот индекс (естественно, хранятся только те поля, которые указаны в данном индексе - эти поля называют проиндексированными) и ссылки на физическое расположение записи в таблице (rowid этой записи). Записи из индекса упорядочены по индексируемым значениям. Например, для индекса pers_comp_indx, построенного по полю company таблицы persons набор составляющих его записей будет выглядеть следующим образом: -----------T---------¬¦ значение ¦ rowid ¦+----------+---------+¦ 101 ¦ 1003 ¦¦ 101 ¦ 1053 ¦¦ 102 ¦ 1063 ¦¦ 105 ¦ 1023 ¦L----------+---------- Тогда при исполнении запроса SELECT .... FROM persons WHERE company = 105 сервер определит, что для данного оператора запроса может быть использован индекс. Затем сервер просмотрит список доступных индексов и обнаружит, что существует индекс по полю, использованному в условии данного оператора. Затем сервер просмотрит этот индекс и определит, что данному условию соответствует запись с rowid равным 1023. Просмотр индекса, за счет того, что он упорядочен, займет существенно меньше времени, чем последовательный просмотр всех записей в исходной таблице. Последним действием сервера при исплнении этого запроса будет считыванрие из таблицы persons записи с rowid, равным 1023. По этому значению сервер определит местоположение записи и без просмотра остальных данных сразу прочитает нужную запись. Очевидно, рассмотренный нами индекс pers_comp_indx сможет быть использован сервером только для тех запросов по таблице persons, где в условии используется поле company. Для следующего запроса, например, данный индекс будет бесполезен: SELECT company FROM persons WHERE lname = "Бендер" Если подобные запросы возникают часто, то для того, чтобы убыстрить их обработку, надо создать индекс и по полю lname: CRATE INDEX second_index ON persons(lname) Можно создавать индексы и по комбинации полей. Например, если часто проводится поиск по комбинации фамилия-имя, то имеет смысл создать соответствующий индекс: CREATE INDEX pers_names ON persons(lname, fname) Заметим, что порядок, в котором указаны поля для составного индекса, существенен. Так, определенный нами индекс по комбинации фамилия-имя будет отличаться от индекса по комбинации имя-фамилия: CREATE INDEX pers_names2 ON persons(fname, sname) Кстати, если создан составной индекс по полям (lname, sname), то отпадает необходимость в индексе по полю lname (но не в индексе по полю sname). То есть, если создан индекс по некоторой последовательности полей (a1, a2, ... aN), то он функционально покрывает индекс по последовательности полей (a1, a2, ... aK), если K < N. У оператора создания индекса помимо базового варианта есть модификации. Более полный синтаксис оператора создания индекса выглядит так: CREATE [UNIQUE] [CLUSTER] INDEX <имя индекса> ON <имя таблицы> (<имя поля>, <имя поля> ...) Ключевое слово UNIQUE (вместо него можно использовать слово DISTINCT) означает, что в таблице не допускаются одинаковые наборы значений по совокупности полей, указанным в индексе. Другими словами, это способ следить за уникальностью указанного набора полей. Поэтому такой индекс называется уникальным. Использование уникального индекса функционально идентично заданию уникального ключа в таблице за тем исключением, что для добавления уникального индекса надо иметь привилегии RESOURCE, а для указания уникального ключа в структуре таблицы - быть владельцем таблицы, иметь права администратора или иметь привилегию на модификацию таблицы. Пример: CREATE UNIQUE INDEX my_index ON persons (person_id, company) Ключевое слово CLUSTER означает создание так называемого кластерного индекса, который не существует в виде отдельного набора данных (как в предыдущем примере), а реализуется путем физического переупорядочивания записей в таблице. Естественно, для каждой таблицы может существовать только один кластерный индекс. Этот индекс, в отличии от всех других, не требует для своего хранения дополнительного выделения памяти. Пример создания кластерного индексак: CREATE CLUSTER INDEX my_2nd_index ON persons (lname, fname) Использование индекса значительно повышает скорость поиска записей при выполнения операторов с логическим условием (то есть для операторов SELECT, DELETE или UPDATE с условием WHERE). При этом, естественно, нужно, чтобы сервер базы данных мог использовать существующие индексы для поиска записей, удовлетворяющих условию в разделе WHERE. С другой стороны, индексы, за исключением кластерных, требуют дополнительных затрат памяти. Кроме того, при выполнении операций, изменяющих содержимое базы данных (INSERT, UPDATE, DELETE), если изменения затрагивают проиндексированные поля, требуется дополнительное время на перестройку индекса. Но так как во многих задачах бОльшая часть обращений к базе данных связана с поиском информации, индексы позволяют значительно увеличить общую производительность информационной системы. Использование индексов в SQL принципиально отличается от того, как они применяются в индексно-последовательных СУБД типа dBase, Clipper или FoxPro. В случае SQL-сервера, поддержание целостности индекса и его использование - это внутренняя задача самого сервера. У пользователя или программиста, работающего с SQL-сервером, есть только возможность создать (оператор CREATE INDEX) и удалить (оператор DROP INDEX) индекс, а также изменить его структуру (с помощью оператора ALTER INDEX, который здесь не рассматривается). Оператор удаления индекса имеет следующий синтаксис: DROP INDEX <имя индекса> Естественно, для удаления индекса надо быть или его владельцем, или иметь права администратора. 5.11.2. Буферизация журнала транзакций Обычно, при завершении транзакции (оператор COMMIT WORK) происходит принудительный сброс обновленной базы данных на внешний носитель (жесткий диск). Причем информация о проведенной транзакции дополнительно записывается в специальный файл - журнал транзакций. Если этот журнал и обновленное содержимое базы данных не сбрасывать на диск по окончании транзакции, а буферизировать в памяти, то в случае падения напряжения питания или сбоя аппаратуры информация о нескольких последних завершенных транзакциях может быть потеряна. Целостность базы данных при этом гарантированно сохранится, однако ее содержимое будет соответсвовать или последней завершенной транзакции, или предпоследней, или пред…предпоследней. Для банковских задач буферизация журнала транзакций, наверное, неприемлима, но для целого ряда других задакч, например регистрации каких-либо параметров, подлежащих статистической обработке. - вполне допустима. Операции обмена с жестким диском - одно из самых узких мест в современных вычислительных системах (с точки зрения производительности). Поэтому буферизация журнала транзакций может заметно повысить общую производительность системы. Естественно, повышать производительность в ущерб надежности мы не рекомендуем, но если задача допускает буферизацию журнала транзакций, то при создании базы данных это можно указать с помощью оператора CREATE DATABASE: CREATE DATABASE <имя базы данных> WITH BUFFERED LOG Буферизация журнала транзакций позволяет в несколько раз повысить скорость исполнения операторов INSERT, UPDATE и DELETE, но при этом практически не влияет на скорость работы оператора SELECT. 5.11.3. Блокировка на уровне записей и страниц При рассмотрении уровней изоляции предполагалось, что блокировки ставятся на уровне записей (то есть блокируется отдельная запись). На самом деле, сервера Informix Dynamic Server позволяют выбирать уровень блокировки между блокировкой на уровне записи и блокировкой на уровне страницы. Практически во всех SQLсерверах (в том числе, и Informix Dynamic Server) память под таблицы выделяется порциями фиксированного объема - страницами или группами страниц. Обычно размер страницы составляет 2 или 4 килобайта. В целях повышения производительности можно блокировать записи не по одной, а целыми страницами.