Часть 4. Понятие уровня изоляции sql Server

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

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

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

SQL Server поддерживает все четыре уровня изоляции, определен­ные стандартом ANSI.

Уровень изоляции устанавливается командой:

SET TRANSACTION ISOLATION LEVEL

{ READ COMMITTED | READ UNCOMMITTED

| REPEATABLE READ | SERIAIZABLE }

Здесь:

• READ UNCOMMITED - разрешается незавершенное чтение, или допустимо черно­вое чтение. Запросы, выполняемые с таким уровнем изоляции, будут обнаруживать любое изменение в базе данных независимо от того, зафиксировано это изменение или нет.

Это низший уровень изоляции. Он гарантирует только физическую целостность данных: если не­сколько пользователей одновременно изменяют одну и ту же строку, то в окончательном варианте строка будет иметь значение, опреде­ленное пользователем, последним изменившим запись. По сути, для транзакции не устанавливается никакой блокировки, которая гаран­тировала бы целостность данных. Это наиболее опасный из всех вариантов выбора уровня изоляции. Но он обеспечивает наивысшую производительность (скорость выполнения транзакций).

Для установки этого уровня ис­пользуется команда:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

• READ COMMITTED – этот уровень изоляции запрещает чтение незафиксированных данных. Он называется «завершенное чтение», при нем отсутствует черновое, «грязное» чтение. Таким образом, чтение разрешается только после завершения конкурирующей транзакции.

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

Это - проблема неповторяемого чтения.

Данный уровень изоляции установлен в SQL Server по умолчанию и устанавливается посредством команды:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

• REPEATABLE READ - повторяющееся чтение. Всякий раз повторное чтение строки будет возвращать первоначально считанные данные, несмотря на любые обновления, произведенные другими пользователями до завер­шения транзакции. Тем не менее, на этом уровне изоляции возможно возникновение фантомов.

Установка этого уровня изоляции выполняется командой:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

• SERIALIZABLE - сериализуемость. В этом случае всякое чтение, в том числе и операции UPDATE, DELETE, INSERT, запрещены до заверше­ния ткущей транзакции. Это максимальный уровень изоляции, который обеспечивает полную изоляцию транзакций друг от друга.

Он уста­навливается командой:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

В каждый момент времени возможен только один уровень изоляции.

Пример 1. Уровень изоляции конкурирующей транзакции принят по умолчанию (т.е. READ COMMITTED).

В этом примере шаги 4, 6 и 8 демонстрируют черновое чтение.

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

Пользователь 1 (конкурирующая транзакция)	Пользователь 2 (текущая транзакция)
USE Торговая_фирма BEGIN TRANSACTION TrA 1. SELECT * FROM Товар	USE Торговая_фирма SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION TrB 2. SELECT * FROM Товар
3. UPDATE Товар SET Категория = 23 WHERE ID_товара = 4
	4. SELECT * FROM Товар (читает измененные и неподтвержденные данные)
5. DELETE FROM Товар WHERE ID_товара = 4
	6. SELECT * FROM Товар (читает измененные и неподтвержденные данные)
7. INSERT INTO Товар (Название, категория) VALUES ('Семга', 999)
	8. SELECT * FROM Товар (читает измененные и неподтвержденные данные)
	9. UPDATE Товар SET Единица_измерения= 'л' WHERE ID_Товара=4 (Блокируется до окончания конкурирующей транзакции)
	10. DELETE FROM Товар WHERE ID_товара = 4 (Блокируется до окончания конкурирующей транзакции)
	11. INSERT INTO Товар (Название, Категория) VALUES (‘Минтай’, 999) (выполняется)
12. ROLLBACK TRANSACTION TrA SELECT @@Trancount
	13. ROLLBACK TRANSACTION TrB SELECT @@Trancount

Пример 2. Уровень изоляции конкурирующей транзакции будет принят по умолчанию (т.е. READ COMMITED).

В шагах 4, 6, 8 осуществляется блокировка данных, захваченных другой транзакцией, в то время как работа с другими данными разрешена (шаг 10)..

