Транзакции

как единое целое и переводящих БД из одного непротиворечивого состояния

Количество операций, входящих в транзакцию, может быть любым от одной до сотен, тысяч

Транзакция должна удовлетворять ACID – требованиям

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

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

работы системы
Atomic (атомарность)

Consistency (согласованность)

Isolation (изолированность)

Durability (устойчивость)
Транзакция не может выполниться частично,

После выполнения транзакции все данные должны находиться в согласованном состоянии

Транзакция должна быт автономной и воздействовать на другие транзакции или завысить от них

После завершения транзакции, внесенные изменения останутся неизменными

ACID  - фундаментальные свойства систем обработки транзакций  

обеспечивающие сохранение на диске изменений БД, сделанные в процессе выполнения

Откат транзакции – это действия, обеспечивающие аннулирование всех изменений БД, сделанные в процессе выполнения транзакции

команда рассматривается как отдельная транзакция
Транзакция начинается с первого оператора SQL

(определенные стандартом)

SQL Server поддерживает все три вида

транзакции используется для вложенных транзакций
Фиксация транзакции
COMMIT [TRAN[SACTION]] [transaction_name | var_

Откат транзакции
ROLLBACK [TRAN[SACTION]] [transaction_name | var_ transaction_name
savepoint_name | var_savepoint_name ]

Сохранение точки отката транзакции
SAVE TRAN[SACTION]] [savepoint_name | var_savepoint_name ]

! При вложении транзакций transaction_name должно быть имя из самой внешней инструкции BEGIN TRANSACTION.

INT
AS
DECLARE @Состояние VARCHAR(10), @Остаток INT, @Цена MONEY
SELECT @Состояние = Состояние

BEGIN TRAN

COMMIT

from Заказы where ЗаказID=@КодЗаказа

begin

-- Возврат кол.товаров в табл. «Склад"
Update Склад set Остаток = Остаток + Количество
from ЗаказаноТоваров
where ЗаказаноТоваров.ЗаказID = @КодЗаказа and
ЗаказаноТоваров.СкладID = Склад.СкладID
-- Удаление заказанных товаров из табл. "ЗаказаноТоваров" для данного заказа
delete from ЗаказаноТоваров where ЗаказID=@КодЗаказа
-- Удаление заказа из табл. "Заказы"
delete from Заказы where ЗаказID=@КодЗаказа

Begin tran

commit tran

end

[transaction_name | var_ transaction_name

Начало транзакции
начинается автоматически при исполнении любого из перечисленных операторов
ALTER TABLE, CREATE объекта БД, DROP объекта БД,
SELECT,
INSERT, DELETE, UPDATE,
OPEN, FETCH,
GRANT, REVOKE,
TRUNCATE TABLE.

( А int Primary rey,
Б char(30) Not null )

Переключение режима транзакций на неявные

Переключение режима транзакций на автоматические

бы при его выполнении имелась необработанная транзакция.
Поэтому COMMIT завершит

ROLLBACK TRANSACTION в триггере:
отменяет все изменения данных, уже выполненные в текущей транзакции, в том числе изменения, выполненные триггером;
 все оставшиеся инструкции после инструкции ROLLBACK продолжают выполняться;
 текущий пакет снимается с выполнения и, для версий 2005 и выше, сгенерируется ошибка 3609
- закрывает и освобождает все курсоры, которые были объявлены и открыты в пакете, содержащем инструкцию, приведшую к срабатыванию триггера.

Чтобы выполнить откат транзакций только в триггере, нужно использовать SAVE TRANSACTION.

 Если в триггере имеется BEGIN TRANSACTION, то создается вложенная транзакция и COMMIT TRANSACTION будет применяться только к вложенной транзакции.

триггера';
DECLARE @КлиентID int, @ОрганизID int

SELECT @КлиентID=ОрганизацииID FROM INSERTED

SELECT @ОрганизID=ОрганизацииID FROM

ROLLBACK TRAN

COMMIT TRAN

в триггере, нужно использовать SAVE TRANSACTION.
Create trigger T on Tх

begin tran
insert into T1 value (1, 'A')
insert into Tx …
insert into T2 value (1, 'JJ')
commit

Результат: будут вставлены строки в Т1 и Т2, в Тх будет вставлена, если условие в триггере не выполнится.

требуется
1) при откатах транзакций




2) при внезапной потери данных в ОП

3)

