MySQL Forums
Forum List  »  Stored Procedures

last_insert_id(), is it safe?
Posted by: carl christianson
Date: May 19, 2012 09:52PM

So last_insert_id() seems to be connection specific? or it's across all connections to the database?

In a stored procedure I'm taking some parameters and based on that.
Putting data into a parent and then child table (innodb, fk relationship).

The logic is an upsert, i.e. insert if not there, update if there.

So I do on the parent.

insert ... on duplicate key update ...

afterwards. I realized I can't directly use last_insert_id() as if I didn't insert but another thing had the last_insert_id() reflects that value.

So instead.

Immediately after the insert I was thinking about doing a check on row_count()
as the documentation says that will be 1 if an insert was done, 2 if an update.
If the value is 1, then I should be able to safely glean the last_insert_id() as the insert fired.

Here's my question.
How multi-user safe is this? Will I get into a race condition where another connection will hit this procedure doing the same thing and I might get the wrong value. Reading the doc I came away not 100% sure on this. (but then maybe that's because it's late)

So is this safe to use in an enterprise/multi connection app?
Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
last_insert_id(), is it safe?
2415
May 19, 2012 09:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.