Волк В.К. Базы данных. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.17 Mb.
|
|
ГЛАВА 9. ЗАДАЧИ АДМИНИСТРИРОВАНИЯ БАЗ ДАННЫХ Эксплуатационные характеристики хранилища данных, интегрированного в состав автоматизированной информационной системы, во многом определяют эффективность работы всего программного комплекса. Грамотное администри- рование базы данных может существенно повысить производительность инфор- мационной системы, обеспечить высокую надежность хранения и требуемый уровень защиты информации. Администрирование как вид профессиональной деятельности направлено на поддержание эффективной и бесперебойной работы баз данных, обеспечи- вающих функционирование информационных систем, и связано с выполнением следующих основных функций: – эксплуатация серверов баз данных; – поддержание баз данных в актуальном состоянии и обеспечение их эф- фективного функционирования; – обеспечение доступности данных для легальных пользователей и защи- та от несанкционированного доступа к данным; – мониторинг и идентификация потребностей пользователей. Приведенный набор типовых функций администраторов баз данных закреплен как российскими, так и зарубежными профессиональными стандар- тами и классификационными системами, в которых явно разделяется деятель- ность по созданию баз данных (разработка концепций, проектирование и про- граммирование) и деятельность по их сопровождению и поддержанию работо- способности (администрирование). Так, в стандарте США SOC (Standard Occupational Classification) (прило- жение Б [1]) работы по сопровождению баз данных включены в подкатегорию «Database Administrators» в составе категории «Database and Systems Admi- nistrators and Network Architects», а функции по созданию баз данных определе- ны в подкатегории «Software Developers», входящей в состав категории «Software and Web Developers, Programmers and Testers». Соответствующий европейский стандарт European ICT Professional Profiles (приложение Б [2]) предусматривает профиль «Developer» для деятель- ности по проектированию баз данных и отдельный профиль «Database Administrator», в котором определены работы по обеспечению их функциони- рования в процессе эксплуатации. Российские профессиональные стандарты «Архитектор программного обеспечения», «Руководитель разработки программного обеспечения» и «Про- граммист» определяют функции проектирования и программирования баз дан- ных, а комплекс работ по их сопровождению регламентирован стандартом «Администратор баз данных» (приложение Б [3]), в котором приведен деталь- 7 / 19 122 ный перечень трудовых функций с указанием для каждой из них квалификаци- онного уровня, требований к профессиональной компетентности, базовому об- разованию и опыту работы. Стандарты «Специалист по информационным си- стемам» (приложение Б [4]), «Системный администратор информационно- коммуникационных систем» (приложение Б [5]) и «Специалист по защите ин- формации в автоматизированных системах» (приложение Б [6]) также регла- ментируют отдельные аспекты администрирования баз данных. 8 / 19 123 ГЛАВА 10. УПРАВЛЕНИЕ ТРАНЗАКЦИЯМИ И БЛОКИРОВКАМИ Важнейшими компонентами СУБД являются менеджеры транзакций и блокировок, обеспечивающие конкурентный многопользовательский доступ к объектам базы данных: наложение и снятие блокировок объектов базы данных, обрабатываемых транзакциями, эффективный откат транзакций при невозмож- ности их штатного завершения, управление распределенными транзакциями. 10.1. Понятие и базовые свойства транзакций Стандартом SQL/92 определено понятие транзакции как последователь- ности SQL-операторов, рассматриваемых как единое целое в контексте их вы- полнения и возможной отмены результатов произведенных ими модификаций базы данных. Формально транзакцией будет считаться любая последователь- ность SQL-операторов, заключенных в «операторные скобки» BEGIN TRANSACTION и COMMIT (при успешном завершении транзакции) или ROLL BACK (при невозможности ее успешного завершения). При отсутствии таких «скобок» СУБД будет считать транзакцией каждый отдельный SQL-оператор. Стандартом определены 4 базовых свойства транзакций, обозначаемых англоязычной аббревиатурой ACID: Atomicity (атомарность), Consistency (согла- сованность), Isolation (изолированность) и Durability (долговременность). Свойство атомарности (A) реализует принцип «или все, или ничего» и предписывает рассматривать транзакцию как единое целое: если какой-либо из операторов, включенных в транзакцию, не может быть выполнен, СУБД обяза- на сделать откат (ROLL BACK) к началу транзакции, отменив все изменения объ- ектов базы данных, произведенные предшествующими операторами этой тран- закции. Свойство атомарности применимо и к транзакциям, содержащим един- ственный оператор: например, если оператором UPDATE производится модифи- кация 1000 строк таблицы, удовлетворяющих некоторому условию, то либо все эти строки будут модифицированы, либо, при невозможности успешного за- вершения этой операции, будет выполнен откат транзакции и модифицируемая таблица останется в исходном состоянии. Согласованность (C) транзакции требует от СУБД гарантий того, что к моменту начала любой транзакции и в момент ее завершения база данных будет находиться в согласованном состоянии. Фактически это допускает рассогласо- ванное состояние базы данных «внутри» транзакции, что весьма существенно при необходимости последовательного внесения изменений во множество вза- имосвязанных таблиц. Изолированность (I) — одно из важнейших свойств транзакций, которое далее будет рассмотрено более детально. Транзакция всегда выполняется от имени определенного пользователя, идентификатор которого присутствует (явно или неявно) в качестве префикса 9 / 19 124 имени транзакции в операторе BEGIN TRANSACTION, поэтому, изолируя тран- закции, СУБД обеспечивает и определенный уровень изолированности пользо- вателей в многопользовательской системе. Реализация изолированности транзакций должна исключить взаимовлия- ние параллельно выполняемых транзакций, конкурирующих в доступе к одно- му и тому же объекту базы данных, и при этом для каждого пользователя, ини- циировавшего выполнение транзакции, должна быть создана достоверная ил- люзия того, что он в системе один. Достоверность такой иллюзии подтвержда- ется (для пользователя) двумя основными факторами: − каждый пользователь, анализируя результаты выполнения иницииро- ванных им запросов, должен быть уверен, что никакие другие пользователи не модифицируют данные, обрабатываемые транзакцией этого пользователя; − пользователь не должен ощущать существенного увеличения времени отклика на его запросы к базе данных и в целом снижения производительности системы из-за ожидания освобождения ресурсов, временно заблокированных конкурирующими транзакциями. Обеспечение свойства долговременности (D) транзакции должно гаран- тировать сохранение в файлах базы данных всех ее изменений, произведенных в буферных областях оперативной памяти каждой успешно завершенной (COMMIT) транзакцией. Заметим, что в соответствии с протоколом WAL (Write Ahead LOG), непо- средственно перед записью изменений во внешнюю память информация об этих изменениях также будет сохранена в файле журнала транзакций. 10.2. Конфликты между транзакциями Поддержка требования согласованности транзакций допускает рассогла- сованное состояние базы данных в процессе их выполнения, а реализация тре- бований атомарности и долговременности, с одной стороны, должна обеспе- чить возможность отката (ROLLBACK) частично выполненных транзакций, а с другой — гарантировать фиксацию в базе данных всех изменений, произведен- ных успешно завершенными (COMMIT) транзакциями. В многопользовательских системах все это может создавать различные проблемы как у транзакций-читателей (R), осуществляющих пассивный доступ к данным (SELECT), так и у транзакций-писателей (W), модифицирующих дан- ные (DELETE, INSERT, UPDATE). Проблема потерянного изменения отражает конфликт типа «W–W» между параллельно выполняемыми транзакциями-писателями, конкурирую- щими в доступе к одному объекту базы данных. Если, например, каждая из транзакций по-своему модифицировала таблицу, то в базе данных будут сохра- нены только те изменения, которые были сделаны транзакцией, последней мо- дифицировавшей объект, а все более ранние изменения, сделаны другой тран- закцией, будут потеряны («затерты» второй транзакцией). При этом согласо- ванное состояние базы данных в целом не будет нарушено, но пользователь, инициировавший первую (успешно завершившуюся раньше) транзакцию, так и 10 / 19 125 не увидит результатов ее завершения. Очевидно, что для решения рассмотрен- ной проблемы СУБД должна обеспечить изолированное выполнение двух кон- курирующих транзакций-писателей. Проблема чтения грязных данных отражает конфликт типа «W–R» и мо- жет проявляться в результате конкуренции транзакции, модифицирующей объ- ект, с другой транзакцией, параллельно читающей данные этого же объекта и принимающей некоторые решения в соответствии с прочитанной информацией. Если СУБД производит откат (ROLLBACK) первой транзакции, то все ре- шения второй транзакции оказываются некорректными, так как они были при- няты на основании ложной (еще не зафиксированной в БД) информации. Если первая транзакция все же завершается успешно (COMMIT), то и в этом случае у второй транзакции могут быть проблемы с корректностью принятых решений, так как в соответствии с требованием согласованности допускается рассогла- сованное состояние БД в процессе выполнения первой транзакции. Для реше- ния рассмотренной проблемы СУБД должна изолировать транзакцию-писателя, запретив другим транзакциям читать соответствующие объекты БД до момента завершения первой транзакции. Проблема неповторяемого чтения отражает конфликт типа «R–W»: первая транзакция многократно читает один и тот же объект базы данных и при этом каждый раз «видит» его в различных состояниях, так как в промежутках между чтениями этот объект изменяет другая транзакция. Обе транзакции мо- гут завершиться успешно (COMMIT), и проблем с согласованностью базы дан- ных не будет, однако у транзакции-читателя остается проблема несоответствия полученных результатов, решением которых должна заниматься СУБД, обес- печивающая изолированность первой транзакции и запрещающая другим тран- закциям модифицировать объект до ее завершения. Последняя из стандартных проблем, связанных с недостаточной изолиро- ванностью транзакций, получила название проблемы чтения кортежей- фантомов. Эта проблема также отражает конфликт типа «R–W» и проявляется в ситуациях, когда одна транзакция многократно сканирует таблицу, производя при каждом сканировании обработку множества ее строк, соответствующих одному и тому же логическому условию, а другая транзакция, независимо от первой, производит вставку в эту таблицу или удаление из нее строк, соответ- ствующих этому же условию. Классический пример: 1) первая транзакция при первом сканировании таблицы производит вы- борку ее строк по некоторому условию и по результатам выборки вычисляет какую-либо статистическую характеристику (например, среднее значение одно- го из атрибутов); 2) вторая транзакция производит вставку или удаление строк, соответ- ствующих этому же условию; 3) первая транзакция – при повторном сканировании таблицы выбирает ее строки по тому же условию, и в эту выборку попадут кортежи-фантомы, вставленные в таблицу 11 / 19 126 второй транзакцией (или, наоборот, в выборке не окажется кортежей, фантомно присутствовавших при первом сканировании и затем удаленных второй тран- закцией); – в выбранных строках модифицирует значение другого атрибута в соот- ветствии со значением статистической характеристики, вычисленной по ре- зультатам первого сканирования; 4) вторая транзакция – завершается успешно (COMMIT), или производится ее откат (ROLLBACK); в любом случае она уже причинила вред первой транзакции, выполнившей не- корректную обработку данных. 10.3. Уровни изолированности транзакций Стандарт SQL-92 определяет 4 уровня изолированности транзакций — от самого слабого нулевого уровня до самого сильного — третьего (табл. 4.1). Таблица 4.1 Уровни изолированности транзакций Уровни изолированности Решаемые проблемы (конфликты между транзакциями) Потерянные изменения Чтение гряз- ных данных Неповторяю- щееся чтение Кортежи- фантомы 0 READ UNCOMMITTED Да Нет Нет Нет 1 READ COMMITTED Да Да Нет Нет 2 REPEATABLE READ Да Да Да Нет 3 SERIALIZABLE Да Да Да Да На каждом уровне изолированности СУБД обеспечивает разрешение определенных конфликтов между конкурирующими транзакциями, при этом на каждом следующем (более сильном) уровне решаются и проблемы всех преды- дущих (более слабых) уровней. Минимальный (нулевой) уровень READ UNCOMMITTED решает только проблему потерянного изменения, запрещая двум любым транзакциям парал- лельно модифицировать один и тот же объект базы данных. Транзакции, тре- бующие модификации объекта, будут ожидать успешного завершения или от- ката транзакции-конкурента, первой начавшей модифицировать этот объект. При этом доступ к объекту со стороны транзакций-читателей не запрещается и сохраняется вероятность чтения грязных данных, сформированных еще не за- вершенными транзакциями-писателями. 1-й уровень READ COMMITTED дополнительно блокирует доступ транзак- ций-читателей к объектам, находящимся в стадии обработки транзакциями- писателями, что обеспечивает решение проблемы чтения грязных данных, но допускает неповторяющееся чтение, так как не запрещает модифицировать объекты, обрабатываемые транзакциями-читателями. На 2-м уровне изолированности REPEATABLE READ дополнительно реша- ется проблема неповторяющегося чтения, так как СУБД блокирует возмож- ность модификации (UPDATE) объекта транзакциями-писателями в течение все- го периода обработки этого объекта транзакцией-читателем. 12 / 19 127 Максимальный 3-й уровень изолированности SERIALIZABLE обеспечивает полную независимость транзакций друг от друга и гарантирует, что никакие транзакции-писатели не смогут вставить в таблицу или удалить из нее строки, соответствующие условию выборки данных из этой таблицы транзакцией- читателем. На уровне SERIALIZABLE дополнительно решается проблема чтения кор- тежей-фантомов, и результат выполнения всех конкурирующих параллель- ных транзакций будет точно таким же, как и в случае их реально последова- тельного выполнения (откуда и название этого уровня), когда каждая очеред- ная транзакция начинается только после завершения предыдущей. Чем выше уровень изолированности транзакций, тем более надежно бу- дет работать система, однако платой за это будет увеличение объема системных ресурсов, требуемых для управления транзакциями, и снижение производи- тельности за счет увеличения интервалов ожидания одними транзакциями освобождения объектов БД, обрабатываемых другими транзакциями. Как правило, СУБД по умолчанию поддерживает некоторый уровень изо- лированности транзакций (например, для MS SQL-Server это уровень READ COMMITTED), однако разработчик вправе назначить требуемый уровень изоли- рованности индивидуально для каждой транзакции, определив тем самым сте- пень влияния на ее операции других параллельно выполняемых транзакций, а также степень влияния данной транзакции на операции транзакций- конкурентов. Соответствующие примеры будут рассмотрены в п. 10.5.1. 10.4. Управление блокировками Блокировка — это механизм, с помощью которого СУБД синхронизирует параллельный доступ нескольких транзакций к одному и тому же объекту базы данных. Перед тем как транзакция получит доступ к объекту для его чтения или модификации, она должна убедиться в том, что объект не заблокирован други- ми транзакциями, и, если он свободен, транзакция запрашивает у СУБД блоки- ровку этого объекта, чтобы защитить его от изменений другими транзакциями во время своего выполнения. Временная блокировка объектов базы данных — основной (хотя и не единственный) метод обеспечения требуемого уровня изолированности тран- закций, реализацией которого занимается специальный компонент СУБД — менеджер блокировок, работающий совместно с другим ее важнейшим компо- нентом — менеджером транзакций. Функции двух этих менеджеров весьма разнообразны, различаются также и способы реализации этих функций в раз- ных СУБД, ниже приведена упрощенная схема, поясняющая алгоритм их взаи- модействия, обеспечивающий требуемый уровень изолированности транзак- ций. Менеджер транзакций: – получает от транслятора SQL-кода информацию о транзакциях, требуе- мых уровнях их изолированности, а также о составе операций каждой транзак- ции и объектах базы данных, затрагиваемых этими операциями; 13 / 19 128 – сохраняет полученную информацию в системном каталоге БД; – формирует очереди транзакций, конкурирующих в доступе к объектам БД; – фиксирует (COMMIT) результаты успешно завершенных транзакций или производит откат (ROLLBACK) транзакций в случае невозможности их успешно- го завершения; – удаляет из очереди завершенные транзакции; – разрушает тупиковые блокировки (п. 10.4.3) в случае их обнаружения менеджером блокировок: • ранжирует транзакции, участвующие в тупиковой блокировке, ис- пользуя поддерживаемую СУБД модель стоимости транзакции; • выбирает транзакцию, имеющую минимальную стоимость; • выполняет принудительный откат (ROLLBACK) этой транзакции; • циклически повторяет процесс принудительного отката транзакций до тех пор, пока тупиковая блокировка не будет разрушена. Менеджер блокировок: – производит мониторинг очередей транзакций в соответствии с инфор- мацией, сохраненной менеджером транзакций в системном каталоге базы дан- ных; – принимает решения о наложении блокировок на объекты, требующие обработки очередными транзакциями: • выбирает режим блокирования (п. 10.4.2) объекта в соответствии с требуемым уровнем изолированности транзакции; • выбирает оптимальный уровень блокируемого объекта (п. 10.4.1) по критерию минимизации затрат ресурсов на поддержание выбранного режима блокирования и в соответствии с требуемым уровнем изолированности тран- закции; – принимает решения о снятии блокировок с объектов, освобождаемых завершенными транзакциями; – сохраняет в системном каталоге информацию о временно заблокиро- ванных объектах БД; – идентифицирует (прогнозирует) ситуации с тупиковыми блокировками (п. 10.4.3), которые не могут быть разрешены естественным путем при осво- бождении завершенными транзакциями заблокированных ими объектов. 10.4.1. Уровни блокирования ресурсов СУБД может блокировать объекты как логической, так и физической 5 моделей данных различных иерархических уровней, типичный набор которых приведен в таблице 4.2. Высокоуровневые блокировки таких объектов, как база данных, файл или таблица, очень экономичны — их реализация не требует больших затрат си- стемных ресурсов хотя бы потому, что количество таких «крупных» объектов относительно невелико. 5 Объекты физической модели данных (файлы, экстенты, страницы, строки), а также индексы различных типов детально рассмотрены в 12-й главе учебника. 14 / 19 129 Таблица 4.2 Уровни блокирования объектов базы данных Блокируемый ресурс Комментарии DATABASE База данных — ресурс блокируется транзакциями, модифицирующими схему базы данных или создающими ее резервные копии FILE Один из множества файлов базы данных TABLE Таблица, включая все ее строки и все созданные в ней индексы EXTENT Группа страниц файла базы данных PAGE Одна из страниц файла базы данных, содержащая множество строк таб- лицы или индекса RID Идентификатор строки — используется для блокировки одной строки таблицы KEY Ключ индекса — используется транзакциями 3-го уровня изолированно- сти для блокировки диапазонов значений атрибутов, включенных в усло- вия выборки строк таблиц С другой стороны, наложение высокоуровневых блокировок уменьшает степень параллелизма выполнения конкурирующих транзакций, что в результа- те снижает производительность системы. Блокирование объектов низких уровней позволяет гибко управлять кон- курирующими транзакциями (например, снимать блокировки строк таблицы, уже обработанных транзакцией, не дожидаясь ее завершения), повышая произ- водительность системы, однако поддержка низкоуровневых блокировок может оказаться недопустимо ресурсоемкой из-за большого их количества. Менеджер блокировок автоматически выбирает оптимальный уровень блокирования ресурсов, выполняя в необходимых случаях так называемую эс- калацию блокировок — повышение уровня блокирования путем замены множе- ства низкоуровневых блокировок одной или несколькими блокировками более высокого уровня, или их деэскалацию, то есть понижение уровня блокирования объектов. В любом случае критерием оптимальности работы менеджера блоки- ровок является максимум производительности системы при условии сохране- ния приемлемой ресурсоемкости поддержки блокировок. Приведем два примера эскалации блокировок. 1. Транзакция заблокировала множество строк таблицы, и при этом все эти строки физически оказались размещенными в одном экстенте. В такой си- туации блокировка множества строк может быть заменена гораздо более эко- номичной блокировкой всего этого экстента или нескольких его страниц. 2. Транзакции требуется выборка и блокировка строк таблицы для вы- полнения их обработки, при этом степень селективности предиката выборки составляет 70% от мощности таблицы, содержащей порядка 1 000 000 строк. В такой ситуации менеджер блокировок может принять решение об эскалации блокировки до уровня таблицы, заменив тем самым 700 000 блокировок строк единственной блокировкой всей таблицы. Примеры выполнения деэскалации блокировок приведены в п. 9.4.2 при рассмотрении специальных режимов блокирования — так называемых блоки- ровок с намерениями. 15 / 19 130 10.4.2. Режимы блокирования Транзакция запрашивает блокировку объекта, необходимую ей для огра- ничения доступа к этому объекту других транзакций, в соответствии с типом операции, выполняемой в рамках транзакции, и требуемого уровня ее изолиро- ванности. СУБД принимает решение о выборе необходимого режима блокиро- вания объекта и, если блокировка в этом режиме не может быть реализована по причине ее несовместимости с ранее установленными блокировками этого объ- екта, ставит транзакцию в очередь для ожидания освобождения объекта от бло- кировок. СУБД поддерживают два основных режима блокирования объектов — монопольная блокировка и совмещаемая блокировка, а также ряд вспомога- тельных режимов (блокировка обновления и различные блокировки с намере- ниями), используемых для повышения эффективности реализации основных режимов блокирования. Совмещаемую блокировку (Shared lock, S) объекта может запрашивать транзакция-читатель, и этот запрос будет выполнен при условии, если объект не заблокирован в монопольном режиме. Наличие совмещаемой блокировки объекта не препятствует другим транзакциям читать заблокированный объ- ект и, соответственно, накладывать на него свои совмещаемые блокировки, что повышает степень параллелизма конкурирующих транзакций и позитивно ска- зывается на производительности системы. При этом транзакции-писатели не получат доступа к этому объекту до снятия с него совмещаемой блокировки, что позволит решить проблемы «гряз- ного чтения», «неповторяющегося чтения» и «кортежей-фантомов» на соответ- ствующих уровнях изолированности транзакции, по запросу которой была установлена совмещаемая блокировка объекта. Если для транзакции установлен 1-й уровень изолированности READ COMMITTED, совмещаемые блокировки снимаются сразу после завершения опе- рации чтения, для более высоких уровней изолированности такие блокировки снимаются только после успешного завершения всей транзакции или ее от- ката. Монопольная блокировка (eXclusive lock, X) решает проблему «последне- го изменения» и устанавливается по запросу транзакции-писателя, модифици- рующей объект, независимо от уровня изолированности, установленного для этой транзакции. Этот режим блокирования несовместим с любыми режима- ми — монопольная блокировка не может быть установлена на объект, заблоки- рованный другими транзакциями как в монопольном, так и в совмещаемом ре- жимах, и при этом совмещаемые блокировки не могут быть установлены на монопольно заблокированный объект. Реализация операций INSERT, UPDATE и DELETE, как правило, требует предварительного чтения данных из таблиц, связанных с модифицируемой таб- лицей (например, для анализа условий выборки модифицируемых строк), по- этому транзакция-писатель, кроме монопольной блокировки обновляемого объ- 16 / 19 131 екта, часто запрашивает также и совмещаемые блокировки связанных с ним объектов. Блокирование объектов в монопольном режиме негативно сказывается на производительности системы как за счет длительного времени ожидания уста- новки самих монопольных блокировок, так и за счет снижения степени парал- лелизма выполнения конкурирующих транзакций, требующих совмещаемых блокировок объектов, заблокированных в монопольном режиме. Для повышения эффективности управления монопольными блокировка- ми СУБД используют различные вспомогательные режимы блокирования, об- суждаемые ниже. Блокировка обновления (Update, U) используется при необходимости обновления объекта и рассматривается как подготовительный этап перед уста- новкой его монопольной блокировки. В отличие от монопольной блокировки, блокировка обновления не конфликтует с совмещаемыми блокировками — она может быть установлена до их снятия и не будет препятствовать завершению установивших их транзакций. При этом блокировка обновления объекта запретит установку на этот объект любых других блокировок и будет ожидать снятия с него ранее установ- ленных совмещаемых блокировок. Как только последняя из них будет снята, статус блокировки обновления будет повышен до монопольной блокировки, после чего транзакция выполнит необходимые обновления объекта. Не допускается одновременная установка нескольких блокировок обнов- ления на один объект, что в ряде случаев позволяет предотвратить возникнове- ние некоторых форм взаимоблокировок, рассматриваемых в п. 10.4.3. Блокировки с намерениями (Intent lock, I) позволяют установить блоки- ровку объекта высокого уровня (например, таблицы) с намерением впослед- ствии провести деэскалацию этой блокировки с понижением уровня блокируе- мого объекта (например, до уровня нескольких строк этой таблицы). Блокировки с намерениями повышают степень параллелизма конкуриру- ющих транзакций, а также позволяют значительно снизить затраты (как вре- менные, так и ресурсные) на установку, снятие и проверку конфликтности бло- кировок. Блокировки с намерениями, так же как и блокировки обновления, мо- гут быть установлены на ранее заблокированные объекты, но, будучи установ- ленными, они препятствуют установке на объект новых блокировок. Если транзакция запрашивает блокировку объекта низкого уровня (например, множества строк таблицы), менеджер блокировок в первую очередь проверяет наличие соответствующей блокировки с намерениями у объекта бо- лее высокого уровня (например, таблицы, экстента или файловой страницы): если такая блокировка с намерениями установлена и если она конфликтует (см. табл. 4.3) с запрашиваемой блокировкой, запрашиваемая блокировка сразу отклоняется, и только в противном случае запускается существенно более дли- тельная процедура проверки конфликтности блокировок на более низких уров- нях блокируемого объекта. 17 / 19 132 СУБД использует несколько разновидностей блокировок с намерениями, основные из которых — это совмещаемая, монопольная и совмещаемая с наме- рением монопольного блокирования. Блокировка с намерением совмещаемого доступа (Intent Shared lock, IS) устанавливается на всю таблицу в начале транзакции, которая намерена читать отдельные строки этой таблицы соответствующими операциями, и заменяет множество низкоуровневых блокировок строк, устанавливаемых непосред- ственно перед выполнением операций чтения. Такой подход позволяет избе- жать длительного ожидания разблокирования низкоуровневых объектов и со- кращает время проверки конфликтности и отклонения монопольных блокиро- вок объекта конкурирующими транзакциями. Блокировка с намерением монопольного доступа (Intent eXclusive lock, IX) используется для блокирования объектов верхнего уровня, в которых необ- ходимо выполнить большое количество изменений. Например, если в середине длинной транзакции встречается операция, требующая массового изменения строк таблицы, то установка блокировки типа IX на всю эту таблицу в начале транзакции позволит сократить время ожидания установки монопольных бло- кировок на модифицируемые строки, так как менеджер блокировок запретит их блокирование другими транзакциями. Совмещаемая блокировка с намерением монопольного доступа (Shared with Intent eXclusive lock, SIX) полезна в ситуациях, когда транзакция выполняет чтение большого объема данных объекта и при этом производит изменение лишь небольшой их части. Менеджер блокировок устанавливает монопольную блоки- ровку намерений типа IX на экстент или группу страниц, которые содержат дан- ные, требующие модификации, а остальные данные остаются доступными для чтения другими транзакциями, которым разрешено устанавливать совмещаемые блокировки намерений на уровне всего объекта и читать ту часть данных, кото- рая не изменяется транзакцией, установившей блокировку типа SIX. СУБД, принимая решения об установке или отклонении блокировок, за- прашиваемых конкурирующими транзакциями, использует информацию о сов- местимости режимов блокирования (см. табл. 4.3). Если к моменту поступления от транзакции запроса на блокировку объекта этот объект оказался заблокиро- ванным другой транзакцией, новая блокировка будет установлена только в том случае, если режим запрашиваемой блокировки совместим с режимом уже су- ществующей блокировки. В противном случае очередная транзакция будет ожидать снятия с объекта несовместимой блокировки. Как видно из таблицы, наиболее бесконфликтным является режим IS — он совместим со всеми режимами блокирования, кроме монопольного (X). Монопольный режим блокирования (X) не совместим ни с одним из ре- жимов: пока транзакция удерживает монопольную блокировку объекта, ни одна из других транзакций не может заблокировать этот объект и произвести его чтение или модификацию, что предотвратит возможность проявления проблем «последнего обновления», «чтения грязных данных» и «неповторяющегося чтения». 18 / 19 133 Таблица 4.3 Совместимость режимов блокирования объектов Установленный на объект режим блокирования Запрашиваемый режим блокирования объекта IS S U IX SIX X С намерением совмещенного доступа IS Да Да Да Да Да Нет Совмещенный доступ S Да Да Да Нет Нет Нет Обновление U Да Да Нет Нет Нет Нет С намерением монопольного доступа IX Да Нет Нет Да Нет Нет Совмещенный с намерением монопольного доступа SIX Да Нет Нет Нет Нет Нет Монопольный доступ X Нет Нет Нет Нет Нет Нет Если транзакция заблокировала объект в совмещенном (S) режиме для его чтения, другие транзакции могут бесконфликтно читать этот объект (S), а также могут устанавливать на него блокировку обновления (U), не дожидаясь завер- шения первой транзакции. При этом другие транзакции не смогут установить на объект блокировку в любом из режимов, требующих его модификации (IX, I, SIX), до момента сня- тия с объекта совмещенной блокировки. Это надежно защитит первую транзак- цию от проявления проблемы «чтения грязных данных», а также (при условии, что совмещенная блокировка не будет снята до момента завершения всей тран- закции) и от проблем «неповторяющегося чтения» и «кортежей-фантомов». 10.4.3. Тупиковые блокировки — прогнозирование и разрушение Тупиковыми блокировками (или просто тупиками — deadlock) называют ситуации, когда две или более конкурирующие транзакции устанавливают та- кие взаимные блокировки объекта, при которых ни одна из транзакций не мо- жет завершиться, пока другая не снимет с объекта своей блокировки (рис. 4.1). Рис. 4.1 Иллюстрация тупиковой блокировки Установленные блокировки обозначены на рисунке сплошными линия- ми — дугами графа, направленными от узлов-транзакций к узлам-объектам, а противоположно направленные (пунктирные) дуги соответствуют блокиров- 19 / 19 134 кам, запрошенным транзакциями, находящимися в очереди ожидания снятия блокировок с объектов. Транзакции T1 и T2 содержат операции чтения и модификации объектов R1 и R2, для обеих этих транзакций установлен 2-й уровень изолированности REPEATABLE READ, при котором снятие блокировок производится только в мо- мент полного завершения транзакции (или ее отката). Транзакции T1 и T2 последовательно запрашивают (и устанавливают) совмещаемые блокировки для чтения объектов, соответственно R1 и R2, а затем последовательно запрашивают монопольные блокировки для изменения других объектов, соответственно R2 и R1. Монопольные блокировки несовместимы с совмещенными (см. табл. 4.3) и не могут быть установлены, пока не будут сняты соответствующие совме- щенные блокировки. В результате обе транзакции будут поставлены в очереди ожидания снятия блокировок с заблокированных объектов, но так и не смогут дождаться своей очередности, так как каждая из них препятствует завершению конкурирующей транзакции и, как следствие, снятию соответствующей блоки- ровки. Для выявления тупиковых блокировок СУБД периодически проводит анализ очередей транзакций, формируемых в системном каталоге базы данных, и в случае обнаружения тупика выполняет его разрушение, жертвуя одной или несколькими транзакциями, участвующими во взаимных блокировках, и вы- полняя их принудительный откат. В качестве жертвы, как правило, выбирается самая «дешевая» из транзак- ций, при этом СУБД ранжирует транзакции с использованием интегральной модели стоимости транзакции, включающей такие параметры, как количество операций и затрагиваемых транзакцией объектов, частота использования тран- закции, ее приоритет и др. Методы распознавания тупиковых блокировок могут быть различными — от простейшего контроля длительности интервала ожидания снятия блокиро- вок, предельное значение которого может задаваться специальным параметром LOCK_TIMEOUT, до выполнения моделирующих прогнозов с использованием ал- горитмов редукции графа ожидания транзакций, иллюстрация одного из кото- рых приведена на рисунке 4.2. Алгоритм редукции графа ожидания транзакций реализуется циклически следующими последовательными этапами. Этап 1. Из графа удаляются все дуги, исходящие из тех вершин- транзакций, в которые не входят дуги от вершин объектов — этим моделирует- ся ситуация, в которой все транзакции, не ожидающие снятия блокировок, установленных другими транзакциями, успешно завершились и освободили за- блокированные ими объекты. Этап 2. Направленность дуг, исходящих из тех вершин-объектов, для ко- торых не осталось входящих дуг от вершин-транзакций, изменяется на проти- воположную — этим моделируется ситуация, в которой все транзакции, ожи- давшие снятия блокировок с объектов, установили свои блокировки. 1 / 19 135 Этап 3. Повторно выполняется 1-й этап алгоритма — и так циклически до тех пор, пока на первом этапе сохраняется возможность удаления дуг, исхо- дящих из вершин-транзакций, в которые не входят дуги от вершин объектов. Этап 4. Если после завершения алгоритма в графе остаются дуги, значит, имеет место тупиковая блокировка, и следует принимать меры по ее разруше- нию, выполняя откат одной из транзакций, а затем, уже для новых условий, по- вторить все этапы алгоритма редукции графа ожидания транзакций. Рис. 4.2 Пример реализации алгоритма редукции графа ожидания транзакций Исходное состояние графа показано на нулевом шаге алгоритма: транзак- ция T1 установила блокировки объектов R1 и R2 и ожидает освобождения объ- екта R3, заблокированного транзакцией T2, которая не претендует на блокиров- ки других объектов. Транзакция T3 установила блокировку объекта R4 и ожида- ет освобождения объекта R1, заблокированного транзакцией T1. На первом шаге удаляется дуга T2 →R3, на втором — дуга R3→T1 заменя- ется на противоположную дугу T1 →R3, далее удаляются все три дуги, исходя- щие из вершины-транзакции T1, затем дуга R2 →T3 меняет свою направленность и, наконец, удаляются две последние дуги графа, что позволяет сделать вывод об отсутствии тупиковой блокировки. Попробуем применить этот алгоритм к графу ожидания транзакций, представленному на рисунке 4.1. На первом этапе алгоритма ни одна из дуг графа не может быть удалена, так как в нем отсутствуют вершины-транзакции, в которые не входят дуги от вершин-объектов — это означает, что ни одна из транзакций не может быть за- вершена, так как обе они ожидают освобождения заблокированных объектов. 2 / 19 136 На втором этапе также невозможно переориентировать ни одну из дуг, исходящих из вершин-объектов, так как в графе отсутствуют вершины-объек- ты, для которых отсутствуют входящие дуги от вершин-транзакций. Как видим, этот граф ожидания транзакций не поддается редукции, из че- го можно сделать вывод о наличии тупиковой блокировки, требующей разре- шения путем принудительного отката одной из конкурирующих транзакций. Заметим, что этот вывод можно сделать на основании визуального образа гра- фа, так как его дуги образуют замкнутый цикл. Иная ситуация представлена на рисунке 4.2 — три транзакции конкури- руют в доступе к четырем объектам базы данных. 10.5. SQL-средства управления транзакциями и блокировками Приведенные ниже синтаксические конструкции и примеры листингов SQL-кода соответствуют требованиям языка Transact SQL, реализованного в MS SQL-Server (начиная с версии 2008). 10.5.1. Уровни изолированности и режимы блокирования После открытия нового соединения по умолчанию устанавливается опре- деленный уровень изолированности транзакций (для MS SQL-Server это уровень READ COMMITED). Для установки требуемого уровня изолированности транзакций использу- ется инструкция SET TRANSACTION ISOLATION LEVEL, формат которой иллюстри- руется листингом 4.1. SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } Листинг 4.1 Формат SQL-инструкции, используемой для установки уровня изолированности транзакций Единственный параметр этой инструкции — требуемый уровень изоли- рованности (обзор уровней изолированности транзакций, за исключением не- стандартного уровня SNAPSHOT, приведен в разделе 9.3). Замечания: 1) если инструкция SET TRANSACTION ISOLATION LEVEL используется в хранимой процедуре, то при возврате управления будет восстановлен уровень изоляции, действовавший к моменту вызова процедуры; 3 / 19 137 2) не включенный в стандарт SQL/92 уровень изолированности SNAPSHOT («моментальный снимок») позволяет отказаться от использования традиционных блокировок строк таблиц за счет хранения последних версий из- мененных строк во временной базе данных; 3) имеется возможность задавать уровни изолированности и режимы блокирования локально для каждого SQL-оператора (SELECT, UPDATE, DELETE, или INSERT), используя для этих целей специальные «подсказки»-хинты (hints) — зарезервированные ключевые слова, записываемые в скобках после слова WITH, как это показано в листинге 4.2. Перечни хинтов и комментарии к их использованию для локального управления уровнями изолированности транзакций и уровнями блокирования объектов БД приведены в таблицах 4.4 и 4.5. SELECT Клиенты.Код_Клиента, Sum(Количество*БазоваяЦенаРеализации) WITH (REPEATABLEREAD) FROM (Клиенты INNER JOIN Заказы ON Клиенты.Код_Клиента = Заказы.Код_Клиента) INNER JOIN Заказано ON Заказы.Код_Заказа = Заказано.Код_Заказа GROUP BY Клиенты.Код_Клиента; Листинг 4.2 Пример использования хинтов Таблица 4.4 Хинты для управления изолированностью транзакций Хинт Условия и результаты использования READUNCOMMITTED Устанавливает уровень изолированности READ UNCOMMITTED READCOMMITTED Устанавливает уровень изолированности READ COMMITTED REPEATABLEREAD Устанавливает уровень изолированности REPEATABLE READ SERIALIZABLE Устанавливает уровень изолированности SERIALIZABLE Таблица 4.5 Хинты для управления уровнями блокирования объектов Хинт Условия и результаты использования ROWLOCK Устанавливает блокировку на уровне строки таблицы PAGLOCK Устанавливает блокировку на уровне файловой страницы TABLOCK Устанавливает блокировку на уровне таблицы и удерживает ее только до конца выполнения операции. Если хинт задан в опера- торе SELECT, другие транзакции могут читать строки таблицы TABLOCKX Устанавливает полное блокирование таблицы, запрещающее дру- гим транзакциям чтение данных HOLDLOCK Удерживает блокировку до конца транзакции, а не снимает ее по- сле завершения операции UPDLOCK Устанавливает блокировку обновления (UPDATE) NOLOCK Снимает блокировки на время выполнения операции SELECT READPAST При выборке данных оператор SELECT будет пропускать строки, заблокированные другими транзакциями, не ожидая их заверше- ния. Используется при условии, что в соединении установлен уровень изолированности READ COMMITTED 4 / 19 138 10.5.2. Программирование начала и завершения транзакций Явные транзакции начинаются с инструкции BEGIN TRANSACTION и за- канчиваются инструкциями COMMIT или ROLLBACK. Инструкция SAVE TRANSACTION используется для создания точек сохранения внутри транзакции. Синтаксис этих инструкций поясняется листингами 4.3–4.5. Начало транзакции BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] Листинг 4.3 Синтаксис инструкции BEGIN TRANSACTION Замечания: 1) имена транзакций transaction_name или соответствующие перемен- ные @tran_name_variable используются только для внешних транзакций; 2) предложение WITH MARK ['description'] позволяет «пометить» транзак- цию параметром 'description' и предписывает сохранять эту пометку в журнале транзакций, что позволит восстанавливать базу данных из резервной копии по журналу транзакций до помеченной транзакции (а не только по дате и времени); 3) если предложение WITH MARK используется без параметра, указание имени транзакции является обязательным — оно будет сохранено в журнале транзакций (вместо отсутствующего параметра 'description') и может быть ис- пользовано при восстановлении базы данных; 4) каждая инструкция BEGIN TRAN производит автоинкремент систем- ной переменной @@TRANCOUNT = @@TRANCOUNT + 1, что позволяет про- граммно контролировать количество активных транзакций (листинг 4.8). Фиксация транзакции COMMIT [ { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] Листинг 4.4 Синтаксис инструкции COMMIT Замечания: 1) значение параметра DELAYED_DURABILITY = OFF присваивает транзак- ции статус «устойчивой» и предписывает сообщать об ее успешной фиксации только после того, как соответствующая запись будет сохранена в журнале транзакций; 2) значение параметра DELAYED_DURABILITY = ON присваивает транзакции статус «отложенно-устойчивой» и предписывает сообщать об ее успешной фикса- ции до того, как соответствующая запись будет сохранена в журнале транзакций; 3) отложенные транзакции получают статус «устойчивы» после сохра- нения журнала транзакций на диск; 5 / 19 139 4) каждая инструкция COMMIT производит автодекремент системной переменной @@TRANCOUNT = @@TRANCOUNT – 1 (листинг 4.8). Точки сохранения Инструкция SAVE TRANSACTION (листинг 4.5) устанавливает внутри тран- закции точку сохранения — именованный маркер, к которому можно выполнить частичный откат транзакции инструкцией ROLLBACK TRANSACTION (листинг 4.6). Таких именованных точек внутри транзакции может быть несколько. SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } Листинг 4.5 Синтаксис инструкции SAVE TRANSACTION Если произошел откат транзакции к точке сохранения, то выполнение транзакции будет продолжено «вниз» от этой точки до ее фиксации (COMMIT) либо отката (ROLLBACK) к началу транзакции или к одной из точек сохранения. Откат транзакции Инструкция ROLLBACK TRANSACTION (листинг 4.6) производит либо пол- ный откат транзакции, либо ее откат до указанной точки сохранения. ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] Листинг 4.6 Синтаксис инструкции ROLLBACK TRANSACTION Замечания: 1) инструкция ROLLBACK TRANSACTION без аргумента savepoint_name или transaction_name выполняет откат к началу транзакции; 2) при наличии вложенных транзакций такая инструкция выполняет от- кат всех вложенных транзакций к началу самой внешней транзакции; 3) инструкция ROLLBACK TRANSACTION без аргумента savepoint_name уменьшает значение системной переменной @@TRANCOUNT до 0; 4) инструкция ROLLBACK TRANSACTION savepoint_name производит ча- стичный откат транзакции до указанной точки сохранения; 5) частичный откат транзакции до точки сохранения не изменяет значе- ния системной переменной @@TRANCOUNT. 10.5.3. Примеры программирования транзакций Листинг 4.7 иллюстрирует эффект отката именованной транзакции: – после создания унарной таблицы запускается именованная транзакция; – в этой транзакции производится вставка трех строк в таблицу; – выполняется безусловный откат к началу транзакции; – вне транзакции производится вставка двух строк в эту же таблицу; 6 / 19 140 – производится безусловная выборка всех строк таблицы, в результате в таблице оказывается только две строки со значениями (4) и (5), вставленными вне транзакции. CREATE TestTran_1 (column_1 int); BEGIN TRAN TranName INSERT INTO TestTran_1 VALUES(1), (2) , (3); ROLLBACK TRAN TranName; INSERT INTO TestTran_1 VALUES(4), (5) ; SELECT column_1 FROM Table_1 ; Листинг 4.7 Пример отката именованной транзакции CREATE TABLE TestTran_2 (a int, b varchar(3)); BEGIN TRANSACTION OuterTran; PRINT @@TRANCOUNT; INSERT INTO TestTran_2 VALUES (1, 'aaa'); BEGIN TRANSACTION InnerTran_1; PRINT @@TRANCOUNT; INSERT INTO TestTran_2 VALUES (2, 'bbb'); BEGIN TRANSACTION InnerTran_2; PRINT @@TRANCOUNT; INSERT INTO TestTran_2 VALUES (3, 'ccc'); COMMIT TRANSACTION InnerTran_2; PRINT @@TRANCOUNT; COMMIT TRANSACTION InnerTran_1; PRINT @@TRANCOUNT; IF @@TRANCOUNT=1 COMMIT TRANSACTION OuterTran; PRINT @@TRANCOUNT; ELSE ROLLBACK TRANSACTION; Листинг 4.8 Пример фиксации и отката вложенных транзакций с контролем счетчика открытых транзакций Листинг 4.8 иллюстрирует фиксацию и откат вложенных транзакций: – создается бинарная таблица TestTran_2; – формируется внешняя транзакция OuterTran — системная переменная @@TRANCOUNTполучает значение 1; – формируются две вложенные транзакции: InnerTran_1 первого уровня вложенности и InnerTran_2 второго уровня — системная переменная @@TRANCOUNTпоследовательно получает значение 2 и затем 3; – последовательно фиксируются все три транзакции, начиная с InnerTran_2 — системная переменная@@TRANCOUNTпоследовательно получа- ет значение 2, затем 1 и затем 0; – если после отката транзакции InnerTran_2 осталась «лишняя» открытая транзакция, производится откат всех транзакций к началу внешней транзакции OuterTran. Листинг 4.9 иллюстрирует использование транзакций в хранимых процеду- рах. 7 / 19 141 CREATE PROCEDURE NewPersonalDisconts @LastYear int, @LastMonth int AS SET @ID_TMP_Table = OBJECT_ID('ПроданоКлиентам'); SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ; BEGIN TRANSACTION IF (@LastYear NOT BETWEEN 2000 AND 2050) OR (@LastMonth NOT BETWEEN 1 AND 12) BEGIN ROLLBACK TRANSACTION PRINT('Ошибка входных данных') RETURN END ; IF @ ID_TMP_Table IS NOT NULL DROP TABLE 'ПроданоКлиентам'; ELSE BEGIN SELECT Клиенты.Код_Клиента, Sum(Количество*БазоваяЦенаРеализации) AS Сумма INTO ПроданоКлиентам FROM (Клиенты INNER JOIN Заказы ON Клиенты.Код_Клиента = Заказы.Код_Клиента) INNER JOIN Заказано ON Заказы.Код_Заказа = Заказано.Код_Заказа WHERE Year(ДатаИсполнения) = @LastYear AND Month(ДатаИсполнения) = @LastMonth GROUP BY Клиенты.Код_Клиента; UPDATE Клиенты INNER JOIN ПроданоКлиентам ON Клиенты.Код_Клиента = ПроданоКлиентам.Код_Клиента SET Клиенты.ПерсональнаяСкидка = 0.00001*ПроданоКлиентам.Сумма; END; DROP TABLE 'ПроданоКлиентам'; COMMIT TRANSACTION RETURN Листинг 4.9 Пример использования транзакции в хранимой процедуре Процедура NewPersonalDisconts обновляет значение персональной скидки клиентам торговой компании (рис. 4.2) пропорционально суммарной стоимости заказанных ими товаров в течение месяца. Процедура содержит одну транзакцию, для которой установлен уровень изолированности REPEATABLE READ, что гарантирует снятие блокировок, уста- новленных этой транзакцией, только по факту ее полного завершения. 8 / 19 142 Процедура контролирует значения переданных ей входных параметров (расчетные год и месяц), и если параметры оказываются некорректными, про- изводится откат транзакции и завершение работы процедуры. В противном случае процедура проверяет наличие в базе данных времен- ной таблицы ПроданоКлиентам, удаляет ее (при наличии) и создает обновлен- ную версию этой таблицы. Далее процедура обновляет значение поля ПерсональнаяСкидка в таблице Клиенты для тех клиентов, которые заказывали товары в расчетном месяце, после чего удаляет временную таблицу и фиксирует транзакцию. Контрольные вопросы и задания 1. Какие из свойств транзакций обеспечиваются SQL-инструкциями BEGIN TRAN, ROLLBACK TRAN и COMMIT? 2. Какие проблемы, связанные с конфликтами конкурирующих тран- закций, решаются на каждом из четырех уровней их изолированности: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE? Приведи- те соответствующие примеры. 3. Для чего используется SQL-инструкция SAVE TRAN и в каких ситуа- циях она может быть полезной? 4. Поясните понятия «режим блокирования» и «уровень блокирова- ния». Перечислите стандартные режимы блокирования объектов. Какие из них и в каких случаях обеспечивают уровень изолированности транзакций REPEATABLE READ? 9 / 19 |