8 февраля [Лекции]

Интерфейс ODBC

ODBC (Open Database Connectivity) – широко распространенный программный интерфейс фирмы Microsoft, удовлетворяющий стандартам ANSI и ISO для интерфейсов обращений к базам данных (Call Level Interface, CLI). Для доступа к данным конкретной СУБД с помощью ODBC необходим ODBC Administrator – приложение, позволяющее определить, какие источники данных доступны для данного компьютера с помощью ODBC, и ODBC-драйвер для доступа к выбранной СУБД. ODBC-драйвер представляет собой динамически загружаемую библиотеку (DLL), которую клиентское приложение использовать для доступа к источнику данных. Для каждой используемой СУБД нужен собственный ODBC-драйвер, так как ODBC-драйверы используют функции клиентских API, разные для различных СУБД.

С помощью ODBC можно манипулировать данными любой СУБД, для которой имеется ODBC-драйвер. Для манипуляции данными можно использовать как вызовы ODBC API, так и другие универсальные механизмы доступа к данным, например OLE DB или ADO, реализующие стандартные функции или классы на основе вызовов ODBC API в драйверах или провайдерах, специально предназначенных для работы с любыми ODBC-источниками.

Спецификация ODBC подразумевает несколько стандартов на ODBC-драйверы (обычно в этом случае используют термины Level 1, Level 2 и так далее). Эти стандарты отличаются различной функциональностью, которая должна быть реализована в таком драйвере. Например, драйверы, соответствующие стандарту Level 1, не обязаны поддерживать работу с хранимыми процедурами, а некоторые ODBC-драйверы не поддерживают двухфазное завершение транзакций (применяемое в том случае, когда требуется согласованное изменение данных в нескольких различных серверных СУБД).

Архитектура ODBC

Архитектура ODBC представлена четырьмя компонентами:

• приложение-клиент, выполняющее вызов функций ODBC;

• менеджер драйверов, загружающий и освобождающий ODBC-драйверы, которые требуются для приложений-клиентов. Менеджер драйверов обрабатывает вызовы ODBC-функций или передает их драйверу;

• ODBC-драйвер, обрабатывающий вызовы SQL-функций, передавая SQL-серверу выполняемый SQL-оператор, а приложению-клиенту - результат выполнения вызванной функции;

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

Основная задача менеджера драйверов – загрузка соответствующего подключаемому источнику данных драйвера, а также инкапсуляция взаимодействия с различными типами источников данных посредством применения различных ODBC-драйверов.

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

• управление коммуникационными протоколами между приложением-клиентом и источником данных;

• управление запросами к СУБД;

• выполнение передачи данных от приложения-клиента в СУБД и из базы данных в приложение-клиент;

• возвращение приложению-клиенту стандартной информации о выполненном вызове ODBC-функции в виде кода возврата;

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

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

8 февраля [Лекции]

Интерфейсы доступа к базам данных: ODBC, OLE DB, ADO

Язык SQL условно разделяют на интерактивный и встроенный. И если интерактивный SQL функционирует в среде СУБД, то для встроенного языка требуются дополнительные средства связи среды, в которую он встроен, с СУБД: для передачи команд SQL и получения результатов выполнения запросов. Именно этим целям служат интерфейсы доступа к базам данных.

Существует несколько основных способов доступа к данным. Подавляющее большинство систем управления базами данных содержит в своем составе библиотеки, предоставляющие специальный прикладной программный интерфейс (Application Programming Interface, API) для доступа к данным этой СУБД. Обычно такой интерфейс представляет собой набор функций, вызываемых из клиентского приложения. В случае настольных СУБД эти функции обеспечивают чтение/запись файлов базы данных, а в случае серверных СУБД инициируют передачу запросов серверу базы данных и получение от сервера результатов выполнения запросов. Библиотеки, содержащие API для доступа к данным серверной СУБД, обычно входят в состав ее клиентского программного обеспечения, устанавливаемого на компьютерах, где функционируют клиентские приложения.

Использование клиентского API является наиболее очевидным способом манипуляции данными в приложении. Однако, в этом случае приложение сможет использовать данные СУБД только того производителя, чьи функции используются, а замена ее на другую повлечет за собой необходимость переписывания значительной части кода приложения (клиентские API и объектные модели не подчиняются никаким стандартам и различны для разных СУБД). Другой способ манипуляции данными в приложении основан на использовании универсальных механизмов доступа к данным.

Универсальный механизм доступа к данным обычно реализуется в виде библиотек и дополнительных модулей, называемых драйверами или провайдерами. Библиотеки предоставляют определенный набор функций, а дополнительные модули осуществляют связь с конкретными СУБД с использованием их API. Наиболее популярными среди универсальных механизмов доступа являются Open Database Connectivity (ODBC), OLE DB и ActiveX Data Objects (ADO).

Вместе интерфейсы ODBC, OLE DB и ADO составляют похожую на иерархическую структуру, где каждый последующий интерфейс может обращаться к данным не только непосредственно, но и с использованием интерфейса более низкого уровня.

8 февраля [Лекции]

Безопасность баз данных. Права доступа. Учетные записи, роли, пользователи, полномочия. Управление безопасностью.

Современные СУБД поддерживают один из двух наиболее общих подходов к вопросу обеспечения безопасности данных: избирательный и обязательный. В обоих случаях «объектом данных», для которого обеспечивается безопасность, может быть как база данных в целом, так и любой ее внутренний объект (например, таблица). А описание совокупности разрешенных операций с теми или иными объектами данных составляет права доступа.

