Introduction to Transactions in SQL Server

Relational databases are governed by the A.C.I.D principle; these are a set of properties of database transactions to guarantee the integrity of the data. The name ACID is an acronym for the four components that make it up:

• Atomic. All grouped statements perform as a single unit of work. Either all of them succeed or none of them succeed.
• Consistency. The database will be in a valid state once a transaction has completed. This means rules such as constraints cannot be violated.
• Isolation. Ensure transactions can operate independently of each other even in a concurrent system.
• Durability. Once a transaction has been committed, it will remain committed even if there is a severe system failure.

SQL Server Transactions provide us a mechanism for dealing with the atomicity part of the ACID principle.

The classic example involves the transfer of money between Bank accounts. If I want to move money from my Current Account to my Savings Account then two operations need to happen:

• The money is deducted from my Current account;
• The money is added to my Savings account.

Let’s show this example in SQL Server. First we need to setup some test data…

-- Create Testing Table

IF OBJECT_ID('dbo.TransactionTest', 'U') IS NOT NULL
   DROP TABLE dbo.TransactionTest;

CREATE TABLE dbo.TransactionTest (
   
    TransactionTestId      int                NOT NULL,
    BankAccountHolderId    int                NOT NULL,
    AccountType            int                NOT NULL,
    AmountInAccount        numeric(10,2)      NOT NULL

);

INSERT INTO dbo.TransactionTest (

	TransactionTestId, BankAccountHolderID, AccountType, 
        AmountInAccount

)

VALUES (

	1, 1, 'Current', 100
	
), (	

	2, 1, 'Savings', 20
	
);

-- Deduct £10 from current account
UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount - 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Current';

-- Add £10 to savings account
UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount + 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Savings';

So what happened here with transactions? Well not a lot… unlike other relational database management systems such as Oracle the default transaction mode is Autocommit. That means that every T-SQL statement is committed or rollback (if it fails) implicitly when it is submitted. Since we have two statements, they were both committed separately after each one was executed. This default functionality won’t help us group both statements together in a single unit of work!

If we introduce an error between the first and second update statements we can see how problematic this could be:

-- Reset the table
UPDATE	dbo.TransactionTest
SET		AmountInAccount = 100 
WHERE	TransactionTestId = 1;

UPDATE	dbo.TransactionTest
SET		AmountInAccount = 20
WHERE	TransactionTestId = 2;

-- Run update 1, then throw up an error
UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount - 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Current';

-- whooooooooooooops!!!
SELECT 1 / 0;

-- We don't make it this far!
UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount + 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Savings';

As we failed during what we wanted to be a “single unit” of work without any transaction control in place we have lost the £10 from the Current account without adding it to our Savings account!

Before:

After – Current Account has lost £10 and Savings Account has stayed the same.

Introducing Explicit Transactions

SQL Server allows us to use the BEGIN TRANSACTION statement to tell SQL that we want to create an explicit transaction. When we open an explicit transaction we need to close it again once we have finished. To do this we have to use either the COMMIT TRANSACTION or ROLLBACK TRANSACTION statements. The commit statements tells the database to “Save” the changes created from the statements we ran within the transaction. Rollback tells the database to “Discard” the changes and revert back to how the data was before we started.

Let’s use the same example as before but introduce the BEGIN TRANSACTION and COMMIT TRANSACTION statements to define our explicit transaction framework.

-- Reset the table
UPDATE	dbo.TransactionTest
SET		AmountInAccount = 100 
WHERE	TransactionTestId = 1;

UPDATE	dbo.TransactionTest
SET		AmountInAccount = 20
WHERE	TransactionTestId = 2;

-- Start the transaction
BEGIN TRANSACTION

UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount - 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Current';

UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount + 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Savings';

-- Lets save the changes and close the transaction
COMMIT TRANSACTION;

Before:

After Update Statements. We can see the updated state in our current session.

After Commit Statement. The data changes have been “Saved” to the database.

Let’s see what happens when we use the ROLLBACK TRANSACTION command after the update statements instead of the COMMIT TRANSACTION command.

-- Reset the table

UPDATE	dbo.TransactionTest
SET		AmountInAccount = 100 
WHERE	TransactionTestId = 1;

UPDATE	dbo.TransactionTest
SET		AmountInAccount = 20
WHERE	TransactionTestId = 2;

-- Start a transaction!
BEGIN TRANSACTION

UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount - 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Current';

UPDATE	dbo.TransactionTest
SET		AmountInAccount = AmountInAccount + 10
WHERE	BankAccountHolderID = 1
		AND AccountType = 'Savings';

-- Lets revert back the changes and close the transaction
ROLLBACK TRANSACTION;

Before:

After Update Statements. Again we can see the updated changes but they have not been confirmed to the database.

After rollback statement. The data has reverted back to how it was before we executed the UPDATE statements.

As you can see from the above examples the BEGIN TRANSACTION statement with the ROLLBACK TRANSACTION and COMMIT TRANSACTION give us a way of implementing effective “single units of work” in SQL Server. Look out for a future post where we will delve into more detail on how to implement transaction frameworks and the potential pitfalls for not handling them correctly.