Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 8 Внесение изменений в базу данных 8.1. Особенности и синтаксис предложений модификации Для изменения содержимого базы данных SQL предусматривает три опера- ции: INSERT (вставка строк в таблицу), DELETE (удаление строк из таблицы) и UPDATE (обновление значений в существующих строках таблицы). Операция вставки INSERT может быть единичной либо групповой. Для еди- ничной вставки необходимо явным образом определить значения колонок новой строки. Если указан неполный перечень колонок таблицы, то остав- шиеся колонки получают неопределенное значение ( NULL ). Операция групповой вставки предполагает добавление строк в таблицу из некоторой другой таблицы, указанной явно или с помощью SELECT - предложения. Операция удаления DELETE является групповой, т. е. применяется ко всем стро- кам таблицы, удовлетворяющим требуемому условию. В отличие от запись-ориентированных языков манипулирования данными, SQL не использу- ет понятия текущей строки в таблице, предполагая, что любая строка может быть однозначно идентифицирована в таблице с помощью значения первично- го ключа. Если условие отсутствует, то удаляются все строки таблицы. Усло- вие может использоваться для проверки вхождения значения в некоторую дру- гую таблицу, заданную с помощью SELECT -конструкции. Операция обновления значений в строках таблицы UPDATE также является групповой. Условие определяет, к каким строкам требуется применить опе- рацию обновления. В условии, аналогично DELETE , можно использовать SELECT -конструкции. В SQL большое внимание уделяется обеспечению целостности данных при вы- полнении операций обновления. Предусмотрена возможность учета специальных Часть III. Язык SQL. Изменение данных 176 ограничений целостности. Любые операции, нарушающие такие ограниче- ния, отклоняются. Наиболее часто используемым примером ограничения целостности является ограничение на диапазон допустимых значений в таблицах. Очень часто зна- чения в таблице являются корректными только в том случае, когда они при- сутствуют в одной или нескольких других таблицах, логически связанных между собой. При выполнении удаления или обновления строк для обеспечения целостно- сти данных иногда необходимо выполнять определенные сопутствующие операции в других логически связанных таблицах. Например, удаление строк в одной таблице может сопровождаться удалением связанных строк в одной или нескольких других таблицах. Может также возникнуть необходимость заменить определенные значения связанных строк другой таблицы на неоп- ределенные значения. При этом такие действия могут выполняться рекурсив- но для достаточно сложных многотабличных структур. Такого рода ограничения целостности определяются при создании отдельных таблиц и определении структуры базы данных. Группу операций модификации данных, имеющих логически законченный смысл, после полного выполнения которых база данных останется кор- ректной, называют транзакцией (см. главу 9). В SQL предусмотрены сред- ства управления транзакциями, позволяющие отслеживать выполнение транзакций, обрабатывать возникающие ошибки и координировать обра- ботку базы данных несколькими приложениями или пользователями в па- раллельном режиме. Утверждение COMMIT означает удачное окончание текущей транзакции и на- чало новой. Утверждение ROLLBACK указывает на необходимость выполнения обратного отката, т. е. автоматического восстановления состояния базы дан- ных на момент начала транзакции. В большинстве случаев координация работы в многопользовательском ре- жиме выполняется с помощью механизма блокировок монопольного захвата некоторой части базы данных. Выполнять блокировки можно автоматически, блокируя данные некоторой транзакцией, как только к ним происходит об- ращение, и освобождать их при обработке COMMIT и ROLLBACK В SQL можно блокировать таблицы в монопольном режиме (чтение и запись со стороны других транзакций откладываются до момента окончания тран- закции) или в режиме разделения (откладываются только обновления со сто- роны других транзакций). Глава 8. Внесение изменений в базу данных 177 8.2. Предложение DELETE Предложение DELETE имеет формат DELETE FROM { имя_таблицы | ONLY (имя_таблицы) } [ { WHERE условие_поиска | WHERE CURENT OF имя_курсора } ]; и позволяет удалить содержимое всех строк указанной таблицы (при отсутст- вии WHERE -фразы) или тех ее строк, которые определяются WHERE -фразой. FROM имя_таблицы Указывается таблица ( имя_таблицы ), из которой будут удаляться строки. ONLY (имя_таблицы) Запрещается каскадное распространение удаления записей на подтаблицы целевой таблицы или представления. WHERE условие_поиска Устанавливается поисковый критерий с использованием одного или несколь- ких условий_поиска , которые обеспечивают удаление только указанных строк. WHERE CURENT OF имя_курсора Удаляет текущую запись в объявленном и открытом курсоре (см. разд. 17.7) с именем имя_курсора 8.2.1. Удаление единственной записи Удалить поставщика с Код_поставщика=7 DELETE FROM Поставщики WHERE Код_поставщика=7; Если таблица Поставки содержит в момент выполнения этого предложения какие-либо поставки для поставщика с Код_поставщика=7 , то такое удаление нарушит непротиворечивость базы данных. К сожалению, нет операции уда- ления, одновременно воздействующей на несколько таблиц. Однако в неко- торых СУБД реализованы механизмы поддержания целостности (см. разд. 2.4 и главу 14), позволяющие отменить некорректное удаление или кас- кадировать удаление в нескольких таблицах. 8.2.2. Удаление множества записей Удалить все поставки: DELETE FROM Поставки; Часть III. Язык SQL. Изменение данных 178 Удалить все мясные блюда: DELETE FROM Блюда WHERE Основа = 'Мясо'; 8.2.3. Удаление с вложенным подзапросом Удалить все поставки для поставщика из Паневежиса: DELETE FROM Поставки WHERE Код_поставщика IN (SELECT Код_поставщика FROM Поставщики WHERE Город = 'Паневежис'); 8.3. Предложение INSERT Предложение INSERT имеет формат INSERT INTO [ ONLY ] {имя_таблицы | имя_представления} [ ( столбец [,...] ) ] { (DEFAULT VALUES | VALUES (скалярное_выражение [,...] ) | предложение_SELECT ); и позволяет записать строки в указанную таблицу (представление). Здесь: ONLY Запрещает вставлять в подтаблицы значения, которые вставляются в таблицу имя_таблицы { имя_таблицы | имя_представления } [ ( столбец [,...] ) ] Объявляет обновляемую целевую таблицу или представление, в которое бу- дут вставляться данные. Список столбцов можно не указывать, если данные будут вставляться во все столбцы и их порядок соответствует порядку столб- цов в описании таблицы. DEFAULT VALUES В таблицу вставляются все значения, определяемые параметром DEFAULT столбца (значения по умолчанию), если таковые существуют, в противном случае вставляются пустые значения ( NULL ). VALUES (скалярное_выражение [,...] ) Указываются скалярные_выражения , значения которых будут вставляться в целевую таблицу. Количество вставляемых скалярных_выражений должно Глава 8. Внесение изменений в базу данных 179 в точности совпадать с количеством столбцов в списке столбцов. Более того, их значения должны быть совместимы по типу и размеру со столбцами целе- вой таблицы. Каждое скалярное_выражение в списке соответствует столбцу в списке столбцов с тем же порядковым номером. Таким образом, данные из первого скалярного_выражения попадают в первый столбец, данные из вто- рого — во второй и т. д. до тех пор, пока все столбцы не будут заполнены. При желании можно использовать ключевое слово DEFAULT , чтобы вставить в столбец значение, заданное по умолчанию, или NULL , чтобы вставить пустое значение. скалярное_выражение Это любое одиночное значение, например строковая константа или числовое значение, скалярная функция или скалярный подзапрос. предложение_SELECT Строки, извлекаемые данным предложением SELECT , вставляются в целевую таблицу или представление. Значения, извлекаемые SELECT , должны прямо соответствовать столбцам, указанным в списке столбцов. На целевую табли- цу или представление нельзя ссылаться во фразе FROM или JOIN предложения SELECT 8.3.1. Вставка единственной записи в таблицу Добавить в таблицу Блюда блюдо: Шашлык (Код_блюда - 34, Блюдо - Шашлык, В - 3, Основа - Мясо, Выход - 150) при неизвестной пока трудоемкости приготовления этого блюда. INSERT INTO Блюда (БЛ, Блюдо, В, Основа, Выход) VALUES (34, 'Шашлык', 3, 'Мясо', 150); Создается новая запись для блюда с номером 34, с неопределенным значени- ем в столбце Труд Порядок полей в INSERT не обязательно должен совпадать с порядком полей, в котором они определялись при создании таблицы. Вполне допустима и та- кая версия предыдущего предложения: INSERT INTO Блюда (Основа, В, Блюдо, БЛ, Выход) VALUES ('Мясо', 3, 'Шашлык', 34, 150); Часть III. Язык SQL. Изменение данных 180 При известной трудоемкости приготовления шашлыка (например, 5 коп.) сведения о нем можно ввести с помощью укороченного предложения: INSERT INTO Блюда VALUES (34, 'Шашлык', 3, 'Мясо', 150, 5); в котором должен соблюдаться строгий порядок перечисления вводимых значений. Так как, не имея перечня загружаемых столбцов, СУБД может ис- пользовать лишь перечень, который определен при создании модифицируе- мой таблицы. В предыдущих примерах проводилась модификация стержневой сущности, т. е. таблицы с первичным ключом Код_блюда (см. разд. 2.4). Почти все СУБД имеют механизмы для предотвращения ввода неуникального первичного ключа, например, ввода "Шашлыка" под номером, меньшим 34. А как быть с ассоциациями или другими таблицами, содержащими внешние ключи? Пусть, например, потребовалось добавить в рецепт блюда Салат летний ( Код_блюда=1 ) немного (15 г) лука ( Код_продукта=10 ), и мы воспользовались предложением INSERT INTO Состав (Код_блюда, Код_продукта, Вес) VALUES (1, 10, 15); Подобно операции DELETE операция INSERT может нарушить непротиворечи- вость базы данных. Если не принять специальных мер, то СУБД не проверя- ет, имеется ли в таблице Блюда блюдо с первичным ключом Код_блюда=1 и в таблице Продукты — продукт с первичным ключом Код_продукта=10 . От- сутствие любого из этих значений породит противоречие: в базе появится ссылка на несуществующую запись. Проблемы, возникающие при использо- вании внешних ключей, подробно рассмотрены в главе 14, а здесь отметим, что все "приличные" СУБД имеют механизмы для предотвращения ввода записей со значениями внешних ключей, отсутствующих среди значений со- ответствующих первичных ключей. 8.3.2. Вставка множества записей Создать временную таблицу К_меню , содержащую калорийность и стоимость всех блюд, которые можно приготовить из имеющихся продуктов. (Эта таб- лица будет использоваться шеф-поваром для составления меню на следую- щий день.) Глава 8. Внесение изменений в базу данных 181 Для создания описания временной таблицы можно, например, воспользо- ваться предложением CREATE TABLE К_меню ( Код_вида NUMBER(2), Блюдо VARCHAR2(16), Калор_блюда NUMBER(4), Стоим_блюда NUMBER(4,2) )'; а для ее загрузки данными, предложением INSERT с вложенным подзапросом: INSERT INTO К_меню SELECT Блюда.Код_вида, Блюдо, ROUND(SUM(((Белки+Углев)*4.1+Жиры*9.3) * Вес/1000)) Колор_блюда, ROUND((SUM(Стоимость/К_во*Вес/1000) + MIN(Труд/100))*10,2) Стоим_блюда FROM Блюда, Виды_блюд, Состав, Продукты, Наличие WHERE Блюда.Код_блюда = Состав. Код_блюда AND Состав.Код_продукта = Продукты.Код_продукта AND Состав.Код_продукта = Наличие.Код_продукта AND Блюда.Код_вида = Виды_блюд.Код_вида AND Блюда.Код_блюда NOT IN (SELECT Код_блюда FROM Состав WHERE Код_продукта IN (SELECT Код_продукта FROM Наличие WHERE К_во = 0)) GROUP BY Блюда.Код_вида, Блюдо ORDER BY Блюда.Код_вида, Колор_блюда; В этом запросе предложение SELECT выполняется так же, как обычно, но ре- зультат не выводится на экран, а копируется в таблицу К_меню . Теперь с этой копией можно работать как с обычной базовой таблицей ( Блюда , Продукты и пр.), т. е. выбирать из нее данные на экран или принтер, обновлять в ней данные и т. п. Никакая из этих операций не будет оказывать влияния на ис- ходные данные (например, изменение в ней названия блюда Салат летний на Салат весенний не приведет к подобному изменению в таблице Блюда , где сохранится старое название). Так как это может привести к противоречиям, то подобные временные таблицы уничтожают после их использования. По- этому программа, обслуживающая шеф-повара, должна исполнять предложе- ние DROP TABLE К_меню после того, как будет закончено составление меню. Часть III. Язык SQL. Изменение данных 182 8.4. Предложение UPDATE Предложение UPDATE имеет формат UPDATE [ ONLY ] { имя_таблицы | имя_представления } SET {{имя_столбца = { DEFAULT | NULL | скалярное_выражение }, имя_столбца = { DEFAULT | NULL | скалярное_выражение } [,...] } [ WHERE условие_поиска | WHERE CURENT OF имя_курсора ]; и позволяет изменить данные в существующей таблице. (Соблюдайте осто- рожность при использовании UPDATE без фразы WHERE , поскольку при этом будут затронуты все строки таблицы.) ONLY Запрещает распространять обновление на подтаблицы целевой таблицы или представления. имя_таблицы | имя_представления Обновляемая целевая таблица или представление. Обновление представле- ний подчиняется особым правилам (см. разд. 7.3.4). SET Столбцу или строке присваивается определенное значение. имя_столбца Используется вместе с фразой SET (например, SET Блюдо = 'Картофель, запеченный с грибами в сметанном соусе' ). Позволяет присваивать столб- цу определенное значение. В одном UPDATE можно обновлять значения в нескольких столбцах, но нельзя обновлять значение одного столбца не- сколько раз. DEFAULT Для столбца устанавливается значение, заданное по умолчанию при опреде- лении таблицы. скалярное_выражение Столбцу присваивается любое одиночное значение, например строковая кон- станта или числовое значение, скалярная функция или скалярный подзапрос. WHERE условие_поиска Устанавливается поисковый критерий с использованием одного или несколь- ких условий_поиска , которые обеспечивают обновление только указанных строк. WHERE CURENT OF имя_курсора Обновляет текущую запись в объявленном и открытом курсоре (см. разд. 17.7) с именем имя_курсора Глава 8. Внесение изменений в базу данных 183 8.4.1. Обновление единственной записи Изменить название блюда с кодом Код_блюда=5 на Форшмак, увеличить его выход на 30 г и установить NULL -значение в столбец Труд UPDATE Блюда SET Блюдо = 'Форшмак', Выход = (Выход+30), Труд = NULL WHERE Код_блюда = 5; 8.4.2. Обновление множества записей Утроить цену всех продуктов таблицы поставки кроме кофе ( Код_продукта=17 ). UPDATE Поставки SET Цена = Цена*3 WHERE Код_продукта <> 17; 8.4.3. Обновление с подзапросом Установить равной нулю цену и количество продуктов для поставщиков из Паневежиса и Резекне. UPDATE Поставки SET Цена = 0, К_во = 0 WHERE Код_поставщика IN (SELECT Код_поставщика FROM Поставщики WHERE Город IN ('Паневежис', 'Резекне')); 8.4.4. Обновление нескольких таблиц Пусть требуется изменить в базе данных Код_продукта=13 на Код_продукта=20 Так как столбец Код_продукта встречается в трех таблицах ( Продукты , Состав и Поставки ), а синтаксис UPDATE не позволяет одновременно обновлять более одной таблицы, то приходится выдавать три сходных запроса: UPDATE Продукты SET Код_продукта = 20 WHERE Код_продукта = 13; Часть III. Язык SQL. Изменение данных 184 UPDATE Состав SET Код_продукта = 20 WHERE Код_продукта = 13; UPDATE Поставки SET Код_продукта = 20 WHERE Код_продукта = 13; Это может привести к противоречию в базе данных (нарушению целостности по ссылкам), поскольку после выполнения первого предложения таблицы Состав и Поставки будут ссылаться на уже несуществующий продукт. База станет непротиворечивой только после выполнения третьего запроса. Глава 9 Транзакции и параллелизм 9.1. Что такое транзакция В разд. 8.4.4 рассматривался пример, в котором требовалось изменить Код_продукта=13 на новое значение Код_продукта=20 и для этого пришлось проводить последовательное изменение в трех таблицах. UPDATE Продукты SET Код_продукта=20 WHERE Код_продукта=13; UPDATE Состав SET Код_продукта=20 WHERE Код_продукта=13; UPDATE Поставки SET Код_продукта=20 WHERE Код_продукта=13; Этот пример приведен здесь для иллюстрации того, что единственная, с точ- ки зрения пользователя, операция может потребовать нескольких операций над базой данных. Более того, в ходе выполнения этих операций может на- рушаться непротиворечивость базы данных. Например, в ней могут временно содержаться записи поставок, для которых не имеется соответствующих записей поставляемых продуктов. Положение не спасает и перестановка по- следовательности обновляемых таблиц. Противоречивость исчезнет только после выполнения всех обновлений, т. е. выполнения логической единицы работы — полной замены кода продукта в базе данных (независимо от коли- чества таблиц, в которых встречается код продукта). Теперь можно дать определение транзакции. Транзакция, или логическая единица работы, — это в общем случае последо- вательность ряда таких операций, которые преобразуют некоторое непроти- воречивое состояние базы данных в другое непротиворечивое состояние, не гарантируя сохранения непротиворечивости во все промежуточные моменты времени. Часть III. Язык SQL. Изменение данных 186 В литературе для объяснения транзакции обычно приводится следующий классический пример. Необходимо перевести с банковского счета номер 5 на счет номер 7 сумму в 10 денежных единиц. Этого можно достичь, например, такой последовательностью действий: Начать транзакцию прочесть баланс на счету номер 5 уменьшить баланс на 10 денежных единиц сохранить новый баланс счета номер 5 прочесть баланс на счету номер 7 увеличить баланс на 10 денежных единиц сохранить новый баланс счета номер 7 Окончить транзакцию Эти действия представляют собой логическую единицу работы "перевод суммы между счетами", и таким образом, являются транзакцией. Если пре- рвать данную транзакцию, к примеру, в середине, и не аннулировать все из- менения, легко оставить владельца счета номер 5 без 10 единиц, тогда как владелец счета номер 7 их не получит. Никто кроме пользователя, генерирующего ту или иную последовательность SQL-предложений, не может знать о том, когда может возникнуть противоре- чивое состояние базы данных и после выполнения каких SQL-предложений оно исчезнет, т. е. база данных вновь станет актуальной. Поэтому в большинст- ве СУБД создается механизм обработки транзакций, при инициировании кото- рого все изменения данных рассматриваются как предварительные до тех пор, пока пользователь (реже система) не выдаст предложения: COMMIT (фиксировать), превращающее все предварительные обновления в окончательные ("зафиксированные"); ROLLBACK (откат), аннулирующее все предварительные обновления. Таким образом, транзакцией можно назвать последовательность SQL- предложений, расположенных между "точками синхронизации", учреж- даемых в начале выполнения программы и издании COMMIT или ROLLBACK , и только в этих случаях. При этом следует иметь в виду, что возможен неяв- ный COMMIT (существует режим AUTOCOMMIT , в котором система издает COMMIT после выполнения каждого SQL-предложения) и ROLLBACK (выполняемый при аварийном завершении программы). Ясно теперь, что пользователь должен сам решать, включать ли механизм обработки транзакций и если включать, то где издавать COMMIT ( ROLLEBACK ), т. е. какие последовательности SQL-предложений являются транзакциями. Глава 9. Транзакции и параллелизм 187 9.2. Предложения COMMIT, ROLLBACK и SAVEPOINT Предложение COMMIT явным образом закрывает открытую транзакцию и де- лает изменения в базе данных постоянными. Открываться транзакции могут неявно, например, при выполнении предложений INSERT , DELETE или UPDATE , или явно, с помощью предложения START (в Oracle все транзакции запуска- ются неявно). В любом случае открытая транзакция закрывается явно пред- ложением COMMIT Синтаксис этого предложения очень прост: COMMIT [WORK] Здесь необязательное слово WORK является пустым и не оказывает никакого влияния. Предложение ROLLBACK возвращает транзакцию в ее исходное состояние или к определенной, заранее заданной точке сохранения ( SAVEPOINT ). Кроме того, это предложение закрывает все открытые курсоры (см. разд. 17.7). Синтаксис предложения имеет вид: ROLLBACK [WORK] [TO SAVEPOINT имя_точки_сохранения] Здесь, как и в предложении COMMIT , необязательное слово WORK является пус- тым и не оказывает никакого влияния. [TO SAVEPOINT имя_точки_сохранения] Позволяет не отменять всю транзакцию, а откатить ее к указанной точке со- хранения (т. е. выполнить частичный откат). Параметр имя_точки_сохранения может представлять собой постоянное выражение или переменную. Если TO SAVEPOINT опущено, то закрываются все курсоры, в противном случае закры- ваются только те курсоры, которые были открыты соответствующим пред- ложением SAVEPOINT Предложение SAVEPOINT разделяет транзакцию на логические точки сохра- нения. В одной транзакции может быть несколько точек сохранения. Синтаксис этого предложения имеет вид: SAVEPOINT имя_точки_сохранения В текущей транзакции устанавливается точка сохранения с именем имя_точки_сохранения Часть III. Язык SQL. Изменение данных 188 9.3. Многопользовательский режим работы 9.3.1. Параллелизм транзакций Поддержание механизма транзакций — показатель уровня развитости СУБД и основа обеспечения целостности базы данных. Транзакции также состав- ляют основу изолированности в многопользовательских системах, где с од- ной базой данных параллельно могут работать несколько пользователей и (или) прикладных программ. Одна из основных задач СУБД — обеспече- ние изолированности, т. е. создание такого режима функционирования, при котором каждому пользователю казалось бы, что база данных доступна толь- ко ему. Такую задачу СУБД принято называть параллелизмом транзакций. Большинство выполняемых действий производится в теле транзакций. По умолчанию каждая команда выполняется как самостоятельная транзакция. Как было показано ранее, при необходимости пользователь может явно ука- зать ее начало и конец, чтобы иметь возможность включить в нее несколько команд. При выполнении транзакции система управления базами данных должна придерживаться определенных правил обработки набора команд, входящих в транзакцию. В частности, разработано четыре правила, известные как тре- бования ACID (Atomicity, Consistency, Isolation, Durability — неделимость, согласованность, изолированность, устойчивость), гарантирующих правиль- ность и надежность работы системы. Транзакция неделима в том смысле, что представляет собой единое целое. Все ее компоненты либо имеют место, либо нет. Не бывает частичной транзакции. Если может быть выполнена лишь часть транзакции, она от- клоняется. Транзакция является согласованной, потому что не нарушает бизнес- логику и отношения между элементами данных. Это свойство очень важ- но при разработке клиент-серверных систем, поскольку в хранилище дан- ных поступает большое количество транзакций от разных систем и объек- тов. Если хотя бы одна из них нарушит целостность данных, то все остальные могут выдать неверные результаты. Транзакция всегда изолирована, поскольку ее результаты самодостаточны. Они не зависят от предыдущих или последующих транзакций — это свой- ство называется сериализуемостью и означает, что транзакции в последо- вательности независимы. Транзакция устойчива. После своего завершения она сохраняется в систе- ме, которую ничто не может вернуть в исходное (до начала транзакции) Глава 9. Транзакции и параллелизм 189 состояние, т. е. происходит фиксация транзакции, означающая, что ее дей- ствие постоянно даже при сбое системы. При этом подразумевается некая форма хранения информации в постоянной памяти как часть транзакции. Указанные ранее правила выполняет сервер. Программист лишь выбирает нужный уровень изоляции, заботится о соблюдении логической целостности данных и бизнес-правил. На него возлагаются обязанности по созданию эф- фективных и логически верных алгоритмов обработки данных. Он решает, какие команды должны выполняться как одна транзакция, а какие могут быть разбиты на несколько последовательно выполняемых транзакций. Следует по возможности использовать небольшие транзакции, т. е. включающие как можно меньше команд и изменяющие минимум данных. Соблюдение этого требования позволит наиболее эффективным образом обеспечить одновре- менную работу с данными множества пользователей. 9.3.2. Блокировки Повышение эффективности работы при использовании небольших транзак- ций связано с тем, что при выполнении транзакции сервер накладывает на данные блокировки. Блокировкой называется временное ограничение на выполнение некоторых операций обработки данных. Блокировка может быть наложена как на от- дельную строку таблицы, так и на всю базу данных. Управлением блокиров- ками на сервере занимается менеджер блокировок, контролирующий их при- менение и разрешение конфликтов. Транзакции и блокировки тесно связаны друг с другом. Транзакции накладывают блокировки на данные, чтобы обес- печить выполнение требований ACID. Без использования блокировок несколь- ко транзакций могли бы изменять одни и те же данные. Блокировка представляет собой метод управления параллельными процесса- ми, при котором объект базы данных не может быть модифицирован без ве- дома транзакции. Производится блокирование доступа к объекту со стороны других транзакций, чем исключается непредсказуемое изменение объекта. Различают два вида блокировки: блокировка записи — транзакция блокирует строки в таблицах таким об- разом, что запрос другой транзакции к этим строкам будет отменен; блокировка чтения — транзакция блокирует строки так, что запрос со сто- роны другой транзакции на блокировку записи этих строк будет отверг- нут, а на блокировку чтения — принят. Часть III. Язык SQL. Изменение данных 190 В СУБД используют протокол доступа к данным, позволяющий избежать проблемы параллелизма. Его суть заключается в следующем: транзакция, результатом действия которой на строку данных в таблице является ее извлечение, обязана наложить блокировку чтения на эту строку; транзакция, предназначенная для модификации строки данных, наклады- вает на нее блокировку записи; если запрашиваемая блокировка на строку отвергается из-за уже имею- щейся блокировки, то транзакция переводится в режим ожидания до тех пор, пока блокировка не будет снята; блокировка записи сохраняется вплоть до конца выполнения транзакции. Решение проблемы параллельной обработки базы данных заключается в том, что строки таблиц блокируются, а последующие транзакции, модифицирую- щие эти строки, отвергаются и переводятся в режим ожидания. В связи со свойством сохранения целостности базы данных транзакции являются под- ходящими единицами изолированности пользователей. Действительно, если каждый сеанс взаимодействия с базой данных реализуется транзакцией, то пользователь начинает с того, что обращается к согласованному состоянию базы данных — состоянию, в котором она могла бы находиться, даже если бы пользователь работал с ней в одиночку. Если бы в СУБД не были реализованы механизмы блокирования, то при одновременном чтении и изменении одних и тех же данных несколькими пользователями могли бы возникнуть следующие проблемы одновременно- го доступа: проблема последнего изменения возникает, когда несколько пользовате- лей изменяют одну и ту же строку, основываясь на ее начальном значе- нии; тогда часть данных будет потеряна, т. к. каждая последующая тран- закция перезапишет изменения, сделанные предыдущей. Выход из этой ситуации заключается в последовательном внесении изменений; проблема "грязного" чтения возможна в том случае, если пользователь выполняет сложные операции обработки данных, требующие множест- венного изменения данных перед тем, как они обретут логически верное состояние. Если во время изменения данных другой пользователь будет считывать их, то может оказаться, что он получит логически неверную информацию. Для исключения подобных проблем необходимо произво- дить считывание данных после окончания всех изменений; проблема неповторяемого чтения является следствием неоднократного считывания транзакцией одних и тех же данных. Во время выполнения первой транзакции другая может внести в данные изменения, поэтому при Глава 9. Транзакции и параллелизм 191 повторном чтении первая транзакция получит уже иной набор данных, что приведет к нарушению их целостности или логической несогласо- ванности; проблема чтения фантомов появляется после того, как одна транзакция выбирает данные из таблицы, а другая вставляет или удаляет строки до завершения первой. Выбранные из таблицы значения будут некорректны. Для решения перечисленных проблем в специально разработанном стандарте определены четыре уровня блокирования. Уровень изоляции транзакции оп- ределяет, могут ли другие (конкурирующие) транзакции вносить изменения в данные, измененные текущей транзакцией, а также может ли текущая тран- закция видеть изменения, произведенные конкурирующими транзакциями, и наоборот. Каждый последующий уровень поддерживает требования пре- дыдущего и налагает дополнительные ограничения: уровень 0 — запрещение "загрязнения" данных. Этот уровень требует, чтобы изменять данные могла только одна транзакция; если другой тран- закции необходимо изменить те же данные, она должна ожидать заверше- ния первой транзакции; уровень 1 — запрещение "грязного" чтения. Если транзакция начала из- менение данных, то никакая другая транзакция не сможет прочитать их до завершения первой; уровень 2 — запрещение неповторяемого чтения. Если транзакция считы- вает данные, то никакая другая транзакция не сможет их изменить. Таким образом, при повторном чтении они будут находиться в первоначальном состоянии; уровень 3 — запрещение фантомов. Если транзакция обращается к дан- ным, то никакая другая транзакция не сможет добавить новые или удалить имеющиеся строки, которые могут быть считаны при выполнении транзак- ции. Реализация этого уровня блокирования выполняется путем использо- вания блокировок диапазона ключей. Подобная блокировка накладывает- ся не на конкретные строки таблицы, а на строки, удовлетворяющие определенному логическому условию. |