В случае избирательного подхода каждый из пользователей обладает различными правами (привилегиями или полномочиями) при работе с объектами данных. При этом разные пользователи могут обладать разными правами доступа при работе с одним и тем же объектом. Очевидным образом, избирательные права характеризуются значительной гибкостью.

В случае обязательного подхода, наоборот, уже объектам данных присваиваются определенные классификационные уровни, а пользователь обладает некоторым уровнем допуска, на основании которого доступом к определенным объектам данных обладают только пользователи с соответствующим уровнем допуска.

Для реализации избирательного подхода в базу данных вводится новый тип объектов – пользователь. Каждому пользователю присваивается уникальный идентификатор (имя), а также для дополнительной защиты каждый пользователь снабжается уникальным паролем. При этом, если идентификаторы (имена) пользователей доступны для просмотра администратору базы данных, то пароли хранятся в специальном закодированном виде и доступны только самим пользователям. Данная информация хранится в базе данных в виде учетной записи. Здесь необходимо отметить, что доступ к базе данных могут осуществлять одновременно несколько источников, формально (с точки зрения имени и пароля) являющиеся одним и тем же пользователем.

Пользователи могут быть объединены в группы. Любой из пользователей может входить сразу в несколько групп. Стандартом определяется понятие группы PUBLIC, для которой должен быть определен стандартный минимальный набор прав. Предполагается, что каждый вновь создаваемый пользователь, если не указано другое, относится к группе PUBLIC. Наличие групп позволяет упростить управление полномочиями, когда их можно изменять не для каждого отдельного пользователя, а для целой группы сразу.

Набор действий (операций), которые пользователь может выполнять над объектами данных, составляет его полномочия (или привилегии).

В современных коммерческих СУБД появилось также новое понятие роль. Ролью является именованный (снабженный именем) набор полномочий. Помимо ряда стандартных ролей, определяемых в момент установки сервера базы данных, имеется возможность создания новых ролей, группируя в них произвольные полномочия. Введение ролей позволяет упростить управление полномочиями пользователей, а также структурировать этот процесс. Кроме того, введение ролей не связано с конкретными пользователями, поэтому роли могут быть определены и сконфигурированы до определения пользователей системы. Каждому пользователю может быть назначена одна или несколько ролей.

Элементарные концепции обеспечения безопасности базы данных исключительно просты. Существуют два фундаментальных принципа: проверка полномочия и проверка подлинности (аутентификация).

Проверка полномочий определяет допустимость выполнения пользователем или процессом того действия над определенным объектом данных, которое он собирается выполнить. Проверка подлинности означает достоверное подтверждение, что пользователь или процесс, пытающийся выполнить санкционированное действие, является именно тем, за кого он себя выдает.

Система назначения полномочий имеет в некотором роде иерархическую структуру. Наибольшими правами и полномочиями обладает администратор сервера базы данных. Традиционно только этот тип пользователей может создавать других пользователей и наделять их определенными правами. Как уже говорилось, СУБД хранит в своих системных каталогах не только описание пользователей, но и описание их привилегий по отношению ко всем объектам.

Каждый объект в базе данных имеет владельца – пользователя, который создал данный объект. Владелец объекта обладает всеми правами (полномочиями) на данный объект. В том числе он имеет право предоставлять другим пользователям полномочия по работе с этим объектом или забирать ранее предоставленные полномочия.

В ряде СУБД вводится также понятие администратора базы данных. Это имеет место в тех СУБД, где один сервер может управлять множеством баз данных (например, MS SQL Server, Sybase). В СУБД Oracle применяется однобазовая архитектура, поэтому там вводится понятие подсхемы – части общей схемы базы данных и вводится пользователь, имеющий доступ к подсхеме.

В стандарте SQL не определена команда создания пользователя. Однако, практически все коммерческие СУБД позволяют создавать пользователя не только в интерактивном режиме, но и программно с помощью специальных хранимых процедур. Однако, для выполнения этой процедуры необходимо обладать соответствующими правами.

С другой стороны, в стандарте SQL определены две команды для работы с правами: GRANT (предоставить права) и REVOKE (лишить прав).

Оператор предоставления прав GRANT имеет следующий синтаксис:

GRANT {список_действий | ALL PRIVILEGES}

ON имя_объекта TO {имя_пользователя | PUBLIC}

[WITH GRANT OPTION]

Список действий определяет набор допустимых действий из общедопустимого перечня действия для данного объекта данных. Использование значения ALL PRIVILEGES означает, что будут разрешены все действия из перечня допустимых для данного объекта данных.

Имя объекта задает имя конкретного объекта данных: таблицы, представления, хранимой процедуры или триггера. Имя пользователя определяет пользователя, кому будут предоставлены права. Использование значения PUBLIC предоставит права всем пользователям группы PUBLIC.

Необязательный параметр WITH GRANT OPTION определяем режим, при котором передаются не только права на указанные действия, но и право передавать эти права другим пользователям. При этом передавать права пользователь сможет только в рамках разрешенных ему действий.

Например, общий вид оператора назначения привилегий для объекта типа таблица будет имеет следующий синтаксис:

GRANT {[SELECT][, INSERT][, DELETE][, UPDATE (список_столбцов)]}

ON имя_таблицы

TO {имя_пользователя | PUBLIC}

[WITH GRANT OPTION]

В качестве практического примера рассмотрим следующую ситуацию. Пусть пользователь admin создал таблицу students базы данных, в которую пользователь dean (декан) должен иметь возможность вносить новые записи, а пользователь professor (преподаватель) – просматривать записи. Тогда для предоставления соответствующих прав необходимо выполнить две следующие операции:

GRANT INSERT

ON students

TO dean

GRANT SELECT

ON students

TO professor

