At the moment we are in the last phase of creating a new product that is built using Silverlight, WCF and the Entity Framework and the last couple of days we have been adding transaction support.

Because we need transaction support for both traditionel ADO.NET as well as the Entity Framework and because we need transactions that spans multiple datasources (and thereby multiple data access layers) we are using System.Transactions - and more specifically we are using TransactionScope for handling transactions.

When specifying the isolation level for Microsoft SQL Server it is often emphasised that because of a low degree of concurrency the Serializable IsolationLevel should only be chosen when necessary (see for example the MSDN article: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)). Actually you should of course only use locking as a whole when it is necessary ;^)

The Entity Framework supports optimistic concurrency. To enable optimistic concurrency the columns that are to be checked are specified by the use of the ConcurrencyMode attribute on the relevant properties in the conceptual model. According to the documentation (Saving Changes and Managing Concurrency (Entity Framework)), this should produce an OptimisticConcurrencyException when the concurrency check fails. But to our surprise it did not - it resulted in an UpdateException with an inner dead lock SqlException (the message of which was "Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."). The reason for this is simply that a TransactionScope has Serializable as its default IsolationLevel. Even though this is documented in various articles on MSDN (see e.g. Implementing an Implicit Transaction using Transaction Scope) it was not entirely obvious for us - probably because the TransactionScope help topic itself does not mention it and because we had the SQL Server recommendations in mind.

There are good reasons for TransactionScope to have Serializable as its default IsolationLevel, but the lack of consistency between SQL Server and TransactionScope is a bit misleading.