SQL Oracle, Db2

5.4 Механизмы обеспечения транзакций

Уровни изоляции, установленные стандартом, во-первых, определены недостаточно четко, во-вторых, не являются обязательными для начального уровня соответствия стандарту. Стандарт также не предусматривает того, как обеспечивается изоляция. Поэтому реальные СУБД достаточно по-разному трактуют изолированность транзакций и используют разные механизмы для обеспечения свойств ACID, в том числе и свойств параллельности.

5.4.1 Механизмы DB2

В DB2 для обеспечения атомарности транзакций применяется "упреждающее протоколирование", при котором изменения в данных записываются в журнальный файл прежде, чем транзакция фиксируется. Изменения в данных производятся в журнальных файлах и лишь при фиксации транзакции переносятся в основную область данных СУБД. Журнал используется для повторения или отката транзакций в случае сбоя.

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

В самом простом случае СУБД обеспечивает блокировки двух типов:

S-блокировка - разделяемая блокировка или блокировка чтения, допускающая совместный доступ к строке таблицы;
X-блокировка - эксклюзивная блокировка или блокировка записи, не допускающая совместного доступа к строке.

Прежде чем считать (select) какую-либо строку из таблицы транзакция должна установить для строки S-блокировку. Прежде чем обновить (update, delete, insert) строку таблицы транзакция должна установить для строки X-блокировку. Если для строки уже установлена блокировка, несовместимая с той, которую пытается установить наша транзакция, то попытка нашей транзакции отвергается, транзакция переводится в ожидание до того момента, пока ранее установленная блокировка не будет снята. Совместимость блокировок показана в следующей таблице.

	`S`	`X`
`S`	да	нет
`X`	нет	нет

Тот или иной режим изоляции определяется той или иной комбинацией проверки и установки блокировок чтения.

Для уменьшения объема проверяемых при любом доступе блокировок вводятся дополнительные типы блокировок, называемые блокировками намерения. Блокировки намерения накладываются на всю таблицу перед наложением S- или X-блокировки на строку таблицы. Основной набор блокировок намерения следующий (хотя в действительности DB2 этот набор несколько шире):

IS - блокировка намерения чтения. Накладывается на некоторую таблицу T и означает намерение блокировать некоторую входящую в T строку в режиме S-блокировки.
IX - блокировка намерения записи. Накладывается на некоторую таблицу T и означает намерение блокировать некоторую входящую в T строку в режиме X-блокировки.
SIX - блокировка чтения с намерением записи. Накладывается на некоторую таблицу T и означает разделяемую блокировку всей этой таблицы с намерением впоследствии блокировать какие-либо входящие в нее строки в режиме X-блокировок.

Совместимость блокировок с учетом блокировок намерения показана в следующей таблице:

	`IS`	`S`	`IX`	`SIX`	`X`
`IS`	да	да	да	да	нет
`S`	да	да	нет	нет	нет
`IX`	да	нет	да	нет	нет
`SIX`	да	нет	нет	нет	нет
`X`	нет	нет	нет	нет	нет

5.4.2 Механизмы Oracle

Механизмы обеспечения транзакций в Oracle называют оптимистическими. При этом имеется в виду то, что СУБД предполагает, что вероятность отката транзакции и конфликта транзакций по доступу к данным невелика. Oracle использует журнал, в котором хранит информацию для повторения транзакций в случае сбоя и "сегмент отката". Если транзакция изменяет базу данных, выполненные изменения сразу заносятся в область данных, а старая версия данных сохраняется в сегменте отката. Блокировки чтения, таким образом не накладываются. Подробный протокол чтения данных приводится ниже:

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

Для транзакции READ ONLY сегмент отката даже не создается.

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

Таким образом, существенная разница в поведении двух рассматриваемых СУБД состоит в следующем: при угрозе возникновения чтения нецелостных данных DB2 блокирует транзакцию, пытающуюся выполнить такое чтение; Oracle же предоставляет транзакции то последнее целостное значение данных, которое существовало на момент начала транзакции. Подход Oracle в этой связи называют многоверсионным, так как одновременно разные транзакции могут видеть разные версии данных. Многоверсионный подход уменьшает количество блокировок, но подход, основанный на блокировках, обеспечивает более согласованное представление данных. Между разработчиками СУБД ведутся ожесточенные дискуссии о превосходстве того или другого подходов, но независимые эксперты, как правило, не могут отдать предпочтения ни тому, ни другому. Следует отметить, что существуют приемы и правила разработки приложений, которые позволяют обеспечить большую согласованность данных в Oracle и меньшее количество блокировок в DB2.

