MySQL - JDBC - 同时选择和删除 [英] MySQL - JDBC - SELECT and DELETE at the same time

问题描述

所以我的客户端程序运行此代码以获取行数据并在之后将其删除.问题是在 SELECT 和 DELETE 之间有 60-130 毫秒的延迟，在这段时间内另一个客户端可能已经抓取了同一行.有什么办法可以同时 SELECT 和 DELETE 来阻止这种冲突的发生?

So my client program runs this code to get the rows data and delete it after. The thing is between the SELECT and DELETE there is a 60-130 millisecond delay and in that time another client could have grabbed the same row. Is there any way I can SELECT and DELETE at the same time to stop this conflict from happening?

String filterQuery = "SELECT token_id FROM table WHERE expires <= ? LIMIT 1;";

PreparedStatement preparedStmt = conn.prepareStatement(filterQuery);
preparedStmt.setLong(1, System.currentTimeMillis());
long startTime = System.currentTimeMillis();
ResultSet result = preparedStmt.executeQuery();
String token_id = null;
while (result.next()) {
    System.out.println(result.getString(1));
    token_id = result.getString(1);
}

filterQuery = "DELETE FROM table WHERE token_id = ?;";
preparedStmt = conn.prepareStatement(filterQuery);
preparedStmt.setString(1, token_id);
preparedStmt.execute();
System.out.println(System.currentTimeMillis() - startTime+" milliseconds");

推荐答案

如果您希望 SELECT 阻止对该行的后续读取，直到您将其删除，然后如 Mark 在评论中指出的那样，您需要

If you want your SELECT to block subsequent reads on that row until you delete it then, as Mark indicates in his comment, you'll need to

• 启用事务 (setAutoCommit(false))
• 将事务隔离设置为 SERIALIZABLE，并且
• 使用 SELECT ... FOR UPDATE

此示例代码适用于我:

conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT token_id FROM tokens ORDER BY token_id LIMIT 1 FOR UPDATE");
rs.next();
int token_id = rs.getInt("token_id");
System.out.printf("Got %d.%n", token_id);
PreparedStatement ps = conn.prepareStatement("DELETE FROM tokens WHERE token_id=?");
ps.setInt(1, token_id);
ps.executeUpdate();
conn.commit();

这篇关于MySQL - JDBC - 同时选择和删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！