How does an atomic increment when the string may not exist?
Suppose I have MyContext
one derived from DbContext
c DbSet<Item> Items
where Item
is defined as follows.
public class Item
{
[Key]
public string Key { get; set; }
[ConcurrencyCheck]
public int Value { get; set; }
}
Given a key, I want to atomically increment the corresponding value when keys that are not yet in the table have an implicit value of 0. That is, atomically incrementing a key that is not in the table results in a value of 1 Here is my current approach:
public static async Task IncrementAsync(string key)
{
using (var context = new MyContext())
{
while (true)
{
var item = await context.Items.FindAsync(key);
if (item == null)
{
context.Items.Add(new Item { Key = key, Value = 1 });
}
else
{
item.Value++;
}
try
{
await context.SaveChangesAsync();
break;
}
catch (DbUpdateException)
{
continue;
}
}
}
}
This does not work with a blocking situation where many calls IncrementAsync
are not executed at the same time.
- What is the correct way to do this?
- If the loop
while
is outusing
, so each try gets a new context? I tried this and it makes everything work, but I feel like I am inefficient creating and destroying so many contexts. - Am I missing something about how the context track changes?
My database experience is mostly query driven, so if you could explain the finer details of what I am doing wrong in this code, I would really appreciate it.
Edit
Since the selected answer does not make it explicit, I posted the corrected code here. Please note that it is context
never reused after DbUpdateException
.
public static async Task IncrementAsync(string key)
{
while (true)
{
using (var context = new MyContext())
{
var item = await context.Items.FindAsync(key);
if (item == null)
{
context.Items.Add(new Item { Key = key, Value = 1 });
}
else
{
item.Value++;
}
try
{
await context.SaveChangesAsync();
break;
}
catch (DbUpdateException)
{
continue;
}
}
}
}
source to share
You need context to avoid sharing between attempts. If at all possible, don't do anything with the context that it had DbUpdateException
, as if you didn't explicitly clear the context, it can never return to normal.
I expect the outer context to cause problems. If concurrent calls to the same key occur over time, you might create a bad context setup (which will be constantly ignored due to your error handler.
If I am wrong, the fact that it Key
exists in the database will not remove the "add" version. You will get one of these contexts:
Add "1", 2
or
Add "1", 1
Update "1", 2
Depending on whether your second iteration is grabbing the first iteration object or a new one.
None of them can be successful, so you'll get a continuous error.
source to share
An alternative to the given answer is to use a stored procedure to do all the work, like in the following example. Then you can call it from your application in one line instead of the code above.
CREATE PROCEDURE SP_IncrementValue
@ValueKey NVARCHAR(100)
AS
BEGIN
BEGIN TRAN
UPDATE Item WITH (SERIALIZABLE) SET Value = Value + 1
WHERE [Key] = @ValueKey
IF @@ROWCOUNT = 0
BEGIN
INSERT Item ([Key], Value) VALUES (@ValueKey, 1)
END
COMMIT TRAN
END
GO
This approach gives you better performance and is less error prone.
Edit: To call the stored procedure from C #, add the following method to the EF ApplicationDbContext class
public int IncrementValue(string valueKey)
{
return this.Database.ExecuteSqlCommand("EXEC SP_IncrementValue @ValueKey", new SqlParameter("@ValueKey", valueKey));
}
Then you can call it from any instance of DBContext class
source to share