MySQL Forums
Forum List  »  General

Is this an issue of Isolation?
Posted by: Rich Koudelka
Date: September 11, 2010 05:41PM

I have a proc that updates a table and a relatinship table, and that proc can be called more than once at the same time. For example:

INSERT INTO Students (autoID, name) values (null, 'John')
SELECT LAST_INSERT_ID() into v_new_id;
INSERT INTO StudentAgeRel (StudentID, Age) values (v_new_id,18)

Im wondering if, as this is called more than once from two different users, I can get the following problem:

User1 inserts a record and creates ID=1
Then before the LAST_INSERT_ID takes place, User2 creates ID=2.
Then when user1's sql runs LAST_INSERT_ID, it gets ID=2 - and uses StudentID=2 in the StudentAgeRel table when it should have used StudentID=1.

Is this simply taken care of by using a transaction? Is that all that is required? Does that enforce the integrity that I need here?

Options: ReplyQuote

Written By
Is this an issue of Isolation?
September 11, 2010 05:41PM

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.