SQL Server Transactions

Transactions in SQL Server allows you to batch multiple inserts/updates/deletes in one single process and an error in one sub-step will invalidate or roll back the entire transaction. This ensures the data integrity and atomicity.

Let's assume for example, you have a set of three tables in a SQL database

• A Employee Profile Table
• An Earnings table recording the salary, taxes paid etc.
• A Tax rate table that records an employee's tax rate.

If the tax rate changes, you would want to update the TaxRate table first, then update the Earnings table to reflect the taxes paid (based on new tax rate). For example, if the earnings were $100,000 and the tax rate was 20%, the taxes paid in the Earnings table would be $20,000. Now, if the tax rate jumps to 25%, TaxRate table would be updated to reflect tax rate of 25% and the Earnings table would be reflected to show the updated tax of $25,000.

Now lets assume you are performing these operations as individual updates without wrapping them in one transaction.

      --1st you update tax rate table

       UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1

       GO

      -- now you update taxes paid 

      UPDATE Earnings SET TaxesPaid = 

      (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

      WHERE EmployeeID=1

      GO

Now, lets imagine first update executed successfully but a deadlock or some other error occurred before second update could be performed (more on deadlocks in future posts), resulting in out of sync data. TaxRate table will show the tax rate of 25% but Earnings table will show only $20,000 taxes paid.

You want to ensure that either both tables are updated together or none at all. You can wrap both updates in one transaction, and if an error occurred, entire transaction will rollback, ensuring the data integrity.

Here is how you would wrap the above updates in a transaction.

DECLARE @errorcode INT

BEGIN TRAN

    --1st you update tax rate table

       UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1

       GO

       -- check if an error occurred

       If @@Error <> 0 GO TO ERROR  -- @@Error would trap any error that may occur

      -- now you update taxes paid 

      UPDATE Earnings SET TaxesPaid =

          (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

          WHERE EmployeeID=1

      GO

     -- check again if an error occurred

       If @@Error <> 0 GO TO ERROR

COMMIT TRAN

ERROR:    --- GO TO ERROR statement will jump here anything an error occurred.

PRINT ('An error occurred')

ROLLBACK TRAN

This allows you to declare transaction at the SQL Script level - in a stored procedure for example. You can achieve the same effect by using ADO.NET Transaction or TransactionScope object in code when executing two or more separate stored procedures or in-line SQL.

Using Transaction

public void updateTaxInformation (string connectstring)

{

   using (SqlConnection conn =  new SqlConnection(connectstring))

{

      conn.Open();

      SqlCommand cmd = connection.CreateCommand();    // create sql command

      SqlTransaction trans;   // create a new sql transcation

      // start the transaction

      trans = conn.BeginTransaction("TransName");

     //assign connection and transaction to the command

     cmd.Connection = conn;

     cmd.Transaction = trans;

     try

     {

          cmd.commandText = "UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          //second update

          cmd.commandText = "UPDATE Earnings SET TaxesPaid =

                                             (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

                                             WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          // commit transaction

         trans.commit();

      }

    catch (Exception ex)

    {

        Console.WriteLine("Exception Occurred: " + ex.Message);

        trans.Rollback();

     }

  }

}

Using TransactionScope

Beginning in .NET 2.0, Microsoft introduced TransactionScope which basically does the same thing as above, except a few subtle differences. TransactionScope simplifies some of the code and takes care of enrolling SQL commands into a transaction autmatically. The above code can be simplified using TransactionScope 

Using (TransactionScope transscope = New TransactionScope())

  {

     using (SqlConnection conn =  new SqlConnection(connectionstring))

       {

          conn.Open();

          SqlCommand cmd = connection.CreateCommand();    // create sql command

          cmd.commandText = "UPDATE TaxRate SET rate=0.25 WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          //second update

          cmd.commandText = "UPDATE Earnings SET TaxesPaid =

                                             (SELECT Salary * 0.25 FROM Earnings WHERE EmployeeID=1)

                                             WHERE EmployeeID=1;

          cmd.ExecuteNonQuery();

          transscope.Complete();

         }

    }

       

That's all there is to it. While a transaction has its advantages such as maintaining data integrity, you have to be careful when creating a transaction. Large transactions could result in dreaded deadlocks. We will discuss deadlocks in future posts.

Thank you and your comments are welcome.