Recently I am working on Entity Framework as data access layer based Project. We found many performance issues with this application.
IntroductionRecently I was working with the Entity Framework as a data access layer based project. We found many performance issues with this application. We encountered some queries that created a deadlock or that require more time to execute because of some other select query created a lock on the table. Generally a deadlock is a situation when two or more tasks are blocking each other by each having a lock on a resource that the other is trying to lock.SQL Transaction Isolation Level and LockIsolation Level defines the degree to which one transaction must be isolated from another transaction that makes changes in the data.There are mainly four types of Isolation Levels available with SQL Server.Read uncommitted
This is the lowest Isolation Level. There is no shared lock on the request, it allow us to read the data being modified by another transaction. In other words, we can read values that are modified by another transaction before they are committed to the SQL Server.Read committed (default Isolation Level)
This is the default Isolation Level of SQL Server. It only retrieves committed data. Here there is a shared lock on the request; there is an exclusive lock on data while it is modified by the other transaction. In other words, modified data will not be visible within another transaction until it is committed to the database. All queries will wait until the transaction is completed and the lock is released.Repeatable ReadThis is similar to Read Committed Isolation Level. This maintains a shared lock on the record and it reads this record until the transaction is not finished. In other words, if any transaction tries to modify records then it is forced to wait for a read transaction to complete. There is a phantom read in this type of transaction Isolation Level because SQL Server locks the row it reads but does not prevent insertion of new rows. Repeatable read Isolation Level does not guarantee that a query result is always the same but it does guarantee that the rows that have been read are locked and no other transaction can modify it.Serializable (highest level)Serializable is very close to repeatable read Isolation Level but it prevents the phantom rows. Serializable Isolation Level is an applied range lock so that an other transaction cannot insert a new row within this row. That prevents phantom rows.Additionally Snapshot Isolation Level is introduced in SQL Server 2005. Snapshot Isolation Level does not apply a lock while reading a row but it does not read uncommitted rows or data. In other words, if we read rows from any table and another transaction is trying to modify rows then snapshot Isolation Level reads the last committed data on that row.Problem statementImplementing NOLOCK in LINQ to Entities QueryHow to prevent LINQ to Entity Query blocking writer from reader and vice versa?SolutionWe have many ways to prevent query blocking. Here I am only discussing how to prevent deadlock in Entity Framework. Entity Framework is never an introduced transaction Isolation Level on queries. Entity Framework uses SQL Server transaction Isolation Level by default.
Entities entities = new Entities();entities.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
//With DBContext
Var entities = newDBContext();
Entities. Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
Conclusion Using the methods described above we can prevent deadlock. We can also use Snapshot Isolation Level at transaction scope. It is a good idea to use Snapshot Isolation Level since then there is no lock placed on a data row when it is read, so snapshot transaction never blocks another transaction.
Basic SQL Queries