Пользователь 1 (конкурирующая транзакция)	Пользователь 2 (текущая транзакция)
USE Торговая_фирма BEGIN TRANSACTION TrA 1. SELECT * FROM Товар	USE Торговая_фирма SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION TrB 2. SELECT * FROM Товар
3. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4
	4. SELECT * FROM Товар (Читать нельзя. Блокируется до окончания конкурирующей транзакции)
5. DELETE FROM Товар WHERE ID_товара = 4
	6. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (блокируется до окончания конкурирующей транзакции)
7. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (выполняется той транзакцией, которая первой захватила данные на изменение или удаление)
	8. DELETE FROM Товар WHERE ID_товара = 4 (Блокируется до окончания конкурирующей транзакции)
9. INSERT INTO Товар (Название, категория) VALUES ('Семга', 999)
	10. INSERT INTO Товар (Название, категория) VALUES ('Семга', 999) (выполняется)
11. ROLLBACK TRANSACTION TrA SELECT @@Trancount
	12. ROLLBACK TRANSACTION TrB SELECT @@Trancount

Пример 3. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED).

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

Пользователь 1 (конкурирующая транзакция)	Пользователь 2 (текущая транзакция)
USE Торговая_фирма BEGIN TRANSACTION TrA 1. SELECT * FROM Товар	USE Торговая_фирма SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION TrB 2. SELECT * FROM Товар (Захватывает данные)
3. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (Блокируется)
	4. SELECT * FROM Товар (Читать нельзя. Блокируется до окончания конкурирующей транзакции)
5. DELETE FROM Товар WHERE ID_товара = 4 (Блокируется)
	6. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (Выполняется, т.к. данные захвачены текущей транзакцией)
7. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (выполняется той транзакцией, которая первой захватила данные на изменение или удаление)
	8. DELETE FROM Товар WHERE ID_товара = 4 (Выполняется т.к. данные захвачены текущей транзакцией)
	9. INSERT INTO Товар (Название, категория) VALUES ('Семга', 999) (выполняется)
10. ROLLBACK TRANSACTION TrA SELECT @@Trancount
	11. ROLLBACK TRANSACTION TrB SELECT @@Trancount

Пример 4. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED).

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

Пользователь 1 (конкурирующая транзакция)	Пользователь 2 (текущая транзакция)
USE Торговая_фирма BEGIN TRANSACTION TrA 1. SELECT * FROM Товар	USE Торговая_фирма SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION TrB 2. SELECT * FROM Товар (Захватывает данные)
3. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (Блокируется)
	4. SELECT * FROM Товар ( Выполняется )
5. DELETE FROM Товар WHERE ID_товара = 4 (Блокируется)
	6. UPDATE Товар SET Категория = 'Хлебобулочные ' WHERE ID_товара = 4 (Выполняется, т.к. данные захвачены текущей транзакцией)
7. INSERT INTO Товар (Название, категория) VALUES ('Семга', 999) (Блокируется)
	8. DELETE FROM Товар WHERE ID_товара = 4 (Выполняется т.к. данные захвачены текущей транзакцией)
	9. INSERT INTO Товар (Название, категория) VALUES ('Семга', 999) (выполняется)
10. ROLLBACK TRANSACTION TrA SELECT @@Trancount
	11. ROLLBACK TRANSACTION TrB SELECT @@Trancount

Журнал транзакций

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

Основным принципом согласованной политики записи изме­нений в журнал и непосредственно в базу данных является то, что запись об изменении объекта базы данных должна попадать во внешнюю память журнала раньше, чем измененный объект оказывается во внешней памяти базы данных. Соответствующий протокол журнализации (и управления буферизацией) называет­ся Write Ahead Log (WAL) - «пиши сначала в журнал», и состоит в том, что если требуется сохранить во внешней памяти изменен­ный объект базы данных, то перед этим нужно гарантировать со­хранение во внешней памяти журнала записи о его изменении.

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

Иногда для восстановления последнего согласованного со­стояния базы данных после сбоя журнала изменений базы дан­ных явно недостаточно. Основой восстановления в этом случае являются журнал и архивная копия базы данных.

