Serverless MySQL 5.6 w/ Data API - Safely select last inserted record?

0

I'm using Aurora Serverless MySQL 5.6, and my schema has a table with an INT AUTO_INCREMENT primary key. I want to insert a record and return it from a NodeJS lambda function, so I wrote some code like this using the Data API:

rds.executeStatement('INSERT INTO table VALUES(...)"); // #1
rds.exeucteStatement('SELECT * from table WHERE id = LAST_INSERT_ID()'); // #2

However I observed that if multiple users are using the system at the same time, sometimes the LAST_INSERT_ID() in #2 does not return the ID created in #1. I believe this is because using the Data API, #1 and #2 aren't guaranteed to share the same database connection.

Is there anything I can do to make this INSERT/SELECT pattern work as expected using INT primary keys and the Data API? If I did SELECT MAX(id)... in a transaction with the INSERT would that work? Otherwise I'm not sure what else I can do besides move to UUID primary keys, which I've read can be a performance problem for MySQL.

Thank you!

mattwms
asked 4 years ago362 views
2 Answers
0

For anyone who comes across this: the generated AUTO_INCREMENT ID is returned in the generatedFields section of the ExecuteStatement response (i.e. generatedFields[0].longValue). That works fine for my needs.

mattwms
answered 4 years ago
0

CAREFUL! The generatedFields will always only contain the FIRST inserted id even though you might have inserted 500 rows.

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions