SalesBuy
1-855-856-7678
Technical SupportSupport
Issues with Transactions and Federated Databases
The code needed to implement transactions in a Windows Azure SQL Database Federated database has pitfalls that must be avoided.
The order in which the following statements are executed is especially important. The order in which they are listed here is generally the correct order.
-
Opening the database connection
-
USE FEDERATION
-
Invoking the transaction
This topic shows the correct code needed for both explicit and implicit transactions, and describes how to trouble shoot incorrect code.
Author: James Podgorski and Rick Saling
Background
SQL Database applications contain multiple layers. One layer, the Services Layer, between the Platform Layer which contains the physical servers, and the Client Layer, acts as a gateway to a number of basic capabilities. One of those basic capabilities is connection routing. SQL Database Federations introduced the ‘USE FEDERATION’ command which must be submitted by the client to provide the SQL Database Gateway with information necessary for the gateway to connect the client to the appropriate federated member.
For a more detailed description of the SQL Database architecture, see Windows Azure SQL Database Architecture and Federations: Building Scalable, Elastic, and Multi-tenant Database Solutions with Windows Azure SQL Database.
The key take-away for implementing transactions with Federated SQL Database is that transactions can only be initiated after the USE FEDERATION command has rerouted the connection to the appropriate federated member. The SQL Database gateway will block all USE FEDERATION commands when there is an active transaction on a connection. Not doing so would be analogous to SQL Database supporting distributed transactions. Thus we will return an error if the following TSQL code is run:
BEGIN TRAN GO USE FEDERATION Fed_1 (range_id = 1) WITH FILTERING = OFF, RESET GO
Explicit Transactions
Here we use the System.Data.SqlClient.SqlTransaction class to invoke the transaction. This class is used to initiate explicit transactions where you explicitly begin and end a transaction. The System.Data.SqlClient.SqlConnection.BeginTransaction() method ultimately causes a TSQL BEGIN TRANSACTION statement to execute.
The following code will connect to the database, choose the right federation, invoke the transaction, and execute an UPDATE command.
public void AdoNetCorrectUsageOfSqlTransaction()
{
System.IO.StringWriter writer = new System.IO.StringWriter();
try
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"USE FEDERATION FED_1 (range_id = 0) WITH FILTERING = OFF, RESET";
cmd.ExecuteNonQuery();
SqlTransaction transaction = conn.BeginTransaction
("SampleTransaction");
cmd.Transaction = transaction;
cmd.CommandText = @"UPDATE Orders SET total_cost = total_cost + 1 WHERE order_id = 1";
int count = cmd.ExecuteNonQuery();
writer.WriteLine("Rows affected: {0}", count);
try
{
transaction.Commit();
}
catch (Exception e1)
{
WriteException(writer, e1);
try
{
transaction.Rollback();
}
catch (Exception e2)
{
WriteException(writer, e2);
}
}
}
}
catch (Exception e)
{
WriteException(writer, e);
}
Console.WriteLine(writer.ToString());
}
An incorrect ordering of commands would be to move the “cmd.ExecuteNonQuery();” command which executes the USE FEDERATION command down two lines, so that it follows the statement that enlists the command in the transaction (cmd.Transaction = transaction;). When run in this order, an exception will be thrown:
System.Data.SqlClient.SqlException Error: 226 USE FEDERATION statement not allowed within multi-statement transaction.
Implicit Transactions
An implicit transaction is created by enclosing a block of code inside a using statement block. The using statement references a System.Transactions.TranscactionScope object.
Implicit transactions with SQL Database Federations follow the same pattern as explicit transactions: the command sequence is:
-
Connect to the database
-
Execute the USE FEDERATION statement
-
Establish the transaction scope and carry out operations within it
The following code shows how to do this correctly.
public void AdoNetCorrectUsageOfTransactionScope()
{
System.IO.StringWriter writer = new System.IO.StringWriter();
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = IsolationLevel.Snapshot;
try
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"USE FEDERATION FED_1 (range_id = 0) WITH FILTERING = OFF, RESET";
cmd.ExecuteNonQuery();
using (TransactionScope scope = new TransactionScope (TransactionScopeOption.RequiresNew, options))
{
cmd.CommandText = @"UPDATE Orders SET total_cost = total_cost + 1 WHERE order_id = 1";
int count = cmd.ExecuteNonQuery();
writer.WriteLine("Rows affected: {0}", count);
}
}
}
catch (Exception e)
{
WriteException(writer, e);
}
Console.WriteLine(writer.ToString());
}
As was the case with explicit transactions, an example of incorrect code would be to reverse the scopes of the using statements, and make the using (TransactionScope scope = new TransactionScope (TransactionScopeOption.RequiresNew, options)) be the outer using statement. In this case the same exception as before would be thrown.
Build Date:
