MySQL - JDBC - 同时选择和删除

Question

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

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");

Answer 1

如果您希望 SELECT 阻止对该行的后续读取，直到您将其删除，那么正如马克在他的评论中指出的那样，您需要启用事务 (&nbsp;setAutoCommit(false))将事务隔离设置为 SERIALIZABLE，并且用&nbsp;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();

Answer 2

编辑整个答案，因为我误解了这个问题 XD看一看 mySQL REPEATABLE READ，你的 SQ 代码看起来像这样SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;SELECT token_id FROM table WHERE expires <= 'Example';DELETE FROM table WHERE token_id = 'ExampleID!';COMMIT;