SQL-Ex blog

В этом руководстве я рассмотрю исходную ситуацию, при которой вы хотите использовать явные транзакции. Мы начнем с понятия явной транзакции и чем она отличается от режима автоматической фиксации (auto-commit), принимаемого по умолчанию. К концу статьи я хочу, чтобы вы приняли обоснованный выбор того, что из них использовать.

Режимы транзакций SQL

Brady Upton написал серию советов с описанием транзакции как единицы работы, выполняемой над базой данных. SQL Server имеет три основных режима транзакций. Давайте вкратце их рассмотрим. Я не собираюсь обсуждать неявные транзакции. Я также не буду сравнивать производительность этих двух крайних случаев использования.

Auto-commit

По умолчанию SQL Server использует автоматическую фиксацию (Auto-commit). При auto-commit конечному пользователю не требуется выполнять какие-либо команды для фиксации транзакции. Например, в нижеприведенном операторе SQL мы создаем две простых таблицы и вставляем по строке в каждую.

CREATE TABLE dbo.Checking

(

    ID INT NOT NULL,

    Amount DECIMAL(19, 2) NOT NULL,

    ShortDescription NVARCHAR(100) NOT NULL,

    TransactionDate DATE NOT NULL

);

GO

CREATE TABLE dbo.Savings

(

    ID INT NOT NULL,

    Amount DECIMAL(19, 2) NOT NULL,

    ShortDescription NVARCHAR(100) NOT NULL,

    TransactionDate DATE NOT NULL

);

GO

INSERT INTO dbo.Checking

(

    ID,

    Amount,

    ShortDescription,

    TransactionDate

)

VALUES

(1, 25.00, 'Starting my checking account', GETDATE());

INSERT INTO dbo.Savings

(

    ID,

    Amount,

    ShortDescription,

    TransactionDate

)

VALUES

(1, 100.00, 'Starting my savings account, Thanks dad!', GETDATE());

SELECT SUM(Amount) AS [Total], -- Оператор SELECT

       'Savings' AS [AccountType]

FROM dbo.Savings

UNION ALL

SELECT SUM(Amount) AS [Total],

       'Checking' AS [AccountType]

FROM dbo.Checking;

GO

Автоматическая фиксация настолько проста, насколько это возможно. Она требует минимального количества кода. Однако у нее есть недостатки. Главное, на чем мы фокусируемся, это то, что если что-то пойдет не так с оператором первой вставки, и вы не хотите, чтобы выполнялась вторая вставка, вас постигнет неудача. Давайте рассмотрим это в действии. В примере ниже я перевожу 100 долларов со своего сберегательного счета на чек:

INSERT INTO dbo.Savings

(

    ID,

    Amount,

    ShortDescription,

    TransactionDate

)

VALUES

(2, -100.00, 'Снимаем деньги со счета.', GETDATE());

INSERT INTO dbo.Checking

(

    ID,

    Amount,

    ShortDescription,

    TransactionDate

)

VALUES

(2, 100.00, 'Снимаю деньги с моего счета за копию новой игры NES, извини, папа! Я верну их после того, как устроюсь на работу.', GETDATE());

GO

Обратите внимание, что второй оператор INSERT завершился неудачно. Я надеюсь, что мой счет не уменьшился на 100 долларов. Однако это не так.



Теперь давайте посмотрим, как обойти такое поведение.

Явные транзакции в T-SQL

При явных транзакциях вы говорите SQL Server начать транзакцию с помощью команды BEGIN TRANSACTION. После завершения вашего оператора вы можете выполнить ROLLBACK (откат) или COMMIT (фиксацию). В идеале вы должны бы обернуть транзакцию в блок TRY...CATCH. Вы можете откатить транзакцию и вызвать исключение при возникновении ошибки.

Если вы не хотите воспользоваться TRY...CATCH, то можете включить SET XACT_ABORT ON. Эта команда удобна, поскольку она откатит транзакцию, если возникнет исключение. По умолчанию XACT_ABORT выключена.

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

SET XACT_ABORT ON;

BEGIN TRANSACTION; -- Оператор начала транзакций

INSERT INTO dbo.Savings

(

    ID,

    Amount,

    ShortDescription,

    TransactionDate

)

VALUES

(2, -100.00, 'Taking money out of my savings account.', GETDATE());

INSERT INTO dbo.Checking

(

    ID,

    Amount,

    ShortDescription,

    TransactionDate

)

VALUES

(2, 100.00, 'Taking money out of my account for a copy of a new NES games, sorry dad! I will replace it after I get a job.', GETDATE());

COMMIT TRANSACTION;

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

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

Изменение рекомендаций

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

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

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

Заключение

Мы рассмотрели два разных режима транзакций. Во-первых, это автоматическая фиксация, которая принимается в SQL Server по умолчанию. Затем был рассмотрены явные транзакции. Явные транзакции могут быть использованы, в первую очередь, для гарантии успешного выполнения нескольких операторов как одной логической единицы, или же невыполнения ни одного из них.