При предоставлении прав на изменение столбцов можно уточнять, какие столбцы разрешено изменять. Например, пусть пользователю dean разрешено изменять номер группы студента, который хранится в столбце group_num. Тогда предоставление нужных прав выполняется следующей операцией:

GRANT UPDATE (group_num)

ON students

TO dean

Если предполагается, что в случае отсутствия администратора его обязанности по надзору за таблицей students будет замещать пользователь senior_user, то передача прав будет осуществляться следующей командой:

GRANT ALL PRIVILEGES

ON students

TO senior_user

WITH GRANT OPTION

В этом случае senior_user может сам предоставить привилегии, например, другому пользователю secretary:

GRANT UPDATE (group_num)

ON students

TO secretary

Если же передача прав пользователю senior_user была бы ограниченной:

GRANT SELECT, INSERT, DELETE

ON students

TO senior_user

WITH GRANT OPTION

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

С точки зрения безопасности также эффективным способом защиты данных может быть создание представлений, которые будут содержать только необходимые столбцы для работы конкретного пользователя, и предоставление прав на работу пользователю только с этим представлением. Однако, так как представления могут соответствовать итоговым запросам, то для этих представлений недопустимы операции изменения, поэтому набор допустимых операций ограничится операцией SELECT. Если же представления соответствуют выборке из базовой таблицы, то для такого представления допустимыми будут все 4 операции: SELECT, INSERT, UPDATE и DELETE.

Отмена ранее присвоенных привилегий осуществляется с помощью оператора REVOKE. Синтаксис его использования следующий:

REVOKE {список_операций | ALL PRIVILEGES}

ON имя_объекта

FROM {список_пользователей | PUBLIC}

{CASCADE | RESTRICT}

Параметры CASCADE и RESTRICT определяют способ отмены привилегий. Параметр CASCADE отменяет привилегии не только непосредственно указанного в запросе пользователя, но и всех тех пользователей, которым он мог передать права, используя параметр WITH GRANT OPTION.

Следующий запрос отменит привилегии не только пользователя senior_user, но и пользователя secretary, которому он эти права передал уже сам:

REVOKE ALL PRIVILEGES

ON students

FROM senior_user CASCADE

Параметр RESTRICT ограничивает отмену привилегий только тем пользователем, который непосредственно указан в запросе. Однако, он не будет выполнении при наличии пользователя, которому были переданы права пользователем, указанным в запросе. Например, запрос в следующем примере не будет выполнен, так как senior_user передал часть прав пользователю secretary:

REVOKE ALL PRIVILEGES

ON students

FROM senior_user RESTRICT

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

REVOKE UPDATE

ON students

FROM senior_user, secretary

Для других объектов базы данных список операций, которые используются в операторах GRANT и REVOKE, изменяется.

По умолчанию действие, соответствующее исполнению (запуску) хранимой процедуры, назначается всем членам группы PUBLIC. Изменить это условие после создания хранимой процедуры можно следующей командой:

REVOKE EXECUTE

ON count_ex

FROM PUBLIC CASCADE

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

GRANT EXECUTE

ON count_exams

TO senior_user

Также администратор может разрешить некоторому пользователю создавать и изменять таблицы в некоторой базе данных. Оператор предоставления прав в этом случае может выглядеть следующим образом:

GRANT CREATE TABLE, ALTER TABLE, DROP TABLE

ON db_university

TO admin_deputy

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

GRANT CREATE DATABASE

ON server

TO admin_deputy

8 февраля [Лекции]

Управление транзакциями. Типы транзакций. Типы блокировок. Управление блокировками: получение информации о процессах и блокируемых ресурсах. Команды создания, завершения, отката транзакции. Команды управления транзакциями.

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

С точки зрения пользователя транзакция рассматривается как некоторое неделимое на части действие над базой данных. Проще говоря, транзакция – это некоторый набор операций, выполнение только части которого либо бессмысленно, либо попросту ошибочно.

Простым примером может служить пример транзакции в банковской сфере, когда требуется некоторую сумму денег перевести с одного счета на другой. Если между операциями списывания денег с одного счета и зачисления денег на другой счет произойдет ошибка, то один из участников транзакции (владелец одного из счетов) окажется в незавидной ситуации, так как бессмысленно и только списание денег с одного счета, и только зачисления денег на другой счет.

Существуют различные виды транзакций. В настоящее время выделяют три типа транзакций: плоские (или классические) транзакции, цепочечные транзакции и вложенные транзакции.

Вложенными называют транзакции, которые были инициированы внутри еще не завершенной транзакции. При использовании вложенных транзакций каждая такая транзакция должна быть фиксирована явно. Однако, окончательная фиксация данных такой вложенной транзакции является условной, так как внешняя транзакция может быть откачена. Результаты работы вложенных транзакций будут окончательно зафиксированы, только если произойдет фиксация внешней транзакции. Аналогично, откат любой из вложенных транзакций приведет к откату и той транзакции, в которую она была вложена.

Плоские (или традиционные) транзакции характеризуются четырьмя классическими свойствами: атомарности, согласованности, изолированности, долговечности (прочности). По-английски эти свойства называются Atomicity, Consistency, Isolation и Durability, сокращенно ACID. Поэтому традиционные транзакции иногда также называют ACID-транзакциями.

Свойство атомарности (Atomicity) выражается в том, что транзакция должна быть выполнена в целом (полностью) или не выполнена вообще.

Свойство согласованности (Consistency) гарантирует, что по мере выполнения транзакций данные переходят из одного согласованного состояния в другое, то есть транзакция не разрушает взаимной согласованности данных.

