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?

Aamir Rizwan
  • 827
  • 5
  • 13
  • 34
  • @Andreas If you have an answer, using an atomic/locking transaction, feel free to post it. – Tim Biegeleisen Mar 08 '18 at 06:07
  • This looks like a duplicate https://dba.stackexchange.com/questions/167273/how-to-perform-conditional-insert-based-on-row-count – ilooner Mar 08 '18 at 06:23
  • Remember, just because something "impacts performance" doesn't mean it "impacts performance in a significant way". – Kayaman Mar 08 '18 at 11:52

3 Answers3

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).

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 2
    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. – Kayaman Mar 08 '18 at 11:51
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?

  • I also made the assumption that you have control over your database schema, i.e. you can create trigger and execute DDL. –  Mar 08 '18 at 11:23
  • This solution is subject to race conditions; see [my answer](https://stackoverflow.com/a/49170770/6464308). – Laurenz Albe Mar 08 '18 at 11:55
  • @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. –  Mar 08 '18 at 12:44
-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.

Tonney Bing
  • 230
  • 2
  • 9