Set transaction isolation level: Postgres Pro Standard : Документация: 9.6: SET TRANSACTION : Компания Postgres Professional

SET TRANSACTION — База знаний MariaDB

Синтаксис

 SET [GLOBAL | СЕССИЯ] ТРАНЗАКЦИЯ
    свойство_транзакции [ свойство_транзакции] ...
transaction_property:
    УРОВЕНЬ ИЗОЛЯЦИИ уровень
  | ЧИТАЙ ПИШИ
  | ТОЛЬКО ЧТЕНИЕ
уровень:
     ПОВТОРЯЕМОЕ ЧТЕНИЕ
   | ПРОЧИТАТЬ СОВЕРШЕНО
   | ЧИТАТЬ БЕЗ ЗАЯВЛЕНИЙ
   | СЕРИАЛИЗУЕМЫЙ
 

Содержание

1. Синтаксис
2. Описание
   1. Уровень изоляции
   2. Уровни изоляции
      1. ЧТЕНИЕ НЕЗАРЕГИСТРИРОВАНО
      2. ЧТЕНИЕ СОВЕРШЕНО
      3. ПОВТОРЯЕМОЕ ЧТЕНИЕ
      4. СЕРИАЛИЗУЕМЫЙ
   3. Режим доступа
3. Примеры

Описание

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

• С ключевым словом GLOBAL оператор устанавливает значение по умолчанию уровне транзакции глобально для всех последующих сеансов. Существующие сеансы незатронутый.
• С ключевым словом SESSION инструкция устанавливает значение по умолчанию уровень транзакции для всех последующих транзакций, выполненных в рамках текущий сеанс.
• Без ключевого слова SESSION или GLOBAL , оператор устанавливает уровень изоляции для следующей (не начатой) транзакции выполняется в рамках текущего сеанса.

Для изменения глобального уровня изоляции по умолчанию требуется SUPER привилегия. Любая сессия может изменить свою уровень изоляции сеанса (даже в середине транзакции) или уровень изоляции уровень для следующей транзакции.

Уровень изоляции

Чтобы установить глобальный уровень изоляции по умолчанию при запуске сервера, используйте --transaction-isolation=опция уровня в командной строке или в файле опций. Значения уровня для этой опции используют дефисы а не пробелы, поэтому допустимые значения

READ-UNCOMMITTED , READ-COMMITTED , REPEATABLE-READ , или СЕРИАЛИЗУЕМЫЙ . Например, чтобы установить изоляцию по умолчанию уровень до REPEATABLE READ , используйте эти строки в [mysqld] раздел файла опций:

 
 [mysqld]
transaction-isolation = REPEATABLE-READ 
 

Для определения уровней изоляции глобальных и сеансовых транзакций в во время выполнения проверьте значение системной переменной tx_isolation :

 SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
 

InnoDB поддерживает каждый из описанных здесь уровней изоляции трансляции использование различных стратегий блокировки. Уровень по умолчанию ПОВТОРЯЕМОЕ ЧТЕНИЕ . Для получения дополнительной информации об InnoDB блокировки уровня записи и то, как они используются для выполнения различных типов операторов, см. режимы блокировки InnoDB, и http://dev.mysql.com/doc/refman/en/innodb-locks-set.html.

Уровни изоляции

В следующих разделах описывается, как MariaDB поддерживает различные уровни транзакций.

READ UNCOMMITTED

Операторы SELECT выполняются без блокировки, но может использоваться более ранняя версия строки. Таким образом, с помощью этого уровне изоляции такие чтения не являются согласованными. Его еще называют «грязным». читать.» В противном случае этот уровень изоляции работает как ЧТЕНИЕ СОВЕРШЕНО .

READ COMMITTED

Несколько похожий на Oracle уровень изоляции в отношении согласованности (неблокирующие) чтения: Каждое последовательное чтение, даже в пределах одного транзакция, устанавливает и читает свой собственный свежий снимок. Видеть http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html.