Восстановление начинается с обратного копирования базы данных из архивной копии. Затем для всех закончившихся транзакций в прямом смысле повторно выполняются все опера­ции. Более точно, происходит следующее: по журналу в прямом направлении выполняются все операции; для транзакций, кото­рые не закончились к моменту сбоя, выполняется откат (англ. back up).

Хотя к ведению журнала предъявляются особые требования по части надежности, реально возможна и его утрата. Тогда единственным способом восстановления базы данных является возврат к архивной копии. Конечно, в этом случае не удастся получить последнее согласованное состояние базы данных.

Рассмотрим способы производства архивных копий базы данных. Самый простой способ - архивировать базу данных при переполнении журнала. В этом случае образование новых тран­закций временно блокируется. Когда все транзакции закончатся, и, следовательно, база данных придет в согласованное состоя­ние, можно выполнять ее архивацию, после чего начинать за­полнять журнал заново.

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

В заключение сформулируем общие требования к системе восстановления данных в составе СУБД.

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

2. Быстрое восстановление данных обеспечивается генераци­ей данных, используемых для восстановления.

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

Для восстановления базы данных СУБД имеют в своем со­ставе сервисные программные средства:

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

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

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

Программы отката ликвидируют последствия выполнения определенной транзакции в БД.

Программы записи контрольных точек и повторного исполнения позволяют ускорить восстановление.

Примеры для самотестирования:

Вариант 1.

1. Пусть дана таблица Товар. На начало транзакций в таблице записей нет:

BEGIN TRAN

SAVE TRANSACTION p1

INSERT Товар (КодТовара, Название, остаток)

