Spring, JPA, and Hibernate - how to increment a counter without concurrency issues

Question

I'm playing around a bit with Spring and JPA/Hibernate and I'm a bit confused on the right way to increment a counter in a table.

My REST API needs to increment and decrement some value in the database depending on the user action (in the example bellow, liking or disliking a tag will make the counter increment or decrement by one in the Tag Table)

tagRepository is a JpaRepository (Spring-data) and I have configured the transaction like this

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"/>

@Controller
public class TestController {

    @Autowired
    TagService tagService

    public void increaseTag() {
        tagService.increaseTagcount();
    }
    public void decreaseTag() {
        tagService.decreaseTagcount();

    }
}

@Transactional
@Service
public class TagServiceImpl implements TagService {


    public void decreaseTagcount() {
        Tag tag = tagRepository.findOne(tagId);
        decrement(tag)
    }

    public void increaseTagcount() {
        Tag tag = tagRepository.findOne(tagId);
        increment(tag)
    }

    private void increment(Tag tag) {
        tag.setCount(tag.getCount() + 1); 
        Thread.sleep(20000);
        tagRepository.save(tag);
    }

    private void decrement(Tag tag) {
        tag.setCount(tag.getCount() - 1); 
        tagRepository.save(tag);
    }
}

As you can see I have put on purpose a sleep of 20 second on increment JUST before the .save() to be able to test a concurrency scenario.

initial tag counter = 10;

1) A user calls increaseTag and the code hits the sleep so the value of the entity = 11 and the value in the DB is still 10

2) a user calls the decreaseTag and goes through all the code. the value is the database is now = 9

3) The sleeps finishes and hits the .save with the entity having a count of 11 and then hits .save()

When I check the database, the value for that tag is now equal to 11.. when in reality (at least what I would like to achieve) it would be equal to 10

Is this behaviour normal? Or the @Transactional annotation is not doing is work?

Answer 1

The simplest solution is to delegate the concurrency to your database and simply rely on the database isolation level lock on the currently modified rows:

The increment is as simple as this:

UPDATE Tag t set t.count = t.count + 1 WHERE t.id = :id;

and the decrement query is:

UPDATE Tag t set t.count = t.count - 1 WHERE t.id = :id;

The UPDATE query takes a lock on the modified rows, preventing other transactions from modifying the same row before the current transaction commits (as long as you don't use READ_UNCOMMITTED).

Answer 2

For example use Optimistic Locking. This should be the easiest solution to solve your problem. For more details see -> https://docs.jboss.org/hibernate/orm/4.0/devguide/en-US/html/ch05.html

Answer 3

There are 3 ways to handle concurrency issues -

1. Wait until lock is free to acquire -

If you are using redis lock and lock is acquired by some other thread then wait until lock is released then perform the increment/decrement operation, it will ensure the correctness of the data.

2. Increment/Decrement the counter in query itself -

Increment/Decrement the counter in the query itself and let the database handle the concurreny and locking on its end.

3. Enable retry mechanism -

In case lock is acquired by some other thread and new threads are unable to acquire the lock to perform the required action then push the msg to some delay queue (lets say rabbitMQ) and put some delay(lets say 10 sec), so that after 10 sec the thread will check if lock is available or released by other thread then increment/decrement the counter.

1st and 3rd solutions are quite similar but in 1st we can use while loop and thread sleep for a delay and in 3rd solution we can use some delay queue and retry mechanism instead.

Answer 4

It's worth to mention here about spring-boot-mongodb JPA - how to increment a counter without concurrency issues:

In MongoDB as per official documentation:

When modifying a single document, both db.collection.findAndModify() and the update() method atomically update the document. See Atomicity and Transactions for more details about interactions and order of operations of these methods.

In the case of Mongo shell, we can simply run the findAndModify as below:

> db.idGenerator.findAndModify({query: {identifier: "identified_by_Id"}, update: {$inc: {counter:1}}  })
{
    "_id" : ObjectId("5e115560ff14992f34fd18c6"),
    "identifier" : "identified_by_Id",
    "counter" : 1
}
> db.idGenerator.find()
{ "_id" : ObjectId("5e115560ff14992f34fd18c6"), "identifier" : "identified_by_Id", "counter" : 2 }
> db.idGenerator.findAndModify({query: {identifier: "identified_by_Id"}, update: {$inc: {counter:1}}  })
{
    "_id" : ObjectId("5e115560ff14992f34fd18c6"),
    "identifier" : "identified_by_Id",
    "counter" : 2
}
>

findAndModify() always increment/decrements and return the actually value previously present for the counter.

In the term of JPA, first, create a Model and then Repository and Service class to get unique ids as below:

// Model class

import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.mapping.Document;

@Document
public class IdGenerator {

  @Id
  private String identifier;

  private long counter;

  public String getIdentifier() {
    return identifier;
  }

  public void setIdentifier(String identifier) {
    this.identifier = identifier;
  }

  public long getCounter() {
    return counter;
  }

  public void setCounter(long counter) {
    this.counter = counter;
  }
}

// Mongo Repository class:

import org.springframework.data.mongodb.repository.MongoRepository;
import sample.data.mongo.models.IdGenerator;


public interface IdGeneratorRepository extends MongoRepository<IdGenerator, String> {

}

// Id Generator Service class

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.FindAndModifyOptions;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;
import sample.data.mongo.models.IdGenerator;
import sample.data.mongo.repository.IdGeneratorRepository;

@Service
public class IdGeneratorService {

  @Autowired
  private IdGeneratorRepository idGeneratorRepository;

  @Autowired
  private MongoTemplate mongoTemplate;

  public long generateId(String key) {

    Query query = new Query();
    // key = identified_by_Id;
    Criteria criteria = new Criteria("identifier").is(key);
    query.addCriteria(criteria);

    Update update = new Update();
    update.inc("counter", 1);

    FindAndModifyOptions options = new FindAndModifyOptions();
    options.upsert(true);
    options.returnNew(true);

    IdGenerator idGenerator = mongoTemplate
        .findAndModify(query, update, options, IdGenerator.class);

    return idGenerator.getCounter();
  }
}

By using the above method generateId it will return always an incremented number for the specific keys.