Для блокировки чтения ( ВЫБЕРИТЕ с ДЛЯ ОБНОВЛЕНИЯ или LOCK IN SHARE MODE ), InnoDB блокирует только индексные записи, а не пробелы перед ними и, таким образом, позволяет свободно вставлять новые записи рядом с заблокированные записи. Для

ОБНОВИТЬ и УДАЛИТЬ инструкции, блокировка зависит от того, использует ли инструкция уникальный индекс с уникальное условие поиска (например, WHERE id = 100 ) или условие поиска по диапазону (например, WHERE id > 100 ). Для уникальный индекс с уникальным условием поиска, InnoDB блокирует только запись индекса найдено, а не пробел перед ним. Для поиска по диапазону InnoDB блокирует индекс сканируется диапазон с использованием блокировки пробела или блокировки следующего ключа (промежуток плюс индексная запись) для блокировать вставки другими сеансами в промежутки, охватываемые диапазоном. Это необходимо, потому что «фантомные строки» должны быть заблокированы для репликации MySQL и восстановление для работы.

Примечание: Если READ COMMITTED

изоляция используется уровень или включена системная переменная innodb_locks_unsafe_for_binlog, нет блокировки промежутка InnoDB, за исключением проверки ограничений внешнего ключа и проверка дубликатов ключей. Кроме того, снимаются блокировки записей для несовпадающих строк. после того, как MariaDB оценил условие WHERE . Если вы используете READ COMMITTED или включаете innodb_locks_unsafe_for_binlog, вы должны использовать двоичное ведение журнала на основе строк.

ПОВТОРЯЕМОЕ ЧТЕНИЕ

Это уровень изоляции по умолчанию для InnoDB. Для последовательного чтения, есть важное отличие от READ COMMITTED уровень изоляции: все непротиворечивые чтения в рамках одной и той же транзакции считывают моментальный снимок, созданный при первом чтении. Это соглашение означает, что если вы выдаете несколько простых (неблокирующих) операторов

SELECT в одна и та же транзакция, эти операторы SELECT согласованы также по отношению друг к другу. Видеть http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html.

Для блокировки чтения (ВЫБРАТЬ с РЕЖИМОМ ОБНОВЛЕНИЯ или БЛОКИРОВКИ В ОБЩЕМ РЕЖИМЕ), UPDATE и DELETE блокировка зависит от того, оператор использует уникальный индекс с уникальным условием поиска или условие поиска по диапазону. Для уникального индекса с уникальным поиском условие, InnoDB блокирует только найденную запись индекса, а не пробел перед этим. Для других условий поиска InnoDB блокирует диапазон индекса. отсканировано, используя блокировки пробелов или блокировки следующего ключа (промежуток плюс индексная запись) для блокировать вставки другими сеансами в промежутки, охватываемые диапазоном.

Это минимальный уровень изоляции для нераспределенных транзакций XA.

SERIALIZABLE

Этот уровень похож на REPEATABLE READ, но InnoDB неявно преобразует все простые операторы SELECT для SELECT ... LOCK IN SHARE MODE if autocommit выключен. Если автофиксация включена, SELECT является собственным сделка. Поэтому известно, что он доступен только для чтения и может быть сериализуется, если выполняется как последовательное (неблокирующее) чтение и необходимость не блокировать для других транзакций. (Это означает, что для принудительного ВЫБЕРИТЕ, чтобы заблокировать, если другие транзакции изменили выбранные строки, вы должны отключить автофиксацию.)

Распределенные транзакции XA всегда должны использовать этот уровень изоляции.

Режим доступа

Режим доступа указывает, разрешена ли транзакции запись данных или нет. По умолчанию транзакции находятся в режиме READ WRITE (см. системную переменную tx_read_only). ТОЛЬКО ДЛЯ ЧТЕНИЯ Режим позволяет подсистеме хранения применять оптимизации, которые нельзя использовать для транзакций, записывающих данные. Единственным исключением из этого правила является то, что транзакции только для чтения могут выполнять операторы DDL для временных таблиц.

