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