Презентация на тему: Транзакции

Реклама. Продолжение ниже
Транзакции
Понятие транзакции
ACID – требования
Варианты завершения транзакций
Виды определения транзакций
Явные транзакции
Пример 1 явной транзакции
Пример 2 явной транзакции
Неявные транзакции
Пример неявных транзакций
Откат и фиксация транзакций в триггерах
Пример отката транзакций в триггере
Откат и фиксация транзакций в триггерах
Журнал транзакций
Логическая структура журнала транзакций
Пример ведения журнала транзакций
Журнал транзакций
Модели ведения журнала транзакций
Протокол с отложенными изменениями
Протокол с отложенными изменениями
Протокол с немедленными изменениями
Протокол с немедленными изменениями
Параллельное выполнение транзакций
Проблема пропавших обновлений
Проблема промежуточного чтения
Проблема несогласованных данных
Проблема чтения фантомов
Уровни изоляции транзакций
Уровни изоляции транзакций
Уровни изоляции транзакций
Уровни изоляции транзакций
Уровни изоляции транзакций
Уровни изоляции транзакций
Блокировки
Блокировки
Типы и уровни блокировки
Типы и уровни блокировки
Блокировки
Типы и уровни блокировки
Типы и уровни блокировки
Специальные блокировки
Совместимость блокировок
Управление блокировками
Блокировки
Управление блокировками
Блокировки
Блокировки
Управление блокировками в запросе
Управление блокировками в запросе
Управление блокировками в запросе
Тупиковые блокировки
Тупиковые блокировки
Тупиковые блокировки
Тупиковые блокировки
Тупиковые блокировки
Тупиковые блокировки
Тупиковые блокировки
1/57
Средняя оценка: 4.3/5 (всего оценок: 2)
Код скопирован в буфер обмена
Скачать (308 Кб)
Реклама. Продолжение ниже
1

Первый слайд презентации: Транзакции

Изображение слайда
1/1
2

Слайд 2: Понятие транзакции

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

Изображение слайда
1/1
3

Слайд 3: ACID – требования

ACID – требования гарантируют правильность и надежность работы системы A tomic ( атомарность) C onsistency (согласованность) I solation (изолированность) D urability ( устойчивость) Транзакция не может выполниться частично, либо все, либо ничего После выполнения транзакции все данные должны находиться в согласованном состоянии Транзакция должна быт автономной и воздействовать на другие транзакции или завысить от них После завершения транзакции, внесенные изменения останутся неизменными ACID  - фундаментальные свойства систем обработки транзакций

Изображение слайда
1/1
4

Слайд 4: Варианты завершения транзакций

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

Изображение слайда
1/1
5

Слайд 5: Виды определения транзакций

Явное Автоматическое Неявное Требуется явно указать команды начала и конца транзакций Каждая команда рассматривается как отдельная транзакция Транзакция начинается с первого оператора SQL и заканчивается явным указанием конца транзакции (определенные стандартом) SQL Server поддерживает все три вида

Изображение слайда
1/1
6

Слайд 6: Явные транзакции

Описываются командами T-SQL Начало транзакции BEGIN TRAN [SACTION ] [transaction_name | var_ transaction_name] Имя транзакции используется для вложенных транзакций Фиксация транзакции COMMIT [ TRAN [ SACTION ]] [transaction_name | var_ transaction_name] Откат транзакции 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.

Изображение слайда
1/1
7

Слайд 7: Пример 1 явной транзакции

CREATE PROC ДобавитьЗаказКолТовар @ КодЗак INT, @ КодТов INT, @ ДопКол INT AS DECLARE @ Состояние VARCHAR (10 ), @ Остаток INT, @ Цена MONEY SELECT @ Состояние = Состояние FROM Заказы WHERE Заказ ID = @ КодЗак IF @ Состояние IS NOT NULL AND @ Состояние <> ‘ отгружен ’ BEGIN SELECT @ Остаток = Остаток, @ Цена = ЦенаОтпускная FROM Склад WHERE Склад ID = @ КодТов IF @ Остаток >= @ ДопКол BEGIN UPDATE Склад SET Остаток = Остаток - @ ДопКол WHERE Склад ID = @ КодТов UPDATE ЗаказаноТоваров SET Количество = Количество + @ ДопКол WHERE Заказ ID = @ КодЗак AND Склад ID = @ КодТов UPDATE Заказы SET ОбщаяСумма = ОбщаяСумма + @ ДопКол * @ Цена WHERE Заказ ID = @ КодЗак RETURN 0 END ELSE RETURN 1 ELSE RETURN 2 BEGIN TRAN COMMIT

