MySQL ODBC Connector - timing problem

Author

timing problem

Tony Leake

2005-09-29, 7: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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-09-29, 9:24 am

--=_alternative 0049E1038525708B_=
Content-Type: text/plain; charset="US-ASCII"

Tony Leake <tony@thx-trade.com> 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
--=_alternative 0049E1038525708B_=--

Tony Leake

2005-09-29, 9:24 am

On Thu, 2005-09-29 at 09:30 -0400, SGreen@unimin.com wrote:

> 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.
>

Many thanks Shawn, I few changes needed to the app but i'll give it a
go.

Regards
tony

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Nuno Pereira

2005-09-29, 11:23 am

SGreen@unimin.com wrote:
> Tony Leake <tony@thx-trade.com> wrote on 09/29/2005 07:08:24 AM:
>
>
>
>
>
> 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/mysql? unsub...sie.nctu.edu.tw

Tony Leake

2005-09-29, 11:23 am

On Thu, 2005-09-29 at 16:15 +0100, Nuno Pereira wrote:
> SGreen@unimin.com wrote:

>
> 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
>

I don't think there is any concurency problem:

App 2 sets the flag and then polls for it to be unset, it is unset by
app 1 when it has written all of the sales data. meanwhile app 2 is
polling for the flag to be unset again and does nothing until it is, if
app 1 dies before unsetting the flag app then app 2 will sit in a loop
forever.

when the flag is unset, app 2 reads it, process it and posts it to a
website via xmlrpc

app 2 site in a shell script while loop: (pseudocide)

while (1){
start app 2
sleep (60)
}

so there can never be more than one instance of app 2 running.

tony

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-09-29, 11:23 am

--=_alternative 005497E58525708B_=
Content-Type: text/plain; charset="US-ASCII"

Nuno Pereira <nuno.pereira@carclasse.pt> wrote on 09/29/2005 11:15:08 AM:

> SGreen@unimin.com wrote:
db[color=darkred]
flag[color=darkred]
[color=darkred]
[color=darkred]
that[color=darkred]
simple.[color=darkred]
to[color=darkred]
[color=darkred]
work[color=darkred]
"COMMIT"[color=darkred]
one[color=darkred]
application[color=da
rkred]
you[color=darkred]
[color=darkred]
>
> 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
--=_alternative 005497E58525708B_=--

Nuno Pereira

2005-09-29, 11:23 am

Tony Leake wrote:
> On Thu, 2005-09-29 at 16:15 +0100, Nuno Pereira wrote:
>
>
>
>
>
> I don't think there is any concurency problem:
>
> App 2 sets the flag and then polls for it to be unset, it is unset by
> app 1 when it has written all of the sales data. meanwhile app 2 is
> polling for the flag to be unset again and does nothing until it is, if
> app 1 dies before unsetting the flag app then app 2 will sit in a loop
> forever.

That isn't true if you are using transactions.

> when the flag is unset, app 2 reads it, process it and posts it to a
> website via xmlrpc
>
> app 2 site in a shell script while loop: (pseudocide)
>
> while (1){
> start app 2
> sleep (60)
> }
>
> so there can never be more than one instance of app 2 running.
>
> tony

You may be right, but app2 is running while app1 is...

You know better if there are problems...

--
Nuno Pereira

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links