Clever Geek Handbook

Is it possible to modify data in a reusable read isolation transaction?

I have some .NET code wrapped in a repeatable read transaction that looks like this:

using (
                var transaction = new TransactionScope(
                    TransactionScopeOption.Required,
                    new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead },
                    TransactionScopeAsyncFlowOption.Enabled))
            {
                int theNextValue = GetNextValueFromTheDatabase();
                var entity = new MyEntity
                               {
                                   Id = Guid.NewGuid(),
                                   PropertyOne = theNextValue, //An identity column
                                   PropertyTwo = Convert.ToString(theNextValue),
                                   PropertyThree = theNextValue,
                                   ...
                               };
                DbSet<MyEntity> myDbSet = GetEntitySet();
                myDbSet.Add(entity);
                await this.databaseContext.Entities.SaveChangesAsync();

                transaction.Complete();
            }

The first method GetNextValueFromTheDatabase

retrieves the maximum value stored in a table column in the database. I use repeatable read because I don't want two users to read and use the same value. Then I just create Entity

in memory and call SaveChangesAsync()

to write the values ​​to the database.

Sporadically, I see entity.PropertyOne, entity.PropertyTwo and entity.PropertyThree values ​​do not match. For example entity.PropertyOne is 500, but entity.PropertyTwo and entity.PropertyThree are 499. How is this possible? Even if the code was not transacted, I would expect the values ​​to match (perhaps they will be duplicated across all objects if two users are working at the same time).

I am using Entity Framework 6 and Sql Server 2008R2.

Edit:
Here is the code forGetNextValueFromTheDatabase 

public async Task<int> GetNextValueFromTheDatabase()
{
    return await myQuerable
        .OrderByDescending(x => x.PropertyOne) //PropertyOne is an identity column (surprise!)
        .Select(x => x.PropertyOne)
        .Take(1)
        .SingleAsync() + 1;
}
+3


source to share


3 answers


I finally figured it out. As described in usr's answer , multiple transactions can read the same maximum value (S-Lock) at the same time. The problem was that one of the columns is this identity

. EF allows you to specify the value of the id column on insert, but ignores the value you specify. So the identity column would generally update with the expected value most of the time, but in reality the value specified in the domain object just matches what the backend database was generating.

So let's say the current maximum is 499, transaction A and transaction B both read 499. When transaction A completes, it successfully writes 500 to all three properties. Transaction B tries to write 500 to all 3 columns. Columns that are not IDs successfully update to 500, but the ID column is automatically incremented to the next available value (without throwing an error).

Several solutions



The solution I have used is to not set a value for any of the columns when I insert a record. After the record is inserted, update the other two columns with the value of the ID column of the database column.

Another option is to change the column setting to .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)

... which will perform better than the first option, but would require the changes suggested to mitigate locking issues.

0


source


So this question cannot be definitively answered because it is GetNextValueFromTheDatabase

not displayed. I'm moving away from what you said it does:

REPEATABLE READ

in SQL Server S-locks the rows you read. When you read the current high, presumably from an index, that row is S-locked. Now, if a new maximum appears, this line will not be blocked by a lock. Therefore, locking does not prevent other competing maximum values ​​from occurring.

You want SERIALIZABLE

isolation if you get the most by reading the largest values ​​from the table. This will lead to deadlocks in your particular case. This can be resolved by blocking hints or attempts.



You can also keep a separate table that stores the current maximum value. REPEATABLE READ

here is enough, because you always access the same row of this table. You will see dead ends here and also even with REPEATABLE READ

no fixing prompts.

Repetitions are a sane solution for deadlocks.

0


source


I think you are basically phantom reading experience .

Consider two transactions T1, T2 that are meant to be executed as shown below. The point is, on the first read of T1, you don't get the value (X) that was inserted from transaction T2. The second time you get the value (X) in your select statement. This is the scary nature of repetitive reading. It does not block insertion on the whole table if some rows are read from it. It only blocks existing lines.

T1                                     T2

SELECT A.X FROM WeirdTable

                                       INSERT INTO WeirdTable TABLE (A) VALUES (X)
SELECT A.X FROM WeirdTable

...

UPDATE

It seems that this answer turned out to be unrelenting for this particular question. This is due to a repeatable read isolation level, matches the keywords of this question, and is not actually wrong, so I'll leave it here.

0


source






More articles:


All Articles