Транзакции, пакеты и блокировки.

 

1 Цель и порядок работы

 

1.1 Цель работы

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

 

1.2 Порядок выполнения работы

- Ознакомиться с описанием работы;

- Выполнить задание для самостоятельной работы;

- Ответить на контрольные вопросы;

- Представить результаты работы преподавателю.

 

2. Общие сведения

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

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

 

UPDATE accounts SET AccSum = AccSum - 1000 WHERE AccNumber = 12345;

UPDATE accounts SET AccSum = AccSum + 1000 WHERE AccNumber = 67890;

 

Что будет, если первый оператор выполнится, а второй по какой-то причине - нет (сбой сервера, неправильный номер счета, переполнение... - мало ли какая может быть ошибка)? Деньги с одного счета списаны, а на другой не поступили...

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

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

 

Операторы управления транзакциями:

- COMMIT

- ROLLBACK

- SAVEPOINT

- ROLLBACK TO

- SET TRANSACTION

 

Немного подробностей по каждому оператору.

- COMMIT. Оператор COMMIT завершает транзакцию и делает любые выполненные в ней изменения постоянными. Освобождаются блокировки.

- ROLLBACK. Оператор отката завершает транзакцию и отменяет все выполненные в ней и незафиксированные изменения. Для этого он читает информацию из сегментов отката и восстанавливает блоки данных в состояние, в котором они находились до начала транзакции. Освобождаются блокировки.

 

Стандартный синтаксис этих команд очень прост(рисунок №73):

Рисунок №73- Операторы COMMIT и ROLLBAC

 

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

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

- ROLLBACK TO <точка сохранения>. Этот оператор используется совместно с представленным выше оператором SAVEPOINT. Транзакцию можно откатить до указанной точки сохранения, не отменяя все сделанные до нее изменения. Таким образом, можно выполнить два оператора UPDATE, затем — оператор SAVEPOINT, а после него — два оператора DELETE. При возникновении ошибки или исключительной ситуации в ходе выполнения операторов DELETE транзакция будет откатываться до указанной оператором SAVEPOINT точки сохранения; при этом будут отменяться операторы DELETE, но не операторы UPDATE.

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

 

 

Синтаксис операторов SAVEPOINT и ROLLBACK в Oracle показан на рисунке №74:

 

 

Рисунок №74 - Операторы ROLLBACK и SAVEPOINT в Oracle

Некоторые особенности выполнения транзакций в ORACLE:

 

1. Транзакция обычно состоит из нескольких операторов DML. Если один оператор дает сбой, то он один откатывается. То есть все операторы, которые раньше были выполнены, не откатываются автоматически – результаты их работы не пропадают. Вы можете дальше продолжать транзакцию. Затем её или зафиксировать, или откатить. А получаем мы такой эффект потому, что ORACLE каждый оператор транзакции помещает в неявные операторы Savepoint так, как это показано далее:

Savepoint statement1;

Оператор1;

If error then rollback to statement1;

Savepoint statement2;

Оператор2;

If error then rollback to statement2;

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

3. ORACLE анонимный блок PL/SQL считает оператором. Например, begin оператор1; оператор2; end; То есть для него применимо предыдущее замечание.

4. Ограничение целостности проверяются после выполнения каждого sql-оператора. ORACLE разрешает делать некоторые строки таблицы несогласованными до конца выполнения sql-оператора.

5. В ORACLE есть возможность отложить проверку целостности на любой момент времени до конца транзакции. Это реализуется с помощью ограничения deferrable таблицы и перевода ограничения в режим deferred.

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

7. В ORACLE можно использовать распределенные транзакции, то есть выполнять транзакцию, в которой операторы работают на удаленных сервера (распределенная база данных). Для доступа к удаленной базе данных используется объект database link. Распределённая транзакция выглядит примерно так:

update table1 set x=1; update remote_table1@remote_database set x=1; commit;

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

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

Пример транзакции в Oracle:

Выберем одного из работников. Например с ID=997

Рисунок №75 – Действие 1

 

Попробуем изменить данные, но указав неверную зарплату.

Рисунок №76 – Действие 2

 

Oracle выдает ошибку. И производит откат всех сделанных изменений. Проверим это.

Рисунок №77 – Действие 3

 

Действительно. Не было изменено ни одно поле.

При выполнении корректного запроса, Oracle сообщает об успешном изменении одной строки.

Рисунок №78 – Действие 4

 

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

Рисунок №79 – Действие 5