ADO.NET in a Nutshell

Name

IsolationLevel

Synopsis

This enumeration specifies the isolation level that is used for a transaction. The isolation level is a measure of the degree that a transaction is isolated from other database activity. Higher isolation levels provide better data integrity, but they also slow performance because of required locking. For example, the highest isolation level, Serializable, places a lock on all the tables a transaction accesses, which prevents other users from updating or inserting rows while the transaction is in process. Their requests are still processed when the transaction ends, provided their commands don’t time out. RepeatableRead is the next highest isolation level; it uses locking to prevent another user from updating or deleting the rows that are being used in the transaction, but it doesn’t guarantee that new rows won’t be inserted. ReadCommitted is often a good compromise with shared locks held while the data is being read, thereby avoiding dirty reads (reads that retrieve information from a transaction that has not yet been committed). However, the data can be changed by another user before the end of the transaction, resulting in nonrepeatable reads or phantom data. This is SQL Server’s default. ReadUncommitted doesn’t use any locking, and dirty reads are possible.

You specify the isolation level when creating a transaction with the IDbConnection.BeginTransaction( ) method. Not all providers support all levels (for example, Chaos isn’t supported by SQL Server). ...