Свойство изолированности (Isolation) означает, что конкурирующие за доступ к базе данных транзакции фактически обрабатываются последовательно, изолированно друг от друга, хотя для пользователя их выполнение может выглядеть параллельным.

Свойство долговечности (Durability) означает, что изменения в данных, которые были произведены успешно завершенной транзакцией, не могут быть потеряны, даже в случае последующих ошибок.

Свойство изолированности заслуживает особого внимания. В идеальном варианте транзакции разных пользователей должны выполняться так, чтобы у пользователя создавалась иллюзия, что он один в системе. Простейший способ обеспечить абсолютную изолированность состоит в том, чтобы выстроить транзакции в очередь и выполнять их строго одну за другой. Однако, при этом теряется эффективность работы системы. Поэтому реально одновременно выполняется несколько транзакций (смесь транзакций), что может приводить к конфликтным ситуациями.

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

• неповторяющееся чтение (non-repeatable read);

• "грязное" чтение (dirty read) - чтение данных, которые были записаны откатанной транзакцией;

• потерянное обновление (lost update);

• фантомная вставка (phantom insert).

Рассмотрим каждую из ситуаций, в которых возможно возникновение данных проблем, подробнее.

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

Транзакция 1 Транзакция 2

SELECT F2 FROM T1 WHERE F1=1; SELECT F2 FROM T1 WHERE F1=1;

UPDATE T1 SET F2=F2+1 WHERE F1=1;

SELECT F2 FROM T1 WHERE F1=1;

В транзакции 2 выбирается значение поля F2, после чего в транзакции 1 изменяется значение этого поля. Тогда при повторной попытке выбора значения из поля F2 в транзакции 2 будет получен другой результат. Эта ситуация особенно опасна в том случае, когда данные считываются с целью их частичного изменения и обратной записи в базу данных.

«Грязное» чтение: возьмем две транзакции, которые были открыты различными приложениями, и в которых выполняются следующие SQL-операторы:

Транзакция 1 Транзакция 2

SELECT F2 FROM T1 WHERE F1=1;

UPDATE T1 SET F2=F2+1 WHERE F1=1;

SELECT F2 FROM T1 WHERE F1=1;

ROLLBACK WORK;

В транзакции 1 изменяется значение поля F2, которое затем выбирается в транзакции 2. После этого происходит откат транзакции 1. В результате значение, полученное второй транзакцией, будет отличаться от значения, хранимого в базе данных.

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

Транзакция 1 Транзакция 2

SELECT F2 FROM T1 WHERE F1=1; SELECT F2 FROM T1 WHERE F1=1;

UPDATE T1 SET F2=20 WHERE F1=1;

UPDATE T1 SET F2=25 WHERE F1=1;

В транзакции 1 изменяется значение поля F1, а затем в транзакции 2 также изменяется значение этого поля. В результате изменение, выполненное первой транзакцией, будет потеряно.

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

Транзакция 1 Транзакция 2

SELECT SUM(F2) FROM T1;

INSERT INTO T1 (F1, F2) VALUES (15, 20);

SELECT SUM(F2) FROM T1;

В транзакции 2 выполняется SQL-оператор, использующий все значения поля F2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомной вставкой и является частным случаем неповторяющегося чтения. При этом, если выполняемый SQL-оператор выбирает не все значения поля F2, а только значение одной строки таблицы (используется предикат WHERE), то выполнение оператора INSERT не приведет к ситуации фантомной вставки (если новая строка не будет удовлетворять условию фильтрации).

Для предотвращения возникновения описанных конфликтных ситуаций в стандарте SQL-92 определили уровни изоляции транзакций:

• SERIALIZABLE – последовательное выполнение транзакций (используется по умолчанию). Этот уровень гарантирует предотвращение всех описанных выше конфликтных ситуаций, но при нем наблюдается самая низкая степень параллелизма;

• REPEATABLE READ – повторяющееся чтение. На этом уровне разрешено выполнение операторов INSERT, приводящих к конфликтной ситуации «фантомная вставка». Этот уровень целесообразно использовать, если на выполняющиеся SQL-операторы не влияет добавление новых строк;

• READ COMMITED – фиксированное чтение. Этот уровень изоляции позволяет получать разные результаты для одинаковых запросов, но только после фиксации транзакции, повлекшей изменение данных;

• READ UNCOMMITED – нефиксированное чтение. Здесь возможно получение разных результатов для одинаковых запросов без учета фиксации транзакции.

Уровень изоляции Предотвращение конфликтной ситуации

неповторяющееся чтение (non-repeatable read) "грязное" чтение (dirty read) потерянное обновление (lost update) фантомная вставка (phantom insert)

SERIALIZABLE + + + +

REPEATABLE READ + + + -

READ COMMITED - + + -

READ UNCOMMITED - - + -

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

Блокировки могут быть применены к разным типам объектов базы данных. Самый крупный из них – вся база данных целиком, однако в этом случае база данных будет недоступна для всех других приложений, работающих с ней. Следующий тип объектов для блокировки – таблицы. В ряде СУБД реализована блокировка на уровне страниц и даже отдельных кортежей (строк). Для повышения производительности также используют комбинирование различных видов блокировки.

Выделяют два типа блокировок:

• совместный режим блокировки – означает разделяемый захват и требуется для выполнения операций чтения, объекты, заблокированные таким образом, не изменяются в процессе выполнения транзакции и доступны другим транзакциям, но только в режиме чтения;

• монопольный режим блокировки – предполагает монопольный захват объекта и требуется для выполнения операция «записи» (добавление записей, удаление, модификация). Объекты, заблокированные таким образом, недоступны для других транзакций до момента завершения работы текущей транзакции.

