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!