When using the MySQL NDB Cluster distributed database, the following general problem has arisen: To insert a new Record / Row, the last row in the database should be read, see if it is compatible with the row to be inserted and then insert the new row. However, the problem arises that in the meantime another instance also runs through the same process and then at the end both new rows have checked to the same last record to be compatible and are inserted. Is there a way to block this with a locking process?

So that one instance gets the last row/ record via JDBC in Java and locks this row for reading, then checks the object and then insert the new record and now a second instance can run through the same process?

Thanks for your answers.

  • How do you get the "last row"?
    – Rick James
    Jun 23, 2022 at 2:05
  • Using FOR UPDATE clause?
    – Rick James
    Jun 23, 2022 at 3:02
  • Like that in Java: String sql = "SELECT * FROM TRANSACTIONS WHERE wallet = ? AND currency = ? ORDER BY created DESC LIMIT 1;"; try { PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, wallet); stmt.setString(2, currency); ResultSet rs = stmt.executeQuery(); Then I check in the Java Code the parameters for some integrity conditions and after that, I insert a new Transaction into the table. How would you use "FOR UPDATE", because I have tried it but hasn't worked? Jun 23, 2022 at 9:11

1 Answer 1

    WHERE  wallet = ?
      AND  currency = ?
    ORDER BY  created DESC
    LIMIT  1
do your checks
if ...

Without the FOR UPDATE and the START...COMMIT, the following could happen between two connections:

connection 1:  SELECT ...
connection 2:  SELECT ...  -- getting same "last" row
connection 2:  INSERT ...  -- new "last"
connection 1:  INSERT ...  -- based on what is now next-to-last

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.