- Original Message -
From: Aragorn [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, March 12, 2005 1:47 AM
Subject: insert - select from two different applications race condition?
I have a simple problem...
I'm using mysql 4.1.9/innodb table and two small python applications A and
B
first application A is executed, and do a simple INSERT into a table
then application B is executed:
- B does a SELECT in the same table, that should return the entry
created by application A
(and based on the result, an UPDATE (to the entry application A has
created) or an INSERT if it can't find it... but in this scenario it
should find it since B is always executed just after A is)
I can be sure that application A is executed before application B is
started, even though B can be started just after A is done.
The problem is that every once in a while, the SELECT doesn't return
anything... even though I can check that application A created the
entry...
Everything looks as if the INSERT from application A hasn't completed
when the SELECT in the application B is performed... Even though
application A terminated before application B started!
Any clue about what's going on?
How can I prevent that?
Are you sure that Application A committed its INSERT before B attempted to
read the row?
Remember, the new row isn't accessible to Application B until A has done the
INSERT *AND* committed the INSERT (and released any locks it holds on the
table into which the row was inserted). You should be checking the return
code from the INSERT and the commit to ensure that both worked; if you
aren't doing that, either step could fail silently and leave you thinking
that the INSERT was successful or that it was successfully committed when it
wasn't. That would explain why Application B doesn't see the row.
The likelihood of this scenario can be minimized if you have turned
'autocommit' on; then, each statement is committed immediately upon
successful completion but, even then, the INSERT has to be successful before
the automatic commit has the desired effect. Again, you need to verify that
the INSERT was successful each time you do one.
Please note that I am NOT saying that you should always use autocommit!
There are many good reasons not to use autocommit, particularly when you
have some sort of multi-part transaction where all parts have to either
succeed or fail together. The classic example of this is a bank transfer
where you move money from one account to another; you definitely don't want
to commit until both the debit and the credit are complete.
--
The other thing you might want to consider is Application B. Is it possibly
encountering a lock that prevents it reading the row created by Application
A? If it tries to read the row before A has released the lock, which could
potentially happen if Application A hasn't committed (or, in some cases
ENDED), it might be encountering a lock preventing reading. Now, if you are
testing for that lock within your code and reacting appropriately, you
should be fine but if you simply return an empty result set without checking
for a lock, you might fool yourself into thinking the row is not there when
it is present but inaccessible.
Let me stress that this second scenario, the one where Application B
encounters a lock, may not be possible in MySQL. I don't know anything about
how locking works in MySQL. However, the situation I describe *is* possible
in DB2, which I've used for many years. Since MySQL behaves like DB2 in many
respects, it seems possible that this scenario could happen in MySQL as
well. But I could be dead wrong so please don't scream at me if I am.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 11/03/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
