5.10.4. Механизм блокирования записей и уровни изоляции

Выше мы расмотрели блокировки на уровне базы данных и таблиц. Блокировка таких крупных объектов используется достаточно редко, так как такая блокировка накладывает серьезные ограничения на многопользовательский доступ. Кому нужна программа резервирования авиабилетов, если только один кассир может вносить изменения а остальные в этот момент времени могут только просматривать данные?

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

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

Уровни изоляции, и это надо отметить, представляют собой очень красивое и изящное решение, сильно упростившее разработку программ. Установка того или иного уровня мзоляции сама по себе явно ничего не блокирует. Сам термин "уровень изоляции" введен потому, что он описывает механизм управления влиянием других процессов на Ваш конкретный процесс, на порядок исполнения Ваших запросов и на то, как Ваш процесс влияет на другие процессы..

Существуют следующие основные уровни изоляции: грязное чтение (DIRTY READ), достоверное чтение (COMMITTED READ), стабильный курсор (CURSOR STABILITY), многократное чтение (REPEATABLE READ).

Уровень изоляции DIRTY READ означает отсутствие изоляции Вашего процесса от других. Программа в данном уровне изоляции может прочитать абсолютно все записи в той или иной таблице (естественно, с учетом прав на доступ). Обновить же она может только не заблокированные другим пользователями записи. Сама программа в данном уровне изоляции не накладывает никаких блокировок на прочитанные записи (но, естественно, накладывает блокировку на модифицированные записи).

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

Уровень изоляции DIRTY READ является единственно допустимым для баз данных без транзакций, но может применяться и для баз данных с транзакциями. Уровень изоляции "грязное чтение" обеспечивает самую высокую производительность. Его обычно используют для доступа к данным, которые хранятся в малоизменяемых таблицах, например для выборки из таблиц с описанием классификаторов или для чтения таблиц с результатами проведенных экспериментов.

Уровень изоляции COMMITTED READ (достоверное чтение) обеспечивает чтение данных, когда они не модифицируются другим процессом. Использование этого уровня изоляции обеспечивает Вам невозможность прочесть так называемые "фантомные" записи, которые появляется в процессе выполнения транзакции другим процессом. То есть если Ваша программа прочитала запись, то эта запись гарантирована существует. Однако, никаких блокировок на прочитанные записи при этом не делается, то есть другой процесс может прочитать эту же запись и удалить или изменить ее. Данный уровень изоляции является уровнем, принимаемым по умолчанию для баз данных с транзакциями.

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

Уровень изоляции «Достоверное чтение», как и все более высокие уровни, описываемые ниже, возможен только для баз данных с транзакциями.

Уровень изоляции CURSOR STABILITY (стабильный курсор) обеспечивает невозможность другим пользователям изменить прочитанную Вами запись до тех пор, пока Вы не прочтете другую запись. То есть, прочитанный ряд блокируется. Обычно последовательный просмотр записей реализуется с помощью механизма, называющегося "курсор", отсюда и название данного уровня изоляции.

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

Самый высший уровень изоляции - это REPETABLE READ (повторяемое чтение). Если программа использует данный уровень изоляции, то SQL-сервер заблокирует все записи, прочитанные данной программой в пределах транзакции. То есть уровень изоляции REPEATABLE READ означает запрещение изменения другими процессами всех записей, задействованных Вами в течении транзакции. Это означает, что Вы можете в течение одной транзакции многократно читать одни и те же записи и они гарантированно не могут быть изменены другими пользователями. Этот уровень изоляции является самым сильным и принимается по умолчанию для баз данных с транзакциями в режиме ANSI.

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

Оператор, с помощью которого можно установить требуемый уровень изоляции выглядит так:

SET ISOLATION TO <уровень изоляции>

Например:

SET ISOLATION TO REPEATABLE READ SET ISOLATION TO DIRTY READ

Выбор того или иного уровня изоляции обусловлен необходимостью обеспечения требуемой безопасности Ваших данных. В то же время не следует завышать этот уровень, так как это ведет к дополнительным накладным расходам и, как следствие, может увеличить время реакции системы. Если это необходимо, то можно менять уровень изоляции впроцессе работы Вашей программы:

SET ISOLATION TO DIRTY READ SELECT ... INTO ... .......................... SET ISOLATION TO CURSOR STABILITY FOREACH my_cursor INTO var, var .......................... END FOREACH