Spring @Transactional + Isolation.REPEATABLE_READ for Rate Limiting

Question

We are trying a scenario of Rate Limiting the total no. of JSON records requested in a month to 10000 for an API. We are storing the total count of records in a table against client_id and a Timestamp(which is primary key). Per request we fetch record from table for that client with Timestamp with in that month. From this record we get the current count, then increment it with no. of current records in request and update the DB.

Using the Spring Transaction, the pseudocode is as below

@Transactional(propagation=Propagation.REQUIRES_NEW, isolation=Isolation.REPEATABLE_READ)
public void updateLimitData(String clientId, currentRecordCount) {

//step 1
    startOfMonthTimestamp = getStartOfMonth();
    endOfMonthTimestamp = getEndOfMonth();

//step 2
    //read from DB
    latestLimitDetails = fetchFromDB(startOfMonthTimestamp, endOfMonthTimestamp, clientId);
    latestLimitDetails.count + currentRecordCount;

//step 3
    saveToDB(latestLimitDetails)

}

We want to make sure that in case of multiple threads accessing the "updateLimitData()" method, each thread get the updated data for a clientId for a month and it do not overwrite the count wrongly. In the above scenario if multiple threads access the method "updateLimitData()" and reach the "step 3". First thread will update "count" in DB, then the second thread update "count" in DB which may not have latest count.

I understand from Isolation.REPEATABLE_READ that "Write Lock" is placed in the rows when update is called at "Step 3" only(by that time other thread will have stale data). How I can ensure that always threads get he latest count from table in multithread scenario.
One solution came to my mind is synchronizing this block but this will not work well in multi server scenario.

Please provide a solution.

If we use different levels of Isolation like "REPEATABLE_READ" & "SERIALIZABLE", is lock placed only at the time of update. Do select statements also cause locks on the rows. — Sreeram, Jan 23 '18 at 14:25

score 0 · Answer 1 · answered Jan 13 '18 at 11:53

A transaction would not help you unless you lock the table/row whilst doing this operation (don't do that as it will affect performance).

You can migrate this to the database, doing this increment within the database using a stored procedure or function call. This will ensure ACID and transactional safety as this is built into the database.

I recommend doing this using standard Spring Actuator to produce a count of API calls however, this will mean re-writing your service to use the actuator endpoint and not the database. You can link this to your Gateway/Firewall/Load-balancer to deny access to the API once their quote is reached. This means that your API endpoint is pure and this logic is removed from your API call. All new API's you developer will automatically get this functionality.

Spring @Transactional + Isolation.REPEATABLE_READ for Rate Limiting

1 Answers1