VALUES (I, 'a^’, 10) INSERT Товар (КодТовара, Название, остаток)

VALUES (2, ‘b^’, 20) INSERT Товар (КодТовара, название, остаток)

VALUES (3, 'с^’, 30) INSERT Товар (КодТовара, Название, остаток)

VALUES (4, 'd', 40) INSERT Товар (КодТовара, Название, остаток)

VALUES (5, 'е^’ , 50)

SAVE TRANSACTION p2

DELETE FROM Товар WHERE КодТовара=2

SAVE TRANSACTION p3

DELETE FROM Товар WHERE КодТовара=5

SAVE TRANSACTION p4

DELETE FROM Товар WHERE КодТоварао!

ROLLBACK TRANSACTION p3

INSERT Товар (КодТовара, Название, остаток)

VALUES (6, 'f',70) COMMIT

Указать вариант ответа

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

• КодТовара: 1, 3, 4 , 5, 6

• КодТовара: 1, 3, 4, 6

• КодТовара: 3, 4, б

• в таблице записей нет

2. Дана таблица Товар. На начало транзакций в таблице записей нет:

BEGIN TRAN А

INSERT Товар (КодТовара, Название, остаток) VALUES (1, 'V’, 40)

BEGIN TRAN В

INSERT Товар (КодТовара, Название, остаток) VALUES (2, 'n’,50) UPDATE Товар SET Название='т' WHERE КодТовара=1

BEGIN TRAN С

INSERT Товар (КодТовара, Название/ остаток)

VALUES (3, 'm¹,60)

ROLLBACK TRAN С

INSERT Товар (КодТовара, Название, остаток) VALUES (4, 'k',70)

COMMIT

Указать вариант ответа

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

• названия: 'm', 'n', 'm', 'k'

• названия: 'v' , 'т', 'п.', 'k'

• названия: 'v' , 'n' , 'k'

• названия: ' n' , ' k'

3. Транзакции выполняют различные действия.

Первой начинает работать транзакция пользователя UserA:

Указать, какие шаги система выполнит, а какие будут заблокированы?

Вариант 2

Дана таблица Товар. На начало транзакций в таблице записей нет:

BEGIN TRAN

SAVE TRANSACTION p1

INSERT Товар (КодТовара, Название, остаток)

VALUES (1, 'a', 10)

INSERT Товар (КодТовара, Название, остаток)

VALUES (2, 'b^’, 20)

INSERT Товар (КодТовара, Название, остаток)

VALUES (3, 'С', 30)

INSERT Товар (КодТовара, Название, остаток)

VALUES (4, 'd', 40)

INSERT Товар (КодТовара, Название, остаток)

VALUES (5, 'e^’, 50)

SAVE TRANSACTION p2

DELETE FROM Товар WHERE КодТовара=4

SAVE TRANSACTION p3

UPDATE Товар SET Название='д' WHERE КодТовара=2

SAVE TRANSACTION p4

DELETE FROM Товар WHERE КодТовара <> 1

AND КодТовара <> 4

ROLLBACK TRANSACTION p3

INSERT Товар (КодТовара, Название, остаток) VALUES (6, 'f',70)

ROLLBACK TRANSACTION p2

DELETE FROM Товар WHERE КодТовара=3

COMMIT

Указать вариант ответа.

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

• названия: 'а', 'д', 'е'

• названия: 'а', 'd', ' с' , 'е'

• названия: 'a’, 'b', 'с', 'е', 'f

• названия: 'а', 'b', 'd', 'е'

2. Дана таблица Товар. На начало транзакций в таблице записей нет:

BEGIN TRAN A

INSERT Товар (КодТовара, Название, остаток)

VALUES (1, 'V, 40)

BEGIN TRAN В

INSERT Товар (КодТовара, Название, остаток) VALUES (2, 'n', 50)

BEGIN TRAN С

INSERT Товар (КодТовара, Название, остаток) VALUES (3, 'm', 60)

ROLLBACK TRAN В

INSERT товар (КодТовара, Название, остаток)

VALUES (4, 'k^’, 70)

COMMIT

Указать вариант ответа

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

• КодТовара: 1, 4

• КодТовара: 4

• КодТовара: 1, 2, 4

• КодТовара: 1, 2, 3, 4

Вариант 3

1. Дана таблица Товар. На начало транзакций в таблице за­писей нет:

BEGIN TRAN

SAVE TRANSACTION p1

INSERT Товар (КодТовара, Название, остаток) VALUES (1, 'a^’, 10)

INSERT Товар (КодТовара, Название, остаток) VALUES (2, 'b^’, 20)

INSERT Товар {КодТовара, Название, остаток) VALUES {3, 'с', 30)

INSERT Товар (КодТовара, Название, остаток) VALUES (4, 'd^’, 40)

INSERT Товар (КодТовара, Название, остаток) VALUES (5, 'е^’, 50)

SAVE TRANSACTION p2

DELETE PROM Товар WHERE КодТовара=2

SAVE TRANSACTION p3

DELETE FROM Товар WHERE КодТовара=5

SAVE TRANSACTION p4

DELETE FROM Товар WHERE КодТовара<2 OR КодТовара>4

ROLLBACK TRANSACTION p3

INSERT Товар (КодТовара, Название, остаток) VALUES (6, 'f',60)

SAVE TRANSACTION p5

INSERT Товар (КодТовара, Название, остаток) VALUES (7, 'g^’,70)

ROLLBACK TRANSACTION p5

COMMIT

Указать вариант ответа

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

• КодТовара: 1, 3 , 4, 5, 6

• КодТовара: 1, 3, 4, б

• КодТовара: 3, 4, 6,7

• в таблице записей нет

2. Дана таблица Товар. На начало транзакций в таблице записей нет:

BEGIN TRAN A

INSERT Товар (КодТовара, Название, остаток) VALUES (1, 'a', 40)

BEGIN TRAN В

INSERT Товар (КодТовара, Название, остаток) VALUES (2, 'b',50)

UPDATE Товар SET Название='т' WHERE КодТовара=1

BEGIN TRAN С

INSERT Товар (КодТовара, Название, остаток) VALUES (3, 'С',60)

ROLLBACK TRAN С

INSERT Товар (КодТовара, Название, остаток) VALUES (4, 'd^’,70)

UPDATE Товар SET Название='k' WHERE КодТовара=1

ROLLBACK TRAN

Указать вариант ответа

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

• в таблице записей нет

• названия: 'k', 'b', 'с', 'd'

• названия: 'k'

• названия: 'd' , 'k'