0

I need to read and write from/to a tasks table from multiple threads. These threads are consumers of the tasks table and remove and update tasks from it. They update the table to "own" a task and remove it from the table when it has finished executing.

I have been reading about the quirks I could find when having some threads working on the same table with entity framework core and I have noticed that DbContext is not thread save. So, I must have a DbContext per thread.

The problem I am foreseing is what happens when a thread is updating a row to assign it to a queue but at the same time another thread is trying to assign that tasks to another queue.

Is there anyway to lock rows? Is this something that entity framwork does in the background when accesing the row?. I would like to have a little overview of the problem before coding all the system.

P.D. I'm using mysql backend with pomelo driver.

Notbad
  • 5,936
  • 12
  • 54
  • 100
  • What database are you using? In Oracle you can do `SELECT FOR UPDATE` which locks records. I assume other databases have something similar. – WSC Oct 20 '20 at 11:49
  • It is a mysql backend – Notbad Oct 20 '20 at 11:49
  • 1
    I'm not overly familiar with EF, but MySQL has `SELECT FOR UPDATE` functionality. You'd need to provide more detail on your implementation if you want more specific help. This question might be a good starting point: https://stackoverflow.com/questions/37984312/how-to-implement-select-for-update-in-ef-core – WSC Oct 20 '20 at 11:53
  • Maybe `lock(dbContext)` can be sufficient in your case. – vernou Oct 20 '20 at 11:57
  • 1
    How about **not** reading and writing from "different threads", and instead having a single reader/writer (probably equipped with some sort of thread-safe queueing mechanism) through which all read/writes must pass. It will greatly simplify things and somewhat reduce the chances of shooting yourself in the foot. – spender Oct 20 '20 at 11:59
  • 1
    @Vernou for this I should be using just one dbContext for all the threads and should lock in every method using this dbContext. Am I right? – Notbad Oct 20 '20 at 12:13
  • @spender It is a good idea, I have been thinking about it but makes a bit complicated to query data from the thread and then later make it available to it when the query can be done. Don't you think? – Notbad Oct 20 '20 at 12:15
  • @Notbad, you get the idea. – vernou Oct 20 '20 at 14:14
  • I have opted for just locking some parts of the queries related to this tasks table. I think they can be isolated and there are all in one place. So I will lock the getting of the task and updates. Remove is not a problem because only a owned task can be removed for its owner and no other queue interacts with it. – Notbad Oct 20 '20 at 14:18
  • Are you familiar with the concept of [concurrency control in databases](https://en.wikipedia.org/wiki/Concurrency_control#Concurrency_control_in_databases)? Two basic modes are available, pessimistic (locking) and optimistic (on failure rollback and restart). – Theodor Zoulias Oct 20 '20 at 15:18
  • 1
    "using just one dbContext for all the threads and should lock in every method using this dbContext" No. This is not a good idea. The context is a lightweight cache that is not designed to be long-lived. It's meant to be used and disposed, usually by means of a `using` statement... i.e. within the shortest possible scope. To deal with concurrency, you either limit your app to one writer, or use the concurrency tools offered by EF... Optimistic concurrency is usually enough unless your writes are highly contentious. My view is that limiting the app is preferable to DB-based conflict resolution. – spender Oct 20 '20 at 21:01
  • https://learn.microsoft.com/en-us/ef/core/saving/concurrency – spender Oct 20 '20 at 21:03

1 Answers1

1

The answer is very simple. You can't achieve full transaction / lock safety with EF Core for now. To do things properly you have to rely on your DB engine mechanisms.

You got 2 ways to solve the problem:

  • access the SQL directly,
  • use linq2db which allows for such operations.

The linq2db may be fluently integrated with EF Core and allow you to handle LOCK / isolation levels etc. corretly.

https://github.com/linq2db/linq2db.EntityFrameworkCore

Posio
  • 962
  • 4
  • 15