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 Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition), the isolation level required by an application determines the locking behavior that SQL Server Compact Edition uses.
SQL Server Compact Edition supports the following isolation levels (from lowest to highest):
- Read Committed (SQL Server Compact Edition 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 Edition 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.
See Also
Concepts
Transactions (SQL Server Compact Edition)
Locking (SQL Server Compact Edition)
Cursors (SQL Server Compact Edition)