0

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.

New contributor
programmer_13 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • How do you get the "last row"?
    – Rick James
    Jun 23 at 2:05
  • Using FOR UPDATE clause?
    – Rick James
    Jun 23 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 at 9:11

1 Answer 1

0
START TRANSACTION;
SELECT  *
    FROM  TRANSACTIONS
    WHERE  wallet = ?
      AND  currency = ?
    ORDER BY  created DESC
    LIMIT  1
    FOR UPDATE;
do your checks
if ...
    INSERT INTO TRANSACTIONS ...
COMMIT;

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

programmer_13 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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