Однако, помимо решения некоторых проблем использование блокировок добавило и несколько новых – блокирование (blocking) и взаимоблокировку (deadlock). Они могут замедлять и даже останавливать работу.

Блокирование возникает в том случае, когда одна транзакция владеет блокировкой по какому-либо ресурсу, а второй транзакции требуется конфликтный тип блокировки по этому ресурсу. Вторая транзакция должна ждать, пока первая транзакция освободит свою блокировку; иными словами, она блокирована первой транзакцией. Проблема блокирования обычно возникает, когда какая-либо транзакция захватывает блокировку на длительный период времени, что приводит к цепочке блокированных транзакций, ожидающих окончания других транзакций, чтобы получить необходимые им блокировки; это состояние называют цепным блокированием.

Взаимоблокировка отличается от блокированной транзакции в том, что взаимоблокировка возникает в случае двух блокированных транзакций, ожидающих друг друга. Например, одна транзакция владеет монопольной блокировкой по Таблице 1, а вторая владеет монопольной блокировкой по Таблице 2. Прежде чем будет освобождена любая монопольная блокировка, первой транзакции требуется блокировка по Таблице 2 и второй транзакции – требуется блокировка по Таблице 1. В результате каждая транзакция ждет, пока другая транзакция освободит свою монопольную блокировку, и ни одна из транзакций не может завершиться, поскольку для продолжения работы ей требуется блокировка, которой владеет другая транзакция.

Поиск взаимоблокировок ведется с помощью построения графа транзакций. Наличие в этом графе замкнутых циклов будет означать неразрешимую взаимоблокировку. В случае выявления такой взаимоблокировки СУБД принимает решении о том, какой из транзакций необходимо «пожертвовать», чтобы разрешить конфликтную ситуацию. Данное решение принимается на основании сложной весовой формулы, учитывающей приоритет, время выполнения и множество других факторов. В результате транзакция с наименьшим весом откатывается.

В языке SQL определен оператор явной блокировки таблицы LOCK TABLE. Его синтаксис следующий:

LOCK TABLE имя_таблицы IN { SHARED | EXCLUSIVE } MODE

При использовании оператора обязательно указывается имя таблицы, а также один из двух возможных типов блокировки. Параметр SHARED означает совместный режим блокировки, а EXCLUSIVE – монопольный режим.

Транзакции начинаются с выполнения первой исполняемой команды SQL и заканчиваются либо фиксацией транзакции (фиксацией изменений в базе данных), либо отказом от фиксации (откатом). Таким образом, окончанием транзакции может служить одно из следующих событий:

- команда COMMIT (фиксации транзакции) или ROLLBACK (откат);

- успешное завершение программы, инициировавшей текущую транзакцию (автоматическая фиксация);

- ошибочное завершение программы, инициировавшей текущую транзакцию (автоматический откат).

При выполнении команд COMMIT и ROLLBACK языка SQL происходит явная обработка транзакций, при этом обеспечивается согласованность данных, и появляется возможность проверить изменения в данных до момента, когда они станут постоянными. В свою очередь неявная обработка транзакций приводит к автоматической фиксации или автоматическому откату изменений.

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

До выполнения команды COMMIT или ROLLBACK:

- предыдущее состояние базы данных может быть восстановлено, так как изменения производятся не в самой базе, а в буфере базы данных;

- текущий пользователь может просмотреть результаты операций DML с помощью оператора SELECT;

- другие пользователи не могут видеть результаты команд DML, выполняемых текущим пользователем;

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

После выполнения команды COMMIT:

- измененные данные вносятся фактически в базу данных;

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

- все пользователи могут видеть результаты произведенных изменений;

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

- все точки сохранения стираются.

После выполнения команды ROLLBACK:

- все незавершенные изменения отменяются;

- фактически данные в базе данных возвращаются в прежнее состояние;

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

Согласно определенной в стандарте ANSI/ISO SQL модели транзакций каждый оператор, который изменяет состояние базы данных, рассматривается как транзакция, поэтому при успешном завершении этого оператора база данных переходит в новое устойчивое состояние. В дальнейшем в СУБД SYBASE была предложена расширенная модель транзакций, включившая ряд дополнительным операций. В модели SYBASE используются следующие четыре оператора:

- BEGIN TRANSACTION – сообщает о начале транзакции, в отличие от модели ANSI/ISO в модели SYBASE начало транзакции задается явно с помощью оператора начала транзакции;

- COMMIT TRANSACTION – сообщает об успешном завершении транзакции, оператор эквивалентен COMMIT в модели ANSI/ISO, фиксирует все изменения, которые были произведены в базе данных в ходе выполнения транзакции;

- SAVE TRANSACTION – создает внутри транзакции точку сохранения, соответствующую промежуточному состоянию базы данных, сохраненному на момент выполнения оператора, оператор допускает использование имени точки сохранения, так что в ходе выполнения транзакции может быть сохранено несколько таких точек;

- ROLLBACK – используется в двух вариантах, без указания дополнительных параметров эквивалентен оператору ROLLBACK в модели ANSI/ISO, при использовании в качестве параметра имени точки сохранения частично откатывает транзакцию в указанное состояние.

Использование точки сохранения позволяет выполнять откат только до определенной точки в транзакции, а не до самого начала транзакции. Все модификации, выполненные до точки сохранения, остаются в силе и не подвергаются откату; но для всех операторов, выполняемых после точки сохранения (которую вы должны указать в транзакции) вплоть до оператора ROLLBACK, будет выполнен откат. Затем начнут выполняться операторы, следующие за оператором ROLLBACK.

Синтаксис оператора SAVE TRANSACTION следующий:

