Page 1 of 1

Preventing scripts from simultaneously accessing a table

Posted: Fri Apr 15, 2005 4:57 pm
by jbrzezniak
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

Posted: Fri Apr 15, 2005 5:12 pm
by Jade
Why are you calling the script two times one right after the other? Can you post some of your code? Can't you make script1 and script2 change things at the same time?

Posted: Sat Apr 16, 2005 2:59 am
by timvw

Posted: Sat Apr 16, 2005 9:13 am
by jbrzezniak
Okay, here's an example code, although it's a bit abstract (but I think it shows the problem clearly):

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));
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:

Code: Select all

mysql_query ("LOCK TABLES table [mode];")
// here the same script
mysql_query ("UNLOCK TABLES;");
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.

Posted: Sat Apr 16, 2005 9:25 am
by feyd
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.

Posted: Sat Apr 16, 2005 6:27 pm
by jbrzezniak
I'm using innoDB now and everything is working fine.
Thanks! :D