C#•13mo ago

❔ updating row sometimes times out EF core 7

this has been confusing me lately, i was going thru my logs and i noticed sometimes my endpoint returns a 500. I have looked thru the logs and found the query which is causing it.

await _dbContext.Servers.Where(s => s.Id == id).ExecuteUpdateAsync(s => s.SetProperty(e => e.HitsReceived, e => e.HitsReceived + 1), cancellationToken: cancellationToken);

await _dbContext.Servers.Where(s => s.Id == id).ExecuteUpdateAsync(s => s.SetProperty(e => e.HitsReceived, e => e.HitsReceived + 1), cancellationToken: cancellationToken);

its not every time but sometimes. the reason why I use this EF Linq query is because the servers rows get hit sometimes at the same time and the hitsReceived needs to be thread safe (locking). this is an error from serilog

{
  "Timestamp": "2023-11-02T22:08:57.6615548+00:00",
  "Level": "Error",
  "MessageTemplate": "Failed executing DbCommand ({elapsed}ms) [Parameters=[{parameters}], CommandType='{commandType}', CommandTimeout='{commandTimeout}']{newLine}{commandText}",
  "Properties": {
    "elapsed": "30,015",
    "parameters": "@__id_0='?' (DbType = Int64)",
    "commandType": "Text",
    "commandTimeout": 30,
    "newLine": "\n",
    "commandText": "UPDATE servers AS s\nSET hits_received = s.hits_received + 1\nWHERE s.id = @__id_0",
    "EventId": {
      "Id": 20102,
      "Name": "Microsoft.EntityFrameworkCore.Database.Command.CommandError"
    },
    "SourceContext": "Microsoft.EntityFrameworkCore.Database.Command",
    "ActionId": "63acc236-57b2-4e9e-919c-839e796d6c7f",
    "ActionName": "xxxx.Web.Controllers.External.ReportController.Report (AbuseBeacon.Web)",
    "RequestId": "0HMURTMAQ7GN8:00000001",
    "RequestPath": "/v1/xxxx",
    "ConnectionId": "0HMURTMAQ7GN8"
  }
}

{
  "Timestamp": "2023-11-02T22:08:57.6615548+00:00",
  "Level": "Error",
  "MessageTemplate": "Failed executing DbCommand ({elapsed}ms) [Parameters=[{parameters}], CommandType='{commandType}', CommandTimeout='{commandTimeout}']{newLine}{commandText}",
  "Properties": {
    "elapsed": "30,015",
    "parameters": "@__id_0='?' (DbType = Int64)",
    "commandType": "Text",
    "commandTimeout": 30,
    "newLine": "\n",
    "commandText": "UPDATE servers AS s\nSET hits_received = s.hits_received + 1\nWHERE s.id = @__id_0",
    "EventId": {
      "Id": 20102,
      "Name": "Microsoft.EntityFrameworkCore.Database.Command.CommandError"
    },
    "SourceContext": "Microsoft.EntityFrameworkCore.Database.Command",
    "ActionId": "63acc236-57b2-4e9e-919c-839e796d6c7f",
    "ActionName": "xxxx.Web.Controllers.External.ReportController.Report (AbuseBeacon.Web)",
    "RequestId": "0HMURTMAQ7GN8:00000001",
    "RequestPath": "/v1/xxxx",
    "ConnectionId": "0HMURTMAQ7GN8"
  }
}

im using postgresql

13 Replies

JakenVeina•13mo ago

sounds like a deadlocking issue of some kind

hitsReceived needs to be thread safe (locking)

how are you achieving this?

Accord•13mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

YOXTOP•13mo ago

Using a executeUpdateAsync

JakenVeina•13mo ago

what is "a executeUpdateAsync"?

YOXTOP•13mo ago

It updates the rows via the sql query. https://learn.microsoft.com/en-us/ef/core/saving/execute-insert-update-delete

JakenVeina•13mo ago

ExecuteUpdateAsync() is not thread-safe so, no

YOXTOP•13mo ago

Then what you suggest to make it thread safe

JakenVeina•13mo ago

depends what your goal is

Core•13mo ago

~~You need to use a distributed lock mechanism to make a code snippet thread safe~~

Core•13mo ago

Distributed locks even work if multiple instances of the same app are running on different VMs. You can read more here: https://redis.io/docs/manual/patterns/distributed-locks/

Redis

Distributed Locks with Redis

A distributed lock pattern with Redis

Core•13mo ago

But I think that in your case distributed lock is not needed. You could lock the database by using transactions That would be the best approach, as it is done on database level

YOXTOP•13mo ago

I’ll try to implement the transaction for that specific update query and see if it decreases my error rate

Core•13mo ago

You can also take a look at the isolation levels and use the one you need: https://learn.microsoft.com/en-us/dotnet/api/system.data.isolationlevel?view=net-7.0&redirectedfrom=MSDN

IsolationLevel Enum (System.Data)

Specifies the transaction locking behavior for the connection.

Gaming

Programming

❔ updating row sometimes times out EF core 7