Dirty read concept in Entity Framework using C#

Question

I am working on Web API using Entity Framework and C#. I need solution for dirty reads.

I have tried below method and also transaction method, I need solution for dirty reads.

Dirty reads or phantom reads is a phenomenon where a user is not updating the latest data.

Let's say user A has opened salesman web page

user B also opened the same salesman web page

A has loaded salesman ID 1001 and B also loaded 1001 A changed salesman name to X and saved.

But B is seeing old data and if B changes the salesman name to Y, A's changes will be overwritten. So we should prevent B from writing the changes to DB.

So I need solution based on the above concept.

using (var transaction = db.Database.BeginTransaction())
{
    try
    {
        db.SaveChanges();
        transaction.Commit();
    }
    catch (Exception excp)
    {
        throw excp;
    }
}

return Ok();

Below is the code that I have tried

using (var transaction = db.Database.BeginTransaction())
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }

    KSTU_COUNTER_MASTER kcm = new KSTU_COUNTER_MASTER();
    kcm.obj_id = Common.GetNewGUID();
    kcm.company_code = Common.CompanyCode;
    kcm.branch_code = Common.BranchCode;
    kcm.counter_code = c.CounterCode;
    kcm.counter_name = c.CounterName;
    kcm.Maincounter_code = c.MaincounterCode;
    kcm.obj_status = c.ObjectStatus;
    kcm.UpdateOn = Framework.Common.GetDateTime();
    kcm.UniqRowID = Guid.NewGuid();

    db.KSTU_COUNTER_MASTER.Add(kcm);

    try
    {
        db.SaveChanges();
        transaction.Commit();
    }
    catch (Exception excp)
    {
        throw excp;
    }
}

return Ok();

That's what the `ROWVERSION` column type in SQL Server (and similar constructs for other platforms) are for: both clients read the row version with the core data, and when they go to update, SQL Server will automatically increase the row version. Both clients must **check** before update if the row in question still has the row version they have read - if **not** (like in the case of user `B` in your scenario), then the update **must not** be applied (also called "optimistic concurrency handling") — marc_s, Aug 05 '19 at 06:20
Read more about [SQL Server's `ROWVERSION` here](https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017) and about [Optimistic Concurrency Control](https://en.wikipedia.org/wiki/Optimistic_concurrency_control) in general — marc_s, Aug 05 '19 at 06:21
thank you for your answer .. i know the concept but please help me with solution code .. — kusuma rajanna, Aug 05 '19 at 06:51
This is not a dirty read. A dirty read is when a user is allowed to read data that is uncommitted in a second transaction. SQL service default is READ COMMITTED isolation so unless you've changed the transaction isolation level you cannot have dirty reads. you are talking about optimistic concurrency. — Fran, Aug 06 '19 at 19:18
Here's a tutorial on [optimistic concurrency](https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application) in ef6 — Fran, Aug 06 '19 at 19:19

score 0 · Answer 1 · answered Aug 07 '19 at 12:10

Let's say user A has opened salesman web page user B also opened the same salesman web page A has loaded salesman ID 1001 and B also loaded 1001 A changed salesman name to X and saved.

The comments are correct that this is a problem that requires client-side Optimistic Concurrency checks, not a database transaction. But I wanted to explain why that is.

You actually could use a transaction here, in SNAPSHOT, REPEATABLE READ, or SERIALIZABLE isolation level. A and B would both be able to open the same salesman page and peform the edits, but whichever one tried to save last would cause an error (This error might be a deadlock depending on the isolation level). But the write-over-write anomaly would be prevented.

However to make that work you would have to keep the database connection, and the transaction open from the time each user navigated to the "salesman web page" to the time the user saved the data. And in a web application, you have no information about or control over how long that is. In a client-server application you can sort of make this work, but in web apps this approach was almost universally abandoned in favor of client-side optimistic concurrency.

Dirty read concept in Entity Framework using C#

1 Answers1