Transactions

A transaction is a set of commands executed as a single unit. If one of the commands fails, the whole transaction is canceled and no changes are applied to the database. Transactions are irreplaceable when we have several commands that are executed one after another and are logically tied to each other. They allow the database to remain consistent even if an error occurred during the execution of a query.

Implicit transactions

Every command we run against a database can be considered a transaction. This is known as implicit transaction. However, because such transactions consist of only a single statement, such as a single INSERT command, they do not allow us to yield the power of using transactions. It is possible to start and end transactions explicitly when we have multiple commands to execute as a single unit.

Why transactions?

Let’s consider the following scenario. We are developing a banking application that allows us to transfer funds from one account to another. A transfer essentially consists of two operations:

• Taking money from one account.
• Debiting the funds to the second account.

These two operations can be completed using two UPDATE statements:

UPDATE Accounts
SET Balance = Balance - @AmountTransferred
WHERE AccountId = @From;

UPDATE Accounts
SET Balance = Balance + @AmountTransferred
WHERE AccountId = @To;

In the snippet above, @From is the account ID from which we’ll be deducting funds, while @To is the account ID to which we’ll be transferring funds. Imagine the first UPDATE ran fine, but there was an unexpected error while executing the second UPDATE. Funds will be deducted from the first account but are never credited to the second one. Money will be lost.

To avoid such situations, we must wrap interrelated queries within a transaction.

Syntax

In its simplest form, a transaction is created with the help of two commands:

BEGIN TRANSACTION
    [Commands to execute]
COMMIT TRANSACTION;

If we want to put the previous example within a transaction, we will do the following:

BEGIN TRANSACTION
    UPDATE Accounts
    SET Balance = Balance - @AmountTransferred
    WHERE AccountId = @From

    UPDATE Accounts
    SET Balance = Balance + @AmountTransferred
    WHERE AccountId = @To
COMMIT TRANSACTION;

Now, let’s make one of the statements fail so that we can see that no changes are applied.

Example

First, we’ll show an example without a transaction. We deduct the amount from the first account but make the second UPDATE statement fail: