SQL 2005+

Using Transactions in SQL Server

by Richard Carr, published at http://www.blackwasp.co.uk/SQLTransactions.aspx

The twentieth part of the SQL Server Programming Fundamentals tutorial describes the use of transactions. Transactions allow you to execute several related T-SQL statements and ensure that the database remains consistent if one of the commands fails.

What is a Transaction?

There are many situations where you need to make several changes to the data in one or more database tables, particularly in a normalised database. In some cases the data could become inconsistent between executing the individual commands. If the first statement was to execute correctly but subsequent commands failed, the data could remain in an incorrect state.

A good example is the balance transfer function of a banking system. A transfer may require two UPDATE statements and an INSERT. Firstly, you may increase the balance of the destination account. Secondly you could decrease the balance of the source account. Finally, you would insert a new row in the Transfers table to provide an audit trail of the transfer. If the first statement executed correctly but the second and third failed, the balance of one account would be increased but the source account would remain unaltered and there would be no audit trail to determine the problem.

In such a situation, we need a way to ensure that all of the related commands execute correctly before the changes are committed to the database. We also need to be able to reverse all of the commands executed so far if one of the changes fails, or if we decide not to commit the data for any other reason. This is achieved using transactions.

Transactions are started before the first data change and committed once all related updates are successfully completed. Until the transaction has been committed, the changes are not permanently stored in the database. At any point during the transaction, you can issue a rollback. This ends the transaction, leaving the data unchanged by the commands executed within the transaction. Changes made on other database connections are not affected by the rollback.

ACID Transactions

Relational database transactions must exhibit four properties, known by the mnemonic, "ACID". The four properties are:

• Atomicity. The changes that are made during a transaction must be atomic. This means that either all of the transaction's commands are committed to the database or none are. It must not be possible for only part of a transaction to be committed.
• Consistency. Each statement executed during a transaction must result in valid data. Any command that breaks the integrity of the database results in the entire transaction being rolled back.
• Isolation. The intermediate changes that are made during a transaction must be isolated from other users and other connections to the database until the transaction is committed.
• Durability. Once the transaction has been committed, the changes must be guaranteed. This includes surviving a total system failure.

Using Transactions in Transact-SQL

We can demonstrate the use of transactions by executing Transact-SQL (T-SQL) statements against the JoBS database. This is the sample database that is being built by the examples in this tutorial. If you do not have a copy of the database, or wish to refresh your copy, download the script using the link at the top of this article. The script will create a new database and populate it with sample data.

Transactions are started using the BEGIN TRANSACTION command, which can be shortened to BEGIN TRAN. The commands that are controlled by the transaction may then be executed and the transaction committed using the COMMIT TRAN(SACTION) statement. For example, to create a new job and link it to an existing job as a follow-up, we could execute the following statements.

BEGIN TRAN

INSERT INTO Jobs (JobId, StandardJobId, ContractNumber, VisitDate)
VALUES ('3612DFDF-99CF-46FE-96DF-1B7CBF62C1A8', 3, 1, '2008-03-11')

UPDATE Jobs
SET FollowUpJobId = '3612DFDF-99CF-46FE-96DF-1B7CBF62C1A8'
WHERE JobId = '76B866DA-2B62-4439-A23D-8C13D7F9F8E0'

COMMIT TRAN

Rolling Back Transactions

When you execute the above commands, the new job will be created and the previous job updated. However, you will not immediately see any effect of the transaction. We can demonstrate further by executing the commands to create a new job and assign it as a follow-up but then rolling back the transaction using the ROLLBACK TRAN(SACTION) statement.

BEGIN TRAN

INSERT INTO Jobs
    (JobId, StandardJobId, ContractNumber, VisitDate)
VALUES
    ('073E2600-2426-4B86-B744-1DA722C73D7C', 3, 1, '2008-03-18')

UPDATE Jobs
SET FollowUpJobId = '073E2600-2426-4B86-B744-1DA722C73D7C'
WHERE JobId = '3612DFDF-99CF-46FE-96DF-1B7CBF62C1A8'

ROLLBACK TRAN

When you execute the above commands you will see the messages that indicate that the changes have been made. However, as the transaction is rolled back, the data is unaffected. If you query the Jobs table you will see that both the INSERT and UPDATE were reversed.

SELECT * FROM Jobs