SAVE TRANSACTION {имя_точки_сохранения | @переменная_с_именем_точки_сохранения}

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

SAVE TRANSACTION save_point_2

После этого можно осуществить откат до данной точки сохранения следующей командой:

ROLLBACK save_point_2

Определение параметров транзакции выполняется оператором SET TRANSACTION, который имеет в стандарте SQL-92 следующий синтаксис:

SET TRANSACTION ISOLATION LEVEL [{SERIALIZABLE |

REPEATABLE READ |

READ COMMITED |

READ UNCOMMITED}] [{READ WRITE |

READ ONLY}]

Ключевое слово ISOLATION LEVEL позволяет установить желаемый уровень изоляции транзакций. По умолчанию предполагается уровень SERIALIZABLE. Если задан уровень READ COMMITED, то допустимы только операции чтения в транзакции, поэтому в этом случае нельзя установить операции READ WRITE.

8 февраля [Лекции]

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

Оператор выборки данных SELECT является наиболее важным и самым часто используемым оператором языка SQL. Он один реализует все 8 операций реляционной алгебры, описанной ранее. Его задача, как уже говорилось, – выбрать из базы данных те или иные данные, исходя из заданных оператором условий. Несмотря на кажущуюся простоту (один оператор заменил 8 операций реляционной алгебры), его использование сродни составлению алгоритмов для простых программ и требует определенных навыков.

Синтаксис оператора SELECT имеет следующий вид:

SELECT [ALL | DISTINCT] (список_полей | *)

FROM список_таблиц

[WHERE условия_выбора_или_соединения]

[GROUP BY выражение_группировки]

[HAVING условие_группы]

[ORDER BY условие_сортировки]

Как и в описаниях предыдущих операторов, квадратные скобки [] указывают на необязательность того или иного предложения в запросе.

Ключевое слово ALL означает, что результирующий запрос содержит все строки, удовлетворяющие условиям запроса. В этом случае в результирующий набор могут попасть одинаковые строки, что нарушает принципы теории отношений (отсутствие одинаковых кортежей). Ключевое слово DISTINCT означает, что результирующий запрос содержит только разные строки, то есть дубликаты не включаются.

Аргумент «список_полей» описывает необходимые столбцы, которые будут включены в результирующее отношение. Символ * в списке полей означает, что будут включены все столбцы исходных таблиц запроса. В случае выбора данных из одной таблицы допустимы следующие варианты: * - все столбцы таблицы, имя_столбца, …, имя_столбца – указанные столбцы таблицы. В случае выбора данных из нескольких таблиц необходимо дополнительно указывать имя таблицы, допустимы следующие варианты: * - все столбцы всех таблиц, имя_таблицы.* - все столбцы указанной таблицы, имя_таблицы.имя_столбца – указанный столбец указанной таблицы.

Если в двух таблицах, участвующих в запросе, есть столбцы с одинаковыми именами, имена второго и последующих столбцов изменяются автоматически (добавляется номер, например два столбца NAME и NAME становятся NAME и NAME1). Чтобы избежать проблем, связанных с подобным автоматическим переименованием, допускается в списке полей указать псевдоним, под которым этот столбец будет присутствовать в результирующем отношении (фактически, определить имя, с которым он и будет переименован). В этом случае к имени столбца необходимо добавить фразу: AS новое_имя.

Аргумент «список_таблиц» указывает перечень имен таблиц, из которых производится выборка сведений, условия которой задаются значением аргумента «условия_выборки_или_соединения». Как видно, условие выборки необязательно, когда оно не указано, будут выбраны все кортежи. Например, следующий запрос выберет все кортежи отношения Студент, отобразив все столбцы:

SELECT * FROM students

Чтобы выбрать только столбцы, соответствующие имени студента, необходимо * заменить на перечень необходимых столбцов. Столбцы указываются в той последовательности, в которой будет осуществляться их вывод, запятые между столбцами обязательны:

SELECT last_name, first_name, mid_name FROM students

Оператор SELECT также позволяет отбирать не только значения столбцов, но и в качестве дополнительного столбца значения арифметических выражений. Например, можно выполнить запрос, отображающий сумму стипендии, выплачиваемой студенту за год:

SELECT last_name, first_name, mid_name, grant, (grant * 12) AS annual_grant

FROM students

Чтобы отобрать не все записи таблицы или набора таблиц, используется условие выборки, которое представляет собой логическое выражение, составленное из простейших операций сравнения с помощью логических операторов AND (И), OR (ИЛИ) и NOT (НЕ) (естественно, как и в любых сложных выражениях допускается использование скобок). В простейших операциях сравнения могут участвовать как имя столбца и некоторая константа, так и имена двух столбцов. Первый случай отвечает собственно за выборку (фильтрацию), второй – за соединение.

Пусть нам требуется определить имя студента, чей студенческий билет имеет номер 123123, тогда необходимо выполнить следующий запрос (в этом примере тип столбца считается числовым):

SELECT last_name, first_name, mid_name FROM students WHERE id_card_num=123123

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

SELECT last_name, first_name, mid_name FROM students WHERE id_card_num=’сб1048а’

Пусть теперь нам требуется выбрать из базы данных информацию о студенте Петрове и преподавателе, который руководит его дипломной работой. Очевидно, в этом запросе уже будут участвовать две сущности Студент и Преподаватель (таблицы students и professors), а условие выбора в этом случае будет составным – одной частью будет условие соединения этих сущностей, а второй частью будет условие на фамилию студента:

SELECT * FROM students, professors

WHERE students.teacher = professors.id AND students.last_name = ‘Петров’

