Fail to read uncommitted data from same session in MySql/.net connector

Question

I've been banging my head against the wall with this one, may be someone can shed some light as of to what may be causing this behavior.

I have an asp.net (2.0) app that as some point does:

using(TransactionScope scope = new TransactionScope(...)) {

//... do a bunch of some queries

InsertOrder();

InsertOrderDetails();

// do some more logic and queries

ReadOrder(); // reads the newly inserted order OK

ReadOrderDetails(); // HERE'S THE PROBLEM, I CANT READ THE NEWLY INSERTED DETAILS

// do more inserts....

scope.Complete();

}

Some more contact Info:

• MySql5.0.27 community
• MySql/net connector 5.2.3
• Order and OrderDetails are InnoDB with FK constraints
• Polling enabled (although I've tried turning polling off and has the same behavior)
• I've tried setting different isolation levels in the transaction just in case with the same behavior, but this is the same connection so it shouldn't matter right?

Anyone has any ideas on what may be causing this?

Any help would be greatly appreciated

Jaime

Answer 1

My guess is that the different functions you're calling are picking up different connections, so they don't see the uncommitted changes from the transaction.

One way of check this is to get the connection ID and compare it.