Вступление
Прежде чем понять уровень изоляции Microsoft SQL Server, мы должны вспомнить первый класс системы управления реляционными базами данных (RDBAMS) и свойство ACID RDBMS.
|
A
|
атомное
|
|
С
|
консистенция
|
|
я
|
Изоляция
Это свойство означает, что каждая транзакция выполняется изолированно от других, и что параллельные транзакции не влияют на транзакцию . |
|
D
|
долговечность
|
Типы уровня изоляции в MS SQL Server
Существует пять типов уровня изоляции в MS SQL Server.
1.
Read Committed (уровень изоляции по умолчанию для MS SQL Server)
2.
Чтение незафиксированного
3.
Повторяемое чтение
4.
Сериализуемый
5.
Снимок
Прежде чем перейти к уровню изоляции, мы должны четко понимать две вещи
Грязные чтения
Это когда мы читаем незафиксированные данные, при этом нет никакой гарантии, что чтение данных когда-либо будет зафиксировано.
Фантомные чтения
Это когда данные, с которыми мы работаем, были изменены другой транзакцией с момента ее первого чтения. Это означает, что последующие чтения этих данных в одной и той же транзакции могут быть разными.
Чтобы проверить текущий уровень изоляции
DBCC
useroptions
useroptions
Прочитанный уровень приверженности изоляции
Указывает, что операторы не могут читать данные, которые были изменены, но не зафиксированы другими транзакциями. Это предотвращает грязное чтение. Данные могут быть изменены другими транзакциями между отдельными операторами в текущей транзакции, что приводит к неповторимым чтениям или фантомным данным. Эта опция используется по умолчанию для SQL Server.
-- Query-1 of Session -1 BEGIN TRAN UPDATE tbl_Test SET Col1 = 2; WAITFOR DELAY '00:00:10'; ROLLBACK --Query-2 of Session -2 SELECT * FROM tbl_Test;
В приведенном выше примере Query-2 ожидает завершения Query-1. Когда Query-1 завершен, Query-2 получает записи.
Чтение незафиксированного уровня изоляции
На этом уровне изоляции транзакция может считывать данные, которые изменены некоторыми другими транзакциями, но еще не зафиксированы. Этот уровень изоляции не выдает разделяемую блокировку, чтобы предотвратить изменение данных, которые считываются некоторыми другими транзакциями. Кроме того, он не создает исключительных блокировок для предотвращения чтения транзакциями данных, которые еще не зафиксированы другими транзакциями. Это может привести к таким проблемам, как грязное чтение, потерянные обновления, фантомные чтения и т. Д. Это наименее ограничительный уровень изоляции.
-- Query-1 of Session - 1 BEGIN TRAN UPDATE tbl_Tests SET Col1 = 2; WAITFOR DELAY '00:00:10'; ROLLBACK -- Query-2 of Session - 2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM tbl_Tests;
Вот в этом примере Query-2 не ждет Query-1. Query-2 возвращает записи, не дожидаясь, пока Query-1 станет причиной грязных данных.
Повторяемый уровень изоляции чтения
Этот уровень изоляции выше, чем в предыдущих двух упомянутых уровнях изоляции, и он не позволяет какой-либо транзакции считывать данные, которые были изменены другой транзакцией, но еще не зафиксированы. Также он не позволяет какой-либо транзакции изменять данные, если они читаются какой-либо другой транзакцией, пока транзакция, считывающая эти данные, не завершит свою работу. Таким образом, это устраняет проблему грязного чтения и повторного чтения, но не устраняет фантомное чтение.
--Query - 1 of Session - 1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT * FROM tbl_Tests; WAITFOR DELAY '00:00:10'; SELECT * FROM tbl_Tests; ROLLBACK --Query - 2 of Session - 2 UPDATE tbl_Tests SET Col1 = -1
В приведенном выше примере Query-1 возвращает одинаковые данные для обоих вариантов выбора, даже если вы выполнили запрос для изменения данных до выполнения второго выбора. Это связано с тем, что запрос на обновление (Query-2) был вынужден ждать завершения Query-1 из-за исключительных блокировок, которые были открыты при задании Repeatable Read.
Сериализуемый уровень изоляции
Этот уровень изоляции не позволяет какой-либо транзакции читать данные, если другие транзакции не завершили свою операцию модификации данных. Также он не позволяет другим транзакциям изменять данные, пока текущая транзакция не завершит свою операцию чтения. Этот уровень изоляции позволяет транзакции получить блокировку чтения (если только операция чтения) или блокировку записи (для вставки, удаления, обновления) для всего диапазона записей, на которые будет влиять транзакция.
--Query - 1 of Session -1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM tbl_Tests; WAITFOR DELAY '00:00:10'; SELECT * FROM tbl_Tests; ROLLBACK --Query - 2 of Session -2 INSERT INTO tbl_Tests(Col1,Col2,Col3) VALUES (100,100,100);
В приведенном выше примере мы увидим, что вставка в Query-2 ожидает завершения Query-1, прежде чем она выполнит, исключая вероятность фантомного чтения.
Если мы изменим уровень изоляции в Query-1 на повторяемое чтение, мы увидим, что вставка больше не блокируется, и два оператора seleclt в Query-1 возвращают различное количество строк.
Уровень изоляции снимка
На этом уровне изоляции транзакция распознает только те данные, которые были зафиксированы до ее начала. Любая модификация данных после начала транзакции не видна никаким операторам выполняемой в данный момент транзакции. Это как снимок данных, передаваемых каждой транзакции. Это делается с помощью версии строки, где отдельная версия каждой измененной строки поддерживается в базе данных temp db, предназначенной для транзакций. Этот уровень изоляции устраняет проблемы грязного чтения, потерянных обновлений, повторяющихся чтений и проблем фантомного чтения.
ALTER DATABASE myDb SET ALLOW_SNAPSHOT_ISOLATION ON;
Надеюсь, вам понравится.