Mysql/php Concurrency Problem

entz · 02-05-2009, 08:01 AM

Hello ,

I'm stepping in the dark at the moment regarding an apparent concurrency problem that i'm facing .

i can give you the following summary ,
i have a couple of php scripts that perform a series of mysql queries (insert , update , select ..etc) it starts with script1 doing an insert then after the execution finishes scripts2 is called to perform a select or update query.

Note here that scripts 1 and 2 should execute sequentially one after the other.

the problem is that scripts2 is giving me errors as if script1 has not inserted a record..or at least not yet.

so it seems like script2 is querying before scripts1 finishes it's job

but this is what really makes me knock my head against the wall because script1 makes a call to mysql_query() and the after the function returns is script2 called into action.

SO i'm suspecting that the query from the call mysql_query() is probably returning even before the actual database table gets updated with the new data.

So what do you think about this?
have you ever encountered a similar problem?
How can i explicitly instruct php not to return mysql_query() until the database has been updated at the end point ?

Regards

rsciw · 02-05-2009, 08:16 AM

use mysql_affected_rows()

http://uk3.php.net/manual/en/functio...ected-rows.php

entz · 02-05-2009, 08:49 AM

alright , actually i used to implement it but the problem still occured however , I'll try it again and see what comes up.

if not I'll post here again

entz · 02-05-2009, 05:13 PM

Hello back,

Well the problem after my lastest research has nothing to do with mysql_rows_affected , because it's not that script1 is not doing it's job but i've learned that when time lapse between the query in script1 and script2 is too small that the second query well behave as if the first query had never been executed.

I also found out that the "bug" happens sporadically and that deliberately delaying execution of the second query solves the problems but this not practical always.

So what i want is to tell the damn mysql to NOT Clear and block the function mysql_query untill all the changes have been done on the back end database and a new snapshot has been generated.

Regards

chrism01 · 02-05-2009, 05:25 PM

Well, I've worked with several DBs (Oracle, Sybase, MySQL) and never had that issue. Its odd. Here's a few things to think about.

1. is it definitely the case that script2 can't start until script1 has finished, no matter what? eg multiple copies running?
2. there is such a thing as 'delayed inserts'. Can't remember for the moment if thats set at the DB/session level or only at query level.
3. try putting an explicit commit in script1 immediately after the insert.

jlinkels · 02-05-2009, 06:33 PM

That should be pretty much impossible. Once you finish a call to mysql, you may assume it is executed, and since such operations are atomic, they can't be stuck halfway or so.

What happens if you forget entirely about script2 for the time being, and query the db again in script1 right after inserting/updating?

Then you have the smallest possible time instant while it is guaranteed that the first call ended before the second one is executed.

Like chris proposed, if you call affected_rows(), the query is guaranteed to be finished, because it gives you the result of the query.

Once you (hopefully) found that the update is done before the next call to a mysql function you can start to work and call script2 again.

Be sure not to have any options set as delayed_write, refer to the mysql manual page. But even if you have a delayed write you would notice that in your next call in script1.

jlinkels

entz · 02-05-2009, 06:37 PM

HI again for the final time in this thread (that's good news

)

yes this problem has been solved !

Now guess what was the problem ,

It's always when you're involved in big things , that you get stopped by something that's so damn trivial , silly and stupid!

you guys remember when i talked about script1 and script2 and how one was doing a query and the other was supposed to check what the other script had done .

Now the catch till now was that script2 was failing to report any changes because I was using an update statement to check the presence of the record plus updating it's contect

and Guess WHat variable was i updating ....A TIMESTAMP!

now here is a excerpt from mysql_affected_rows() documentation:

Code:

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. 
This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched,
 only the number of rows that were literally affected by the query.

Now after reading that (and i admit that i had skipped that part before and i always thought that i had recited the doc of the function completely)

...I Said to myself...WTF ???! ...Damn i'm updating a TIMESTAMP!!!
no wonder the function is not gonna work...UNLESS.....I delay
the execution between the 2 scripts !!

That's why delaying script2 was appearing to solve the problem ...at least partially.

I think the mysql developers deserve a punch right into there faces for this stupid meaningless mysql behaviour ..GRRR

However , Thanks everybody for reading this

regards

jlinkels · 02-05-2009, 07:23 PM

Quote:

Originally Posted by entz

I think the mysql developers deserve a punch right into there faces for this stupid meaningless mysql behaviour ..GRRR

No, why?

It is not stupid because it provides you with information and it is quite well documented, and when you run the command line mysql client you see that an update where the values are not updated returns 0 as number of changed rows.

It is so well documented that they even mention the replace command and how the return value for affected rows tells you something about if there was a match before an update.

One thing I didn't get. You update a time stamp. But somehow your program produces an error when the row is not updated. Why? It is quite normal that when updating timestamps sometimes the value changes and sometimes not. Actually, the time you read is completely dependent on the moment in time you read it. Just out of interest, what made your program fail?

jlinkels

chrism01 · 02-05-2009, 07:29 PM

Here's the soln (in Perl) from when I discovered that...

Code:

From subroutine header

#******************************************************************************
#
# Function      : db_connect
#
# Description   : Connect to the Databases.
#
#                 NB: Set mysql_client_found_rows=1 -> mysql will set 
#                 rows updated to rows matched (like eg Oracle). 
#                 Otherwise, if you update a row to the same as it's 
#                 current value, it'll say zero rows updated (sigh...).


From code

    $dsn = "DBI:mysql:".
               "database=".$cfg::params{'DB_NAME'}.";".
               "host=".$cfg::params{'DB_HOST'}.";".
               "port=".$cfg::params{'DB_PORT'}.";".
                "mysql_client_found_rows=1";    # See fn hdr

entz · 02-06-2009, 07:41 AM

Quote:

Originally Posted by jlinkels

No, why?

One thing I didn't get. You update a time stamp. But somehow your program produces an error when the row is not updated. Why?

jlinkels

because when the it was detected by script2 that the row was not updating
it would think that the previous insert didn't work , and it would try to insert the record itself ...creating more chaos and confusion..

but the thing is that basing the number of affected rows whether their contents change or not instead of on the actual matching query is completely ridiculous ,

I mean you have to do extra coding to ensure the thing works how you would expect it to work , at least they should have included a flag or something which you could append to the query in case you want that "special" behaviour .

jlinkels · 02-06-2009, 07:56 AM

It seems we have different opinions about whether that is correct or smart or stupid or not. That doesn't matter.

If you are interfacing throught php functions with your database it is a tradeoff to decide what you do it php and what you do in the query. Sometimes the decision is based on logic, sometimes on execution time, etc.

It requires a careful consideration. Being involved myself in writing a rather large application based on php/mysql, which involves scheduling, event logging and real-time process control, I can recommend you to have a look at mysql functionality like REPLACE and UPDATE ... ON DUPLICATE KEY

jlinkels