Using last_id and transactions

[fjm]

Hi all,

I am having a really hard time on this and I hope someone can help me out.

What I have are about 4 inserts that need need to happen at the same time using a transaction. I am having a major problem with my php code and the transaction playing "nice".

Table A
Table B
Table C
Table D

Table A is the parent and B, C and D are the children. I need to get the surrogate from table A by way of insert_id that will grab the last auto-inc. value from table A to insert FKs into tables B C and D. The problem is, when I use BEGIN to start the transaction I can't get the id value from table A because A has not yet been committed.

How can I get this to work? Someone told me to use a stored procedure but I am really clueless on that.

Thanks

[Atli]

Hi.

You could grab the next ID before you insert anything into any of your tables and use that.

SELECT MAX(id) + 1 AS 'next_id' FROM tblA
 

[fjm]

Atli, thanks for the reply.

I have thought about doing just that but the app will be used in a multiuser environment and I am concerned about race conditions. If I am incorrect about this, please let me know.

Is this a factor?

Thanks

[Atli]

That may indeed be a factor. It would perhaps be best to look at some alternatives first.

You could try a stored procedure.
Something simple like:

DELIMITER $$
CREATE PROCEDURE AddSomething(IN someText VARCHAR(255))
BEGIN
DECLARE insert_id;
 
INSERT INTO tblA(textField) VALUES(someText);
 
SELECT last_insert_id() INTO insert_id;
 
INSERT INTO tblB(tblA_fk_reference) VALUES(insert_id);
 
END $$
DELIMITER ;
 

There are some helpful examples on how you can extend this in the MySQL Reference Manual. (Check out the User Comments)

[fjm]

Atli, thanks you for the code example. It should be enough to get me started on the right track.

I will post back if I nedd further help. :)

That may indeed be a factor. It would perhaps be best to look at some alternatives first.

You could try a stored procedure.
Something simple like:

DELIMITER $$
CREATE PROCEDURE AddSomething(IN someText VARCHAR(255))
BEGIN
DECLARE insert_id;
 
INSERT INTO tblA(textField) VALUES(someText);
 
SELECT last_insert_id() INTO insert_id;
 
INSERT INTO tblB(tblA_fk_reference) VALUES(insert_id);
 
END $$
DELIMITER ;
 

There are some helpful examples on how you can extend this in the MySQL Reference Manual. (Check out the User Comments)