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 does not 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 longe R 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 can not be overwritten. SQL Server does not 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 can not be determined .......................... ..
