Java方法同步和数据库读/写

Question

我有一个读写数据库的方法。它会检查用户数量是否小于MAX_USERS，然后添加一个新用户。请参考以下代码：

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
    }
}     

这里的问题是，如果以上代码被多个线程调用，则可能会获得相同的计数值。这将导致users_table中的条目超过MAX_USERS。

一种解决方法是同步整个方法，但这将影响性能。有没有更好的处理方式？

- Aamir Rizwan

---

@Andreas 如果你有一个使用原子/锁定事务的答案，欢迎发表。 - Tim Biegeleisen

这看起来像是一个重复的问题 https://dba.stackexchange.com/questions/167273/how-to-perform-conditional-insert-based-on-row-count - ilooner

记住，仅仅因为某些东西“影响性能”，并不意味着它“以显著的方式影响性能”。 - Kayaman

Answer 1

除了同步方法之外，所有其他答案都存在竞态条件：

如果同时运行两个这样的INSERT语句，则检查行数的子查询将同时发现计数不超过最大值，并且两个INSERT语句都会插入其行，可能将用户计数推向超出最大值。

使用AFTER INSERT ON user_table触发器的解决方案也会存在相同的问题，因为事务的影响在事务提交之前对并发事务不可见。

唯一完美的解决方案是在数据库中使用SERIALIZABLE事务。

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;
    }
}

这只有在所有将用户插入表中的交易都使用此代码片段（或至少使用可串行化交易）才能起作用。

- Laurenz Albe

---

所以（总结一下），如果表只通过Java代码访问，那么实现一个带同步的Java解决方案是最简单的方法（它并不像人们想象的那样低效），但如果该表可以从Java解决方案之外的地方访问（无论是什么），则需要使用可序列化事务。 - Kayaman

Answer 2

如果你想避免在 Java 代码中使用同步和事务管理机制（我认为应该这样做），那么你就需要在数据库中处理它。假设你正在使用 PostgreSQL，最好的方法是使用触发器函数。请参见以下链接以获取类似情境的示例：

如何编写关于 PostgreSQL 中行数的最大约束条件？

- user9461715

---

我还假设您对数据库模式有控制权，即可以创建触发器并执行DDL。 - user9461715

此解决方案存在竞态条件，请参见我的答案。 - Laurenz Albe

@LaurenzAlbe 我已经阅读了你的答案。你试图在应用程序中实现数据库锁定（乐观锁），而数据库可以以更高效和一致的方式进行操作。不过，你提到了这个解决方案中的竞争条件问题，如果使用序列化隔离级别执行查询，则你是正确的。 - user9461715

Answer 3

我认为你可以像这样使用插入 SQL：

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

当插入返回0时，意味着计数超过了MAX_USERS，那么您可以使用一个本地变量保存结果，下次不需要选择数据库，只需检查本地变量即可。

- Tonney Bing

---

a) PostgreSQL 中没有 dual 表 b) 您的子查询未返回 boolean 值。 - Laurenz Albe