|
||
|
Business Software | ERP | CRM | BI | ECM | DMS | SCM > BI Системы бизнес-анализа > Microsoft SQL Server 2005/2008 > Техническая информация > Язык запросов SQL - учебник > 6-й час. Управление транзакциями
6-й час. Управление транзакциями
На этом уроке мы обсудим понятия, лежащие в основе управления транзакциями базы данных. Основными на этом уроке будут следующие темы. • Определение транзакции
Транзакция может представляться одним оператором DML или группой таких операторов. При управлении группами транзакций под успешным завершением выполнения понимается успешное завершение выполнения всех транзакций группы, иначе все они считаются не завершившимися успешно. Следующий список раскрывает природу транзакций. • Каждая транзакция имеет начало и конец.
Что такое управление транзакциями?
При успешном завершении выполнения транзакции соответствующая таблица не изменяется немедленно, хотя соответствующие сообщения вывода создают именно такое впечатление. Для успешно завершившихся транзакций предусмотрены команды управления транзакциями, позволяющие либо сохранить в базе данных все предлагаемые транзакцией изменения, либо отменить их. Управление транзакциями осуществляется с помощью следующих трех команд: • COMMIT
После завершения транзакции соответствующая ей информация сохраняется в специальном разделе (области) базы данных, предназначенной для хранения временных данных. Все соответствующие изменения будут сохраняться в этой области, пока к ним не будет применена соответствующая команда управления транзакциями. В результате применения такой команды соответствующие изменения либо вносятся в базу данных, либо отменяются, после чего область для хранения временных данных освобождается. Рис. 6.1 иллюстрирует процесс внесения изменений в реляционную базу данных. Команда COMMIT используется для передачи базе данных (сохранения) изменений, инспирированных транзакцией. Команда COMMIT сохраняет все транзакции, выполненные с момента предыдущего применения либо команды COMMIT, либо команды ROLLBACK.
Рис. 6.1. Область изменений, допускающих отмену Синтаксис команды следующий.
Здесь обязательным является только ключевое слово COMMIT вместе с зависящим от реализации символом или командой, означающей завершение оператора. Ключевое слово WORK абсолютно необязательно — его единственной целью является придание команде более понятного вида. Начнем с выбора всех данных из таблицы PRODUCTS_TMP.
11235 КОСТЮМ ВЕДЬМЫ 29.99 222 ПЛАСТИКОВЫЕ ТЫКВЫ 7.75 13 ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 1.1 90 ФОНАРИ 14.5 15 КОСТЮМЫ В АССОРТИМЕНТЕ 10 9 СЛАДКАЯ КУКУРУЗА 1.35 6 ТЫКВЕННЫЕ КОНФЕТЫ 1.45 87 ПЛАСТИКОВЫЕ ПАУКИ 1.05 119 МАСКИ В АССОРТИМЕНТЕ 4.95 1234 ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ 5.95 2345 ПОЛОЧКА ИЗ ДУБА 59.99 11 строк выбраны. Затем удалим из таблицы все записи для товаров, стоимость которых меньше $14,00.
WHERE COST < 14;
Для того чтобы передать базе данных изменения, проведенные транзакцией, применяется оператор COMMIT:
При внесении в базу данных большого количества изменений настоятельно рекомендуется использовать команду COMMIT как можно чаще. Хотя слишком частое применение команды COMMIT приводит к существенному замедлению выполнения запрашиваемых операций. Не забывайте о том, что все изменения сначала размещаются в области, допускающей отмену изменений. Если эта область переполнится и не сможет принять очередную порцию информации о вносимых изменениях, база данных скорее всего совсем перестанет отвечать на запросы, что сделает дальнейшее осуществление транзакций вообще невозможным.
Команда ROLLBACK используется для отмены транзакций, которые еще не были сохранены в базе данных. Команду ROLLBACK можно использовать для отмены только тех транзакций, которые были выполнены после применения последней из команд COMMIT или ROLLBACK. Синтаксис команды ROLLBACK следующий.
Как и в операторе COMMIT, ключевое слово WORK здесь тоже является необязательным. В следующем примере сначала выберем все записи из таблицы PRODUCTS_TMP, оставшиеся в ней после удаления 8 записей.
11235 КОСТЮМ ВЕДЬМЫ 29.99 90 ФОНАРИ 14.5 2345 ПОЛОЧКА ИЗ ДУБА 59.99 3 строки выбраны. Затем изменим таблицу, приписав стоимость $39.99 товару с кодом 11235.
SET COST = 39.99 WHERE PROD_ID = "11235';
Запрос к таблице покажет, что изменения как будто бы внесены. SELECT * FROM PRODUCTS_TMP;
11235 КОСТЮМ ВЕДЬМЫ 39.99 90 ФОНАРИ 14.5 2345 ПОЛОЧКА ИЗ ДУБА 59.99 3 строки выбраны. Теперь используем команду ROLLBACK, чтобы отменить последние изменения.
Наконец, проверим, что изменения не были переданы базе данных.
11235 КОСТЮМ ВЕДЬМЫ 29.99 90 ФОНАРИ 14.5 2345 ПОЛОЧКА ИЗ ДУБА 59.99 3 строки выбраны. Команда SAVEPOINT определяет точку в транзакции, к которой можно будет возвратиться, чтобы не отменять всю транзакцию. Синтаксис команды SAVEPOINT следующий.
Эта команда предназначена только для создания точек отката между операторами транзакций. Для отмены групп транзакций используется команда ROLLBACK. Команда SAVEPOINT позволяет упростить процесс управления транзакциями путем разделения больших групп транзакций на более мелкие и лучше управляемые группы.
Команда возврата к точке отката имеет следующий синтаксис.
В следующем примере мы сначала удалим оставшиеся три. записи из таблицы, но перед каждым удалением создадим по точке отката, чтобы иметь возможность вернуться к любой из них и вернуть соответствующие данные в их прежнее состояние. SAVEPOINT SPl; Точка отката создана. DELETE FROM PRODUCTSJTMP WHERE PROD.ZD - '11235'; 1 строка удалена. SAVEPOINT SP2; Точка отката создана. DELETE FROM PRODUCTS_TMP WHERE PROD_ID - '90'; 1 строка удалена. SAVEPOINT SP3; Точка отката создана. DELETE FROM PRODUCTSJTMP WHERE PROD_ID - '2345'; 1 строка удалена. Теперь после выполнения всех удалений вы имеете возможность передумать и вернуться, например, к точке отката, которую вы назвали SP2. Поскольку SP2 была создана после выполнения первого удаления, возврат к ней означает отмену двух последних удалений. ROLLBACK SP2; Отмена выполнена. При откате к точке SP2 оказывается выполненным только первое удаление. SELECT * FROM PRODUCTS_TMP; PROD_ID PROD_DESC COST 90 ФОНАРИ 14.5 2345 ПОЛОЧКА ИЗ ДУБА 59.99 2 строки выбраны. Помните, что сама по себе команда ROLLBACK возвращает к состоянию, сложившемуся после применения последнего из операторов COMMIT или ROLLBACK. В нашем случае операторы COMMIT не применялись, поэтому будут отменены все удаления: ROLLBACK; Отмена выполнена. SELECT * FROM PRODUCTS_TMP; PPOD_ID PROD_DESC COST 11235 КОСТЮМ ВЕДЬМЫ 29.99 90 ФОНАРИ 14.5 2345 ПОЛОЧКА ИЗ ДУБА 59.99 3 строки выбраны.
Команда RELEASE SAVEPOINT используется для удаления точек отката. После удаления точки отката у вас не будет возможности осуществить откат к этой точке с помощью КОМаНДЫ ROLLBACK.
Команда SET TRANSACTION используется для инициализации транзакций. Эта команда используется для задания характеристик транзакций, следующих после нее. Так, можно потребовать, чтобы транзакции выполняли только чтение данных либо осуществляли и чтение, и запись. Например, SET TRANSACTION READ WRITE; SET TRANSACTION READ ONLY; Транзакциям можно назначить и другие характеристики, но их рассмотрение выходит за рамки этой книги. Дополнительную информацию вы найдете в документации той реализации SQL, которую используете. Управление транзакциями и производительность базы данных Недостаточный контроль за транзакциями может замедлить работу базы данных и даже совсем блокировать ее. Частое замедление работы базы данных может быть следствием недостаточного контроля за транзакциями при массовом вводе, обновлении или удалении данных. Эти процессы не только сами по себе требуют больших ресурсов процессора и памяти, но и вызывают заполнение области хранения временных данных, освобождающейся только в результате выполнения команд COMMIT И ROLLBACK. В результате применения команды COMMIT результаты выполнения транзакций записываются в базу данных, а информация в области для временных данных, обеспечивающая возможность отмены, стирается. В случае использования команды ROLLBACK, в базу данных изменения не вносятся, но информация в области для временных данных, обеспечивающая возможность отмены, стирается. Если команды COMMIT и ROLLBACK не использовать достаточно долго, то область для временных данных постепенно заполнится, что приведет к остановке всех процессов в базе данных до тех пор, пока пространство не будет освобождено. На этом уроке вы ознакомились с основами управления транзакциями, осуществляемого с помощью команд COMMIT, ROLLBACK и SAVEPOINT. Команда COMMIT используется для сохранения результатов транзакции в базе данных, ROLLBACK используется для отмены транзакций, a SAVEPOINT — для разделения транзакций на группы, дающие возможность отката к заранее выбранным точкам в последовательности выполняемых транзакций. Не забывайте достаточно часто использовать команды COMMIT и ROLLBACK при выполнении большого числа транзакций, чтобы не вызвать переполнения доступного базе данных пространства. Не забывайте также, что команды управления транзакциями используются только с командами DML (командами INSERT, UPDATE и DELETE). Обязательно ли подтверждение после каждого использования команды INSERT? Нет, не обязательно. Если вам требуется ввести в таблицу несколько сотен тысяч строк, то команду COMMIT рекомендуется использовать после ввода каждых 5000—10000 строк, в зависимости от размера области, предназначенной для хранения временных данных. Помните, что база данных не может работать, когда эта область переполнена. Каким образом команда ROLLBACK отменяет изменения, сделанные в результате транзакции? Команда ROLLBACK удаляет информацию о сделанных изменениях из области с временными данными. Допустим, при выполнении транзакции 99 % ее операторов завершились успешно, а 1 % вернули ошибки, имеется ли возможность отменить только эти ошибочные операторы? Нет, транзакция должна быть полностью успешной — иначе возникает угроза нарушения целостности данных. Команда COMMIT делает транзакцию перманентной. Можно ли после этого изменить данные? В данном случае перманентность означает внесение изменений в базу данных. У вас всегда имеется возможность внесения новых изменений в базу данных с помощью оператора UPDATE. Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе теку- щего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы . 1. Верно ли следующее утверждение: "Если вы подтвердили несколько транзакций а затем ввели еще несколько, не подтвердив их, то в результате применения команды ROLLBACK будут отменены вообще все^транзакции, выполненные в течение данного сеанса доступа к базе данных?" 2. Верно ли следующее утверждение: "Команда SAVEPOINT сохраняет транзакции после выполнения определенного количества транзакций?" 3. Кратко охарактеризуйте назначение команд COMMIT, ROLLBACK и SAVEPOINT. 1. Рассмотрите следующие транзакции и создайте точки отката после каждой третьей из них. После этого подтвердите все транзакции. transaction1; transaction2; transaction3; transaction4; transaction5, transaction6, transaction7; transaction8; transaction9; transaction10; transaction11; transaction12; |
Реклама |
|
Транзакция — это набор действий, выполняемых по отношению к базе данных и рассматриваемый как единое целое. Транзакции являются единицами активности или, иначе, последовательностями действий, выполняемыми в своем логическом порядке. Они могут выполняться как вручную, так и в автоматическом режиме с помощью соответствующих программ. В реляционных базах данных, управляемых с помощью SQL, транзакции осуществляются с помощью команд DML (INSERT, UPDATE и DELETE), уже обсуждавшихся в ходе урока 5, "Манипуляция данными". Транзакция представляет собой внесение в базу данных некоторых изменений. Например, вы осуществляете транзакцию, когда для изменения информации об имени персоны выполняете оператор UPDATE по отношению к соответствующей таблице. 
Способ, каким осуществляется требование начать выполнение транзакции, зависит от конкретной реализации SQL. Соответствующие инструкции вы найдете в документации по своей конкретной реализации. В стандарте ANSI средств для явной активизации начала работы транзакций не предусмотрено.
COMMIT [ WORK ]; 
SELECT * FROM PRODUCTS_TMP; 
PROD_ID PROD_DESC COST