Tony Leake <[EMAIL PROTECTED]> wrote on 09/29/2005 07:08:24 AM: > Hi > > I have 2 applications communicating via a mysql database > > the db is 4.1.8 running on a debian linux system. > All tables are innodb > > app 1 1 runs on a windows machine, is written in c# and talks to the db > with odbc > > app 2 runs on the same machine as the db and is writtin in php. > > > Here's the problem > > app 1 writes sales data to a table, when it is finished it unsets a flag > in another table to indicate that it is finished > > app 2 polls for the flag to be unset, then reads the sales data. > > Sometimes whem app 2 reads the data there is nothing to be read, i am > logging the queries that app 2 is using to do the inserts, then by the > time i can open up a terminal and query the table manually the data is > there. > > The only thing I can assume it that there is some kind of timing issue > and the data is not fully written when I try to read it, this doesn't > happen every time and may only be when the server is loaded. Does this > happen? If so what can I do about it, would putting the inserts into > one big transaction help? At the moment All of the inserts are done by > implicit commits. > > > Sorry for the essay, i an just trying to fully document what I know. > > Thanks > tony >
When it comes to problems, more information is better. The key here is that you are making multiple changes from app1 that really should be within a transaction. The entire process of writing sales data and unsetting a flag from app1 needs to be transacted. That way the other server (app2) will either have consistent data or will never find out that anything was going on in the first place. The good thing is that you are already using InnoDB for all of your tables. This makes wrapping your process in a transaction fairly simple. Before you begin the "sales data write", issue the command "START TRANSACTION;". Do your writes and unset your flag. Then if all seems to have completed correctly, issue the command "COMMIT;" and if something went wrong issue the command "ROLLBACK;". The trick to making this work is that everything that happens between "START TRANSACTION" and "COMMIT" happens on the same connection. You cannot start a transaction from one connection and finish it from another. Depending on how your application (app1) is designed, you may have to do a little work to make sure that you use the same connection for the entire process. Once you add those two commands, app2 should never see that flag unset unless the sales data is actually available. For more detais, RTFM: http://dev.mysql.com/doc/mysql/en/commit.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
