3 Replies Latest reply on Oct 17, 2011 6:19 AM by Soabirw

    Adobe AIR SQLite Async Events Not Dispatching


      This is a cross post from Stack Overflow, as it hasn't gotten much attention over there (http://stackoverflow.com/questions/7635161/adobe-air-sqlite-async-events-not-dispatching).


      Working on an application that has very heavy use of the local sqlite db.  Initially it was setup for synchronous database communication, but with such heavy usage we were seeing the application "freeze" for brief periods fairly often.  Just to give an idea of the DB traffic, some jobs do 3-5K inserts and some do upwards of a million in 1K chunks.  All the while doing read-only selects to feed data to the interactive UI.


      After doing a refactor to asynchronous communication we are seeing a different issue.  The application seems to be far less reliable.  Jobs seem to simply not complete.  After much debugging and tweaking the problem seems to be the database event handles not always being caught.  I'm seeing this specifically when beginning a transaction or closing the connection.


      Here is an example:


          con.addEventListener(SQLErrorEvent.ERROR, tran_ErrorHandler);
          con.addEventListener(SQLEvent.BEGIN, con_beginHandler);


      Most of the time this works just fine.  But every now and then con_beginHandler isn't hit after con.begin is called.  This makes it so we have an open transaction that never gets committed and can really hang up future requests.  When investigating this same issue with the connection close handler, one of the solutions was to simply delay it.  In that context it was OK to wait even several seconds.


          setTimeout(function():void{ con.begin(SQLTransactionLockType.IMMEDIATE); }, 1000);


      Changing to something like this does seem to make the transaction more reliable, however, that really stretches out the time it takes for the application to complete actions.  This is a very db heavy application, so even adding 200ms has a noticeable affect.  But something as short as 200ms also doesn't seem to fully solve the issue.  It has to be 500-1000ms or higher in order for me to stop seeing this issue.


      I've written a separate AIR application to try and stress test our code and the transactions, but am unable to reproduce this in that environment.  I even have it try to do something that will "freeze" the application (long loops that do some math or other processing) to see if application strain is what makes them misfire, but everything seems reliable.


      I'm at a loss for how to resolve this at this point.  I even tried running con.begin off of a binding event, just to add more time.  The only thing that seems to work is excessively long timers/timeouts, which I don't think is an acceptable solution.


      Has anybody else run into this?  Is there some trick to async that I'm missing?

        • 1. Re: Adobe AIR SQLite Async Events Not Dispatching
          xia rao

          Could you please open a new bug report on this over at bugbase.adobe.com? Please include sample media, code, project or app to help us reproduce the problem. Did this occur on the desktop or mobile device? Finally, once the bug has been added would you mind posting back with the URL so that others affected can add their votes and comments?


          Thanks a lot.

          • 2. Re: Adobe AIR SQLite Async Events Not Dispatching
            Soabirw Level 1

            This issue is on a desktop application.  I'm not confident I can provide reproducable code, but I will try.  As I mentioned in the post, when I make simple AIR applications to try to reproduce this, then I'm able to run millions of transactions very quickly without any issue.  It's only when inside a larger and busier application that this issue comes up.

            • 3. Re: Adobe AIR SQLite Async Events Not Dispatching
              Soabirw Level 1

              I had a few more ideas to try after the refreshing weekend, none of which panned out; however, during these attempts and more investigations I finally found a pattern to the issue. Even though it doesn’t happen consistently, when it does happen it is fairly consistent on where it happens.  There are 1 or 2 spots during the problematic processes that try to compact the DB after doing data clearing, in order to help keep the file sizes smaller.  I think the issue here is compact wasn’t worked into the async flow properly.  So while we are trying to compact the db, we are also trying to start up the new transaction.  So if the compact takes a bit of time every once in a while, then we get a hang up.  I think the assumed behavior was for async event handling to dispatch when the transaction is finally started instead of just never happening at all, but this does make some amount of sense.


              With this in mind, I might be able to write a stand alone application to replicate this behavior, if you are still interested.  However, I don't feel this is really a defect for Adobe.  More of improper implementation on my part.