Не разрешается указывать в одном операторе как READ WRITE , так и READ ONLY .

READ WRITE и READ ONLY также можно указать в операторе START TRANSACTION , и в этом случае указанный режим действителен только для одной транзакции.

Примеры

 SET ГЛОБАЛЬНЫЙ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ SERIALIZABLE;
 

Попытка установить уровень изоляции в рамках существующей транзакции без указания ГЛОБАЛЬНЫЙ или СЕССИЯ .

 НАЧАТЬ СДЕЛКУ;
УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ SERIALIZABLE;
ОШИБКА 1568 (25001): характеристики транзакции не могут быть изменены во время выполнения транзакции
 

Уровни изоляции в SQL Server — SQLServerCentral

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

1. Чтение незафиксировано
2. Чтение зафиксировано
3. Повторяемое чтение
4. Сериализуемое
5. Снимок

Чтение зафиксировано — это уровень изоляции по умолчанию. Однако его можно изменить из окна запроса, а также из инструментов Management Studio.

Синтаксис:

УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ
    {ЧИТАТЬ НЕЗАЯВЛЕННОЕ
    | ПРОЧИТАТЬ СОВЕРШЕНО
    | ПОВТОРЯЕМОЕ ЧТЕНИЕ
    | СНИМОК
    | СЕРИАЛИЗУЕМЫЙ
    } 

В меню инструментов SSMS следует выбрать «Параметры». В разделе «Выполнение запроса» ->

«Дополнительно» можно изменить раскрывающийся список «Установить уровень изоляции транзакций».

Предварительные условия

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

Dept_Exam_StudentMarks.sql:

Этот сценарий содержит операторы создания таблицы и вставки примеров данных для таблиц Dept и Exam. В этом скрипте также есть операторы создания таблицы и примеры вставки данных для таблицы StudentMarks. Вы можете добавить столько образцов данных, сколько пожелаете. Для моей настройки таблица StudentMarks изначально заполнена 2,397616 записей.

СОЗДАТЬ ТАБЛИЦУ
(
       deptId INT ПЕРВИЧНЫЙ КЛЮЧ,
       имя_отдела VARCHAR(100),
       deptDesc VARCHAR(300)
)
ВСТАВИТЬ В ОТДЕЛ
(идентификатор отдела, имя отдела, описание отдела)
ЦЕННОСТИ
(101, «Информатика и инженерия», «Курсы UG и PG по информатике и инженерии»),
(102, «Электроника и телекоммуникации», «Курсы UG и PG по электронике и телекоммуникациям»)
СОЗДАТЬ ТАБЛИЦУ Экзамен
(
       экзамен INT PRIMARY KEY,
       имя_экзамена VARCHAR(100),
       Описание экзамена VARCHAR(300)
)
ВСТАВИТЬ В ЭКЗАМЕН
(examId,examName,examDesc)
ЦЕННОСТИ
(201, «Структура данных», «Теоретическая статья и лабораторная работа по структуре данных»),
(202, «Система управления базами данных», «Теоретическая работа и лабораторная работа по системе управления базами данных»)
--создать таблицу
СОЗДАТЬ ТАБЛИЦУ
(
       studentId INT IDENTITY(1,1) ПЕРВИЧНЫЙ КЛЮЧ,
       Идентификатор отдела INT,
       экзамен INT,
       оценкиПолучено INT
)
ВЫБЕРИТЕ СЧЕТ (1) ИЗ StudentMarks
--2397616
--Неоднократно вставлять запись в StudentMarks
ВСТАВИТЬ В StudentMarks (deptId,examId,marksObtained)
ЦЕННОСТИ
(101,201,95)
--дублировать записи, чтобы увеличить количество
ВСТАВИТЬ В StudentMarks (deptId,examId,marksObtained)
SELECT deptId,examId,marksAbtained FROM StudentMarks 

Обсуждение уровней изоляции

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

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

Каждый из различных уровней изоляции показан ниже.

Read Uncommitted

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