Изображение слайда
1/1
Реклама. Продолжение ниже
8

Слайд 8: Пример 2 явной транзакции

CREATE proc АннулированиеЗаказа @ КодЗаказа int AS if exists ( select * from Заказы where Заказ ID =@ КодЗаказа and Состояние = ‘ оформление ’) begin -- Возврат кол.товаров в табл. «Склад" Update Склад set Остаток = Остаток + Количество from ЗаказаноТоваров where ЗаказаноТоваров.ЗаказID = @ КодЗаказа and ЗаказаноТоваров.СкладID = Склад.СкладID -- Удаление заказанных товаров из табл. "ЗаказаноТоваров" для данного заказа delete from ЗаказаноТоваров where ЗаказID = @ КодЗаказа -- Удаление заказа из табл. "Заказы" delete from Заказы where Заказ ID = @ КодЗаказа Begin tran commit tran end

Изображение слайда
1/1
9

Слайд 9: Неявные транзакции

Фиксация транзакции COMMIT [ TRAN [ SACTION ]] [transaction_name | var_ transaction_name] Откат транзакции ROLLBACK [ TRAN[SACTION ]] [transaction_name | var_ transaction_name savepoint_name | var_savepoint_name ] Начало транзакции начинается автоматически при исполнении любого из перечисленных операторов ALTER TABLE, CREATE объекта БД, DROP объекта БД, SELECT, INSERT, DELETE, UPDATE, OPEN, FETCH, GRANT, REVOKE, TRUNCATE TABLE.

Изображение слайда
1/1
10

Слайд 10: Пример неявных транзакций

