Locking?

[Lüpher Cypher]

Hi,
I'm wondering if there is a way to lock the database (or do something
else) to execute more than one query with assurance that they are
executed exactly one after another (figure if there are several threads
and I give it a couple of queries they may mix). Thereason is that
suppose we have two tables, one with an auto_increment key, and the
other that references that key:

table order (id ... primary key auto_increment ...
table transaction(order_id, ... foreign key refences order(id) ...

Now, I do something like:

insert into order ...
id = last_insert_id()
insert into transaction(id,0,0,...

Say, in this case, we need to place an order, obtain the row's newly
created id, and create an empty transaction row for it. But if we have
two or more threads trying to insert and then obtain the id, it may very
well get messed up.. So, anyone can suggest anything?

Thanks

[2metre]

Lüpher Cypher wrote:

...Say, in this case, we need to place an order, obtain the row's newly
created id, and create an empty transaction row for it. But if we have
two or more threads trying to insert and then obtain the id, it may very
well get messed up.. So, anyone can suggest anything?

Shouldn't be a problem.

To quote from the manual:

"The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients. "

[Lüpher Cypher]

2metre wrote:

Lüpher Cypher wrote:

...Say, in this case, we need to place an order, obtain the row's
newly created id, and create an empty transaction row for it. But if
we have two or more threads trying to insert and then obtain the id,
it may very well get messed up.. So, anyone can suggest anything?

Shouldn't be a problem.

To quote from the manual:

"The value of mysql_insert_id() is affected only by statements issued
within the current client connection. It is not affected by statements
issued by other clients. "

Thanks, I thought it's going to be a problem.