Hi!
I would like to know if there is a way to change current transaction isolation level without starting transaction. What I need is actually to send this statement to the DB:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
We are using LLBLGen v2.0.0.61107 in .NET 2.0 with MS SQL Server 2000/2005, Adapter templates. ASP.NET 2.0 for user interface, console/services for background processing.
We have a potentially large database (both in size and number of rows) with a lot of read/write activity. In order to prevent at least part of the most visible dead locks we decided to lower the isolation level on a few specific list pages by reading data in a READ UNCOMMITTED transaction (since setting NOLOCK on DQE would affect all queries):
_transactionIsolationLevel = IsolationLevel.ReadUncommitted;
...
adapter.StartTransaction(_transactionIsolationLevel, "AppListPageFetchTypedList");
adapter.FetchTypedList(fields, listData, filter, pageLength, sort, true, null, pageNumber, pageLength);
adapter.Commit();
adapter.CloseConnection();
The SQL Server database has a default isolation level set to READ COMMITTED so other lists (and details) pages honour any locks and display only committed data when it is necessary.
We used this technique to tweak the performance of the application while using ASP.NET and LLBLGen v.1.0.2005.something with quite nice results.
Unfortunately this is not the case in .NET 2.0. After we fetch the data in the read uncommitted transaction, all subsequent data operations use the same isolation level. Only after we open a page with a new isolation level set specifically to that of read committed (again while performing data operation in transaction), new level is set.
I suspect this may be a result of some new features in ADO.NET 2.0 specifically MARS and connection pooling/sharing but at the moment I have no solution or workaround for this problem. Also – performing all selects in transactions is something I would rather not do.
As I’ve mentioned above – sending the SQL commend to set the transaction isolation level would be enough.
Have anyone encountered this problem?
Thanks for any help and/or suggestions.
Jakub
