所以我的客戶端程序運(yùn)行此代碼以獲取行數(shù)據(jù)并在之后將其刪除。問題是在 SELECT 和 DELETE 之間有 60-130 毫秒的延遲，在這段時(shí)間內(nèi)另一個(gè)客戶端可能已經(jīng)抓取了同一行。有什么辦法可以同時(shí) SELECT 和 DELETE 來阻止這種沖突的發(fā)生？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");
2 回答
動(dòng)漫人物
TA貢獻(xiàn)1815條經(jīng)驗(yàn) 獲得超10個(gè)贊
如果您希望 SELECT 阻止對(duì)該行的后續(xù)讀取，直到您將其刪除，那么正如馬克在他的評(píng)論中指出的那樣，您需要
啟用事務(wù) (
setAutoCommit(false))將事務(wù)隔離設(shè)置為 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();
慕蓋茨4494581
TA貢獻(xiàn)1850條經(jīng)驗(yàn) 獲得超11個(gè)贊
編輯整個(gè)答案，因?yàn)槲艺`解了這個(gè)問題 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;
添加回答
舉報(bào)
0/150
提交
取消