CODEWITHSUNDEEP
sqljdbcsynchronizationcounter
Shamal Karunarathne
Shamal Karunarathne

Reputation: 1849

SQL Counter Increment/Decrement Synchronization

Counters Table (SQL database table) 

CounterID     |CounterValue
1             |5
2             |3

I have multiple clients accessing/modifying (increment & decrement) these counters via JDBC. What is the best way to handle the counters synchronously.

Currently clients select the counter value first, then increment it and update the value.But the problem is if two clients do select the same value before the other update the counter value.

Please advice. Thanks.

Upvotes: 0

Views: 714

Answers (1)

Klas Lindbäck
Klas Lindbäck

Reputation: 33283

The following will ensure that each call gets a unique counter value. Run this in an autonomous transaction:

While (true) do
  SELECT CounterValue into cv FROM CountersTable WHERE CounterID = 1;
  UPDATE countertable SET CounterValue = cv+1 WHERE CounterID = 1 and CounterValue = cv;
  IF (rows updated > 0) THEN
     COMMIT;
     EXIT LOOP; --No one else modified the CounterValue
  END IF
End do;

Upvotes: 1

Related Questions