This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".
Transactions
Transactions are groups of database commands that execute as a package and provide an ability to commit or roolback (abort) all changes made during the transaction processing. Transaction changes will be committed if there was no error during the transaction processing. If an error occurs during the transaction processing, all changes will be aborted and data will be the same as it was before any transactions started. To start a transaction processing, you call Begin Transaction. At the end you can call CommitTransaction or Rollback Transaction based on the status of transactions. The CommitTransaction reflects all changes to the databases and RollBack aborts all changes.
For example, say you have an application with two tables: Inventory and Orders. When a customer places an order, the Inventory table needs to be reduced. Now imagine that an update to the Orders table was successful, but an update to the Inventory table failed. This scenario will lead to data inconsistency. To maintain the integrity of data, you could package both commands into a single transaction. If one table updated successfully and the other table did not, the transaction can be rolled back; otherwise, the traction can be committed.
Nested transactions are transactions within the scope an existing transaction. The changes made within the nested transactions are invisible to the top-level transactions until the nested transactions are committed. To create nested transactions, you call BeginTransaction with CommitTransaction and RollBackTransaction within the existing transaction. For example:
Begin Transaction A
Begin Transaction B
Do something
Commit Transaction B
Commit Transaction A
Savepoints are useful when you're working with nested transactions. There are occasions when you want to abort a portion of transaction, but not all of it. For example, you're processing four commands as nested transactions, but you want to commit only two commands and abort two of them. A save point is a temporary point in the transaction that you want to save (or back up) without aborting the entire transaction. In transaction processing, you set the save point call and come back later when you think it's safe to process the transaction. A unique number represents the save point. For example
Begin Transaction A
Do something
Set save point
Do Something
Commit or Roll back Transaction A
Managing and writing reliable and scalable multi-tier distributed applications is one of the most challenging jobs for database developers. Think about multiple clients accessing the same server database simultaneously. Some of them are accessing data, and some of them are updating the same data, and some of them are trying to delete the same data that other clients are using in their operations.
To prevent data inconsistency, it's important to provide some kind of mechanism so other users can't update data when a user is already using the same data. The mechanism to deal with this situation is called concurrency control. Concurrency is the method by which multiple clients can access and update the same data simultaneously without being concerned that they're forcing data redundancy and inconsistency.
There are three common ways to manage concurrency:
-
Pessimistic concurrency control. In this type of concurrency control, a row (or a record) is unavailable to other users from the time the record is fetched by user until it's updated in the database.
-
Optimistic concurrency control. In this type of concurrency control, a row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made.
-
Last in wins concurrency control. In this case of concurrency control, a row is unavailable to other users only while the data is actually being updated. The update overwrites the changes made by other users.
In pessimistic concurrency, the data is unavailable to other users from the time the record is fetched by user until it's updated in the database. This is useful when a user accessing a row is going to play a major role based on the data he/she is accessing. Another advantage of pessimistic concurrency is less locking overhead. Once a row is locked, it's locked until the first user done. The main draw back of this type of concurrency is that data is not available to other users. For example, if a user accessing data left his terminal, other users have to wait for him to release the connection.
In optimistic concurrency, data is available all the time except when a user is updating the data. In this type of concurrency, the locks are set and held only while the database is being accessed. The locks prevent other users from attempting to update records at the same instant, if other users try to update the data that is locked by the first user, the update fails.
The last in wins type of concurrency is only useful when the last user's update counts. For example, it's useful if you're keeping track of the last winner of a race. In other words, many users are updating the same data and the person who updates it last is the latest data. The data updated by other users will be lost. In this case, data could easily lead to inconsistency because of some network slowness when previously posted data arrives last.
Transaction in ADO.NET
ADO.NET providers a transaction class that represents a transaction. All data providers provide their own version of the transaction class. The IDbTransaction interface implements the basic functionality of the transaction class. All data provider-specific classes implement this namespace. Figure 5-43 shows some of the classes that implement IDTransaction.
Figure 5-43. Data provider specific classes that implement IDbTransaction
In the provider classes, a transaction is represented as an object returned after BeginTransaction is called on a Connection.
You can commit (Make permanent) or roll back (cancel and return to the original state) the transactions. Table 5-32 describes the methods for the OleDb provider's Transaction class. As discussed earlier, all data provider transaction classes provider's similar methods.
Table 5-32. Methods of the Transaction Class
|
METHOD |
DESCRIPTION |
|
Commit |
Commits the transaction to the database |
|
Rollback |
Rollbacks a transaction to the previous database state |
|
Begin(IsolationLevel) |
Begins a nested database transaction passing the isolation level |
An interesting thing to note in connecting to a database is the IsolationLevel, which allows you to lock your transaction in various ways. The default isolation level is ReadCommitted, which allows you to alter data during a transaction. If you use an isolation level of RepeatableRead locks are placed on all the data, so you can't alter the data in this transaction. If you lock at the Serializable level, locks are placed on the entire DataSet, preventing changes to all the data in the DataSet. Table 5-33 describes different isolation levels.
Table 5-33. Isolation Level Available for Transactions
|
ISOLATION LEVEL |
DESCRIPTION |
|
ReadCommitted (default) |
Locks are shared to prevent inconsistent reads between multiple users. Data can be altered during the transaction. |
|
ReadUncomitted |
Locks are not placed on the data on the data, so a dirty read is possible. |
|
RepeateableRead |
Locks are placed on all the data of the database query, so the data can't be altered during a read. |
|
Chaos |
The changes made on transactions awaiting commitment can't be altered. |
|
Serializable |
A range lock is placed on an entire DataSet preventing changes being made to the DataSet. |
|
Unspecified |
The IsolationLevel can't be determined. |
Concurrency in ADO .NET
The ADO.NET model assumes that the optimistic concurrency is the default concurrency because of its disconnected nature of data. A user reads data in a data through a data adapter, and data is available to user as a local copy of the data. The server database is available to all other users.
Even though database the ADO .NET model supports optimistic concurrency by default that doesn't mean you can't implement pessimistic concurrency in ADO .NET .the following two examples will show you both cases.
Listing 5-56 shows you how to implement optimistic concurrency. You can handle the optimistic concurrency by creating an Update command that checks the database to make sure the original data of the database row hasn't changed when an immediate update is about to be performed. It does this by creating two sets of parameters for the update command: a current set of parameters and an original set of parameters. The original parameters maintain the data that was originally read in from the Data Set. If the data has changed in the data source, when you run the Update command with the WHERE clause filter, the filter won't find the row and an update won't occur. If the data has not changed in the data source. Then the WHERE clause will find the original row you're updating, and the row will be updated with the new data. Listing 5-56 has a WHERE clause on the original data built in. the framework generates the parameters such as @Order Date and @ShippedDate. The framework even generates the Update command, but I shortened it, so could see it more clearly.
Note: To test these samples, create a windows application; add a reference to the System.Data and System.Data.Sql Client namespaces, add two buttons to the form, write code on the button event handler and make sure SQL server and running.
Listing 5-56 Optimistic concurrency example
private void TestOptimisticConcurrency()
{
try
{
string ConnectionString = "Integrated security = SSPI;" +
"Initial Catalog = Northwind;" +
"Data Source = localhost;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", conn);
DataSet ds = new DataSet("test");
SqlCommand updateCmd = new SqlCommand();
updateCmd.CommandText = @"UPDATE Orders SET CustomerID = @CustomerID," + "OrderData = @OrderDate, ShippedDate = @ShippedData WHERE " +
"(OrderID = @Original_OrderID)AND(CustomerID = @Original_CustomerID" +
"OR @Original_CustomerID IS NULL AND customer ID IS NULL) AND " +
"(OrderDate = @Original_OrderDate OR @Original_OrderDate " +
"IS NULL AND OrderDate IS NULL) AND (ShippedDate = " +
"@Original_ShippedDate OR @Original_ShippedDate IS NULL AND " +
"ShippedDate IS NULL); SELECT CustomerID, OrderDate, ShippedDate, " +
"OrderID FROM Orders WHERE (OrderID = @OrderID)";
updateCmd.Connection = conn;
// CustomerID parameter
updateCmd.Parameters.Add(new SqlParameter
("@CustomerID", SqlDbType.NVarChar, 5, "Customer ID"));
// orderDate Parameters
updateCmd.Parameters.Add(new SqlParameter
("@ order Date", SqlDbType.DateTime, 8, "OrderDate"));
// ShippedDate Parameter
updateCmd.Parameters.Add(new SqlParameter
("@ShippedDate", SqlDbType.DateTime, 8, "ShippedDate"));
updateCmd.Parameters.Add(new SqlParameter
("@original_OrderID", SqlDbType.Int, 4,
ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CustomerID",
DataRowVersion.Original, null));
updateCmd.Parameters.Add(new SqlParameter
("@Original_CustomerID", SqlDbType.NVarChar,
5, ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "CustomerID",
DataRowVersion.Original, null));
updateCmd.Parameters.Add(new SqlParameter
("@Original_OrderDate", SqlDbType.DateTime,
8, ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "order Date",
DataRowVersion.Original, null));
updateCmd.Parameters.Add(new SqlParameter
("@Original_ShippedDate", SqlDbType.DateTime,
8, ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ShippedDate",
DataRowVersion.Original, null));
updateCmd.Parameters.Add
(new SqlParameter("@OrderID", SqlDbType.Int,
4, "OrderID"));
da.UpdateCommand = updateCmd;
da.Fill(ds, "Orders");
// update the row in the dateset
ds.Tables["Orders"].Rows[0].BeginEdit();
ds.Tables["Orders"].Rows[0]["order Date"] = DateTime.Now;
ds.Tables["Orders"].Rows[0].EndEdit();
// update the row in the data source (Orders Table)
da.Update(ds, "Orders");
MessageBox.Show("Finished updating First row.");
// close connection
conn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
Another way of handling optimistic concurrency that you may be familiar with is by checking to see if a timestamp on the data source row has changed or the row version number has changed on the row being updated.
Pessimistic locking on the database isn't really supported by the data providers because the connection to the database is not kept open, so you must perform all locking with business logic on the DataSet.
You can do a form of pessimistic concurrency, however, using ADO.NET on the data source through transactions. The way to do this is to keep the connection open on the database and create a transaction that has a certain isolation level on a row. Listing 5-57 opens a connection and creates a transaction that locks out the rows in the update of the Orders table in the Northwind database.
Listing 5-57. Pessimistic concurrency example
void TestPessimisticConcurrency()
{
try
{
//Create a connection object
string ConnectionString = "Integrated security = SSPI;" +
"Initial Catalog = Northwind;" + "Data Source = localhost;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
// Create a transaction that locks the records of the query
SqlTransaction tr = conn.BeginTransaction
(IsolationLevel.RepeatableRead, "test");
//Create a command that updates the order of
//the database using the transaction
SqlCommand cmd = new SqlCommand("UPDATE Orders SET " +
"ShippedDate = '5/10/01', ShipCity = 'Columbus' WHERE" +
"OrderID = 10248", conn, tr);
// Execute the update
cmd.ExecuteNonQuery();
// Generate Message
MessageBox.Show("Wait for KeyPress….");
tr.Commit(); // Transaction is committed
conn.Close();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
Conclusion
Hope this article would have helped you in understanding Transactions and Concurrency in ADO.NET. See my other articles on the website on ADO.NET.
|
|
This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET. |