I've just switched over from MyISAM tables to InnoDB as the row locking seems like it would be better than table locking.
Problem is that now many of things I was doing before don't work.
For example, I have one script that recommends items to a user based on their browsing history. This is done by inserting or updating rows into a recommendations table. The actual calculation of the recommendation is done though an SQL statement. So sometimes I am performing the recommendation statement, then immediately (in the same PHP script) requesting the results of this so it can be displayed to the user right there. With MyISAM this worked perfectly, but with InnoDB it sometimes will return the old results, or no results (if there didn't used to be any).
Also I have a table that acts as a queue for something. To use it I perform and update and then a select on the results of this update. Again it works for MyISAM but not for InnoDB.
How can I ensure that the rows are inserted or updated immediately in some cases? In other cases I would be happy with a delayed insert or update if it speeds the query up. But in the particular cases outlined about I need it to be immediate. Is there a PHP command to do this? Or is it do with MySQL settings?
It sounds like you could be having transaction issues since moving to InnoDB http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html .
Unlike MyISAM, InnoDB supports database transactions that isolate your previous SQL commands until you have committed them with a special COMMIT sql command. Normally, this isn't a problem since you will be running off of the same transaction on the same database connection and AUTOCOMMIT is enabled by default. The autocommit feature of MySQL will automatically commit the transaction after a data change, such as after an UPDATE. However, I would guess that you are connecting to the database multiple times (or perhaps using a library that is doing so) and your library or configuration is disabling autocommit.
You can test this easily by issuing the SQL COMMIT after your UPDATE and seeing if the problem disappears. If this is the problem, the long-term solution depends on the libraries you are using, but generally you can enable autocommit again (either through your library or by removing the disabling entry from your my.cnf or my.ini file) or you can preferably start using transactions! There are plenty of articles everywhere for "MySQL data transactions tutorial."
You can enable autocommit via PHP by executing the SQL SET AUTOCOMMIT=1; once before executing the rest of your SQL.
This all assumes you are not having replication issues.