mysql-jdbc-同时选择和删除

cgyqldqp 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(337)

所以我的客户机程序运行这个代码来获取行数据，然后删除它。问题是在select和delete之间有60-130毫秒的延迟，在这段时间内，另一个客户机可能已经抓取了同一行。是否有任何方法可以同时选择和删除以阻止此冲突的发生？

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

Java mysql jdbc

来源：https://stackoverflow.com/questions/51749963/mysql-jdbc-select-and-delete-at-the-same-time

2条答案

按热度按时间

anauzrmj1#

因为我误解了问题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;

赞(0）回复(0）举报 2021-06-23

wz8daaqr2#

如果您希望select阻止对该行的后续读取，直到您删除它，那么正如mark在其注解中所指出的那样，您需要
启用事务处理( setAutoCommit(false) )
将事务隔离设置为可序列化，然后
使用 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();

赞(0）回复(0）举报 2021-06-23

我来回答

mysql-jdbc-同时选择和删除

2条答案

相关问题

热门标签

最新问答