Transactions

Transactions are a key concept when working with databases. You cannot avoid working with transactions as soon as concurrent data accesses occur. In a nutshell a transaction is the encapsulation of all partial operations that belong to a certain activity so that the activity appears as an atomic activity.

Let’s use a simple example. Suppose, you want to create an object and write it back to the database. In the case of autonumbered integer id’s, storing the data does not only mean that the object data is transmitted to the database. The database also creates a new id for the new data row and returns this id back to the caller. In ADO.NET this does not happen automatically. The database has to be explicitly queried for the Id, as shown in the following example for SQL server:

INSERT Into Employee ......;

SELECT Id from Employee WHERE Id = %%IDENTITY;

The keyword identity instructs SQL server to return the Id of the row that was last inserted in the current transaction. If such inserts are not encapsulated in a transaction, they can get a mixed up identity:

// Command from the first user

INSERT Into Employee ......; 

// Command from the second user

INSERT Into Employee ......; 

// The next command from first user, receives wrong Id

SELECT * from Employee WHERE Id = %%IDENTITY;

// Another command from second user, also receives wrong Id

SELECT * from Employee WHERE Id = %%IDENTITY;

The result is an error that is very hard to find: a wrong id is returned. The bottom line of this sample is: You need a transaction for inserting a single data row. The commands belonging to a transaction run in a transaction context. That ensures that commands from other transactions cannot interfere. But what exactly does »not interfering« mean? Do we want to disallow that someone else can change data we are currently changing? Or does it mean someone else must not change data we have read without the intent to change it? The decision is up to you and depends on your application. You decide it by selecting a certain Isolation Level. ADO.NET supplies the enumeration IsolationLevel to define the Isolation Level. This enumeration is used by NDO too. The implementation of the isolation levels is up to the database that is accessed with ADO.NET.

The duration of a transaction needs to be specified too. An optimistic transaction lasts only for the period it takes to store changes. A pessimistic transaction starts with the first reading of data and lasts until the data is saved.