I am using some stored procedures in my application which depend on each others results. These procedures are called from PHP, because there's a large ruleset to determine when the procedure needs to be executed.
Still they are always called in the correct order.
The problem I am having is that sometimes (1 in 100000) the second procedure does not seem to have the results from the first procedure available, although the results from procedure 1 are stored 2 seconds prior to the execution of procedure 2.
Both procedures are called within the same transaction.
So the second procedure is only called once the first has completed, and yet sometimes can't read the row(s) the first procedure wrote?
Sorry, but I don't have any idea what the problem could be, hopefully someone more experienced than me can help you.
The only thing I can think of is that you could alter the second procedure so that it first checks whether the expected data exists, and if not sleeps before checking again (in a loop). But this wouldn't get to bottom of what the problem is, and if the expected data never gets created (or can't be read for some reason) the second procedure would get stuck in an infinite loop (unless you also set a maximum number of loops).
Thanks for your reply.
The data is not stored in temporary tables, but in permanent tables that are used for logging. The data from the first procedure is available in this table with a creation timestamp before the timestamp that the data from the second procedure was created.
Both procedure only execute insert statement. No delete or update statement is ever executed on those tables.
How are you storing the results of one procedure for the next procedure to access? I presume you're using temporary tables?
Temporary tables are session specific, so unless you're doing something that would clear the temporary tables for that particular session, I don't know how they would disappear or become empty.
Have you checked whether the first procedure does actually produce any results when this problem occurs?