AUTO_INCREMENT and LAST_INSERT_ID

Question

I'm using AUTO_INCREMENT and I would like to get that ID of inserted row so that I could update another table using ID as common field between the 2 tables.

I understood that LAST_INSERT_ID will get last ID. However, my concern is that, the database is accessed at same time by many users. Hence, there might be another process accessed the table and also inserted a new row at same time.

Does LAST_INSERT_ID return just the last ID regardless of the connection used, or only return last ID for the connection that I'm using?

Notice, I'm accessing MySQL database using connection pool in Tomcat server.

In summary, I need to insert a row in table A with auto increment, than I need to insert row in table B, which need to be linked to table A using the AUTO_INCREMENT value.

Answer 1

SELECT max(employeeid) FROM Employee;

The above query returns the value of employeeid of last inserted record in Employee table because employeeid is an auto increment column. This seems to be OK, but suppose two threads are executing insert operation simultaneously, there is a chance that you get wrong id of last inserted record!

Don’t worry, MySQL provides a function which returns the value of auto increment column of last inserted record.

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() is always connection specific, this means even if insert operation is carried out simultaneously from different connections, it always returns the value of current connection specific operation.

So you have to first insert record in Employee table, run the above query to get the id value and use this to insert in second table.

Answer 2

LAST_INSERT_ID() work in context, it should be in transactions or inside user defined stored procedures or user defined functions.