В этом примере сценарий Query2.sql выполняется сразу после запуска Query1.sql. В Query1 для значения столбца markersObtained установлено значение 90 в первом операторе обновления. Затем значение этого столбца устанавливается равным 80 в третьем операторе обновления. В конце транзакции 1 фиксируется значение 80.

Транзакция 2 начинается после того, как первый оператор обновления выполнен транзакцией1. Транзакция 2 считывает значение 90. Но это не зафиксированные данные. Чтение незафиксированных изменений называется грязным чтением. После завершения выполнения сценария Query1.sql снова выполнялся Query2.sql. На этот раз он выдает результат 80, что является последними данными, зафиксированными транзакцией 1.

Выполнение запросов может быть организовано следующим образом:

Запущена транзакция 1 (Query1.sql).

НАЧАТЬ СДЕЛКУ
ОБНОВЛЕНИЕ
Получено баллов SET = 90
ГДЕ deptId = 101 И examId = 201
 

Выполнение транзакции 1 продолжается, пока транзакция 2 (Query2.sql) запущена и зафиксирована.

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ оценкиПолучено
ОТ StudentMarks
ГДЕ deptId = 101 И examId = 201 И studentId = 1
COMMIT TRANSACTION 

Транзакция 1 (Query1. sql) завершена и зафиксирована.

…
Обновить экзамен
SET examDesc = 'Теоретическая работа и лабораторное задание в структуре данных'
ГДЕ экзаменId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 80
ГДЕ deptId = 101 И examId = 201
COMMIT TRANSACTION 

Read Committed

При Read Committed транзакции устанавливают эксклюзивные блокировки во время модификации данных, не позволяя другим транзакциям читать измененные данные, которые еще не зафиксированы. Уровень изоляции Read Committed предотвращает проблему Dirty Read. Однако данные могут быть изменены другими транзакциями между отдельными операторами в рамках текущей транзакции, что приводит к неповторяемому чтению или фиктивной строке.

Поведение READ COMMITTED зависит от настройки параметра базы данных READ_COMMITTED_SNAPSHOT. Если для параметра READ_COMMITTED_SNAPSHOT установлено значение OFF, общие блокировки предотвращают изменение строк другими транзакциями, пока текущая транзакция выполняет операцию чтения. Общие блокировки также блокируют оператор от чтения строк, измененных другими транзакциями, до тех пор, пока другая транзакция не будет завершена. Если для параметра READ_COMMITTED_SNAPSHOT задано значение ON (по умолчанию для базы данных SQL Azure), управление версиями строк используется для представления каждой инструкции согласованным с транзакциями моментальным снимком данных в том виде, в каком они существовали в начале инструкции. Данные не блокируются.

Пример 1

В этом первом примере для READ_COMMITTED_SNAPSHOT установлено значение OFF. Сценарий Query2.sql выполняется сразу после запуска Query1.sql. В Query1 для значения столбца markersObtained установлено значение 90 в первом операторе обновления. Затем значение этого столбца устанавливается равным 70 в третьем операторе обновления. В конце транзакции 1 фиксируется значение 70.

Пока выполнение Query1.sql не будет завершено, пакет Query2.sql не возвращает никаких выходных данных. Он находится в состоянии ожидания. Как только транзакция 1 завершена, транзакция 2 возвращает результат 70, то есть последние зафиксированные данные.

Выполнение запросов было завершено, как показано ниже.

Запущена транзакция 1 (Query1.sql).

НАЧАТЬ СДЕЛКУ
ОБНОВЛЕНИЕ
Получено баллов SET = 90
ГДЕ deptId = 101 И examId = 201
… 

Выполнение транзакции 1 продолжается, а также запускается и фиксируется транзакция 2 (Query2.sql).

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ оценкиПолучено
ОТ StudentMarks
ГДЕ deptId = 101 И examId = 201 И studentId = 1
СОВЕРШИТЬ ТРАНЗАКЦИЮ 

Транзакция 1 (Query1.sql) завершена.

