[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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
