MySQL Innodb session table: Update query doesn't find a match

Question

My PHP sessions are implemented on a MySQL database, with the session table InnoDB type.

During the same execution of a PHP script, I note that a new row has been added in the session table but I'm unable to do an UPDATE statement. mysql_info() reports that there were 0 matched rows, so it never found it! But manual checks show the row exists in the table.

The thing is the UPDATE statement works if I run it in a subsequent PHP script.

It's also definitely has to do with the InnoDB type, as once switching to MyISAM, it works fine. I'm new to InnoDB table type, is there something that I need to know when dealing with InnoDB tables? row-level locking?

[Edit: There's also no errors returned by MySQL when running the UPDATE statement]

[Edit, example of SQL query]

"UPDATE session SET user_id='" . mysql_real_escape_string($user_id) . "' WHERE session_id='" . mysql_real_escape_string(session_id()) . "'";

Any help much appreciated! Thanks.

Answer 1

Could this be a timing issue?

As an example: Take two PHP scripts that are run in rapid succession (for example a website). One of the script completes and starts writing the session data, the second starts before the session data has completed writing and reads the 'current' session information from the DB. With InnoDB, the second script would get the old session data.

Don't you love async systems.