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 阻止对该行的后续读取，直到您将其删除，那么正如马克在他的评论中指出的那样，您需要

• 启用事务 ( 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();

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;