…
Обновить экзамен
SET examDesc = 'Теоретическая работа и лабораторное задание в структуре данных'
ГДЕ экзаменId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 70 --80
ГДЕ deptId = 101 И examId = 201
COMMIT TRANSACTION 

Пример 2

В этом примере для READ_COMMITTED_SNAPSHOT установлено значение ON. Оператор ALTER необходимо выполнить, чтобы установить для свойства моментального снимка уровень изоляции Read Committed.

ИЗМЕНИТЬ БАЗУ ДАННЫХ <Имя БД>
УСТАНОВИТЬ READ_COMMITTED_SNAPSHOT НА 

Сценарий Query2.sql выполняется сразу после запуска Query1. sql. В Query1 для значения столбца markersObtained установлено значение 90 в первом операторе UPDATE. Затем значение этого столбца устанавливается равным 80 в третьем операторе UPDATE. В конце транзакции 1 фиксируется значение 80

Оператор SELECT в транзакции 2 не ожидает фиксации транзакции 1. Он мгновенно возвращает последние зафиксированные данные, пока Query1.sql все еще работает. Результат был 70 в моем тесте. Если Query2.sql снова выполняется после завершения Query1.sql, он получит последние зафиксированные данные.

Выполнение запросов показано ниже.

Запущена транзакция 1 (Query1.sql).

НАЧАТЬ СДЕЛКУ
ОБНОВЛЕНИЕ
Получено баллов SET = 90
ГДЕ deptId = 101 AND examId = 201 

Выполнение транзакции 1 продолжается, а транзакция 2 (Query2.sql) запускается и фиксируется.

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ оценкиПолучено
ОТ StudentMarks
ГДЕ deptId = 101 И examId = 201 И studentId = 1
COMMIT TRANSACTION 

Транзакция 1 (Query1.sql) завершена.

…
Обновить экзамен
SET examDesc = 'Теоретическая работа и лабораторное задание в структуре данных'
ГДЕ экзаменId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 70 --80
ГДЕ deptId = 101 И examId = 201
COMMIT TRANSACTION 

Пример 3

В Query3 транзакция 1 сначала извлекает значения столбца для examId 201 из таблицы Exam. После этого оператора он выполняет два оператора UPDATE для таблицы StudentMarks и, наконец, тот же оператор SELECT для извлечения значений столбца для examId 201 из таблицы Exam выполняется снова.

Первое выполнение оператора SELECT дает значение examDesc, где

examDesc = ‘Теоретическая работа и лабораторное задание в структуре данных’.

Сценарий Query4.sql выполняется сразу после запуска Query3.sql. Когда статусы обновления StudentMarks выполняются транзакцией 1, транзакция 2 запускается и фиксирует измененное значение examDesc для examId 201, где

examDesc = «Исправлено: теоретическая работа и лабораторное задание в структуре данных».

Второе выполнение оператора SELECT в транзакции 1 дает измененное значение examDesc. Здесь многократное выполнение одного и того же оператора select в одной и той же транзакции дает разные выходные данные. Эта проблема известна как неповторяемое чтение.

Выполнение запросов выглядит следующим образом. Сначала запускается транзакция 1 (Query3.sql).

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ экзаменId = 201
… 

Выполнение транзакции 1 продолжается. Транзакция 2 (Query4.sql) запущена и зафиксирована.

НАЧАТЬ СДЕЛКУ
Обновить экзамен
SET examDesc = 'Исправлено: теоретическая работа и лабораторное задание в структуре данных'
ГДЕ экзаменId = 201
COMMIT TRANSACTION 

Транзакция 1 (Query3.sql) завершена.

ОБНОВЛЕНИЕ
Получено баллов SET = 61
ГДЕ deptId = 101 И examId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 62
ГДЕ deptId = 101 И examId = 201
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ экзаменId = 201
COMMIT TRANSACTION 

Повторяемое чтение

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

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

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

Пример 4

