469,623 Members | 1,304 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

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 <http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html>
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?

Jul 9 '06 #1
1 2355
>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

Jul 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by francis70 | last post: by
11 posts views Thread by Markus Breuer | last post: by
3 posts views Thread by Silvio Lopes de Oliveira | last post: by
5 posts views Thread by Laertes | last post: by
9 posts views Thread by fniles | last post: by
7 posts views Thread by Igor | last post: by
6 posts views Thread by =?Utf-8?B?LnBhdWwu?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.