mysql transactions - foreign key failure

[Aaron Peachey]

Hi all,

I have a couple of tables in MySQL. The id in table 1 (parent) is an auto_increment value and this is a foreign key in table 2 (child).

I'm writing a python program which starts a transaction. First it tries to insert a new record into the parent table.
It then uses:

cur.execute("select LAST_INSERT_ID() from parent")

to get the auto increment id.
This id is then used as a foreign key to insert a row into the child table before committing the transaction.

The second insert keeps throwing an error, saying the foreign key constraint has been broken.

My understanding of transactions (could be wrong) is that whilst they are not available to other connections until the transaction is committed, the statements inside the transaction should be able to use the results of the earlier statements as if they had already been written to the database.
Is this not the case?

If not, how would I handle the situation of using the auto increment id generated in the first insert to insert the second row? The kicker here is that i want to be able to roll back the first insert if the second one fails - that's why I am using transactions.

Any ideas?

thanks
Aaron