Transactions in SQL Server
Part one of a four-part series of blogs

This blog examines how to use Transactions in Microsoft SQL Server to allow you to control when data changes are committed to the database. You'll also discover how you can rollback transactions, effectively giving you the chance to undo changes to your data.

  1. Transactions in SQL Server (this blog)
  2. Errors within Transactions
  3. Nesting Transactions
  4. Transaction Savepoints

This blog is part of a complete SQL Server Tutorial.

Posted by Andrew Gould on 11 October 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Transactions in SQL Server

Normally, when you change data in a SQL database, such as inserting new records, or deleting or updating existing records, you don't get the opportunity to undo your changes. If you begin a transaction before you modify your data, however, you do have the chance to undo your changes if things go wrong.

Beginning a Transaction

To have the opportunity to undo changes made to your data you must begin a transaction before you attempt to make those changes. You can begin a transaction with the BEGIN TRANSACTION statement, as shown below:

--Begin a new transaction

BEGIN TRANSACTION

--Make some changes to data

You can also abbreviate the word TRANSACTION to TRAN if you're a lazy typist like me.

--Begin a new transaction (the short way)

BEGIN TRAN

--Make some changes to data

Naming a Transaction

It's possible to open multiple transactions within the same script and so it's useful to be able to name individual transactions in order to help identify them.

--Begin a new transaction with a name

BEGIN TRAN MyTransactionName

--Make some changes to data

You can also use a variable to set the name of a transaction, like so:

DECLARE @TranName VARCHAR(50)

SET @TranName = 'MyTransaction'

BEGIN TRAN @TranName

Ending a Transaction

When you've started a transaction you must always explicitly end it in one of two ways. You can either commit the transaction, which saves the changes you have made, or you can rollback the transaction, which undoes the changes you have made.

To commit a transaction you use the COMMIT statement, as shown below:

--Start a new transaction

BEGIN TRAN

--Make some changes to data

--Save the changes you have made

COMMIT

You can be more specific when you commit a transaction. The code below shows several ways to commit a transaction:

--Various ways to commit a transaction

COMMIT

COMMIT TRAN

COMMIT TRANSACTION

COMMIT TRAN MyTransactionName

If you'd rather undo your changes you can use the ROLLBACK statement. Just as with the COMMIT statement you have a variety of ways to do this:

--Various ways to rollback a transaction

ROLLBACK

ROLLBACK TRAN

ROLLBACK TRANSACTION

ROLLBACK TRAN MyTransactionName

You could also replace the words TRAN or TRANSACTION with the word WORK in the above examples. The only difference between COMMIT/ROLLBACK TRANSACTION and COMMIT/ROLLBACK WORK is that you can't use a named transaction with the WORK keyword.

What's Next?

The next part of this series explains what happens when things go wrong within a transaction.

This blog has 0 threads Add post