5.4.3 Реализация сценариев

5.4.3.1 Потерянные изменения

В обеих СУБД, независимо от установленного уровня изоляции, действия по сценарию п.5.3.1. будут следующими:

после ввода оператора UPDATE на шаге 2 транзакция Т1 "зависнет" (заблокируется):
оператор шага 2 завершится только, когда будет введен оператор COMMIT в транзакции T1.
на шаге 5 будет выбрано:

   id         dat 
---------- -----------
         1         101
         2         110
         3         120
         4         130

5.4.3.2 Грязное чтение

В DB2 при уровне изоляции CS и выше транзакция транзакция T1 на шаге 2 заблокируется. Оператор этого шага завершится только тогда, когда в транзакции T2 будет введен оператор ROLLBACK или COMMIT. Если транзакция T2 будет зафиксирована, то в транзакции T1 на шаге 3 будет выбрано:

        id         dat 
---------- -----------
         1         101
         2         101
         3         120
         4         130

Если транзакция T2 откатится, то в транзакции T1 на шаге 3 будет выбрано:

        id         dat 
---------- -----------
         1         100
         2         100
         3         120
         4         130

В Oracle блокировок не будет. На шаге 3 будет выбрано:

        id         dat 
---------- -----------
         1         100
         2         100
         3         120
         4         130

Поскольку транзакция T1 не завершается до шага 5, то на шаге 5 будет выбрано исходное состояние таблицы. (Если транзакция T2 зафиксируется, а не откатится, то на шаге 5 будут отображены только изменения, выполненные в T2).

5.4.3.3 Неповторяющееся чтение

В DB2 при уровне изоляции RS и выше транзакция T2 будет заблокирована на шаге 2, пока в транзакции T1 не будет выполнен оператор COMMIT или ROLLBACK. Если зафиксировать транзакцию T1 на шаге 2, то на шаге 4 будут выбраны уже обновленные значения, но это будет уже другая транзакция.

В Oracle при уровне изоляции SERIALIZABLE на шагах 1 и 4 будет выбрано одно и то же:

        id         dat 
---------- -----------
         1         100

Но если ту же выборку повторить после фиксации транзакции T1, то будет выбрано:

        id         dat 
---------- -----------
         1         101

5.4.3.4 Фантом

В DB2 при уровне изоляции RR транзакция T2 будет заблокирована на шаге 2, пока в транзакции T1 не будет выполнен оператор COMMIT или ROLLBACK. Если завершить транзакцию T1 на шаге 2, то на шаге 4 будут выбраны уже обновленные значения, но это будет уже другая транзакция.

В Oracle при уровне изоляции SERIALIZABLE на шагах 1 и 4 будет выбрано:

        id         dat 
---------- -----------
         3         120
         4         130

Если ту же выборку повторить после фиксации транзакции T1, будет выбрано:

        id         dat 
---------- -----------
         3         120
         4         130
         5         150

5.4.4 Тупики

Поскольку обе СУБД используют блокировки записи для предотвращения, например, такого нежелательного эффекта, как потерянные изменения, возможно возникновение тупиков. Тупик может возникнуть в том случае, если транзакция T1 требует каких-то ресурсов, которые эксклюзивно блокированы транзакцией T2, а транзакция T2 требует ресурсов, которые эксклюзивно блокированы транзакцией T1. Сценарий возникновения тупика, например, следующий:

Транзакция T1	Шаг	Транзакция T2
`UPDATE example SET dat=101 WHERE id=1`	1
	2	`UPDATE example SET dat=112 WHERE id=2`
`UPDATE example SET dat=111 WHERE id=2`	3
	4	`UPDATE example SET dat=102 WHERE id=1`

Транзакция T1 должна заблокироваться на шаге 3, а транзакция T2 - на шаге 4. Единственным способом "развязки" тупика является принудительное освобождение одной из транзакций удерживаемого ею критического ресурса.

Обе СУБД обнаруживают и развязывают тупики. Дойдя до зависания обеих транзакций на шагах 3-4, обе СУБД после некоторой временной выдержки развязывают тупик, но делают это несколько по-разному. DB2 принудительно откатывает одну из транзакций с сообщением об ошибке, вторая транзакция при этом разблокируется и завершается. Oracle откатывает только оператор UPDATE одной из транзакций с сообщением об ошибке. Вторая транзакция при этом продолжает оставаться заблокированной. Транзакция, в которой был выполнен откат оператора, теперь должна явным образом завершиться (зафиксироваться или откатиться), только после этого будет разблокирована другая транзакция.

5.4.5 Эскалация блокировок

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

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