Export (0) Print
Expand All
Expand Minimize

IsolationLevel Enumeration

Specifies the transaction locking behavior for the connection.

Namespace:  System.Data
Assembly:  System.Data (in System.Data.dll)

type IsolationLevel

Member nameDescription
Supported by the XNA FrameworkChaosThe pending changes from more highly isolated transactions cannot be overwritten.
Supported by the XNA FrameworkReadCommittedShared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
Supported by the XNA FrameworkReadUncommittedA dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
Supported by the XNA FrameworkRepeatableReadLocks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
Supported by the XNA FrameworkSerializableA range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.
Supported by the XNA FrameworkSnapshotReduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.
Supported by the XNA FrameworkUnspecifiedA different isolation level than the one specified is being used, but the level cannot be determined.

When using OdbcTransaction, if you do not set IsolationLevel or you set IsolationLevel to Unspecified, the transaction executes according to the isolation level that is determined by the driver that is being used.

The IsolationLevel values are used by a .NET Framework data provider when performing a transaction.

The IsolationLevel remains in effect until explicitly changed, but it can be changed at any time. The new value is used at execution time, not parse time. If changed during a transaction, the expected behavior of the server is to apply the new locking level to all statements remaining.

This application demonstrates how to use IsolationLevel in DbTransaction. The sample will demonstrate which of the following behaviors are allowed in the different isolation levels:

  • Dirty reads.

  • Non-repeatable reads.

  • Phantoms.

This application will execute in the following isolation levels:

  • ReadUncommitted

  • ReadCommitted

  • RepeatableRead

  • Serializable

  • Snapshot

The PhantomReadThreads class demonstrates if the specific transaction allows the Phantom Read behavior. If the transaction allows the behavior, the threads will operate in the following order:

  • In first thread, select the products(All).

  • In the second thread, insert a new product.

  • Commit the transaction in second thread.

  • Select the products again.

  • Commit the transaction in first thread.

If the transaction allows the behavior, the two Select operations will get the different results.

The NonrepeatableReadThreads class demonstrates if the specific transaction allows the Nonrepeatable Read behavior. If the transaction allows the behavior, the threads will operate in the following order:

  • In first thread, select the product(ProductId=1).

  • In the second thread, update the Quantity value(ProductId=1).

  • Commit the transaction in second thread.

  • Select the product again.

  • Commit the transaction in first thread.

If the transaction allows the behavior, the two Select operations will get the different results.

The ExchangeValuesThreads class demonstrates the difference between the Serializable and Snapshot transaction. For the the Serializable transaction, threads will operate in the following order:

  • In first thread, get the Price of product(ProductId=2) and store in the variable.

  • In first thread, update the Price of product(ProductId=1) with the price of product(ProductId=2).

  • Commit the transaction in first thread.

  • In second thread, get the Price of product(ProductId=1) and store in the variable.

  • In second thread, update the Price of product(ProductId=2) with the price of product(ProductId=1).

  • Commit the transaction in second thread.

Now the values of the Price(ProductId=1 and ProductId=2) are as same as the original Price of Product(ProductId=2).

For the Snapshot transaction, threads will operate in the following order:

  • In first thread, get the Price of product(ProductId=2) and store in the variable;

  • In first thread, update the Price of product(ProductId=1) with the price of product(ProductId=2).

  • In second thread, get the Price of product(ProductId=1) from the snapshot and store in the variable.

  • In second thread, update the Price of product(ProductId=2) with the price of product(ProductId=1).

  • Commit the transaction in second thread.

  • Commit the transaction in first thread.

Now exchange the Price of products(ProductId=1 and ProductId=2).

The DirtyReadThreads class demonstrates if the specific transaction allows the Dirty Read behavior. If the transaction allows the behavior, the threads will operate in the following order:

  • In first thread, begin a transaction and add the Quantity value(ProductId=1).

  • In the second thread, read the Quantity value and add the value again.

  • Commit the transaction in second thread.

  • Roll back the transaction in first thread.

If the transaction allows the behavior, the Quantity value will be added twice.

C# and Visual Basic projects with this code sample can be found on Developer Code Samples.

No code example is currently available or this language may not be supported.

.NET Framework

Supported in: 4.6, 4.5, 4, 3.5, 3.0, 2.0, 1.1, 1.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Show:
© 2014 Microsoft