- явно (оператор ROLLBACK)
- при аварийном завершении клиентского приложения
- принудительный откат при взаимной блокировке

- при отключении электропитания
- сбои процессора

номер
Идентификатор транзакции
Операция
Атрибут
Новое значение атрибута
Старое значение атрибута
Log Sequence Number

Маркирует транзакцию с учетом пользователя

Выполняемые команды, в т.ч. завершения транзакции

Имя таблицы, имя поля и т.п.

Tab2
Commit
Transaction 1
Transaction 2
Insert into Tab3
Insert into Tab4
Insert into Tab2
Delete from

Insert into Tab4

Commit

10.01

10.02

10.05

10.06

10.10

10.12

10.15

10.16

10.20

10.21

10.23

Tr1 новая …старая …

Tr1 Begin tran

Tr2 Begin tran

Tr1 новая …старая

Tr2 новая

Tr2 новая

Tr1 новая

Tr1 новая

Tr2 старая …

Tr1 Commit , фиксация

Tr2 новая

Tr2 Commit , фиксация

Transaction log

быть сохранены в восстановленном состоянии БД
Результаты незафиксированных транзакция должны быть

немедленными изменениями

сделаны в БД, только в журнал транзакций
Окончание выполнения транзакции состоит

– это конец выполнения транзакции (с фиксированием или отменой)
Ничего не

Сбой при фиксация транзакции
–восстанавливается работоспособность сервера
- выполняется процедура REDO()

Процедура REDO() переписывает результаты транзакции в БД, проходя по протоколу начиная с первой команды транзакции

в журнал транзакций.
При откате транзакции выполняется процедура UNDO()
Ничего не

UNDO() возвращает все старые значения в БД, выполняя по журналу, начиная с последней команды отмененной транзакции, обратные команды.

Сбой при выполнении транзакции
–восстанавливается работоспособность сервера
- если есть Begin, но нет Commit, выполняется UNDO()
- если есть Begin Commit, то выполняется фиксация в журнале

КЭШ БД считываются требуемые транзакцией страницы и все изменения происходя

проблемам одновременного доступа к БД:
1. Проблемы пропавших обновлений
2. Проблемы

3. Проблемы несогласованных данных

4. Проблемы чтения фантомов

тоже строку, основываясь на её начальном значении
Склад
Транзакция 1
Транзакция 2
Begin

Begin tran

UpDate Склад Set Кол = Кол - 10

30

40

10

30

UpDate Склад Set Кол = Кол - 30

40

40

10

commit

commit

её промежуточные данные
Склад
Транзакция 1
Транзакция 2
Begin tran
Begin tran
Select Кол from

Select Кол from Склад …

40

40

10

40

10

10

40

UpDate Склад Set Кол = Кол - 30

if Кол < 30 then

Select Сумма from Оплата …

if Сумма < 1000 then

rollback

Else commit

…

if Кол > 20 then

Else rollback

данные несколько раз, а другая транзакция вносит в эти данные

Склад

Транзакция 1

Транзакция 2

200

220

10

40

несколько раз, а другая транзакция вставляет новые строки до завершения

Склад

Транзакция 1

Транзакция 2

Это способ решения проблем одновременного доступа
Стандартом ANSI SQL-92 определены 4

0 –й уровень READ UNCOMMITTED (незавершенное чтение)

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

1 –й уровень READ COMMITTED (завершенное чтение)

2 –й уровень REPEATABLE READ (несогласованные данные)

3 –й уровень SERIALIZABLE (сериализуемость)

В SQLServer 2008 есть SNAPSHOT (Моментальный срез)— транзакция, в которой требуется чтение не ждёт завершения транзакции изменяющей данные, а считывает их версию, по состоянию на момент начала этой транзакции. Не входит в стандарта SQL 92.

MS SQL SERVER 2008 по умолчанию установлен READ COMMITTED
В Oracle

использованием Transact-SQL или через API доступа к СУБД:
Используется инструкция SET

- OLE DB

Перед вызовом ITransactionLocal::StartTransaction устанавливается параметр isoLevel в значение ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED,ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, ISOLATIONLEVEL_SERIALIZABLE

- ADO.NET

Перед вызовом метода SqlConnection.BeginTransaction устанавливается параметр IsolationLevel в значение ReadUncommitted, ReadCommitted RepeatableRead, Serializable или Snapshot

- ODBC

 вызывают функцию SQLSetConnectAttr с установленным параметром Attribute в значение SQL_ATTR_TXN_ISOLATION и параметром ValuePtr в значение SQL_TXN_READ_UNCOMMITTED, TXN_READ_COMMITTED, …

