No Read or Write between INSERT and UPDATE

I am using mysql with the InnoDB engine. I wrote a perl script that
first selects something from a table, and then updates a second table
based on the select from the first table. I need to make sure that
there is no read or write to the tables while my script performs the
insert and update.

I looked at
and it says this:

------------------------------------------------------------ ------------------------------------------------------------ -----------
If you are using a storage engine in MySQL that does not support
transactions, you must use LOCK TABLES if you want to ensure that no
other thread comes between a SELECT and an UPDATE. The example shown
here requires LOCK TABLES to execute safely:

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
SET total_value=sum_from_previous_statement
WHERE customer_id=some_id;
UNLOCK TABLES;

Without LOCK TABLES, it is possible that another thread might insert a
new row in the trans table between execution of the SELECT and UPDATE
statements.
------------------------------------------------------------ ------------------------------------------------------------ -----------

However, I am using InnoDB and it DOES support transaction. So, does
that mean that even if I don't lock my tables, it will still work? If
not,
what do I need to do?

Re: No Read or Write between INSERT and UPDATE

>If you are using a storage engine in MySQL that does not support
>transactions, you must use LOCK TABLES if you want to ensure that no
>other thread comes between a SELECT and an UPDATE. The example shown
>here requires LOCK TABLES to execute safely:
>
>LOCK TABLES trans READ, customer WRITE;
>SELECT SUM(value) FROM trans WHERE customer_id=some_id;
>UPDATE customer
> SET total_value=sum_from_previous_statement
> WHERE customer_id=some_id;
>UNLOCK TABLES;
>
>Without LOCK TABLES, it is possible that another thread might insert a
>new row in the trans table between execution of the SELECT and UPDATE
>statements.
>----------------------------------------------------------- ------------------------------------------------------------ ------------
>
>However, I am using InnoDB and it DOES support transaction. So, does
>that mean that even if I don't lock my tables, it will still work? If
>not,
>what do I need to do?

You need to execute the two queries *IN A SINGLE TRANSACTION*.
Support of transactions isn't enough; you need to actually use them.

Gordon L. Burditt