transaction in dot net

• Phantoms Transaction 1 reads a set of rows returned by a specified WHERE clause. Transaction 2 then inserts a new row, which also happens to satisfy the WHERE clause of the query previously used byTransaction 1. Transaction 1 then reads the rows again using the same query but now sees the additional row just inserted by Transaction 2. This new row is known as a "phantom," because to Transaction 1, this row seems to have magically appeared.

• Nonrepeatable reads Transaction 1 reads a row, and Transaction 2 updates the same row just read by Transaction 1. Transaction 1 then reads the same row again and discovers that the row it read earlier is now different. This is known as a "nonrepeatable read," because the row originally read by Transaction 1 has been changed.

• Dirty reads Transaction 1 updates a row but doesn't commit the update. Transaction 2 reads the updated row. Transaction 1 then performs a rollback, undoing the previous update. Now the row just read by Transaction 2 is no longer valid (or it's "dirty") because the update made by Transaction 1 wasn't committed when the row was read by Transaction 2.

To deal with these potential problems, databases implement various levels of transaction isolation to prevent concurrent transactions from interfering with each other. The SQL standard defines four isolation levels, which are shown in Table 14.3. These levels are shown in order of increasing isolation.

Table 14.3: SQL Standard Isolation Levels

ISOLATION LEVEL	DESCRIPTION
READ UNCOMMITTED	Phantoms, nonrepeatable reads, and dirty reads are permitted.
READ COMMITTED	Phantoms and nonrepeatable reads are permitted, but dirty reads are not. This is the default for SQL Server.
REPEATABLE READ	Phantoms are permitted, but nonrepeatable and dirty reads are not.
SERIALIZABLE	Phantoms, nonrepeatable reads, and dirty reads are not permitted. This is the default for the SQL standard.

SQL Server supports all of these transaction isolation levels. The default transaction isolation level defined by the SQL standard is SERIALIZABLE, but the default used by SQL Server is READ COMMITTED, which is acceptable for most applications.

Warning

When you set the transaction isolation level to SERIALIZABLE, any rows you access within a subsequent transaction will be "locked," meaning that no other transaction can modify those rows. Even rows you retrieve using a SELECT statement will be locked. You must commit or roll back the transaction to release the locks and allow other transactions to access the same rows. Use SERIALIZABLE only when you must ensure that your transaction is isolated from other transactions. You'll learn more about this later in the section "Understanding SQL Server Locks."

In addition, ADO.NET supports a number of transaction isolation levels, which are defined in the System.Data.IsolationLevel enumeration. Table 14.4 shows the members of this enumeration.

Table 14.4: IsolationLevel Enumeration Members

ISOLATION LEVEL	DESCRIPTION
Chaos	Pending changes from more isolated transactions cannot be overwritten. SQL Server doesn't support this isolation level.
ReadCommitted	Phantoms and nonrepeatable reads are permitted, but dirty reads are not. This is the default.
ReadUncommitted	Phantoms, nonrepeatable reads, and dirty reads are permitted.
RepeatableRead	Phantoms are permitted, but nonrepeatable and dirty reads are not.
Serializable	Phantoms, nonrepeatable reads, and dirty reads are not permitted.
Unspecified	A different isolation level than the one specified is being used, but the level cannot be determined. SQL Server doesn't support this isolation level.

In the next two sections, you'll learn how to set the transaction isolation level using T-SQL and a SqlTransaction object.