В Query3 транзакция 1 извлекает значения столбца для examId 201 из таблицы Exam. После этого оператора пакет выполняет два оператора UPDATE для таблицы StudentMarks. Наконец, снова выполняется тот же оператор SELECT.

Сценарий Query4.sql выполняется сразу после запуска Query3. sql. Пока выполняется транзакция 1, транзакция 2 пытается изменить значение examDesc для examId 201. Поскольку общая блокировка активна до тех пор, пока транзакция 1 не будет зафиксирована, транзакция 2 должна дождаться фиксации.

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

Выполнение запросов выглядит следующим образом.

Запущена транзакция 1 (Query3.sql).

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
WHERE examId = 201 

Выполнение транзакции 1 продолжается, а транзакция 2 (Query4.sql) запускается и пытается изменить значение examDesc для examId = 201. Но транзакция 1 использует эту запись для чтения, а данные заблокированы для обновления. пока транзакция 1 не будет зафиксирована. Итак, транзакция 2 находится в состоянии ожидания. Он может зафиксировать изменение только тогда, когда транзакция 1 фиксируется или откатывается.

НАЧАТЬ СДЕЛКУ
Обновить экзамен
SET examDesc = 'Исправлено: теоретическая работа и лабораторное задание в структуре данных'
ГДЕ экзаменId = 201
COMMIT TRANSACTION 

Транзакция 1 (Query3. sql) завершена.

ОБНОВЛЕНИЕ
Получено баллов SET = 61
ГДЕ deptId = 101 И examId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 62
ГДЕ deptId = 101 И examId = 201
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ экзаменId = 201
СОВЕРШИТЬ ТРАНЗАКЦИЮ 

Пример 5

В Query5 транзакция 1 извлекает значения столбца Exam для examName = «Структура данных». После этого оператора он выполняет два оператора UPDATE для таблицы StudentMarks и, наконец, тот же оператор SELECT для получения значений столбца Exam для examName = «Структура данных» выполняется снова.

После выполнения первого оператора SELECT запускается транзакция 2 (Query6.sql). Эта транзакция вставляет новую запись в таблицу Exam для examName = ‘Data Structure’ и фиксирует изменение. Здесь общая блокировка активна для записей, выбранных в операторе SELECT в Query5.sql, до тех пор, пока транзакция 1 не будет зафиксирована, но вставка новой записи с тем же условием поиска не предотвращается. В результате транзакция 2 добавляет новую запись в таблицу Exam.

Теперь при втором вхождении оператора SELECT в транзакции 1 будет получена дополнительная запись. Тот же оператор SELECT в той же транзакции дает несколько новых строк для второго выполнения по сравнению с первым. Эти дополнительные строки известны как фантомные строки.

Выполнение запросов может быть организовано по времени их начала и времени окончания следующим образом:

Запущена транзакция 1 (Query5.sql).

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ examName = 'Структура данных' 

Выполнение транзакции 1 продолжается, а транзакция 2 (Query6.sql) запущена и зафиксирована.

НАЧАТЬ СДЕЛКУ
ВСТАВИТЬ В ЭКЗАМЕН
(examId,examName,examDesc)
ЦЕННОСТИ
(203, «Структура данных», «Дубликат: исправлено: теоретическая работа и лабораторное задание в структуре данных»)
COMMIT TRANSACTION 

Транзакция 1 (Query5.sql) завершена.

ОБНОВЛЕНИЕ
Получено баллов SET = 61
ГДЕ deptId = 101 И examId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 62
ГДЕ deptId = 101 И examId = 201
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ examName = 'Структура данных'
СОВЕРШИТЬ ТРАНЗАКЦИЮ 

Serializable

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

Пример 6

В Query5 транзакция 1 извлекает значения столбца Exam для examName = ‘Data Structure’. После этого оператора он выполняет два оператора UPDATE для таблицы StudentMarks и, наконец, тот же оператор SELECT для получения значений столбца Exam для examName = «Структура данных» выполняется снова.

