How to Maintain Integrity using Transactions

Many business activities depend on an action being accompanied by a corresponding action.
For example, if you are a customer and pay for goods but don't receive them, something is wrong.
Similarly, if you take goods and don't pay for them, again, something is wrong. The expected typical

scenario is a transaction where you pay for goods and you receive the goods. More than one action
is required to make up a transaction.

Another common example of more than one operation making up a transaction is when you transfer
money from one bank account to another. Suppose you're transferring a regular payment to a company.

Your bank takes the money out of your account and puts it into the account of the company or person
that you're paying. You would be annoyed if the money was taken out of your account and didn't reach
the account where it was supposed to go. If the money was never transferred to the company's account
you, as a customer of a bank, would not be happy whether the money was put in the wrong account or
just disappeared. The different parts of that transaction must be kept together. There are two possible
scenarios:
No money is taken from your account and nothing is transferred to the

    other account (possibly because of insufficient funds in your account or

    a network problem is preventing the transfer).

The right amount of money is taken from your account and is placed in

    the other account.

In a SQL Server transaction, either all the component parts of a transaction are carried out or none of
them are. This concept is called atomicity.

Transactions

In SQL Server 2005, there are several levels of transaction. In the preceding paragraphs, I mention
business level transactions. These are the subject of this chapter. Behind the scenes in SQL Server 2005,
other transactions take place routinely. For example, if you add data to a table that has an index, both
the table and the index need to be updated or neither is updated. If that coordination of operations

doesn't happen, then the index and table are inconsistent with each other, which is unacceptable.

ACID

ACID describes four essential characteristics of a transaction:

Atomicity: Atomicity means that the transaction cannot be divided and still make sense. With the
    transfer between bank accounts, either both parts of the transaction take place successfully or neither
     happens.

Consistency: Consistency means that the database is in a consistent state before the transaction
    takes place and remains in a consistent state after the transaction. For example, if you add a row to
    a table, then the index must also be updated.

Isolation: This is the idea that a transaction should be able to proceed as if it were completely
    isolated from any other transaction. For multi-userdatabases, it is increasingly important that the
    product supports this.

Durability: This is the concept that a transaction survives even if there is a hardware failure. It
    should be possible to re-create the data up to thelast completed transaction that completed a split
    second before the hardware failure.

The transaction log
Each SQL Server 2005 database has an associated transaction log. The transaction log contains
information about recent changes that have been made in the database that have not yet been
committed to disk. When SQL Server is restarted, any transactions not yet committed to disk are
committed to disk during startup. This facility supports durability of the ACID acronym that I discuss
in the preceding section.

Coding Transactions

When a transaction involves, for example, removing money from one account and transferring it to
another account, then both accounts are updated. First,look at how SQL Server carries out a simple update.

A simple update

Imagine that you have a database called Departments that has columns, which include DepartmentName
and DepartmentManager. When the manager of the IT department is replaced, you need to update the
information inthe DepartmentManager column. To do this, you use code like the following:

UPDATE Departments

SET DepartmentManager = 'John Smith'

WHERE Department = 'IT'

The WHERE clause works much as it does in a SELECT statement. It selectsthe rows where the
Department column contains the IT value and the SETclause causes the value in the
DepartmentManager column to update to the John Smith value.

A simple transaction

To demonstrate a simple transaction, I create a database called TransactionDemo. In that database,
I create a couple of tables called PersonalAccount and CompanyAccount:

CREATE DATABASE TransactionDemo

USE TransactionDemo

CREATE TABLE PersonalAccount (AccountID INT PRIMARY KEY,

Name VARCHAR(30), BALANCE MONEY)

I create two accounts, one for John Smith and one for Jane Doe, with each individual having a balance
of $100.00:

INSERT INTO PersonalAccount

VALUES (1, 'John Smith', 100.00)

INSERT INTO PersonalAccount

VALUES (2, 'Jane Doe', 100.00)

Similarly, I create a CompanyAccount table:

CREATE TABLE CompanyAccount (AccountID INT PRIMARY KEY,

Name VARCHAR(30), BALANCE MONEY)

Then I create two rows in it, with each company having a balance of $10,000:

INSERT INTO CompanyAccount

VALUES (1, 'Acme Company', 10000.00)

INSERT INTO CompanyAccount

VALUES (2, 'XMML.com', 10000.00)

To confirm that the two tables have been created with appropriate values in

each column, use the following code:

SELECT * FROM PersonalAccount

SELECT * FROM CompanyAccount

The following code transfers $50.00 from John Smith's personal account to

XMML.com's company account by using a transaction:

BEGIN TRANSACTION

UPDATE PersonalAccount

SET BALANCE = 50.00

WHERE Name = 'John Smith'

UPDATE CompanyAccount

SET BALANCE = 10050.00

WHERE Name = 'XMML.com'

COMMIT TRANSACTION

GO

Confirm that the balance in John Smith's personal account and XMML.com's company account have
changed appropriately by using the following code:

SELECT * FROM PersonalAccount

SELECT * FROM CompanyAccount

Often, a transaction has some error checking included in the code. To include error checking when
making a transfer from Jane Doe to Acme Company, you can use @@ERROR:

BEGIN TRANSACTION

UPDATE PersonalAccount

SET BALANCE = 0.00

WHERE Name = 'Jane Doe'

IF @@ERROR <> 0

PRINT N'Could not set balance in PersonalAccount.'

UPDATE CompanyAccount

SET BALANCE = 10100.00

WHERE Name = 'Acme Company'

IF @@ERROR <> 0

PRINT N'Could not set balance in CompanyAccount.'

COMMIT TRANSACTION

GO

To confirm that you have changed the row for Jane Doe in the Personal Account table and the row for Acme
Company in the CompanyAccount table, use the following code:

SELECT * FROM PersonalAccount

SELECT * FROM CompanyAccount

The BEGIN TRANSACTION statement marks the beginning of the T-SQL code to be treated as a transaction.
If the T-SQL code in the transaction executes successfully, the COMMIT TRANSACTION statement is
reached and the transaction is committed.

If an error occurs during processing, the ROLLBACK TRANSACTION statement executes. After the COMMIT
TRANSACTION statement executes, you cannot use a ROLLBACK TRANSACTION statement to roll back the
transaction.

Implicit transactions

The preceding examples showed explicit transactions. T-SQL also supports implicit transactions.

To start implicit transactions, you use the SET IMPLICIT_TRANSACTIONS ON statement. Each statement after that statement until a transaction is committed is considered to be part of that transaction. After you do that,you must explicitly commit the statements that make up each transaction by using the COMMIT

TRANSACTION statement. The statements after the COMMIT TRANSACTION statement are considered to be the first statement of the next transaction. Again, that transaction must be explicitly committed.

To turn implicit transactions off, you use the SET IMPLICIT_TRANSACTIONS OFF statement. The default behavior, if you do not SET IMPLICIT TRANSACTIONS ON, is that each individual T-SQL statement is treated as a transaction rather than as a group of T-SQL statements.

You can't combine Data Definition Language (DDL) statements in a single transaction.