Transactions – the basics

I’m writing this blog because I’m currently working toward the 70-464 exam and transactions are bound to be covered. I wanted to consolidated my understanding and this is what I think I know…

A transaction is a logical unit of work that either completes as a whole or doesn’t complete at all. There is no way for a transaction to partially complete with only some data being updated. Anything that updates anything in SQL Server is handled in a transaction. Reading data out of SQL Server is also handled in a transaction.

SQL Server adheres to the ACID properties of transactions which ensure data integrity.

The ACID properties stand for Atomicity, Consistency, Isolation and Durability.

The atomicity property ensure that each transaction is handled as a single piece work that either succeeds in it’s entirety or not at all. If you’re trying to update 100 rows and one of them causes a error none of the 100 rows will be updated.

The consistency property ensures that the DB is left in a consistent state after the transaction completes. If not the transaction is rolled back. SQL Server uses constraints such as foreign keys to enforce this.

The isolation property ensures that transactions are isolated from each other during processing. There are various levels of isolation that make the locking of data behaving in different ways including a level that causes no locking and uses row versioning but this will be the subject of another blog.

The durability property ensures that even if there is a service interruption the data is in a usable state. SQL Server uses the transaction log to enforce durability.

By default all individual T-SQL statements are automatically committed. If you run two update statements in the same batch SQL Server will treat them as two single transactions. The first will either completely commit or not and then the second one will either completely commit or not.

You can configure SQL Server so that it does not automatically commit each statement. This forces you to issue a COMMIT or ROLLBACK command to finish the transaction. This is called implicit transaction mode. I have never used this mode but I think this is default behaviour in Oracle databases.

There is also explicit transaction mode which is where you issue a BEGIN TRAN command to start a transaction. You then execute your statement or batch or statements within this explicit transaction and either COMMIT or ROLLBACK all statements within that transaction. It’s a good idea to have error handling in your explicit transactions so that you can rollback the entire transaction completely if any statement fails.

Whenever a transaction starts the @@TRANCOUNT function gets increment by 1. If there are no transactions open the @@TRANCOUNT is 0. When a transaction starts it gets upped to 1. You can use this in your error handling to determine if there are any open transactions that need to be closed.

There is also a function XACT_STATE() that indicates the current state of any transaction. This is another useful function in error handling. A state of 0 means there are no open transactions. 1 means there is an uncommitted open transaction that can be committed. 2 means there is an open transaction that has caused a fatal error as cannot be committed. This must be rolled back.

You can turn on the XACT_ABORT property by running SET XACT_ABORT ON before starting a transaction. This will automatically rollback all statements within a transaction if any one of them fails. This property is enabled by default in triggers and causes not only the statement(s) in the trigger to rollback but also the statement that caused the trigger to fire.

You can nest transaction within each other, i.e. issue a BEGIN TRAN statement within another BEGIN TRAN statement. However be aware that nested transaction do not actually work in a completely nested way.

When you issue a BEGIN TRAN command the @@TRANCOUNT is upped to 1 (assuming it was originally 0). You can execute some statements within this transaction. If you then crated a nested transaction by issuing another BEGIN TRAN command the @@TRANCOUNT is upped to 2. You can execute some more statements and then issue a COMMIT command. All that actually happens here is the @@TRANCOUNT value is lowered to 1. Nothing is actually committed to the DB as the initial transaction is still open. If you now issue a ROLLBACK command the entire set of statements since the first BEGIN TRAN are rolled back. If you issue a COMMIT command the entire set of statements are committed. Nested transactions just force you to issue multiple COMMIT commands and it’s only the COMMIT that is executed when the @@TRANCOUNT is 1 that commits all the statements to the DB.

Rolling back statements in a nested transaction is handled slightly differently. If you’ve got five transactions nested within each other you do not have to issue five ROLLBACK commands to rollback the statements. As soon as you issue a ROLLBACK command the entire transaction is rolled back regardless of the nesting level.

You can rollback part of a transaction by using save points.  Here’s a little demo to show how save points work.

Create a table.

CREATE TABLE dbo.SavePointTest
(
 Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 SomeData VARCHAR(10) NOT NULL
);

Check the @@TRANCOUNT and XACT_STATE.  Both should be 0 as we’ve not started a transaction yet.

SELECT @@TRANCOUNT;
SELECT XACT_STATE();

Start a transaction.

BEGIN TRAN;

Recheck the @@TRANCOUNT and XACT_STATE.  Both should now be 1.

Insert a row into the table.

INSERT INTO dbo.SavePointTest (SomeData)
VALUES ('Simon');

If you run a SELECT statement within the transaction you will be able to see the new row in the table.

SELECT *
FROM dbo.SavePointTest;

Transactions1

Now we can create a named save point by the running the following.

SAVE TRANSACTION t1;

@@TRANCOUNT and XACT_STATE are still set to 1.  We can now insert a second row and it gets returned.

INSERT INTO dbo.SavePointTest (SomeData)
VALUES ('Peacock');
SELECT *
FROM dbo.SavePointTest;

Transactions2

Now if we decide we don’t the second row we can now rollback to the point in the code where we issued our save point.  We just need to include the name of the save point in the ROLLBACK command as follows.

ROLLBACK TRAN t1;
SELECT *
FROM dbo.SavePointTest;

Transactions3

The transaction is still open and @@TRANCOUNT and XACT_STATE are still set to 1.  At this point we can either commit or rollback the open transaction.  If we commit the row is added to the table, if we rollback the table remains empty.

COMMIT TRAN;

@@TRANCOUNT and XACT_STATE will now be set to zero.

This is a good time to point out that not everything gets completely rolled back when we issue a ROLLBACK command.  All the DDL and DML statements will be completed rolled back but one thing that isn’t are identity values.  During the transaction above we added a row “Simon” to the table.  This upped the identity value on the Id to 1.  We then added a second row “Peacock”.  This upped the identity value to 2.  Even though we then issued a ROLLBACK only the data change was rolled back and the identity value remained at 2.  So if we now try and add the row “Peacock” again we see the new row if added but this time it’s been given an Id of 3.

INSERT INTO dbo.SavePointTest (SomeData)
VALUES ('Peacock');
SELECT *
FROM dbo.SavePointTest;

Transactions4

 

 

 

Advertisements

One thought on “Transactions – the basics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s