Как видно из примера, в случае выбора более чем из одной таблицы необходимо указывать не только имена столбцов, но и имена таблиц, из которых эти столбцы берутся. В нашем примере в столбце teacher сущности Студент хранится уникальный идентификатор преподавателя, который руководит его работой. Этот уникальный идентификатор должен совпадать со значением столбца id сущности Преподаватель (условие соединения). Условием фильтрации является равенство значения столбца last_name (фамилия) значению «Петров».

Выражение группировки и ключевое слово GROUP BY позволяют сгруппировать строки результирующего отношения по значению определенного столбца (выражение). Этот столбец или выражение должно быть указано в качестве выражения группировки. В этом случае можно использовать групповые функции для получения информации по каждой группе.

Например, можно подсчитать количество студентов, учащихся в группе номер 113. Для этого потребуется выполнить следующий запрос:

SELECT group_num, count(group_num) FROM students WHERE group_num=113

GROUP BY group_num

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

SELECT group_num, count(group_num) FROM students

GROUP BY group_num

Для исключения некоторых групп нельзя использовать ключевое слово WHERE. Для этого требуется использовать специальное ключевое слово HAVING. Синтаксис его использования следующий:

HAVING условие_ограничения_групп

Условие, определяющее ограничения на группы, определяется так же, как и условия для ключевого слова WHERE – это логическое выражение, состоящее из простейших сравнений, в которые входит столбец или выражение, по которому производится группировка. Например, если требуется вывести группы только пятого курса, условие может выглядеть следующим образом:

SELECT group_num, count(group_num) FROM students

GROUP BY group_num

HAVING group_num >=500

Ключевое слово ORDER BY позволяет отсортировать кортежи результирующего отношения в необходимом порядке. Синтаксис использования оператора следующий:

ORDER BY имя_столбца [порядок_сортировки], …, имя_столбца [порядок_сортировки]

Поддерживаются два порядка сортировки: по убыванию и по возрастанию. При этом сортировка может производиться не только по одному столбцу, но и по нескольким столбцам в разных порядках. Для сортировки по возрастанию используется ключевое слово ASC, для сортировки по убыванию – DESC. Порядок сортировки можно и не указывать, тогда по умолчанию производится сортировка по возрастанию. Например, для сортировки списка студентов по фамилии в алфавитном порядке (по возрастанию), необходимо выполнить запрос:

SELECT last_name, first_name, mid_name FROM students

ORDER BY last_name ASC

или просто без указания порядка:

SELECT last_name, first_name, mid_name FROM students

ORDER BY last_name

Если требуется отобразить студентов по дате поступления, так чтобы поступившие недавно возглавляли список, необходимо отсортировать данные по столбцу «Дата поступления» (столбец date_in) в порядке убывания:

SELECT last_name, first_name, mid_name, date_in FROM students

ORDER BY date_in DESC

В случае использования в запросе не только столбцов, но и выражений, допускается сортировка результирующего отношения по выражению. Тогда вместо всего выражения можно указать его номер:

SELECT last_name, first_name, mid_name, grant, (grant * 12) AS annual_grant

FROM students ORDER BY 5

Чтобы отсортировать сведения о студентах по полному имени, требуется указать все три составляющие имени в качестве столбцов сортировки:

SELECT last_name, first_name, mid_name FROM students

ORDER BY last_name ASC, first_name ASC, mid_name ASC

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

SELECT group_num, count(group_num) FROM students

GROUP BY group_num

ORDER BY group_num ASC

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

COUNT Вычисляет количество кортежей в группе

SUM Вычисляет в группе сумму всех значений в указанном столбце

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

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

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

Необходимо также отметить, как влияет неопределенное значение NULL на работу групповых функций. Функция COUNT: если в столбце есть неопределенные значения, то функция COUNT вернет количество тех кортежей, для которых значение определено и к которым применимо условие группирования. Функция COUNT(*) подсчитает все кортежи, включая те, в которых встречается неопределенное значение. Функция AVG: если в столбце есть неопределенные значения, то функция AVG вычислит среднее значение среди всех известных значений. Если же все значения будут NULL, то функция AVG вернет также значение NULL.

Особый интерес представляют вложенные запросы, когда оператор SELECT выбирает данных на основе результата выборки другого оператора SELECT. В этом случае синтаксис оператора SELECT следующий:

SELECT (список_полей | *)

FROM имя_таблицы

WHERE выражение оператор

(SELECT (список_полей | *)

FROM имя_таблицы

……............................)

Здесь в качестве «оператора» могут выступать обычные арифметические операции сравнения или оператор IN. По количеству кортежей, возвращаемых вложенным запросом, подзапросы разделяют на однострочные и многострочные. В первом случае, когда подзапрос возвращает один кортеж (одно значение), можно пользоваться операторами =, >, <, <=, >=, <>. А вот в случае многострочных подзапросов, когда возвращается целое множество значений, допустимо использование только оператора IN (NOT IN).

Правила оформления подзапроса следующие:

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

- подзапрос должен находиться справа от оператора сравнения в выражении сравнения;

- в подзапросе нельзя использовать оператор ORDER BY.

Вложенная команда SELECT выполняется первой. После этого результат выполнения передается во внешнюю команду. Например, пусть нам требуется выбрать всех студентов, обучающихся в той же группе, что и студент «Петров Иван Павлович», тогда запрос должен быть следующим:

SELECT last_name, first_name, mid_name, group_num

FROM students

WHERE group_num = (SELECT group_num

FROM students

WHERE last_name = ‘Петров’ AND first_name = ‘Иван’

AND mid_name= ‘Павлович’)

