Лабораторный практикум. Лабораторная работа Обследование предметной области Построение модели ide создание контекстной диаграммы Создание диаграммы декомпозиции Создание диаграммы
 Скачать 5.73 Mb.
|
|
10.8 Создание составного индекса и использующего его запроса 1. На панели «Customers.PK_Cu...», а затем Рисунок 8 – Управление индексами В диалоговом окне 164 Рисунок 9 – Список индексов, созданных для таблицы «Customers» 2. Щелкните Выводится диалоговое окно 3. В текстовом поле 4. В столбце ниже текстового поля 5. Выберите строку 6. Щелкните <ОК> (рисунок 10). 165 Рисунок 10 – Создание составного индекса «Contact» В списке индексов диалогового окна «Contact» (рисунок 11). Рисунок 11 – Обновленный список индексов, созданных для таблицы «Customers» 166 7. Щелкните 8. На панели SELECT companyname, contactname, city, country, phone FROM customers ORDER BY city На вкладке Обратите внимание, что он упорядочен по значению Рисунок 12 – Просмотр содержимого полей 9. Щелкните вкладку 167 Рисунок 13 – Отображение плана исполнения просмотра содержимого полей «Customers», отсортированных по полю План исполнения показывает, что оптимизатор запросов использовал некластерный индекс «Contact». Обратите внимание, что упорядочение списка не требует вычислений, поскольку составной индекс «Contact» упорядочен вначале по значениям столбца 10. На панели SELECT companyname, contactname, city, country, phone FROM customers ORDER BY country На вкладке Обратите внимание, что он упорядочен по значению 168 Рисунок 14 – Просмотр содержимого полей 11. Щелкните вкладку Рисунок 15 – Отображение плана исполнения просмотра содержимого полей «Customers», отсортированных по полю 169 План исполнения показывает, что оптимизатор запросов, использовал некластерный индекс «Contact». Обратите внимание, что сортировка списка не требует обработки, поскольку составной индекс «Contact» отсортирован вначале по значениям 10.9 Контрольное задание Создайте составной индекс в базе данных согласно выбранному вами варианту. 10.10 Контрольные вопросы 1. Что такое индекс? 2. Какие существуют типы индексов? В чем их отличия? 3. Как создать составной индекс? 170 11 Лабораторная работа № 11. Управление транзакциями и блокировками в MS SQL Server 2017 Цель работы: изучить механизм формирования транзакций, уровни изоляции транзакций и взаимные блокировки. Используемое программное обеспечение: Microsoft SQL Server 2017. Концепция транзакций – неотъемлемая часть любой клиент-серверной базы данных. Под транзакцией понимается неделимая с точки зрения воздействия на базу данных последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации), приводящая к одному из двух возможных результатов: либо последовательность выполняется, если все операторы правильные, либо вся транзакция откатывается, если хотя бы один оператор не может быть успешно выполнен. Обработка транзакций гарантирует целостность информации в базе данных. Таким образом, транзакция переводит базу данных из одного целостного состояния в другое. Поддержание механизма транзакций – показатель уровня развитости системы управления базами данных. Корректное поддержание транзакций одновременно является основой обеспечения целостности базы данных. Транзакции также составляют основу изолированности в многопользовательских системах, где с одной базой данных параллельно могут работать несколько пользователей или прикладных программ. Одна из основных задач системы управления базами данных – обеспечение изолированности, т. е. создание такого режима функционирования, при котором каждому пользователю казалось бы, что база данных доступна только ему. Такую задачу системы управления базами данных принято называть параллелизмом транзакций. Большинство выполняемых действий производится в теле транзакций. По умолчанию каждая команда выполняется как самостоятельная транзакция. При 171 необходимости пользователь может явно указать ее начало и конец, чтобы иметь возможность включить в нее несколько команд. При выполнении транзакции система управления базами данных должна придерживаться определенных правил обработки набора команд, входящих в транзакцию. Характеристики транзакций: − неделимость; − согласованность; − изолированность; − устойчивость. Транзакция неделима в том смысле, что представляет собой единое целое. Все ее компоненты либо имеют место, либо нет. Не бывает частичной транзакции. Если может быть выполнена лишь часть транзакции, она отклоняется. Транзакция является согласованной, потому что не нарушает бизнес- логику и отношения между элементами данных. Это свойство очень важно при разработке клиент-серверных систем, поскольку в хранилище данных поступает большое количество транзакций от разных систем и объектов. Если хотя бы одна из них нарушит целостность данных, то все остальные могут выдать неверные результаты. Транзакция всегда изолирована, поскольку ее результаты самодостаточны. Они не зависят от предыдущих или последующих транзакций – это свойство называется сериализуемостью и означает, что транзакции в последовательности независимы. Транзакция устойчива. После своего завершения она сохраняется в системе, которую ничто не может вернуть в исходное (до начала транзакции) состояние, т. е. происходит фиксация транзакции, означающая, что ее действие постоянно даже при сбое системы. При этом подразумевается некая форма хранения информации в постоянной памяти как часть транзакции. 172 Указанные выше правила выполняет сервер. Программист лишь выбирает нужный уровень изоляции, заботится о соблюдении логической целостности данных и бизнес-правил. На него возлагаются обязанности по созданию эффективных и логически верных алгоритмов обработки данных. Он решает, какие команды должны выполняться как одна транзакция, а какие могут быть разбиты на несколько последовательно выполняемых транзакций. Следует по возможности использовать небольшие транзакции, т. е. включающие как можно меньше команд и изменяющие минимум данных. Соблюдение этого требования позволит наиболее эффективным образом обеспечить одновременную работу с данными множества пользователей. 11.1 Блокировки Повышение эффективности работы при использовании небольших транзакций связано с тем, что при выполнении транзакции сервер накладывает на данные блокировки. Блокировкой называется временное ограничение на выполнение некоторых операций обработки данных. Блокировка может быть наложена как на отдельную строку таблицы, так и на всю базу данных. Управлением блокировками на сервере занимается менеджер блокировок, контролирующий их применение и разрешение конфликтов. Транзакции и блокировки тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обеспечить выполнение характеристик транзакций. Без использования блокировок несколько транзакций могли бы изменять одни и те же данные. Блокировка представляет собой метод управления параллельными процессами, при котором объект базы данных не может быть модифицирован без ведома транзакции, т. е. происходит блокирование доступа к объекту со стороны других транзакций, чем исключается непредсказуемое изменение объекта. Различают два вида блокировки: 173 – блокировка записи – транзакция блокирует строки в таблицах таким образом, что запрос другой транзакции к этим строкам будет отменен; – блокировка чтения – транзакция блокирует строки так, что запрос со стороны другой транзакции на блокировку записи этих строк будет отвергнут, а на блокировку чтения – принят. В системах управления базами данных используют протокол доступа к данным, позволяющий избежать проблемы параллелизма. Его суть заключается в следующем: − транзакция, результатом действия которой на строку данных в таблице является ее извлечение, обязана наложить блокировку чтения на эту строку; − транзакция, предназначенная для модификации строки данных, накладывает на нее блокировку записи; − если запрашиваемая блокировка на строку отвергается из-за уже имеющейся блокировки, то транзакция переводится в режим ожидания до тех пор, пока блокировка не будет снята; − блокировка записи сохраняется вплоть до конца выполнения транзакции. Решение проблемы параллельной обработки баз данных заключается в том, что строки таблиц блокируются, а последующие транзакции, модифицирующие эти строки, отвергаются и переводятся в режим ожидания. В связи со свойством сохранения целостности базы данных транзакции являются подходящими единицами изолированности пользователей. Действительно, если каждый сеанс взаимодействия с базой данных реализуется транзакцией, то пользователь начинает с того, что обращается к согласованному состоянию базы данных – состоянию, в котором она могла бы находиться, даже если бы пользователь работал с ней в одиночку. Если в системе управления базами данных не реализованы механизмы блокирования, то при одновременном чтении и изменении одних и тех же 174 данных несколькими пользователями могут возникнуть следующие проблемы одновременного доступа: − проблема последнего изменения возникает, когда несколько пользователей изменяют одну и ту же строку, основываясь на ее начальном значении; тогда часть данных будет потеряна, т. к. каждая последующая транзакция перезапишет изменения, сделанные предыдущей. Выход из этой ситуации заключается в последовательном внесении изменений; − проблема «грязного» чтения возможна в том случае, если пользователь выполняет сложные операции обработки данных, требующие множественного изменения данных перед тем, как они обретут логически верное состояние. Если во время изменения данных другой пользователь будет считывать их, то может оказаться, что он получит логически неверную информацию. Для исключения подобных проблем необходимо производить считывание данных после окончания всех изменений; − проблема неповторяемого чтения является следствием неоднократного считывания транзакцией одних и тех же данных. Во время выполнения первой транзакции другая может внести в данные изменения, поэтому при повторном чтении первая транзакция получит уже иной набор данных, что приводит к нарушению их целостности или логической несогласованности; − проблема чтения фантомов появляется после того, как одна транзакция выбирает данные из таблицы, а другая – вставляет или удаляет строки до завершения первой. Выбранные из таблицы значения будут некорректны. Для решения перечисленных проблем в специально разработанном стандарте определены четыре уровня блокирования. Уровень изоляции транзакции определяет, могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией, а также может ли текущая транзакция видеть изменения, произведенные конкурирующими транзакциями, и наоборот. Каждый последующий уровень поддерживает требования предыдущего и налагает дополнительные ограничения: 175 − уровень 0 – запрещение «загрязнения» данных. Этот уровень требует, чтобы изменять данные могла только одна транзакция; если другой транзакции необходимо изменить те же данные, она должна ожидать завершения первой транзакции; − уровень 1 – запрещение «грязного» чтения. Если транзакция начала изменение данных, то никакая другая транзакция не сможет прочитать их до завершения первой; − уровень 2 – запрещение неповторяемого чтения. Если транзакция считывает данные, то никакая другая транзакция не сможет их изменить. Таким образом, при повторном чтении они будут находиться в первоначальном состоянии; − уровень 3 – запрещение фантомов. Если транзакция обращается к данным, то никакая другая транзакция не сможет добавить новые или удалить имеющие строки, которые могут быть считаны при выполнении транзакции. Реализация этого уровня блокирования выполняется путем использования блокировок диапазона ключей. Подобная блокировка накладывается не на конкретные строки таблицы, а на строки, удовлетворяющие определенному логическому условию. 11.2 Управление транзакциями Под управлением транзакциями понимается способность управлять различными операциями над данными, которые выполняются внутри реляционной системы управления базами данных. Прежде всего, имеется в виду выполнение операторов 176 После успешного выполнения команд, заключенных в тело одной транзакции, немедленного изменения данных не происходит. Для окончательного завершения транзакции существуют так называемые команды управления транзакциями, с помощью которых можно либо сохранить в базе данных все изменения, произошедшие в ходе ее выполнения, либо полностью их отменить. Существуют три команды, которые используются для управления транзакциями: − COMMIT – для сохранения изменений; − ROLLBACK – для отмены изменений; − SAVEPOINT – для установки особых точек возврата. После завершения транзакции вся информация о произведенных изменениях хранится либо в специально выделенной оперативной памяти, либо во временной области отката в самой базе данных до тех пор, пока не будет выполнена одна из команд управления транзакциями. Затем все изменения или фиксируются в базе данных, или отбрасываются, а временная область отката освобождается. Команда Команда Команда (при этом отката всей транзакции не происходит). Команда имеет следующий вид: 177 SAVEPOINT имя_точки_сохранения Она служит исключительно для создания точек сохранения среди операторов, предназначенных для изменения данных. Имя точки сохранения в связанной с ней группе транзакций должно быть уникальным. Для отмены действия группы транзакций, ограниченных точками сохранения, используется команда ROLLBACK TO имя_точки_сохранения Поскольку с помощью команды 11.3 Управление транзакциями в среде MS SQL Server. Определение транзакций MS SQL Server предлагает множество средств управления поведением транзакций. Пользователи в основном должны указывать только начало и конец транзакции, используя команды SQL или API (прикладного интерфейса программирования). Транзакция определяется на уровне соединения с базой данных и при закрытии соединения автоматически закрывается. Если пользователь попытается установить соединение снова и продолжить выполнение транзакции, то это ему не удастся. Когда транзакция начинается, все команды, выполненные в соединении, считаются телом одной транзакции, пока не будет достигнут ее конец. MS SQL Server поддерживает три вида определения транзакций: − явное; − автоматическое; − подразумеваемое. По умолчанию MS SQL Server работает в режиме автоматического начала транзакций, когда каждая команда рассматривается как отдельная транзакция. 178 Если команда выполнена успешно, то ее изменения фиксируются. Если при выполнении команды произошла ошибка, то сделанные изменения отменяются и система возвращается в первоначальное состояние. Когда пользователю понадобится создать транзакцию, включающую несколько команд, он должен явно указать транзакцию. Сервер работает только в одном из двух режимов определения транзакций: автоматическом или подразумевающемся. Он не может находиться в режиме исключительно явного определения транзакций. Этот режим работает поверх двух других. Для установки режима автоматического определения транзакций используется команда: SET IMPLICIT_TRANSACTIONS OFF При работе в режиме неявного (подразумевающегося) начала транзакций MS SQL Server автоматически начинает новую транзакцию, как только завершена предыдущая. Установка режима подразумевающегося определения транзакций выполняется посредством другой команды: SET IMPLICIT_TRANSACTIONS ON Явные транзакции требуют, чтобы пользователь указал начало и конец транзакции, используя следующие команды: − начало транзакции: в журнале транзакций фиксируются первоначальные значения изменяемых данных и момент начала транзакции; BEGIN TRAN[SACTION] [имя_транзакции | @имя_переменной_транзакции [WITH MARK ['описание_транзакции']]] − конец транзакции: если в теле транзакции не было ошибок, то эта команда предписывает серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакций помечается, что изменения зафиксированы и транзакция завершена; 179 COMMIT [TRAN[SACTION] [имя_транзакции | @имя_переменной_транзакции]] − создание внутри транзакции точки сохранения: система управления базами данных сохраняет состояние базы данных в текущей точке и присваивает сохраненному состоянию имя точки сохранения; SAVE TRAN[SACTION] {имя_точки_сохранения | @имя_переменной_точки_сохранения} − прерывание транзакции; когда сервер встречает эту команду, происходит откат транзакции, восстанавливается первоначальное состояние системы и в журнале транзакций отмечается, что транзакция была отменена. Приведенная ниже команда отменяет все изменения, сделанные в базе данных после оператора ROLLBACK [TRAN[SACTION] [имя_транзакции | @имя_переменной_транзакции | имя_точки_сохранения |@имя_переменной_точки_сохранения]] Функция <@@TRANCOUNT> возвращает количество активных транзакций. Функция <@@NESTLEVEL> возвращает уровень вложенности транзакций. BEGIN TRAN SAVE TRANSACTION point1 В точке сохраняется первоначальное состояние таблицы «Товар» DELETE FROM Товар WHERE Код_товара=2 180 SAVE TRANSACTION point2 В точке сохраняется состояние таблицы «Товар» без товаров с кодом 2. DELETE FROM Товар WHERE Код_товара=3 SAVE TRANSACTION point3 В точке сохраняется состояние таблицы «Товар» без товаров с кодом 2 и с кодом 3. DELETE FROM Товар WHERE Код_товара<>1 ROLLBACK TRANSACTION point3 Происходит возврат в состояние таблицы без товаров с кодами 2 и 3, отменяется последнее удаление. SELECT * FROM Товар Оператор |