No Read or Write between INSERT and UPDATE
- From: "Dragon" <vanjiminster@xxxxxxxxx>
- Date: 9 Jul 2006 11:48:20 -0700
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 table, will still work? If not,
what do I need to do?
.
- Prev by Date: Sol 10 X64 AMD Cannot connect using simple DBI->connect() (tnsping works)
- Next by Date: LAST_INSERT_ID and Global Variable
- Previous by thread: Sol 10 X64 AMD Cannot connect using simple DBI->connect() (tnsping works)
- Next by thread: LAST_INSERT_ID and Global Variable
- Index(es):
Relevant Pages
|
|
