0

Problem:

I have a multi-threaded application (or multiple client applications) needing to access a "counter" stored in the database. They need to read the value, then +1 to the counter, and store it again. During the read and write, there is an additional layer of logic where the counter value is compared with an input obtained externally, and the result of the logic dictates whether to +1 to the counter or not.

Pseudocode:

var counter = Database.GetCurrentValueOfCounter();

counter = max(counter, input);

Database.Save(counter + 1);

return counter;

For example, in a multiple client scenario, all clients get an input externally, which all equals the same value (since they are obtained at the same time).

When the first client enters this function, the other clients should wait until the counter is updated before entering. Thus for multiple clients in the above scenario, each would obtain a sequential counter value.

What I've tried

I am using c# EF Core to implement the code and database logic. I have tried to use a serialisable transaction via Database.BeginTransaction(IsolationLevel.Serializable).

Then SELECT counter from Table -> logic in c# -> UPDATE Table SET counter ...

However, this approach gives me a deadlock transaction. Researching deadlocks, I believe the deadlock occurs on the second thread SELECT statement, which makes sense, since the first thread would be locking it in the transaction.

Question

What is the correct way to implement this locking/queuing mechanism on the database level?

Currently, I have resorted to entered a critical section in the code via lock(object), however, this WILL FAIL in a multi-server scenario. I can look into using SQL Distributed locks, however, it doesn't feel right to be doing the lock at the application level.

Can anyone point me to how I can achieve this sequential locking at the database level?

l3utterfly
  • 2,106
  • 4
  • 32
  • 58
  • that is why identidy exists – nbk May 15 '21 at 10:47
  • @nbk can you elaborate please? – l3utterfly May 15 '21 at 10:48
  • every cpu can reorder commands as it see fit the same goes fpr a rdms, so the only way to ensure is to set an auto increment https://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 which makes this on a low level. – nbk May 15 '21 at 10:55
  • That would require me to insert new rows each time. Also it doesn't take into account the external logic: `max(counter, input)`. Input may be much greater than the current counter, in that case, the current counter "jumps" up to the input – l3utterfly May 15 '21 at 11:13
  • That is a perc of modern cpu design, and you have to live with it and see another methods for example locking the tabke, wihich will present another problem on heavy duty servers. There is always the possibility for uuids – nbk May 15 '21 at 11:18
  • 5
    You could update the database and return the new value with an atomic update query like `UPDATE dbo.CustomCounter SET CounterValue = CASE WHEN CounterValue > @InputValue THEN CounterValue ELSE @InputValue END + 1 OUTPUT inserted.CounterValue;` – Dan Guzman May 15 '21 at 11:21
  • You could implement a queue like Redis or Azure storage queue that would serially compute the number and be available in a multiple server scenario. – Crowcoder May 15 '21 at 11:32
  • I believe @DanGuzman's approach would perfectly with this specific case (being the external logic is simple enough to encode in one sql statement). – l3utterfly May 15 '21 at 11:40
  • The only way you can lock is inside the SQL Server and not in the c# code. A multicuser environment is not always going through your one application. – jdweng May 15 '21 at 14:28
  • I'm not an expert on this, but I'm facing a similar problem in my DB. What about using SQL triggers along with an stored procedure? That way every time I create a record, I can update the row the way I want. Thoughts? – carloswm85 Sep 30 '22 at 13:08

0 Answers0