Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Топовые новости
Новое

Инструкция SET TRANSACTION

Инструкция SET TRANSACTION управляет многими характеристиками процесса модификации данных, и в первую очередь параметрами чтения/записи и уровнем изоляции транзакции.


Синтаксис SQL 2003

SET [LOCAL]-TRANSACTION [READ ONLY | READ WRITE]
[ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED |
REPEATABLE READ | SERIALIZABLE} [DIAGNOSTIC SIZE int]


Ключевые слова

LOCAL

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

READ ONLY

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

READ WRITE

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

ISOLATION LEVEL

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

READ COMMITED

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

READ UNCOMMITTED

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

REPEATABLE READ

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

SERIALIZABLE

Все сеансы могут видеть записи, зафиксированные до того, как начнется их транзакция. Другие открытые сеансы могут видеть, но не могут вставлять или обновлять записи в других пользовательских сеансах до того, пока транзакция не будет завершена. Этот уровень изоляции является наиболее ограничивающим, А* он установлен по умолчанию в стандарте SQL 2003.

DIAGNOSTIC SIZE int

Указывается количество сообщений об ошибках (int), которые будут регистрироваться для транзакции. Инструкция GET DIAGNOSTICS позволяет получить эти сообщения об ошибках.


Общие правила

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

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

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

Если вы не знакомы с уровнями изоляции, обязательно прочитайте подраздел «Синтаксис SQL 2003» в разделе «Инструкция SET TRANSACTION*, поскольку объяснения уровней изоляции приводятся только в этом подразделе.


Советы и хитрости программирования

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

Грязное чтение

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

Не воспроизводимое чтение

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

Фантомные записи

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


DB2

Не поддерживается. Платформа DB2 поддерживает все уровни изоляции, но не через использование инструкции SET TRANSACTION. Управление уровнями изоляции осуществляется при помощи процесса подготовки программы и параметров сервера, настраиваемых администратором базы.

Хотя платформа DB2 поддерживает уровни изоляции стандарта ANSI, они здесь называются по-другому. Платформа DB2 также позволяет пользователям определять уровень изоляции для отдельных инструкций, включая SELECT, DELETE, INSERT и UPDATE. В DB2 используются следующие названия уровней изоляции.

cs

Уровень изоляции Cursor Stability (стабильность курсора) (serializable).

NC

Уровень изоляции No Commit (без фиксации). (Не поддерживается DB2.)

RR

Уровень изоляции Repeatable Read (воспроизводимое чтение).

RS

Уровень изоляции Read Stability (стабильность чтения) (READ COMMITED).

UR

Уровень изоляции Uncommited Read (нефиксированное чтение) (READ UNCOMMITTED).

Платформа DB2 также поддерживает две инструкции — CHANGE ISOLATION LEVEL и SET CURRENT ISOLATION, которые изменяют способ изоляции данных от других процессов при доступе к базе данных.


MySQL

Платформа MySQL позволяет устанавливать уровень изоляции для следующей отдельной транзакции, для всего сеанса или глобально, для всего сервера. Делается это при помощи следующего синтаксиса.

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE]

По умолчанию MySQL устанавливает уровень изоляции для той транзакции, которая следует непосредственно за инструкцией.

GLOBAL

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

SESSION

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

TRANSACTION ISOLATION LEVEL

Устанавливается указанный уровень изоляции транзакций, как это описывалось ранее в разделе «Инструкция SET TRANSACTION)).

Если данная инструкция опущена, по умолчанию используется уровень REPEATABLE READ.

Для установки уровня изоляции GLOBAL требуется привилегия SUPER. Вы можете также устанавливать принимаемый по умолчанию уровень изоляции при помощи переключателя в командной строке исполняемого файла MYSQL (-transaction-isolation). Ниже приводится пример, в котором для всех последующих процессов, как пользовательских, так и системных, устанавливается уровень изоляции SERIALIZABLE.

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;


Oracle

Платформа Oracle позволяет перевести транзакцию в режим только-для-чтения, режим для-чтения-записи, установить уровень изоляции транзакции или указать сегмент отката для транзакций.

SET TRANSACTION {[READ ONLY | READ WRITE] |
[ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}] |
[USE ROLLBACK SEGMENT имя_сегмента] | NAME ‘имя_транзакции’};

Где:

READ ONLY

Следующая транзакция становится транзакцией для чтения с уровнем изоляции SERIALIZABLE. Это предложение недоступно для пользователя SYS. В сеансах READ ONLY можно использовать только следующие инструкции: SELECT, ALTER SESSION, ALTER SYSTEM, LOCK TABLE и SET ROLE.

READ WRITE

Тип транзакций по умолчанию в Oracle. Транзакции могут читать и записывать данные.

READ COMMITTED

Уровень изоляции по умолчанию в Oracle. Соответствует стандарту ANSI.

SERIALIZABLE

Уровень изоляции транзакций, соответствующий стандарту ANSI. Необходимое условие — инициализационный параметр COMPATIBLE должен быть установлен в значение 7.3.0 или выше.

USE ROLLBAСК SEGMENT имя_сегмента

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

NAME

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

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

Инструкция SET TRANSACTION должна быть первой в любом пакете SQL, хотя Oracle, по сути, рассматривает ее как аналог инструкции START TRANSACTION. Так что вы можете заменять одну инструкцию на другую.

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

SET TRANSACTION READ ONLY NAME ‘Chicago’;

SELECT
prod_id,
ordqty
FROM sales
WHERE stor_id=5;

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

SET TRANSACTION USE ROLLBACK SEGMENT huge_tran_01;


PostgreSQL

Инструкция SET TRANSACTION в PostgreSQL влияет только на новую, запускаемую транзакцию. Следовательно, эту инструкцию вам, возможно, понадобится выполнять перед каждой новой транзакцией. Синтаксис следующий.

SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE};

Где:

READ COMMITTED

Уровень изоляции транзакции устанавливается в READ COMMITTED (соответствует стандарту ANSI). Этот уровень принимается по умолчанию.

SERIALIZABLE

Уровень изоляции транзакции устанавливается в SERIALIZABLE (соответствует стандарту ANSI).

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

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}
SET default_transaction_isolatiori={‘read committed’ | ‘serializable’}

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

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Кроме того, вы можете установить уровень SERIALIZABLE для всех транзакций всего сеанса.

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;


SQL Server

Инструкция SET TRANSACTION в SQL Server устанавливает уровень изоляции для всего сеанса. Все запросы, которые идут после инструкции SET TRANSACTION, будут использовать уровень изоляции, указанный в этой инструкции, до тех пор, пока он не будет изменен.

SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}

Где:

READ COMMITTED

Уровень изоляции транзакции устанавливается в READ COMMITTED (соответствует стандарту ANSI). Этот уровень принимается по умолчанию.

READ UNCOMMITTED

Уровень изоляции транзакции устанавливается в READ UNCOMMITTED (соответствует стандарту ANSI). Эффект аналогичен действию подсказки оптимизатору NOLOCK.

REPEATABLE READ

Уровень изоляции транзакции устанавливается в REPEATABLE READ (соответствует стандарту ANSI).

SERIALIZABLE

Уровень изоляции транзакции устанавливается в SERIALIZABLE (соответствует стандарту ANSI). Сходные результаты можно получить с помощью подсказки оптимизатору HOLDLOCK.

Например, следующая команда понижает уровень изоляции транзакций для всех инструкций SELECT сеанса с READ COMMITED до REPEATABLE READ.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO

Источник