Transaction Isolation Level

Microsoft SQL Server Compact 4.0 operates at an isolation level of Read Committed by default. However, an application might need to operate at a different isolation level. To implement different isolation levels in applications, you can customize locking for an entire session by setting the isolation level of the session with the SET TRANSACTION ISOLATION LEVEL statement.

Important

Although the default isolation level in SQL Server Compact 4.0 is Read Committed, using this isolation level does not result in S locks being taken when data is read. This behavior is unlike Microsoft SQL Server. In SQL Server, when using Read Committed, an S lock is requested whenever a row is read, and this will wait if there is a conflicting lock on that row. SQL Server Compact 4.0 does not require an S lock because versions of data pages are automatically maintained to ensure that committed data can be read without the need to take a lock. This is important because, in SQL Server Compact 4.0, SELECT operations do not need to take any locks on the data and almost always succeed. SELECT operations will not wait if some data has an X lock, as opposed to SQL Server. The SELECT operation still requires a Sch-S lock. The operation will fail only if the table is being modified, because a conflicting Sch-X lock will exist.

When the isolation level is specified, the locking behavior for all SELECT statements in the SQL Server Compact 4.0 session operates at that isolation level and remains in effect until the session terminates, or until the isolation level is set to another level. For example, to set the transaction isolation level to Serializable, and to ensure that no phantom rows can be inserted by concurrent transactions into the Employee table, use the following SQL statement:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT EmployeeID FROM Employee;

You can also set the transaction isolation level programmatically. The following is an example of how to set transaction isolation level by using ADO .NET:

SqlTransaction myTrans;

myTrans = myConnection.BeginTransaction(IsolationLevel.RepeatableRead);

See Also

Concepts

Understanding Locking

Displaying Locking Information

Locking Hints

Lock Time-out