Isolation Levels
When locking is used as the transaction control mechanism, it can solve concurrency problems. Locking permits all transactions to run in complete isolation from one another, although more than one transaction can run at any time. The level at which a transaction is prepared to accept inconsistent data is known as the isolation level. An increased isolation level offers more protection against data inconsistency, but the disadvantage is a decrease in concurrency.
In SQL Server Compact 3.5, the isolation level required by an application determines the locking behavior that SQL Server Compact 3.5 uses.
SQL Server Compact 3.5 supports the following isolation levels (from lowest to highest):
- Read Committed (SQL Server Compact 3.5 default)
- Repeatable Read
- Serializable (transactions are completely isolated from one another)
As the isolation level increases, the data gains more protection against data inconsistency. However, this lowers concurrency of the transaction and affects multiuser access.
Note
SQL Server Compact 3.5 ensures that one transaction cannot read the uncommitted data of another transaction (dirty read). This is achieved by using a page versioning mechanism that lets users read data without requesting S locks on the data. This offers exceptional concurrency for read (SELECT) operations.
Isolation Levels and Concurrency Problems
The following table shows the levels of isolation and the concurrency problems that the levels address.
Note
Concurrency problems that can occur include uncommitted dependency, inconsistent analysis, and phantom reads. For more information about concurrency problems, see Concurrency.
| Isolation Level | Uncommitted Dependency (Dirty Reads) | Inconsistent Analysis (Nonrepeatable Reads) | Phantom Reads |
|---|---|---|---|
Read Committed |
No |
Yes |
Yes |
Repeatable Read |
No |
No |
Yes |
Serializable |
No |
No |
No |
The isolation levels can be set either programmatically or by using the SQL syntax SET TRANSACTION ISOLATION LEVEL. For more information, see Transaction Isolation Level.
Isolation Level Under Transaction Scope
The default isolation level for System.Transaction is Readcommitted. However, by default, a transaction in a transaction scope executes with isolation level set to Serializable. Therefore, when a SQL Server Compact 3.5 connection is enlisted to a transaction scope, the implicit transaction (which is a SQL Server Compact 3.5 transaction created internally for transaction scope) by default runs at Serializable isolation level. Some of the overloaded constructors of TransactionScope class accept isolation level as parameter. These should be used to specify a different value of isolation level for the implicit transaction. If the specified isolation level is not supported by SQL Server Compact 3.5, enlisting a SQL Server Compact 3.5 connection to a transaction scope will throw an exception.
Also, the property default lock timeout which is specified as part of the connection string, governs the time a transaction will wait. However, when a connection is enlisted to a TransactionScope, the TimeSpan of the TransactionScope overrides this property. For example, if the ConnectionString contained 1000 ms as the value of default lock timeout and the connection is enlisted in a TransactionScope of 100 ms, the ambient transaction will last for only 100 ms and not 1000 ms. This means that if you want the application to have longer transaction timeouts and run commands in a TransactionScope, you will have to specify the time-out value in both the connection string and the TimeSpan for the TransactionScope.
Note
The default value of default lock timeou" is 2000ms and that of a TransactionScope is 1 minute.
See Also
Concepts
Transactions (SQL Server Compact)
Locking (SQL Server Compact)
Cursors (SQL Server Compact)