Transactions

Chapter 9 - Data-Driven ASP.NET Applications in the Real World
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Transactions are groups of database commands that execute as a package. When dealing with real-world applications, you may need to ensure that multiple actions within the application either succeed in their entirety, or fail in their entirety. In other words, all actions should succeed, or none of them should. Transactions help you to accomplish this. You begin a transaction, and then, if all actions are successful, you commit it. Committing a transaction causes the actions to be saved to the data source. Otherwise, you roll it back, which puts the database back to its state was prior to the transaction beginning.

As so often, there's an acronym for the concepts that make up transaction processing. The word is ACID, and it works like this:

Term	Description
Atomicity	This describes the concept that all actions within the transaction should either fail or succeed. A classic example is a bank transfer from one account to another. You either want the debit from account A and the credit to account B both to be written, or you want neither of them to be written. If one is written and the other isn't, one account will end up with more or less money than it should have.
Consistency	The results of the actions that occur during the transaction should not be changed in any way by the transaction processing itself.
Isolation	The idea that multiple transactions occurring at the same time will not conflict with each other. One transaction will never see the results of another transaction until that other transaction is committed. This ensures that transactions don't read data involved in other transactions that may be rolled back, or that might not yet be completely written.
Durability	The results of the actions, once committed, should remain even if system errors occur after the commitment. Usually, transaction logs such as those used by SQL Server ensure durability: at the exact moment of commitment, the transaction is written to the log, before an attempt to update the database is made. Even if a system error occurs immediately after the log is written to, the transaction still exists and can be processed after the system is restored.

Transactions can be handled in three places within .NET database applications.

Location	Description
Database	SQL Server has T-SQL statements that allow you to group related actions into a transaction. They are BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
ADO.NET	ADO.NET allows you to create transactions through the use of the Connection object, the Transaction object, and the Command object. If you're using a SQL Server database, TRANSACTION T-SQL statements will be used behind the scenes. For other DBMS systems, the transaction statements that they support will be used.
Enterprise Services	.NET components can run within the COM+ space by inheriting from the System.EnterpriseServices.ServicedComponent class or a derived class. EnterpriseServices allows you to utilize the COM+ services for transaction handling. This is beyond the scope of this book.

Transactions do affect performance - they keep resources locked until the transaction is committed or rolled back. Because of this, other applications will have to wait if they need to access these resources. You should therefore be prudent when deciding whether you need transactions. Also, you should start transactions as late as possible within your code, and then commit them or roll them back as soon as possible, to make the lock time as short as possible.

Transactions in SQL

As stated above, transactions in SQL are handled through the use of the BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements, whose names are self-explanatory. If you wish, you can shorten them to use the keyword TRANS instead of TRANSACTION.

• BEGIN TRANSACTION begins the transaction context. Any statements after this one are a part of the transaction, until it is committed or rolled back.

• COMMIT TRANSACTION commits all the changes that occurred during the transaction to the database. At this point, the changes are permanent, they cannot be undone, and the transaction no longer exists.

• ROLLBACK TRANSACTION causes all changes that have occurred within the transaction context to be canceled, and the transaction context to end.

Any statements executed after a COMMIT TRANSACTION or a ROLLBACK TRANSACTION are no longer a part of a transaction, unless a new transaction is begun.

Try It Out - Transactions in SQL

Let's start putting transactions to use. A good example of a situation where you need transactions within a stored procedure is when you're auditing the various data changes taking place within your database. Auditing is important when you need accountability for the changes that are made to an application. If you have multiple users maintaining employee records, for example, and someone creates an employee with erroneous data, you need to be able to look at an audit entry and find out exactly who performed the operation, so that the erroneous data can be corrected.

1. Before we can do this type of auditing in our example, we need an audit log table. Here's the SQL code to create it:

    CREATE TABLE [tblAuditLog] (   [AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,   [Action] [varchar] (20) NOT NULL ,   [Msg] [varchar] (100) NULL ,   [AuditDate] [datetime] NULL     CONSTRAINT [DF_tblAuditLog_ErrorDate] DEFAULT (getdate()),   CONSTRAINT [PK_tblAuditLog] PRIMARY KEY CLUSTERED   (     [AuditLogID]   ) ON [PRIMARY] ) ON [PRIMARY] 

2. Next, let's modify our InsertEmployee stored procedure to use transactions. If the employee is successfully created, we'll insert a record into tblAuditLog to reflect this.

    ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                @LastName nvarchar(20)) AS BEGIN TRANSACTION INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) IF @@ERROR = 0 BEGIN   INSERT INTO tblAuditLog (Action, Msg)     VALUES ('New employee created', 'Employee''s name is ' +             @FirstName + ' ' + @LastName)   IF @@ERROR = 0   BEGIN     COMMIT TRANSACTION   END   ELSE   BEGIN     ROLLBACK TRANSACTION     RAISERROR('Audit of employee creation failed.', 1, 1)     INSERT INTO tblErrorLog (ErrType, Msg)       VALUES ('Audit Failure', 'Audit of employee creation failed.')     IF @@ERROR <> 0     BEGIN       RAISERROR('Error log couldn''t be updated.', 1, 1)     END   END END ELSE BEGIN   ROLLBACK TRANSACTION   RAISERROR('Employee duplicate record couldn''t be created.', 1, 1)   INSERT INTO tblErrorLog (ErrType, Msg)     VALUES ('Duplicate record',             'Employee duplicate record couldn''t be created.')   IF @@ERROR <> 0   BEGIN     RAISERROR('Error log couldn''t be updated.', 1, 1)   END END 

