CODEWITHSUNDEEP
javamysqljdbc
joe
joe

Reputation: 29

MySQL - JDBC - SELECT and DELETE at the same time

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

Upvotes: 1

Views: 883

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123409

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

  • enable transactions (setAutoCommit(false))
  • set transaction isolation to SERIALIZABLE, and
  • use SELECT ... FOR UPDATE

This sample code works for me:

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

Upvotes: 1

koFTT
koFTT

Reputation: 74

Editing the whole answer since I misunderstood the question XD

Take a look at mySQL REPEATABLE READ, your SQ code will look similar to this

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;

Upvotes: 0

Related Questions