Hello
I use MySQL and I have a problem with my script. First I obtain some data from a table (with a SELECT query) then I do something with it and update the field the data was taken from (with an UPDATE query). It works fine as long as the script is not run twice almost at the same time, because then the following problem arises:
1. Script 1 obtains data
2. Script 2 obtains data
3. Script 1 modifies the data
4. Script 2 modifies the data
5. Script 1 writes the modified data
6. Script 2 writes the modified data
The problem is that the data is modified only once (the data obtained is the same, and the result is the same for both scripts), not twice, as it should be. I tried to use LOCK TABLES (with READ, WRITE and READ WRITE), but it didn't work. Does anyone know how to lock a table so that two or more scripts cannot access it at the same time?
Thanks
Preventing scripts from simultaneously accessing a table
Moderator: General Moderators
-
jbrzezniak
- Forum Newbie
- Posts: 3
- Joined: Fri Apr 15, 2005 4:46 pm
-
jbrzezniak
- Forum Newbie
- Posts: 3
- Joined: Fri Apr 15, 2005 4:46 pm
Okay, here's an example code, although it's a bit abstract (but I think it shows the problem clearly):
Now, if I run the script twice simultanously, the new value is increased only by one, not by two, as I would like it to happen. This is because the second script reads the value before the first script can update it. I would like to lock the table somehow, so that between the SELECT and UPDATE queries, no other script can read any data from the table.
I tried using LOCK TABLES in the following way:
and I tried setting [mode] to READ, WRITE and READ WRITE but it doesn't seem to change anything in this case (I'm not even sure whether the LOCK command is intended for use in such cases, but I haven't found enything esle). And as far as I know, transactions won't help here either.
Code: Select all
$result = mysql_query ("SELECT * FROM table;");
$assoc = mysql_fetch_assoc ($result);
$value = $assoc ["value"];
$newValue = $value + 1;
// this loop is simply to give me enough time to run the script
// for the second time (for example to simulate running the script
// by two visitors) before the first one finishes
for ($i = 0; $i < 5000000; $i++) $x = 1;
mysql_query (sprintf ("UPDATE table SET value=%s;", $newValue));I tried using LOCK TABLES in the following way:
Code: Select all
mysql_query ("LOCK TABLES table [mode];")
// here the same script
mysql_query ("UNLOCK TABLES;");- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
as tim already said.. InnoDB. InnoDB is the MySQL's only supported locking table storage engine, I think.. If you aren't using it, your table won't lock.
If you can perform the update immediately, do it. If the update is simple, like adding 1 to the value, then you don't need a select on it to update the value.
If you can perform the update immediately, do it. If the update is simple, like adding 1 to the value, then you don't need a select on it to update the value.
-
jbrzezniak
- Forum Newbie
- Posts: 3
- Joined: Fri Apr 15, 2005 4:46 pm