Unreliable data querying from ASP.NET page

Hi,

The sample code below works when making single requests to this test web site but when I run 100 simultaneous queries against it, I occasionally see "no results" which indicates that the query completed but the data it had been asked to add a few lines earlier has mysteriously disappeared.

Have I mis-understood how to use transactions or UnitOfWorkScope? Or can you see any other glaring mistakes?

Incidentally, the web requests also occasionally time out (40 seconds) so this may be related. If the only problem is that we are throwing too many requests at the server then that's OK (sort of) but I am surprised that we are actually receiving incorrect results from the query due to a time-out (I'd expect an exception to be thrown if the MySQL 5.1 server is unable to cope with the load)

Thanks in advance for any help or tips you can give to this newbie.

protected void Page_Load(object sender, EventArgs e)
{
    LightSpeedContext<VideoSharingUnitOfWork> context = new LightSpeedContext<VideoSharingUnitOfWork>("PrimaryMaster");

    using (var unitOfWork = new PerRequestUnitOfWorkScope<VideoSharingUnitOfWork>(context).Current)
    {
        using (var transaction = unitOfWork.BeginTransaction(System.Data.IsolationLevel.Serializable))
        {
            Query toRemove1 = new Query
            {
                EntityType = typeof(Member),
                QueryExpression = Entity.Attribute("Email") == "test@test.com"
            };
            Query toRemove2 = new Query
            {
                EntityType = typeof(Contribution),
                QueryExpression = Entity.Attribute("Title") == "Dolphins"
            };

            unitOfWork.Remove(toRemove1);
            unitOfWork.Remove(toRemove2);
            unitOfWork.SaveChanges();

            var member = new Member { Email = "test@test.com" };
            var contribution = new Contribution { Title = "Dolphins", Contributor = member };

            unitOfWork.Add(member);
            unitOfWork.Add(contribution);
            unitOfWork.SaveChanges();

            var res = unitOfWork.Find<Contribution>(Entity.Attribute("Title") == "Dolphins");
            if (res.Count() == 0)
                contributionLabel.Text = "no results";
            else
            {
                var contr = res.First();
                contributionLabel.Text = contr.Title;
            }
            transaction.Commit();

        }
    }

}

Your code looks fine to us and we've been unable to reproduce the problem, though admittedly we were using SQL Server.  Are you running 2.2 RTM or the nightly build?  If 2.2 RTM, could you upgrade to the nightly build (available from the Downloads page) and let us know if you still see the problem?  (We upgraded the MySQL provider recently.)  If you do then let us know and we'll try to set up a repro here.

Important note: when upgrading from 2.2 to a nightly you MUST uninstall 2.2 first (the installer will not alert you to this) -- this is a one-off requirement -- subsequent nightly installs will not require a manual uninstall.

Thanks for looking in to this. I'm glad there are no glaring faults with my code but on the other hand, that would have at least resulted in an easy fix!

After a lot of investigation I'm still not certain what the underlying problem is. It appears that the Lightspeed designer creates new tables using the non-transactional MyISAM storage engine (rather than the transactional InnoDB). I thought this could be the source of the problem but even when running the same tests on an InnoDB version of the database tables I get the "no results" problem in a small number of cases. I wonder if MySQL provides no guarantee of statement execution order within a transaction? I think I have ruled out a problem with Lightspeed (I commented out all the previous code and just wrote ADO.NET SQL statements to replicate the statements that Lightspeed was using and this also causes the problem).

I think I'll just have to accept that MySQL isn't 100% reliable under heavy write load but since the website I'm working on will be pretty-much read-only for the foreseeable future it shouldn't be a massive problem.

Despite Lightspeed not being at fault here, I think it does highlight a possible improvement. I understand why one would choose the MyISAM engine (performance) and I realise I should have verified what SQL the Lightspeed designer actually created but I would suggest that the designer-generated default engine is changed to InnoDB. In particular, the choice of MyISAM seems to contradict the statement (in the manual and support forums) that "LightSpeed ensures that all database flush operations run within a transaction." (MyISAM just ignores Lightspeed's "set transaction" statement). In addition, it may be worth putting a brief line in the manual to outline the differences between these two engines and what risks one is exposing themselves to if they choose to use MyISAM (or another non-transactional storage engine).

PS: I'm running the 20090526 nightly (I also saw the same behaviour in the 20090514 nightly). I also tested with SQL Server 2008 Express and couldn't reproduce the problem.

Curious that you're seeing the LightSpeed designer creating MyISAM tables: in our test environment it always creates InnoDB tables -- which I agree is what it should be doing.

We don't explicitly specify a storage engine when we create a MySQL table (as you can see if you do a Log SQL on the update), so I think this is probably down to the database default settings.  Could you pop open MySQL Administrator and check Startup Variables > General Parameters tab > General group (near the bottom of the page) > Default storage and see if that is set to MyISAM?  If so I think that's probably why you're seeing this behaviour.

I don't know what MySQL Administrator is - I assume it's an alternative to management programs such as SQLyog and phpMyAdmin. The server variables list in SQLyog shows a "storage_engine" of InnoDB for a default Windows installation of MySQL but MyISAM for the default Linux version. (When I tested on windows I copied the tables from the Linux server rather than using the designer again).

So it may still be a good idea to explicitly set the storage engine in the designer SQL code to cover any cases where the default installation specify a different storage engine.

I wonder if there is any easy way that you can test the existing storage engine used by a table and warn the developer if they have previously chosen (or been given by default) a MyISAM table? There should at least be a brief warning on the MySQL specific page of the manual.

Interesting -- thanks for the info.  We've seen differences between the Windows and Linux versions of MySQL, notably around case sensitivity, but weren't aware of this one.  We'll have a think about how to handle this -- I don't think we want to warn the developer every time we create a MyISAM table because that could get annoying if it's deliberate, but nor do we necessarily want to override their default and always force it to InnoDB.  We could of course make it a configuration setting on the designer (model or table level) but this opens up the slippery slope to cluttering things up with special configuration settings for every database under the sun.

In the meantime, we'll add that warning to the manual, and thanks again for the info about the differences between the Windows and Linux setups.