Maintaining Database Integrity

Introduction

Transactions allow a system to maintain integrity when interacting with multiple data sources. If an update to one data source fails, all changes are rolled back to a known good state. This chapter focuses on using transactions from ADO.NET, maintaining database integrity, and resolving conflicts and concurrency problems.

.NET supports both manual and automatic transactions. Manual transactions are supported through ADO.NET classes and the transactional capabilities of the data source. The Microsoft Distributed Transaction Coordinator (DTC) manages automatic transactions.

In a manual transaction, a transaction object is associated with a connection to a data source. Multiple commands against the data source can be associated with the transaction, grouping the commands together as a single transaction. Manual transactions can also be controlled using SQL commands in a stored procedure. Manual transactions are significantly faster than automatic transactions because they do not require interprocess communication (IPC) with the DTC. Manual transactions are limited to performing transactions against a single data source.

Automatic transactions are easier to program, can span multiple data sources, and can use multiple resource managers. They are significantly slower than manual transactions.

Concurrency problems occur when multiple users attempt to modify unlocked data. Possible problems include lost updates, dirty reads, nonrepeatable reads, and phantom reads. Isolation levels specify transaction locking behavior. Locking data ensures database consistency by controlling how changes made to data within an uncommitted transaction can be used by concurrent transactions. Higher isolation levels increase data accuracy at the expense of data availability. Recipe 6.12 shows how to use transaction isolation levels. Recipe 6.13 shows how to simulate pessimistic concurrency without database locks. Recipe 6.14 shows how to use pessimistic concurrency implemented using SQL Server database locks.

Even in well designed applications, concurrency violations often occur by design. The ADO.NET DataAdapter raises events that can be handled programmatically to resolve concurrency violations as required by application requirements. Recipe 6.10 shows how to use a timestamp to check for concurrency violations, while Recipe 6.11 shows how to resolve concurrency violations with DataAdapter event handlers.

A DataSet can contain both foreign key and unique constraints as well as relationships between tables to define and maintain data and referential integrity. The order in which DataRow changes from a DataSet containing hierarchical data are updated back to the data source is important to avoid referential integrity errors during the update process. ADO.NET allows data changes of a certain typeinserts, deletes, and updatesto be identified so that they can be processed separately as required. Recipe 6.6 shows how to update a DataSet containing hierarchical data back to the data source.