Приведенный выше пример является случаем однострочного подзапроса (при условии, что полный тезка студента не учится вместе с ним в университете). Рассмотрим теперь случай многострочного подзапроса, например, когда нужно имена студентов, которыми руководят преподаватели «Кузнецов Петр Иванович» и «Селиванов Константин Сергеевич»:

SELECT last_name, first_name, mid_name

FROM students

WHERE professor_id IN (SELECT id

FROM professors

WHERE (last_name = ‘Кузнецов’ AND first_name = ‘Петр’

AND mid_name= ‘Иванович’) OR

(last_name = ‘Селиванов’ AND first_name = ‘Константин’

AND mid_name= ‘Сергеевич’))

Очевидно, что в данном случае вложенный подзапрос вернет множество из двух значений, а значит необходимо уже пользоваться оператором IN.

Также подзапросы можно использовать не только в предложении WHERE, но и в предложении HAVING (в случае группировки результатов).

Вернемся теперь к собственно манипулированию данными, то есть к наполнению базы данных сведениями (оператор INSERT), исправлению данных (оператор UPDATE) и удалению данных (оператор DELETE).

Синтаксис команды INSERT следующий:

INSERT INTO имя_таблицы [(имя_столбца [, имя_столбца…])]

VALUES (значение [, значение, …, значение])

Команда INSERT вставляет в таблицу новую строку целиком, то есть содержащую значения для каждого из столбцов, в том числе неопределенные. Для того, чтобы вставить значения для нескольких столбцов, необходимо явно указать их имена после названия таблицы, при этом значения для столбцов, не указанных в запросе, считаются неопределенными. Значения для столбцов указываются после слова VALUES в том порядке, в каком следуют названия столбцов. Строковые значения и даты заключаются в одинарные кавычки.

Пусть в таблице students содержится 5 полей: фамилия, имя, отчество, дата рождения и номер группы. Тогда добавление записи в таблицу может производится следующим запросом:

INSERT INTO students VALUES (‘Петров’, ‘Семен’, ‘Сергеевич’, ’12.12.1983’, 331)

Любое из добавляемых значений может быть принудительно задано неопределенным, для этого вместо значения необходимо указать ключевое слово NULL:

INSERT INTO students VALUES (‘Петров’, ‘Семен’, ‘Сергеевич’, NULL, 331)

Если же требуется ввести данные не во все столбцы таблицы, то имена требуемых столбцов необходимо задать явно:

INSERT INTO students last_name, first_name, group_num

VALUES (‘Петров’, ‘Семен’, 331)

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

При добавлении строк в таблицу допускается использование подзапросов. В этом случае синтаксис запроса следующий:

INSERT INTO имя_таблицы [(имя_столбца [, имя_столбца…])]

подзапрос

Однако, в этом случае надо быть особенно внимательным, если явно не указывать имена столбцов, так как в этом случае потребуется совпадение структуры исходной таблицы и той, в которую данные переносятся. В любом случае, количество столбцов таблицы и количество столбцов подзапроса должно совпадать.

Например, можно перенести данные из таблицы поступающих в таблицу студентов (после принятия экзамена, также считая, что структуры таблиц идентичны):

INSERT INTO students

SELECT * FROM entrants WHERE exam_pass = ‘true’

Если же структуры таблиц не совпадают, то потребуется указать имена переносимых столбцов (фамилия, имя, отчество, дата рождения):

INSERT INTO students (last_name, first_name, mid_name, birth_date)

SELECT last_name, first_name, mid_name, birth_date FROM entrants

WHERE exam_pass = ‘true’

Обновление значений в таблице происходит во многом аналогично. Синтаксис оператора UPDATE следующий:

UPDATE имя_таблицы

SET имя_столбца = значение [, имя_столбца = значение, …]

[WHERE условие]

Условие оператора WHERE позволяет ограничить перечень строк, в которых будут обновлены значения. В отсутствие условия значения будут обновлены во всей таблице.

Например, для перевода студентов из 211 в 311 группу, необходимо выполнить запрос:

UPDATE students

SET group_num = 311

WHERE group_num = 211

Чтобы изменить дату рождения преподавателя «Петров Алексей Дмитриевич», необходимо выполнить запрос:

UPDATE professors

SET birth_date = ‘1.4.1958’

WHERE last_name = ‘Петров’ AND first_name = ‘Алексей’ AND mid_name = ‘Дмитриевич’

Увеличить зарплату каждого преподавателя на 3000 можно следующим запросом (обратите внимание, что оператор WHERE отсутствует):

UPDATE professors

SET salary = salary + 3000

Вместе с зарплатой можно одновременно увеличить и премии на 1000 рублей, тогда запрос будет следующий:

UPDATE professors

SET salary = salary + 3000, bonus = bonus + 1000

С помощью оператора UPDATE можно также и изменить значение на неопределенное:

UPDATE professors

SET birth_date = NULL

WHERE last_name = ‘Петров’ AND first_name = ‘Алексей’ AND mid_name = ‘Дмитриевич’

Удаление строк из таблиц производится еще более просто. Синтаксис оператора DELETE следующий:

DELETE FROM имя_таблицы

[WHERE условие]

Как и в случае оператора UPDATE, указание условия фильтрации (предложение WHERE) необязательно. В этом случае будут удалены все строки из таблицы. Если же требуется удалить не все строки, то необходимый перечень строк определяется условием.

Например, очистить таблицу с абитуриентами после поступления можно следующим образом:

DELETE FROM entrants

Удалить отдельного студента из таблицы после отчисления можно с помощью следующего запроса:

DELETE FROM students

WHERE last_name=’Курилова’ AND first_name = ‘Юлия’ AND mid_name = ‘Викторовна’