Using Transactions

for RuBoard

As shown in Figure 8.1, providers can support transactions that implement the IDbTransaction interface and that are associated to the connection class. Both the SqlClient and OleDb providers include transaction objects that are created using the BeginTransaction method of the appropriate connection class. Transactions are obviously very useful for making sure that multiple commands (statements) executed against a single data store are treated as a logical unit of work. In that way, if one of the statements fails, all of them can be rolled back (undone). Likewise, if all of them succeed, they will all be committed (made permanent) on the data store.

Note

As you'll see momentarily, using transactions necessarily implies that the data store needs to lock data while the transaction is active. As a result, you'll want to make sure that your transactions are short-lived, always are either committed or rolled back, and lock only the data they need to. Failing to complete a transaction will cause the locks to remain and possibly prevent other users from accessing the data.


In general, if no transaction has been created, commands executed against a data store operate as a series of implicit transactions. In other words, the scope of the transaction is limited to single statements that make up the command. An example is an OleDbCommand object that executes a stored procedure using the ExecuteNonQuery method that both inserts a row and then deletes a different row from a table. Without any explicit transaction control, each statement (even if it is contained in a stored procedure) is treated as an implicit transaction. If the insert succeeds and the delete fails, the insert is still persisted to the data store. Of course, stored procedures and other database objects could contain specific transactional statements that control how the transaction occurs. The use of a transaction object in ADO.NET ensures that you can use explicit transactions to control how and when commits and rollbacks occur.

Note

As you'll learn on Day 12, transactions can also be used with data adapters to ensure that either all the changes to a DataSet are successfully synchronized with the data store or are rolled back. However, note that each of the command objects associated with the data adapter ( SelectCommand , InsertCommand , UpdateCommand , and DeleteCommand ) must have its Transaction property set to the instance of the transaction created with the BeginTransaction .


Transactions with SqlClient

To use explicit transactions with SQL Server, you can call the BeginTransaction method of the SqlConnection class. This method creates the SqlTransaction object, which can then be manipulated using its methods . The BeginTransaction method is overloaded to accept various combinations of the isolation level and transaction name (a string used to refer to the transaction). However, the BeginTransaction method can't be called unless the connection has been opened. This is because it immediately sets the isolation level and issues a Transact -SQL BEGIN TRANSACTION statement against the server. Calling BeginTransaction on a closed connection results in an InvalidOperationException . In addition, closing a connection with a pending transaction will automatically issue a ROLLBACK TRANSACTION statement. As a result, the typical pattern for using transactions is shown in Listing 9.5.