LEVEL { READ UNCOMMITTED |

Установленный уровень изоляции действует на протяжении всего сеанса подключения или до явной замены на другой уровень

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

DBCC USEROPTIONS

синхронизационных захватах объектов БД запрещение доступа к объекту из других

Многоверсионный подход уменьшает количество блокировок, но подход, основанный на блокировках, обеспечивает более согласованное представление данных.

- временные метки (версионность)

Суть временных меток - одновременно разные транзакции могут видеть разные версии данных (в SQL Server копия первоначальных данных используемых текущей транзакцией с номером операции сохраняется в системной БД TempDB; в Oracle старая версия данных сохраняется в сегменте отката).

накладываемое ограничение на доступ к объектам БД во время выполнения

Самый простой вариант блокировки – это блокировка объекта на все время действия транзакции.

В момент начала работы с любым объектом (если он не заблокирован другой транзакцией) он блокируется до окончания заблокировавшей его транзакции.

После окончания транзакции все заблокированные ею объекты разблокируются и становятся доступными другим транзакциям.

Если транзакция обращается к заблокированному объекту, то она остается в состоянии ожидания до момента разблокировки этого объекта, после чего она может продолжать обработку данного объекта.

Т1
UpDate Т2
Commit
Begin tran A
Begin tran B
Select Т2
Разблокиров.
Разблокиров.
Блокировка

Разблокиров.

Commit

X

ожидание

Разблокиров.

01

Таблица Т1

Таблица Т2

05

03

06

07

02

04

08

09

09

02

04

08

различные типы и уровни блокировок.
Различают основные и специальные типы блокировок
Тип

Уровень изоляции транзакций определяет тип блокировки и продолжительность при выполнении команд в транзакции

Блокировки обновления (U) (Update)
2. Монопольные блокировки (X) (eXclusive)
Накладывается при выполнении

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

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

Например, для одиночной команды UPDATE требуется сначала произвести чтение данных, а потом их замену. Тогда и подойдет блокировка U.

Т1
UpDate Т2
Commit
Begin tran A
Begin tran B
Select Т2
Разблокиров.
Разблокиров.
Commit

Ожидания нет

Разблокиров.

01

Таблица Т1

Таблица Т2

05

03

06

07

02

04

05

02

04

07

монопольная А

ещё другие основные блокировки
4. Блокировка диапазона ключа
6. Блокировка изменения схемы

5. Блокировка стабильности схемы (Sch-S) (Stability Lock)

Накладывается на диапазон строк, удовлетворяющих определенному условию. Решается проблема возникновения фантомов.

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

Накладывается на объект, как только начинается изменение структуры объекта. Никакая другая блокировка не может быть наложена на объект, пока установлена эта блокировка.

но создает другую проблему –
Для решения проблемы потери производительности

1. RID – блокировка уровня строки

Уровни блокировок реализованы на уровнях иерархии объектов БД:

2. KEY– блокировка уровня индекса (группа строк)

3. PAG– блокировка уровня страницы

4. EXT– блокировка уровня группы страницы

5. TAB– блокировка уровня таблицы

6. DB – блокировка уровня базы данных

задержки выполнения транзакций.

наложения блокировок на различных уровнях
1. Коллективные блокировки намерения (IS) (Intent

3. Коллективно - монопольная блокировка намерения (SIX) (Shared Intent with eXclusive)

2. Монопольные блокировки намерения (IX) (Intent eXclusive)

Накладывается при намерении транзакции читать данные вниз по иерархии объекта (например, таблица – группа страниц – страница – строка). Другим транзакциям запрещается устанавливать монопольные блокировки вниз по иерархии.

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

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

страницу нельзя наложить монопольную (X) блокировку, блокировки намерения коллективно –

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

Решение об уровне блокировки принимается автоматически во время оптимизации запроса.

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

а при большом объеме данных для оператора SELECT
(например, SELECT * FROM tableX )
устанавливается блокировка на уровне страницы или таблицы.

Управление блокировками выполняет специальный компонент сервера – менеджер блокировок.

Пользователю чаще всего не нужно предпринимать никаких действий по управлению блокировками.

В функции менеджера блокировок входит установка, снятие и разрешение конфликтов блокировок.

данных, и какого типа;
- как долго удерживать блокировки;
- как

Решение о типе блокировки принимается автоматически по действующему уровню изоляции транзакции.

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

Например, при выполнении оператора Select для
READ UNCOMMITTED – не устанавливается ни каких блокировок на считываемые данные и игнорируется другие блокировки;
READ COMMITTED – устанавливается коллективная блокировка (S) на считываемые данные только на время выполнения команды;
REPEATABLE READ - устанавливается коллективная блокировка (S) на считываемые данные на время до конца выполнения транзакциию.

в которой удерживается блокировка
Идентификатор объекта, где удерживается блокировка
Идентификатор индекса, где

Уровень блокировки

Значение блокируемого ресурса

Тип блокировки

Состояние блокировки

Для получения имени идентификаторов можно воспользоваться функциями:
DB_NAME(spid)
OBJECT_NAME(ObjId)

Состояние блокировки:
GRANT: блокировка получена
WAIT : ожидание блокировки
CNVRT: блокировка в конфликтном режиме

случаи, в которых должен быть выполнен её откат, но также

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

Уровни изоляции транзакций определяют:

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

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

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

который был установлен командой,
Форматы команд с хинтами
SELECT … FROM table_name

SET TRANSACTION ISOLATION LEVEL

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

INSERT table_name (list_col) WITH (hint) VALUES …

UPDATE table_name WITH (hint) SET …

DELETE table_name WITH (hint) WHERE …

или по умолчанию)

(хинты)
NOLOCK (READUNCOMMITTED) – разрешает чтение незафиксированных данных, которые были изменены

PAGLOCK – устанавливает блокировку страницы вместо таблицы

UPDLOCK– определяет применение блокировки обновления до завершения транзакции

HOLDLOCK (SERIALIZABLE)– устанавливает совмещаемую блокировку до завершения транзакции

XLOCK – определяет применение монопольной блокировки на соответствующем уровне до завершения транзакции

ROWLOCK – устанавливает блокировку на уровне строки

TABLOCK – устанавливает соответствующую блокировку на уровне таблицы

TABLOCKХ – устанавливает монопольную блокировку на уровне таблицы до завершена транзакция

для уровня изоляции READ COMMITTED (либо блокировка строк либо управление

REPEATABLEREAD – определяет выполнение просмотра с семантикой блокировки, как для уровня изоляции REPEATABLE READ

READCOMMITTEDLOCK – определяет правила для чтения, как для уровня изоляции READ COMMITTED с использованием блокировки

и некоторые др.

и для завершения работы каждой из них необходим доступ к

Транзакция А

Транзакция B

UpDate Т1

UpDate Т2

монопольная А

монопольная B

UpDate Т2

Таблица Т1

Таблица Т2

UpDate Т1

X

X

ожидание

ожидание

специальные механизмы обнаружения и разрешения тупиковых блокировок.
Один из алгоритмов обнаружения

1. Для каждого ресурса строится граф ожидания транзакций

2. Определяется в графе наличия цикла

Если цикл обнаружен, то в системе имеется тупиковая блокировка и далее должна выполнится процедура её разрешения

Т10

Т12

Т4

Т8

Т3

Транзакция Т10 ожидает освобождения ресурса транзакцией Т12

из транзакции, вызвавших её конфликт.
Т10
Т12
Т4
Т8
Т3
Выбор на откат транзакции выполняется по

X

Для установки приоритета блокировки используется команда
SET DEADLOCK_PRIORITY {LOW | NORMAL}.

транзакций нужно придерживаться следующих правил:
1. Создание индексов для команд UPDATE

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

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

При выполнении команды INSERT для вставки много строк осуществляется монопольная блокировка всей таблицы. При наличии INSERT для вставки одной строки монопольная блокировка устанавливается на строку.

HOLDLOCK в SELECT все коллективные блокировки будут оставаться в силе,

4. Использовать как можно более короткие транзакции.

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

транзакций все установленные в ней блокировки сохраняются до завершения внешней

6. Исключать использования взаимодействия с пользователем во время выполнения транзакции.

Например, READ UNCOMMITTED вместо SERIALIZABLE позволит нескольким транзакциям одновременно читать данные: каждая транзакция сможет установить коллективную блокировку не дожидаясь пока друга считает данные и снимет блокировку.

7. Использовать как можно более низкий уровень изоляции

8. Установить на севере дополнительную оперативную память

Это увеличит КЭШ буферов и следовательно скорость выполнения транзакций и снизит конкуренцию за доступ к ресурсам.