USE pubs /* первая автоматическая транзакция CREATE table t1 ( А int Primary rey, Б char(30 ) Not null ) /* вторая автоматическая транзакция SELECT * FROM t 1 SET IMPLICIT_TRANSACTIONS ON /* первая неявная транзакция INSERT INTO t1 VALUES ( 1, ‘AAA’ ) INSERT INTO t2 VALUES (….) COMMIT TRAN /* вторая неявная транзакция INSERT INTO t1 VALUES ( 2, ‘BBB’ ) INSERT INTO t 3 VALUES (….) SELECT * FROM t4 COMMIT TRAN SET IMPLICIT_TRANSACTIONS O FF Переключение режима транзакций на неявные Переключение режима транзакций на автоматические

Изображение слайда
1/1
11

Слайд 11: Откат и фиксация транзакций в триггерах

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

Изображение слайда
1/1
12

Слайд 12: Пример отката транзакций в триггере

CREATE TRIGGER Add_Клиенты ON Клиенты FOR INSERT AS PRINT 'Выполнение триггера '; DECLARE @ КлиентID int, @ ОрганизID int SELECT @ КлиентID= Организации ID FROM INSERTED SELECT @ ОрганизID= ОрганизацииID FROM Организации WHERE ОрганизацииID= @ КлиентID IF @ ОрганизID IS NULL BEGIN PRINT 'нет организации ‘ -- отменить вставку записи END ELSE BEGIN PRINT 'Клиент вставлен ‘ END ROLLBACK TRAN COMMIT TRAN

Изображение слайда
1/1
13

Слайд 13: Откат и фиксация транзакций в триггерах

Чтобы выполнить откат транзакций только в триггере, нужно использовать SAVE TRANSACTION. Create trigger T on T х instead of insert As save tran tr insert into Т x … from inserted select @n= count (*) from Тх, T1 … if @n > 1 rollback tran tr begin tran insert into T1 value (1, 'A') insert into Tx … insert into T2 value (1, 'JJ') commit Результат: будут вставлены строки в Т1 и Т2, в Тх будет вставлена, если условие в триггере не выполнится.

Изображение слайда
1/1
14

Слайд 14: Журнал транзакций

Это системная структура, обеспечивающая восстановление состояния БД Восстановление состояния БД требуется 1) при откатах транзакций 2) при внезапной потери данных в ОП 3 ) при отказе HD - явно (оператор ROLLBACK) - при аварийном завершении клиентского приложения - принудительный откат при взаимной блокировке - при отключении электропитания - сбои процессора

Изображение слайда
1/1
Реклама. Продолжение ниже
15

Слайд 15: Логическая структура журнала транзакций

- это последовательность записей, содержащих следующую информацию Порядковый номер Идентификатор транзакции Операция Атрибут Новое значение атрибута Старое значение атрибута Log Sequence Number (LSN)- последовательно увеличивающееся значение Маркирует транзакцию с учетом пользователя Выполняемые команды, в т.ч. завершения транзакции Имя таблицы, имя поля и т.п.

Изображение слайда
1/1
16

Слайд 16: Пример ведения журнала транзакций

Begin tran Begin tran 2 10.00 Update Tab1 Insert into Tab1 Update Tab2 Commit Transaction 1 Transaction 2 Insert into Tab3 Insert into Tab4 Insert into Tab2 Delete from Tab5 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 Tr 2 Begin tran Tr1 новая …старая Tr 2 новая Tr 2 новая Tr 1 новая Tr 1 новая Tr 2 старая … Tr1 Commit, фиксация Tr 2 новая Tr 2 Commit, фиксация Transaction log

Изображение слайда
1/1
17

Слайд 17: Журнал транзакций

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

Изображение слайда
1/1
18

Слайд 18: Модели ведения журнала транзакций

- протокол с отложенными изменениями - протокол с немедленными изменениями

Изображение слайда
1/1
19

Слайд 19: Протокол с отложенными изменениями

- предполагает внесение изменений, которые должны быть сделаны в БД, только в журнал транзакций Окончание выполнения транзакции состоит из 2-х состояний: - завершение - фиксация

Изображение слайда
1/1
20

Слайд 20: Протокол с отложенными изменениями

При фиксация транзакции выполняется процедура REDO(). Завершение – это конец выполнения транзакции (с фиксированием или отменой) завершение транзакции Фиксация транзакции Commit Rollback Ничего не делаем Сбой при фиксация транзакции –восстанавливается работоспособность сервера - выполняется процедура REDO() Процедура REDO() переписывает результаты транзакции в БД, проходя по протоколу начиная с первой команды транзакции

Изображение слайда
1/1
21

Слайд 21: Протокол с немедленными изменениями

- предполагает внесение изменений и БД и в журнал транзакций. При откате транзакции выполняется процедура UNDO() завершение транзакции откат транзакции Commit Rollback Ничего не делаем UNDO() возвращает все старые значения в БД, выполняя по журналу, начиная с последней команды отмененной транзакции, обратные команды. Сбой при выполнении транзакции –восстанавливается работоспособность сервера - если есть Begin, но нет Commit, выполняется UNDO() - если есть Begin Commit, то выполняется фиксация в журнале

Изображение слайда
1/1
22

Слайд 22: Протокол с немедленными изменениями

внешняя память оперативная память транзакция БД Log КЭШ БД КЭШ LOG во 2 -ю очередь в 1-ю очередь begin commit В КЭШ БД считываются требуемые транзакцией страницы и все изменения происходя в КЭШ, а не на диске. Используется согласованная политика выталкивания буферов

Изображение слайда
1/1
23

Слайд 23: Параллельное выполнение транзакций

Параллельное выполнение нескольких транзакций может привести к следующим проблемам одновременного доступа к БД: 1. Проблемы пропавших обновлений 2. Проблемы промежуточного чтения 3. Проблемы несогласованных данных 4. Проблемы чтения фантомов

Изображение слайда
1/1
24

Слайд 24: Проблема пропавших обновлений

Возникает когда несколько транзакций изменяют одну и тоже строку, основываясь на её начальном значении мониторы Склад Транзакция 1 Транзакция 2 Begin tran Begin tran UpDate Склад Set Кол = Кол - 1 0 30 40 10 3 0 UpDate Склад Set Кол = Кол - 30 40 40 10 commit commit

Изображение слайда
1/1
25

Слайд 25: Проблема промежуточного чтения

Возникает когда при выполнении одной транзакции другая использует её промежуточные данные мониторы Склад Транзакция 1 Транзакция 2 Begin tran Begin tran Select Кол from Склад … Select Кол from Склад … 40 40 10 40 1 0 10 40 UpDate Склад Set Кол = Кол - 30 if Кол < 30 then Select Сумма from Оплата … if Сумма < 10 00 then rollback Else commit … if Кол > 20 then Else rollback

Изображение слайда
1/1
26

Слайд 26: Проблема несогласованных данных

Возникает когда транзакция считывает одни и те же данные несколько раз, а другая транзакция вносит в эти данные изменения. мониторы Склад Транзакция 1 Транзакция 2 2 0 0 2 20 10 40 UpDate Склад Set Кол = Кол - 30 Begin tran Select Цена, Кол from Склад … 200 if Кол > 30 then Select Сумма from Оплата … if Сумма > 30*Цена then commit Insert into Накладные Values ( Кол, Цена, Кол*Цена ) Select Кол,Цена from Склад … 2 2 0 UpDate Склад Set Цена =220 … Begin tran 10 2 0 0 UpDate Оплата Set Сумма = Сумма - 30*Цена commit 40

Изображение слайда
1/1
27

Слайд 27: Проблема чтения фантомов

Возникает когда одна транзакция выбирает данные из таблицы несколько раз, а другая транзакция вставляет новые строки до завершения первой. мониторы 40 Склад Транзакция 1 Транзакция 2 Begin tran Begin tran Insert Склад values( … ) if … then rollback UpDate … Set СрЦена = Сумма/ Кол Else … commit commit Сумма = Select Sum( Кол ) from Склад … Кол = Select Count (*) from Склад …

Изображение слайда
1/1
28

Слайд 28: Уровни изоляции транзакций

- определяют степень зависимости транзакций друг от друга. Это способ решения проблем одновременного доступа Стандартом ANSI SQL-92 определены 4 уровня изоляции транзакций: 0 –й уровень READ UNCOMMITTED ( незавершенное чтение) Каждый последующий уровень изоляции соответствует требованиям всех предыдущих уровней и обеспечивает дополнительную защиту транзакций. 1 –й уровень READ COMMITTED ( завершенное чтение) 2 –й уровень REPEATABLE READ ( несогласованные данные) 3 –й уровень SERIALIZABLE ( сериализуемость) В SQLServer 2008 есть SNAPSHOT (Моментальный срез) — транзакция, в которой требуется чтение не ждёт завершения транзакции изменяющей данные, а считывает их версию, по состоянию на момент начала этой транзакции. Не входит в стандарта SQL 92.

Изображение слайда
1/1
29

Слайд 29: Уровни изоляции транзакций

Проблемы одновременного доступа Уровни изоляции Пропавшие обновления Промежуточ-ное чтение Несогласован- ных данных Строки - признаки SERIALIZABLE Х Х Х Х REPEATABLE READ Х Х Х READ COMMITTED Х Х READ UNCOMMITTED Х Уровни изоляции и решаемые им проблемы одновременного доступа В MS SQL SERVER 200 8 по умолчанию установлен READ COMMITTED В Oracle поддерживаются READ COMMITTED и SERIALIZABLE Уровни изоляции транзакции сильно влияют на скорость работы системы

Изображение слайда
1/1
30

Слайд 30: Уровни изоляции транзакций

Уровень изоляции транзакций выбирается в зависимости от задачи, которая выполняет транзакция. Если транзакция изменяет данные в БД и при этом проверяет, чтобы эти данные не противоречили уже существующим записям в БД, то скорее всего нужен уровень изоляции serializable. Для чтения данных обычно достаточно использовать уровень изоляции по умолчанию ( read committed ) без какой либо транзакции. Но если вставка новых записей в параллельных транзакциях никак не может повлиять на результат текущей транзакции, то можно использовать уровень изоляции repeatable read. При чтении агрегатов, части которых могут быть изменены во время чтения, может понадобится использовать транзакцию с уровнем изоляции repeatable read или даже serializable, иначе можно получить из базы агрегат в некорректном состоянии, в котором он может быть только в процессе выполнения транзакции изменения. При получении в реальном времени статистики по постоянно изменяющимся данным, то лучше использовать уровень изоляции read uncommitted. В этом случае в статистике будет некоторое количество грязных данных (хотя вряд ли это будет заметно), но зато построение отчетов практически не будет влиять на скорость работы системы.

Изображение слайда
1/1
31

Слайд 31: Уровни изоляции транзакций

- Transact-SQL Уровни изоляции транзакций могут быть установлены с использованием Transact-SQL или через API доступа к СУБД: Используется инструкция SET TRANSACTION ISOLATION LEVEL - 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,   …

Изображение слайда
1/1
32

Слайд 32: Уровни изоляции транзакций

Команда Transact-SQL установки уровня изоляции транзакции SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT } Установленный уровень изоляции действует на протяжении всего сеанса подключения или до явной замены на другой уровень Текущий уровень изоляции транзакции можно получить консольной командой DBCC USEROPTIONS

Изображение слайда
1/1
33

Слайд 33: Уровни изоляции транзакций

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

Изображение слайда
1/1
34

Слайд 34: Блокировки

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

Изображение слайда
1/1
35

Слайд 35: Блокировки

Транзакция А Транзакция B Таблица Т3 UpDate Т1 UpDate Т 3 Блокировка А Блокировка B Select Т 2 Блокировка А UpDate Т1 UpDate Т 2 Commit Begin tran A Begin tran B Select Т 2 Разблокиров. Разблокиров. Блокировка B Разблокиров. Commit X ожидание Разблокиров. 01 Таблица Т1 Таблица Т2 05 03 06 07 02 04 08 09 09 02 04 08

Изображение слайда
1/1
36

Слайд 36: Типы и уровни блокировки

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

Изображение слайда
1/1
37

Слайд 37: Типы и уровни блокировки

Основные типы блокировок 1. Коллективные блокировки (S) ( Shared) 3. Блокировки обновления ( U) ( Update) 2. Монопольные блокировки (X) ( eXclusive) Накладывается при выполнении операций чтения данных. Заблокированные объекты доступны другим транзакциям в режиме чтения Накладывается при выполнении операций изменения данных. Заблокированные объекты не доступны другим транзакциям ни в режиме чтения, ни в режиме изменения. Переходная блокировка. Накладывается при установленной коллективной блокировке на объект. Другие транзакции уже не могут установить никакие другие блокировки. После того как будет снята коллективная блокировка эта блокировка будет заменена на монопольную, если необходимо изменять данные, или на коллективную, если данные не изменяются. Например, для одиночной команды UPDATE требуется сначала произвести чтение данных, а потом их замену. Тогда и подойдет блокировка U.

Изображение слайда
1/1
38

Слайд 38: Блокировки

Транзакция А Транзакция B Таблица Т3 UpDate Т1 UpDate Т 3 монопольная А монопольная B Select Т 2 коллективн А UpDate Т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 монопольная А

Изображение слайда
1/1
39

Слайд 39: Типы и уровни блокировки

В SQL Server с версии 2008 имеется ещё другие основные блокировки 4. Блокировка диапазона ключа 6. Блокировка изменения схемы ( Sch-M) (Modification Lock) 5. Блокировка стабильности схемы ( Sch-S) (Stability Lock) Накладывается на диапазон строк, удовлетворяющих определенному условию. Решается проблема возникновения фантомов. Накладывается на схему объекта, если любая транзакция установила блокировку любого типа (запрещается изменять схему, когда над таблицей производятся действия). Когда все блокировки снимаются, то автоматически снимается эта блокировка. Накладывается на объект, как только начинается изменение структуры объекта. Никакая другая блокировка не может быть наложена на объект, пока установлена эта блокировка.

Изображение слайда
1/1
40

Слайд 40: Типы и уровни блокировки

Введение типов блокировок ликвидирует проблемы одновременного доступа, но создает другую проблему – Для решения проблемы потери производительности введены уровни блокировок. 1. RID – блокировка уровня строки Уровни блокировок реализованы на уровнях иерархии объектов БД: 2. KEY – блокировка уровня индекса (группа строк) 3. PAG – блокировка уровня страницы 4. EXT – блокировка уровня группы страницы 5. TAB – блокировка уровня таблицы 6. DB – блокировка уровня базы данных задержки выполнения транзакций.

Изображение слайда
1/1
41

Слайд 41: Специальные блокировки

Специальные блокировки или блокировки намерения используются для разрешения конфликтов наложения блокировок на различных уровнях 1. Коллективные блокировки намерения (IS) ( Intent Shared) 3. Коллективно - монопольная блокировка намерения ( SIX) ( Shared Intent with eXclusive ) 2. Монопольные блокировки намерения (IX) ( Intent eXclusive ) Накладывается при намерении транзакции читать данные вниз по иерархии объекта (например, таблица – группа страниц – страница – строка). Другим транзакциям запрещается устанавливать монопольные блокировки вниз по иерархии. Накладывается при намерении транзакции изменять данные вниз по иерархии объекта. Другим транзакциям запрещается устанавливать любые блокировки вниз по иерархии. Накладывается при намерении транзакции читать данные вниз по иерархии объекта и выполнять их частичное изменение, устанавливая монопольные блокировки.

Изображение слайда
1/1
42

Слайд 42: Совместимость блокировок

Тип запрашиваемой блокировки Тип наложенной блокировки IS S U IX SIX X IS + + + + + S + + + U + + IX + + SIX + X Если на страницу наложена коллективная блокировка, то на эту страницу нельзя наложить монопольную ( X) блокировку, блокировки намерения коллективно – монопольную ( SIX) и монопольную (IX). Совместимость блокировок определяет возможности транзакций одновременно получить блокировку одного и того же ресурса.

Изображение слайда
1/1
43

Слайд 43: Управление блокировками

SQL Server динамически управляет выбором типа и уровня блокировок. Решение об уровне блокировки принимается автоматически во время оптимизации запроса. Например, для операторов модификации данных и SELECT для небольшого количества данных блокировка задается на уровне строки или ключа, а при большом объеме данных для оператора SELECT (например, SELECT * FROM tableX ) устанавливается блокировка на уровне страницы или таблицы. Управление блокировками выполняет специальный компонент сервера – менеджер блокировок. Пользователю чаще всего не нужно предпринимать никаких действий по управлению блокировками. В функции менеджера блокировок входит установка, снятие и разрешение конфликтов блокировок.

Изображение слайда
1/1
44

Слайд 44: Блокировки

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

Изображение слайда
1/1
45

Слайд 45: Управление блокировками

SQL Server 2008 просмотреть блокировки можно sp_lock Идентификатор сеанса Идентификатор БД в которой удерживается блокировка Идентификатор объекта, где удерживается блокировка Идентификатор индекса, где удерживается блокировка (0 – собственно таблица) Уровень блокировки Значение блокируемого ресурса Тип блокировки Состояние блокировки Для получения имени идентификаторов можно воспользоваться функциями: DB_NAME( spid ) OBJECT_NAME( ObjId ) Состояние блокировки: GRANT : блокировка получена WAIT : ожидание блокировки CNVRT : блокировка в конфликтном режиме

Изображение слайда
Изображение для работы со слайдом
1/2
46

Слайд 46: Блокировки

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

Изображение слайда
1/1
47

Слайд 47: Блокировки

Но при необходимости в запросе можно явно указать какой тип блокировки необходимо использовать в том или ином случае. Так же можно управлять временем ожидания разблокирования ресурса с помощью команды SET LOCK_TIMEOUT < время_в_мс >

Изображение слайда
1/1
48

Слайд 48: Управление блокировками в запросе

При выполнении запроса уровень изоляции действует тот, который был установлен командой, Форматы команд с хинтами SELECT … FROM table_name WITH (hint) WHERE … SET TRANSACTION ISOLATION LEVEL Если в запросе необходимо установить блокировку или её продолжительность отличную от устанавливаемой по действующему уровню изоляции, то это можно сделать, указав в команде соответствующие специальные ключевые слова (хинты). INSERT table_name (list_col) WITH (hint) VALUES … UPDATE table_name WITH (hint) SET … DELETE table_name WITH (hint) WHERE … или по умолчанию)

Изображение слайда
1/1
49

Слайд 49: Управление блокировками в запросе

Ключевые слова для явного указания типа блокировки (хинты) NOLOCK ( READUNCOMMITTED ) – разрешает чтение незафиксированных данных, которые были изменены другими транзакциям. PAGLOCK – устанавливает блокировку страницы вместо таблицы UPDLOCK– определяет применение блокировки обновления до завершения транзакции HOLDLOCK ( SERIALIZABLE ) – устанавливает совмещаемую блокировку до завершения транзакции XLOCK – определяет применение монопольной блокировки на соответствующем уровне до завершения транзакции ROWLOCK – устанавливает блокировку на уровне строки TABLOCK – устанавливает соответствующую блокировку на уровне таблицы TABLOCK Х – устанавливает монопольную блокировку на уровне таблицы до завершена транзакция

Изображение слайда
1/1
50

Слайд 50: Управление блокировками в запросе

READCOMMITTED – определяет правила для чтения, как для уровня изоляции READ COMMITTED (либо блокировка строк либо управление версиями, в зависимости, что установлено) REPEATABLEREAD – определяет выполнение просмотра с семантикой блокировки, как для уровня изоляции REPEATABLE READ READCOMMITTEDLOCK – определяет правила для чтения, как для уровня изоляции READ COMMITTED с использованием блокировки и некоторые др.

Изображение слайда
1/1
51

Слайд 51: Тупиковые блокировки

- возникают когда две транзакции блокируют два блока данных и для завершения работы каждой из них необходим доступ к данным, заблокированным ранее другой транзакцией. Транзакция А Транзакция B UpDate Т1 UpDate Т 2 монопольная А монопольная B UpDate Т2 Таблица Т1 Таблица Т2 UpDate Т 1 X X ожидание ожидание

Изображение слайда
1/1
52

Слайд 52: Тупиковые блокировки

Для разрешения конфликта тупиковых блокировок в современных СУБД имеются специальные механизмы обнаружения и разрешения тупиковых блокировок. Один из алгоритмов обнаружения тупиковых блокировок. 1. Для каждого ресурса строится граф ожидания транзакций 2. Определяется в графе наличия цикла Если цикл обнаружен, то в системе имеется тупиковая блокировка и далее должна выполнится процедура её разрешения Т 10 Т 12 Т 4 Т 8 Т 3 Транзакция Т 10 ожидает освобождения ресурса транзакцией Т 12

Изображение слайда
1/1
53

Слайд 53: Тупиковые блокировки

В основе стратегии разрешения тупиковых блокировок положен откат одной из транзакции, вызвавших её конфликт. Т 10 Т 12 Т 4 Т 8 Т 3 Выбор на откат транзакции выполняется по принципу: 1. минимальный приоритет; 2. любая из двух с одинаковым приоритетом. X Для установки приоритета блокировки используется команда SET DEADLOCK_PRIORITY {LOW | NORMAL}.

Изображение слайда
1/1
54

Слайд 54: Тупиковые блокировки

Для минимизации возможностей возникновения тупиковых блокировок при разработке кода транзакций нужно придерживаться следующих правил: 1. Создание индексов для команд UPDATE и DELETE, содержащих предложение WHERE. При выполнении этих команд без использования индексов осуществляется монопольная блокировка всей таблицы. При наличии индекса монопольная блокировка устанавливается на строку или страницу. 2. Вместо команды INSERT с большим количеством вставляемых строк использовать команду вставки по одной строке (в цикле, используя курсор). При выполнении команды INSERT для вставки много строк осуществляется монопольная блокировка всей таблицы. При наличии INSERT для вставки одной строки монопольная блокировка устанавливается на строку.

Изображение слайда
1/1
55

Слайд 55: Тупиковые блокировки

3. Избегать использования в запросах ключевого слова HOLDLOCK При использовании HOLDLOCK в SELECT все коллективные блокировки будут оставаться в силе, пока вся транзакция не будет завершена. Без его использования – блокировка снимается, как это станет возможным, не дожидаясь окончания всей транзакции. 4. Использовать как можно более короткие транзакции. а) разбивать продолжительную транзакцию на короткую; б) минимизировать количество некластерных индексов (плотный); в) сокращать число столбцов в таблицах (увеличит количество строк на странице и, следовательно, время выполнения транзакций).

Изображение слайда
1/1
56

Слайд 56: Тупиковые блокировки

5. Избегать использования вложенных транзакций Во всех случаях использования вложенных транзакций все установленные в ней блокировки сохраняются до завершения внешней транзакции. 6. Исключать использования взаимодействия с пользователем во время выполнения транзакции. Например, READ UNCOMMITTED вместо SERIALIZABLE позволит нескольким транзакциям одновременно читать данные: каждая транзакция сможет установить коллективную блокировку не дожидаясь пока друга считает данные и снимет блокировку. 7. Использовать как можно более низкий уровень изоляции 8. Установить на севере дополнительную оперативную память Это увеличит КЭШ буферов и следовательно скорость выполнения транзакций и снизит конкуренцию за доступ к ресурсам.

Изображение слайда
1/1
57

Последний слайд презентации: Транзакции: Тупиковые блокировки

serializable - могут происходить любые взаимные блокировки. При использовании транзакций с уровнем repeatable read - некоторые из взаимных блокировок не могут произойти. read uncommitted практически не могут возникнуть взаимные блокировки из-за чтения, так как она не накладывает shared блокировки (но могут быть взаимные блокировки с транзакциями изменяющими схему БД). read committed могут возникнуть только простейшие взаимные блокировки.

Изображение слайда
1/1
Реклама. Продолжение ниже