1

Is there any side effect for this code:


///This code runs per request 
public static MyObjectContext CreateEntity()
{
  MyObjectContext db=new MyObjectContext();

  db.Connection.Open();
  var con = (SqlConnection)((EntityConnection)hi.Connection).StoreConnection;
  SqlCommand cmd = new SqlCommand("set transaction isolation level read uncommitted",con);
  cmd.ExecuteNonQuery();

  return db;
}

Now "db" instance will run ReadUncommited mode?

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
Serhat Dincel
  • 195
  • 1
  • 3
  • 12

1 Answers1

0

The side effect is that you must handle your connection manually and that the connection is held by the context for whole its lifetime. It is also kept opened for the whole lifetime of the context which can be less efficient.

Another side effect is read uncommited isolation level itself which is quite dangerous to use because it allows reading uncommited transactional data.

Why do you need this? EF by default uses default transaction isolation mode of the database server. For SQL server it is read commited. EF also by default doesn't hold locks on records because each read operation is only part of a transaction with duration just for that read. Only SaveChanges uses transaction for multiple database commands.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • I have a table (User_Notification). The table has heavy insert load. – Serhat Dincel Apr 26 '11 at 17:18
  • So what about using read uncommited only when accessing this table? – Ladislav Mrnka Apr 26 '11 at 17:35
  • I have been struggling with the same issue for quite a few hours now. Our DB team insists on setting the isolation level to READ UNCOMMITTED. I'm doing that at the context level using a store command. However, subsequent queries fail giving me the following error. 'You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.' Some query is using a READPAST lock by default and I'm not seeing that query in the profiler. Any ideas? – Praveen Apr 27 '11 at 01:31
  • Turns out one of the Views had a READPAST in it. Duh! – Praveen Apr 27 '11 at 15:17