3. Before we execute this, we'll clear the old records in tblErrorLog that were created during previous examples.

    DELETE FROM tblErrorLog 

4. Now, use Visual Studio .NET to execute the InserteEmployee stored procedure, passing 'Judd" and "Pipes" as the values for @FirstName and @LastName. If you then examine the tblAuditLog and tblErrorLog tables, you should see the following:

   

   In these tables, you can see the audit log record that was created when the new employee was added, and the fact that we don't have an error.

How It Works

All of the interest here lies in the revisions to the stored procedure. The first change happens right at the start, where we begin the transaction.

    BEGIN TRANSACTION 

Next, as usual, we attempt to insert the new employee record, and then check to see whether that attempt was successful:

    INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName)    IF @@ERROR = 0 

If @@ERROR = 0, we know that the attempt was successful, and we can insert an entry into the audit log.

    BEGIN      INSERT INTO tblAuditLog (Action, Msg)        VALUES ('New employee created', 'Employee''s name is ' +                @FirstName + ' ' + @LastName) 

But we now have to check to see if the audit log entry itself was successful.

       IF @@ERROR = 0       BEGIN 

And if it was, then we commit the transaction.

         COMMIT TRANSACTION       END 

However, if the audit log entry wasn't successfully created, we roll back the transaction. This will cause the employee record that we created above to be removed. We don't want a new employee added without its associated audit log entry.

       ELSE       BEGIN         ROLLBACK TRANSACTION 

We also raise a custom error message, and create an entry in the error log. It's important to note that since the transaction has been rolled back, any commands following the rollback are executed outside of the transaction scope.

         RAISERROR('Audit of employee creation failed.', 1, 1)         INSERT INTO tblErrorLog (ErrType, Msg)           VALUES ('Audit Failure', 'Audit of employee creation failed.') 

As seen in previous examples, if an error occurrs while creating the entry in the error log, we raise another error.

         IF @@ERROR <> 0         BEGIN           RAISERROR('Error log couldn''t be updated.', 1, 1)         END       END     END 

If the attempt to insert a new employee wasn't successful, we come to this code block:

     ELSE     BEGIN 

The first thing we do after an unsuccessful attempt to insert a new employee is to roll back the transaction.

       ROLLBACK TRANSACTION 

Then, finally, we raise our error and create an entry in the audit log.

       RAISERROR('Employee duplicate record couldn''t be created.', 1, 1)       INSERT INTO tblErrorLog (ErrType, Msg)         VALUES ('Duplicate record',                 'Employee duplicate record couldn''t be created.')       IF @@ERROR <> 0       BEGIN         RAISERROR('Error log couldn''t be updated.', 1, 1)       END     END 

To complete this example, let's create a situation where the audit log entry can't be created. As we've done before, we'll change one line in the stored procedure so that a null value is specified for the Action field, which doesn't allow nulls. Here's the changed line.

       INSERT INTO tblAuditLog (Action, Msg)         VALUES (NULL, 'Employee''s name is ' + @FirstName + ' ' + @LastName) 

Now when we try to insert a new employee - say, Sharon Pipes - we get an error that says we can't insert our record into the tblAuditLog table. And if we look at the tblErrorLog table, that's exactly what we find:

The important thing to look out for here, however, is that even though the original insert into the Employees table succeeded, it was rolled back because no entry could be placed into tblAuditLog. Take a look at the Employees table, and you'll find that no new entry exists. Moreover, as we mentioned in the walkthrough of the stored procedure, the entry in tblErrorLog was successful because the transaction was over by the time the insert into that table took place.

Transactions in ADO.NET

Transactions in ADO.NET work the same way as they do in SQL: you begin a transaction and then, depending on the circumstances, you either commit that transaction or roll it back.

When you need a transaction in your ADO.NET code, the connection object is used to provide a transaction object. This object is then used with each command object that you want to include in the transaction. The diagram below shows the relationship between the three object types.

Here's how you use transactions within your ASP.NET applications:

1. Create your connection object.

2. Obtain a transaction object through the use of the connection object's BeginTransaction() method.

3. Create a command object.