Listing 9.5 Using transactions. This partial method shows how transactions are typically used.
 SqlConnection con = new SqlConnection(_connect); SqlTransaction trans; try {     con.Open();     trans = con.BeginTransaction();     try     {         // do other work here         // all is well         trans.Commit();     }     catch (SqlException e)     {        // log the error        trans.Rollback();     } } catch (SqlException e) {     // connection failed     // log error     return; } finally {    con.Close(); } 
graphics/analysis.gif

In this snippet, the connection is instantiated with a connection string and the transaction object is declared but not created. After the connection is open, the BeginTransaction method is called. Note that this work is placed in its own try catch block to ensure that the transaction can be initiated once the connection is opened. In the nested try catch block, the actual work would be performed and the transaction's Commit method called if it succeeded. The catch block is used to handle any SQL Server errors and call the Rollback method if one is encountered . As you might imagine, the Commit method executes the Transact-SQL COMMIT TRANSACTION statement while the Rollback method executes the ROLLBACK TRANSACTION method.

As noted previously, the BeginTransaction method accepts an argument in its constructor that specifies the isolation level in which the transaction should run using the IsolationLevel enumeration. By default, SQL Server operates in ReadCommitted mode, which means that the transaction can read only data that has been committed on the server. In addition, share locks are placed on the data only while it is being read. This ensures that transactions are isolated and not able to read data that could in fact end up being rolled back.

However, it doesn't ensure that if the transaction attempts to read the same data twice, that it will receive the same results. As a result, ReadCommitted is a compromise between transactions that lock the range of rows they read or modify ( Serializable ) and those that don't lock anything ( ReadUncommitted ). There are obviously scenarios where each of the options makes sense. Generally, the higher the isolation level, the less concurrency your application will support. This is the case because a higher isolation level necessarily means that more locks will be placed on the data, thereby increasing lock contention and allowing fewer users to read or write to the database at one time. To use an isolation level other than the default, you could, for example, use a statement like the following:

 trans = con.BeginTransaction(IsolationLevel.ReadUncommitted); 

Tip

Using ReadUncommitted is particularly effective for applications that simply need to build quick reports (sometimes referred to as flash reports ) on the status of a database that is used for an OLTP application. Reading the data without applying any locks is both very fast and won't interfere with the locking that the OLTP application has to do.


graphics/newterm.gif

Transactions in SQL Server also support the concept of save points. Basically, a save point is a marker or point in the transaction that can be rolled back to. In other words, if you use a save point, you can roll back only to the save point and not the entire transaction. SqlTransaction supports save points by exposing a Save method that accepts the name of the save point to create. The Save method issues a SAVE TRANSACTION statement against the server. At some point later, you can execute the transaction's Rollback method and pass it the name of the save point. This results in all the work from the save point to the point at which Rollback was called to be undone. Although this is handy, keep in mind that any locks created after the save point will still be held until the entire transaction is either committed or rolled back.

Finally, SQL Server supports the idea of nested transactions. Although you can't nest transactions by calling the BeginTransaction method on the same connection more than once, you can execute commands or call stored procedures that issue their own BEGIN , COMMIT , SAVE , and ROLLBACK TRANSACTION Transact-SQL statements after you have called BeginTransaction . SQL Server allows this to occur; however, the actual work of the transaction and any of its nested transactions is not actually committed until the outermost transaction is committed when you call the Commit method of the SqlTransaction object. If an inner transaction is rolled back, all the nested transactions and the outermost transaction are rolled back. This feature of SQL Server is present primarily to support calling stored procedures that contain their own transaction statements.

Note

Experienced developers might be wondering why the programming model for transaction seems a little strange . For example, why doesn't the SqlConnection class simply expose begin, commit, save, and rollback methods instead of using a second object? The reason is that this model can be used to support the parallel transaction feature of the next release of SQL Server. In parallel transactions, a connection could spawn multiple transactions and so the BeginTransaction method could be called more than once.


Transactions with OleDb

Just as in the SqlClient provider, the OleDb provider includes an OleDbTransaction class that represents a transaction on the data store communicated with by the OleDbConnection class. The programming model is identical: New transactions are created by calling the BeginTransaction method and are then committed or rolled back using the methods of OleDbTransaction . The only method that SqlTransaction supports that OleDbTransaction does not is the Save method. This is because most providers don't support the concept of save points.

Transactions in Serviced Components

graphics/newterm.gif

The use of the BeginTransaction method with the connection object is a means of using explicit local transactions directly in ADO.NET. A local transaction is one whose scope is restricted to a single database server. However, the classes you write in .NET can also participate in distributed transactions by using the services provided by COM+ (Component Services). A distributed transaction is one that spans multiple data sources and even sources of different types. For example, a distributed transaction can coordinate modifications to an Oracle database, a SQL Server database, and a Microsoft Message Queue (MSMQ) server and ensure that if all the modifications succeed, all will be committed. However, if one of the changes fails, all will be rolled back. This is done using a two-phase commit protocol and the Microsoft Distributed Transaction Coordinator (MSDTC) service that runs on Windows servers. The MSDTC serviced is a Transaction Processing (TP) monitor. Typically, distributed transactions are used in enterprise-scale applications that work with multiple data stores.

Code written with both the SqlClient and OleDb providers can participate in distributed transactions; however, their respective transaction classes aren't used. Instead, as mentioned previously, your .NET class that will participate in the transaction must be a serviced component. A serviced component is a class that is derived from System.EnterpriseServices.ServicedComponent , which allows the class to utilize the services of COM+, one of which is distributed transactions. After you have derived from ServicedComponent , you use attributes to indicate that your class supports or requires transactions. Finally, the methods in your class must then indicate when the transaction is complete or needs to be rolled back. The ComputeBooksStore serviced component from Listing 9.2 has been modified to use distributed transactions as shown in Listing 9.6.

Note

In order for the OleDb .NET Data Provider to participate in transactions, the data store you are connecting to must support the X/Open transaction- processing model. In other words, the underlying OLE DB provider must understand certain commands from the MSDTC service.


Listing 9.6 Using distributed transactions. This serviced component requires transactions.
 namespace ComputeBooks.Data {     [ConstructionEnabled(       Default="server=ssosa;Initial Catalog=compubooks;trusted_security=yes"),     Transactions(TransactionOption.Required)]     public class ComputeBooksStores : ServicedComponent     {         private string _connect;         public string ConstructString         { //returns the connection string             get             {                 return _connect;             }         }         protected  override void Construct(string s)         {             // called each time an instance of this class is created             _connect = s;         }         public ComputeBooksStores(string connect)         {             _connect = connect;         }         // other methods here         public void SaveStore(Object parms[])         {             try             {                 // open a connection                 // use the parms to execute a command with ExecuteNonQuery                 ContextUtil.SetComplete();             }             catch (Exception e)             {                 ContextUtil.SetAbort();                 // throw an exception             }         }     } } 
graphics/analysis.gif

In Listing 9.6, you'll notice that the class has now been decorated with the TransactionAttribute and, in this case, set to the Required value of the TransactionOption enumeration. By setting this attribute, you ensure that when any method of the class is called, a distributed transaction (either an existing one or a new one) will be created. Other values of TransactionOption include Disabled , NotSupported , Supports , and RequiresNew . As you can imagine, using Disabled or NotSupported will in no circumstances allow this class to participate in a distributed transaction. Using Supports indicates that if a transaction already exists, the methods of this class will participate. However, a new transaction will not be created. Using RequiresNew will always create a new transaction for this class.

Note

As you can see, this model supports the idea that individual classes needn't know nor be concerned with the transactional behavior of other classes. This means that transactional components can be loosely coupled , which increases the maintainability and extensibility of your applications.


The second aspect of Listing 9.6 to notice is the SaveStore method. This method includes calls to the SetComplete and SetAbort static methods of the ContextUtil class. The ContextUtil class is found in System.EnterpriseServices and exposes methods and properties that serviced components can use to control how they interact with the COM+ runtime. In this case, calling SetComplete when the method is successful indicates that the component's vote in the transaction is to commit. Calling SetAbort indicates that the transaction should be aborted. If all other components participating in the transaction also vote to commit, the MSDTC service will commit them. If any one of the components votes to abort, all will be rolled back. In addition to voting on the transaction, SetComplete and SetAbort also tell the COM+ runtime to deactivate the object instance after the method has returned. If you do not want to deactivate the object, you could alternatively call EnableCommit and DisableCommit .

Rather than having to call the SetComplete and SetAbort methods directly, you can alternatively decorate each method with AutoCompleteAttribute . This attribute indicates that if the method terminates normally, SetComplete will be called automatically. Conversely, if an exception is thrown, SetAbort will be called.

As indicated previously, connections are associated with a particular transaction context. If pooling is enabled when the SaveStore method calls the Open method of the connection, a connection is pulled from the pool with the appropriate transaction context.

for RuBoard


Sams Teach Yourself Ado. Net in 21 Days
Sams Teach Yourself ADO.NET in 21 Days
ISBN: 0672323869
EAN: 2147483647
Year: 2002
Pages: 158
Authors: Dan Fox

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net