0

I have a table in MySQL that contains a special ID in a particular column. My requirement is to get last inserted ID value (Select... ORDER BY ID DESC LIMIT 1), generate a new value based on the acquired ID value and then insert a new record with this generated value.

The problem is that two users will be able to read last inserted ID concurrently and generate two identical IDs.

What I need to do is avoid concurrent read access across the entire table for other threads when one thread is already reading the value.

Is there a way I can achieve this in MySQL without using stored procedures? I've already synchronized the method in my Java application (using spring JdbcTemplate) to read/write to db.

Would synchronizing Java method be a good way to implement this behavior or should I use stored procedures to get the behavior?

Bashir
  • 2,057
  • 5
  • 19
  • 44
ShashiKanth Chill
  • 104
  • 3
  • 19
  • 2
    One possible way to do this would be to have mysql generate the next key for you - and then retrieve it in your code. https://stackoverflow.com/questions/4246646/mysql-java-get-id-of-the-last-inserted-value-jdbc shows you how. Locking the table for concurrent updates will always slow you down – Jan Jun 16 '20 at 14:02
  • Is there a case two Program instances calling it via different JDBC connections? – мalay мeнтa Jun 16 '20 at 14:11
  • Java synchronisation is sufficient. I think you haven't implemented synchronisation properly. Please share the Java code also – shihabudheenk Jun 16 '20 at 14:57

0 Answers0