| The second check box, Use Optimistic Concurrency, determines how the wizard will create the Insert and Update SQL statements. ADO .NET does not fully rely on SQL Server (or other database system) record locking to implement concurrency. For those who may not be used to working in multiuser environments, concurrency is the way a database system handles conflicts between users. For example, user A reads some data. User B reads the same data. What happens when users A and B make changes and try to update the data at the same time? The way a database system handles these situations is called concurrency management. Most systems use some combination of row locking and row versioning to manage concurrency. Row locking is the oldest way of managing concurrency. Also called pessimistic concurrency, row locking places a logical lock on the data when the first user reads it. Typically, the user can place a read lock or a write lock on the row. A read lock gives the first user exclusive access to the row. No other user can read the same row until the first user releases the lock. With a write lock, other users can read the row, but they cannot update the row until the first user releases the lock. The problem with this is that if the second user updates the row, the first user 's updates are overwritten. This is known as the-last-one-wins rule. With optimistic concurrency, the row is never locked when it is read. Instead, row versioning is used to determine if any changes have been made to the row since it was last read by user A. If user B changed the record after user A read it, the row version would change and user A would know that the record changed. The row is only locked when the update begins, and it is released immediately after the update is complete. This minimizes the time any one row is actually locked. The theory behind optimistic concurrency is based on the statistical probability that any given row is normally read many more times than it is updated, and that the odds of any two users updating the same row at almost the same time is relatively low. The downside of optimistic concurrency is that it requires the row to be read a second time just before it is updated. This read is required to check the row version. This will cause a slight increase in network traffic. As a general best practice, use optimistic concurrency when updates are relatively infrequent. This would include almost all online interactive applications, including Windows GUI and web applications. Pessimistic concurrency is better used for batch type operations where there is no user interaction because performance is usually better. Since batch jobs run fast, the time any given row is locked is usually minimized and the system doesn't have to read the row twice for each update. The caveat here is that ADO .NET does not support pessimistic concurrency. You can use other techniques to achieve similar results. Stored procedures and dynamic SQL statements both achieve high performance for batch jobs. Now that we understand general concurrency management, we can look at how ADO .NET handles it. ADO .NET uses a combination of row locking (on servers that support it) and row versioning. The row versioning it uses is implemented in code in the DataSet and the DataAdapter. The DataSet does its part by storing both the original row values, which are never changed, and the current row values, which may change programmatically due to a user editing a bound control. We will look at this later on in the chapter on DataSets. The DataAdapter does its part by creating Insert, Update, and Delete statements that use each value in the original select clause in the where clause of the query. This way, if any data changed since the row was read, the where clause will fail and the data will not be updated. The DataAdapter uses the original values stored in the DataSet as parameters for the where clause, and the changed (or current) values in the DataSet as the parameters used in the update (set clauses for Update statements and value lists for Insert statements) clauses of the SQL statements. When I first saw this, I was confused by why it was using all of the columns in the original Select statement as where clause parameters. I thought that this must be very inefficient. Would I have to index all the columns in each table? I was partially right. It can be very inefficient on a poorly designed database. As long as there is a unique key included in the select clause, it should be okay. I have not done any testing on this. As a standard practice, I always include a primary key in my tables that is not dependent on the data in the row. I use an identity column with autoincrement as my primary key on all my tables. That way, I always have a unique row identifier. I always include this key in my select clauses. There are other ways of checking row versions. You could use a time stamp column, which gets updated automatically each time a row is changed. You could then use this column to make your comparison. It seems that this would be more efficient than checking each column value. For the purposes of this book, we'll stick with the way the DataAdapter handles it. In a minute we'll dissect the SQL statements the DataAdapter generates using our sample application. First, let's look at the last check box, Refresh the DataSet. What this does is place a copy of the Select statement used to create the update queries immediately after the respective Insert or Update statement. This Select statement is then called to cause an automatic refresh of any DataSet that is referenced in the call to the DataAdapter's Update method. This is another area with which to be very careful. You could generate a lot of network traffic if you are refreshing large DataTables each time a single row is updated. It has always been a best practice to keep your returned row counts as low as possible. This has never been truer than with ADO .NET. |