После выполнения первого оператора SELECT запускается транзакция 2 (Query8.sql). Эта транзакция пытается вставить новую запись в таблицу Exam для examName = «Структура данных» и зафиксировать изменение. Но транзакция 2 не может вставить новую запись в таблицу Exam с тем же значением ключа, которое используется в критериях поиска инструкции SELECT, используемой в транзакции 1, которая еще не зафиксирована. Таким образом, транзакция 2 должна ждать, чтобы зафиксировать изменения, пока транзакция не будет завершена.

При втором появлении инструкции SELECT в транзакции 1 теперь будет извлечено то же количество записей, что и при первом появлении инструкции SELECT. Таким образом, проблема с фантомными рядами решена.

Выполнение запросов может быть организовано по времени их начала и времени окончания следующим образом:

Запущена транзакция 1 (Query5.sql).

НАЧАТЬ СДЕЛКУ
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ examName = 'Структура данных' 

Выполнение транзакции 1 продолжается, и транзакция 2 (Query8.sql) запускается. Транзакция 2 должна ждать, чтобы зафиксировать свои изменения, пока транзакция 1 не завершится.

НАЧАТЬ СДЕЛКУ
ВСТАВИТЬ В ЭКЗАМЕН
(examId,examName,examDesc)
ЦЕННОСТИ
(204, «Структура данных», «Дубликат: исправлено: теоретическая работа и лабораторное задание в структуре данных»)
COMMIT TRANSACTION 

Транзакция 1 (Query5. sql) завершена.

ОБНОВЛЕНИЕ
Получено баллов SET = 61
ГДЕ deptId = 101 И examId = 201
ОБНОВЛЕНИЕ
Получено баллов SET = 62
ГДЕ deptId = 101 И examId = 201
ВЫБЕРИТЕ экзаменId,examName,examDesc
С экзамена
ГДЕ examName = 'Структура данных'
СОВЕРШИТЬ ТРАНЗАКЦИЮ 

Изоляция моментального снимка

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

Для параметра базы данных ALLOW_SNAPSHOT_ISOLATION необходимо установить значение ON перед запуском транзакции с уровнем изоляции SNAPSHOT.

ИЗМЕНИТЬ БАЗУ ДАННЫХ <Имя БД>
  ВКЛЮЧИТЕ ALLOW_SNAPSHOT_ISOLATION
 

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

Если для параметра базы данных READ_COMMITTED_SNAPSHOT установлено значение ON, ядро ​​базы данных по умолчанию использует управление версиями строк и изоляцию моментальных снимков вместо использования блокировок для защиты данных.

ИЗМЕНИТЬ БАЗУ ДАННЫХ <Имя БД>
  SET READ_COMMITTED_SNAPSHOT ON 

Значение различных уровней изоляции

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

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

READ COMMITTED — это уровень изоляции по умолчанию для SQL Server. Он предотвращает грязное чтение, указывая, что операторы не могут читать значения данных, которые были изменены, но еще не зафиксированы другими транзакциями. Если для параметра READ_COMMITTED_SNAPSHOT установлено значение ON, транзакциям чтения не нужно ждать, и они могут получить доступ к последним зафиксированным записям. Другие транзакции могут изменять, вставлять или удалять данные между выполнениями отдельных операторов SELECT в рамках текущей транзакции, что приводит к неповторяющимся операциям чтения или фиктивным строкам.

ПОВТОРЯЕМОЕ ЧТЕНИЕ — более строгий уровень изоляции, чем READ COMMITTED. Он охватывает READ COMMITTED и дополнительно указывает, что никакие другие транзакции не могут изменять или удалять данные, которые были прочитаны текущей транзакцией, до тех пор, пока текущая транзакция не будет зафиксирована. Параллелизм ниже, чем для READ COMMITTED, потому что общие блокировки для чтения данных удерживаются на время транзакции, а не снимаются в конце каждого оператора. Но другие транзакции могут вставлять данные между выполнениями отдельных операторов SELECT в текущей транзакции, что приводит к появлению фантомных строк.

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

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