4

I have following method that reads and writes to a database. It checks if the user count is less than MAX_USERS and then adds a new user.

addUser(User user){
    //count number of users in the DB, SELECT count(*) from user_table
    count = ...

    if(count<MAX_USERS){
        //add the new user to the DB, INSERT INTO users_table
    }
}

The issue here is that if above code is called by multiple threads then they may get the same count value. This will result in more than MAX_USERS entries in the users_table.

One solution would be to synchronize the whole method but it will impact performance. It there a better way to handle this?

Improve this question
3
  • @Andreas If you have an answer, using an atomic/locking transaction, feel free to post it. Commented Mar 8, 2018 at 6:07
  • This looks like a duplicate dba.stackexchange.com/questions/167273/… Commented Mar 8, 2018 at 6:23
  • Remember, just because something "impacts performance" doesn't mean it "impacts performance in a significant way". Commented Mar 8, 2018 at 11:52

3 Answers 3

Reset to default
3

All of the other answers (except the synchronized method) are subject to a race condition:

If two such INSERT statements are running concurrently, the subqueries checking the number of rows will both find the count not exceeding the maximum, and both INSERT statements will insert their row, potentially pushing the user count beyond the maximum.

A solution using an AFTER INSERT ON user_table trigger would have the same problem, since the effects of a transaction are not visible to concurrent transactions before the transaction is committed.

The only bullet-proof solution is to use SERIALIZABLE transactions in the database:

import java.sql.Connection;
import java.sql.SQLException;

Connection conn;

...

conn.setAutoCommit(false);

boolean transaction_done = false;

while (! transaction_done) {
    try {
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

        // run the SELECT statement that counts the rows

        if (count < MAX_USERS) {
            //add the new user to the DB, INSERT INTO users_table
        }

        conn.commit();

        transaction_done = true;
    } catch (SQLException e) {
        // only retry the transaction if it is a serialization error
        if (! "40001".equals(e.getSQLState()))
            throw e;
    }
}

This will only work if all transactions that insert users into the table use this piece of code (or at least use serializable transactions).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

So (just as a wrap-up), if the table is accessed only through Java code, it would be the path of least resistance to implement a Java solution with synchronization (it's not as ineffective as people make it out to be), but if the table can be accessed from outside the Java solution in the question (whatever that might be), serializable transactions are needed.
0

If you want to avoid synchronization and transaction management magic in your Java code (which IMO you should) you have to handle it in database. Assuming you're using PostgreSQL, the best way to do it is Trigger Functions. See this for a similar scenario:

How to write a constraint concerning a max number of rows in postgresql?

Improve this answer

3 Comments

I also made the assumption that you have control over your database schema, i.e. you can create trigger and execute DDL.
This solution is subject to race conditions; see my answer.
@LaurenzAlbe I've read your answer. What you're trying to do is to implement database locking (optimistic) in the application, while the database is well capable of doing it in a more efficient and consistent way, You have a point though about race condition in this solution, if your execute the query with serialized isolation level.
-2

I think you can use insert SQL like this:

INSERT INTO test1(name) 
SELECT 'tony'
 WHERE
   EXISTS (
    (select count(*) from test1) <= MAX_USERS
   );

when the insert return 0 mean then count more than MAX_USERS, then you can use a local variable save the result, Not needed select database next time only check the local variable.

Improve this answer

1 Comment

a) there is no dual table in PostgreSQL b) your subselect does not return a boolean value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.