No Read or Write between INSERT and UPDATE

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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;

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

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
what do I need to do?

Re: No Read or Write between INSERT and UPDATE

Quoted text here. Click to load it

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

Site Timeline