4. Set the command object's Transaction property to the transaction object created in Step 2.

5. Execute the command.

6. Repeat steps 3-5 for each command you want included in the transaction.

7. If all commands execute successfully, execute the Commit() method on the transaction object created in Step 2.

8. If any commands don't execute successfully, execute the Rollback() method on the transaction object created in Step 2.

9. Regardless of whether the transaction was committed or rolled back, be sure to close the connection object.

Try It Out - Transactions in ADO.NET

Let's create a new example that uses the Employees table and the tblAuditLog table we created earlier. We're going to create a new employee named Brian Berry.

1. First up, delete any existing employees by that name, as well as all audit log entries, with the following SQL statements:

    DELETE FROM tblAuditLog DELETE FROM Employees WHERE FirstName = 'Brian' And LastName = 'Berry' 

2. Create the ASPX page below, naming it Trans.aspx. You'll notice immediately that we're adding the audit log entry first. This lets us show that an insert action (in this case, the audit log entry) was executed successfully, and yet was rolled back when the employee insert failed.

    <%@ Import namespace="system.data" %> <%@ Import namespace="system.data.sqlclient" %> <script language="vb" runat="server"> Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim conn As New SqlConnection(strConnection)   Dim Tran As SqlTransaction   Dim cmdl, cmd2 As SqlCommand Dim sql As String   conn.Open()   Tran = conn.BeginTransaction()   Try     sql = "INSERT INTO tblAuditLog (Action, Msg) " & _           "VALUES ('Insert the man', 'Brian Berry')"     cmd1 = New SqlCommand(sql, conn)     cmd1.Transaction = Tran     cmd1.ExecuteNonQuery()     Response.Write("Audit Log entry added<br/>")     sql = "INSERT INTO Employees (FirstName, LastName) " & _           "VALUES (@FirstName, @LastName)"     cmd2 = New SqlCommand(sql, conn)     cmd2.Transaction = Tran     cmd2.Parameters.Add("@FirstName", "Brian")     cmd2.Parameters.Add("@LastName", "Berry")     cmd2.ExecuteNonQuery()     Response.Write("Employee added<br/>")     Tran.Commit()   Catch     Tran.Rollback()     Response.Write("There was an error<br/>")   Finally     conn.Close()   End Try End Sub </script> 

The first time you run this, you'll see the following messages:

     Audit Log entry added     Employee added 

However, the second time it's run, the employee insert will fail because an employee called Brian Berry already exists. Therefore, you'll see the error message:

     Audit Log entry added     There was an error 

When you look at the database, however, you'll only see one audit log entry:

As you can see, when we ran it the second time, the successful audit entry that was inserted was rolled back, leaving only the first audit log entry that was created during the first run.

How It Works

What are the important parts of this code? It starts right after we open the connection to the database, when we create a SqlTransaction object through the use of the connection's BeginTransaction() method:

      conn.Open()      Tran = conn.BeginTransaction() 

We then create the (first) command object. On this occasion, we've created a variable to hold the SQL query, and passed this variable to the command's constructor, along with the connection object.

       Try         sql = "INSERT INTO tblAuditLog (Action, Msg) " &_               "VALUES ('Insert the man', 'Brian Berry')"         cmd1 = New SqlCommand(sql, conn) 

Next, we set the command to be a part of the transaction. cmd1 is now within the Tran transaction object.

         cmd1.Transaction = Tran 

We then attempt to execute the command. If this takes place successfully, we should display the message below.

         cmd1.ExecuteNonQuery()         Response.Write("Audit Log entry added<br/>") 

Now we create another command and attempt to execute it. Notice that we assign cmd2 to the same Tran transaction object.

         sql = "INSERT INTO Employees (FirstName, LastName) " & _               "VALUES (@FirstName, @LastName)"         cmd2 = New SqlCommand(sql, conn)         cmd2.Transaction = Tran         cmd2.Parameters.Add("@FirstName", "Brian")         cmd2.Parameters.Add("@LastName", "Berry")         cmd2.ExecuteNonQuery() 

If this is successful, we display the message below, and then commit the transaction.

         Response.Write("Employee added<br/>")         Tran.Commit() 

But if any of the statements within the Try section above fail, we catch the exception and roll back the transaction, letting the user know there was an error.

       Catch         Tran.Rollback()         Response.Write("There was an error<br/>") 

Finally, regardless of whether we were successful or not, we need to close the connection.

       Finally         conn.Close()       End Try 

Note 

You've seen how to use BEGIN TRANS, COMMIT TRANS, and ROLLBACK TRANS from within stored procedures. When you use transactions within ADO.NET, these same statements are still being used. For the most part, you can let them do their work without worry. However, if you use ADO.NET to manage a transaction, and during that transaction you execute a stored procedure that uses SQL's transactions, they can conflict.

To see a performance comparison of the different ways of handling transactions, take a look at this article on MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch13.asp.