473,549 Members | 2,751 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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=som e_id;
UPDATE customer
SET total_value=sum _from_previous_ statement
WHERE customer_id=som e_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 2468
>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=som e_id;
UPDATE customer
SET total_value=sum _from_previous_ statement
WHERE customer_id=som e_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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
40230
by: francis70 | last post by:
Hi, I have these 2 problem? Is there a way in Oracle to read UNCOMMITED data. i.e. in Oracle the normal behaviour is that a user's updates to a table are visible to other users ONLY when the user commits. But in Informix there is this thing called ISOLATION LEVELS. For example by setting the ISOLATION LEVEL to DIRTY READ, a user will...
11
12673
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to read the new date. So process B starts "select ..." but does not get the previously inserted row. The timespan between commit and select is very short....
3
12096
by: Silvio Lopes de Oliveira | last post by:
Hello, I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a ODBC data source for a SQL Server 7 database. The application worked properly with a MySQL database, but after swapping it for SQL Server problems emerged. In particular, any calls to CRecordset::AddNew() and CRecordset::Edit() cause an exception to be thrown...
5
1386
by: Laertes | last post by:
Hi, I have created a DB and I want to set up a few read-only users. The problem is that I have a few "automated" insert/delete procedures. E.g. when the user logs in his name is recorded on a table. I can solve this problem by granitng insert access only for the specific table. That would be ok for this problem, but not for the one...
3
7686
by: rcamarda | last post by:
Hello, While working through my encryption questions from preivous posts, I am finding that I may have to resort to use triggers to do the encryption (not that this is the only way, but might be the best way for my circumstances). I would like to create a trigger that will encrypt the field before the write is committed. I've found serveral...
0
3548
by: DC | last post by:
The problem I'm using the .NET GridView and FormView objects for the first time and im getting the error "An OleDbParameter with ParameterName '@ID' is not contained by this OleDbParameterCollection" whenI try to write a new record. Delete and Modify work fine its just the add record function causes the error.
9
1724
by: fniles | last post by:
When using VB6 and ADO, if I only do a Read, I will open a recordset with Forward Only cursor and Read Only lock, thus it will be faster than a non Read recordset. In VB.NET, when only need to do a Read, is it correct that I want to use OLEDBDataReader (SQLDataReader for SQL Server), when when doing a Read/Write I use OLEDBDataAdapter...
7
13249
by: Igor | last post by:
1. In this topic http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/b4a07b516f4a2fcd/cb21516252b65e7c?lnk=gst&q=SET+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED&rnum=10#cb21516252b65e7c, someone wrote: "I've implemented SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of a number of stored...
6
12358
by: =?Utf-8?B?LnBhdWwu?= | last post by:
how can i write to an excel .xls file using odbc? i've read in several places that its possible but i can't find an example. i have managed to read an excel file using odbc
0
7451
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7720
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7960
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7475
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7812
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5372
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5089
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3483
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1944
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.