Nuno Pereira <[EMAIL PROTECTED]> wrote on 09/29/2005 11:15:08 AM: > [EMAIL PROTECTED] wrote: > > 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 > > This one is interesting in terms of concurrency... > > Is the app2 the one responsable for setting the flag? I supose that it is. > If that happens it's important that app2 doesn't mess with the flag, i > mean, it may lead to problems if app2 sees that app1 writes the sales > data, app2 sees it, starts reading it, app1 writes more data and app2 > sets the flag without seeing that app1 have written more data, and sets > the flag without reading the new one. > > Make this work without problems can be tricky, and I don't see a good > solution to this in five minutes. When app2 reads data what app does > with it? How app2 sees what was the last data she read? > > -- > Nuno Pereira Transactions provide for process atomicity and process isolation. Both of which help to avoid the concurrency issues you discuss. If app2 opens a transaction to read the data then reset the flag, app1 won't be able to mess with that particular set of data until app2 finishes and COMMIT-s. If at the same time app1 tries to update the data and unset the flag that app2 has it's transaction open, app1's changes won't be visible to app2 until: 1) app2 finishes it's transaction (COMMIT or ROLLBACK) and 2) app1 COMMIT-s its transaction. If app1 does a ROLLBACK, nothing changes. That kind of process coordination and data consistency is precisely what transactions and row-level locking are intended to enable. They can't solve every problem but they are a good 90%-level solution to database coordination and are exactly what's needed for the OP's situation (IMHO) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
