C#•7mo 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•7mo ago

sounds like a deadlocking issue of some kind

hitsReceived needs to be thread safe (locking)

how are you achieving this?

Accord•7mo 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.

YOXT•7mo ago

Using a executeUpdateAsync

JakenVeina•7mo ago

what is "a executeUpdateAsync"?

YOXT•7mo ago

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

JakenVeina•7mo ago

ExecuteUpdateAsync() is not thread-safe so, no

YOXT•7mo ago

Then what you suggest to make it thread safe

JakenVeina•7mo ago

depends what your goal is

Core•7mo ago

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

Core•7mo 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•7mo 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

YOXT•7mo ago

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

Core•7mo 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