Adobe AIR has very easy integration with SQLite. And because of this ease, there is likeihood of hitting a little-known limitation with SQLite recursion which can cause your application to lock up. Anyone who uses the local SQLite database in Adobe AIR may encounter this during their development when dealing with large sets of data. The purpose of this article is to explain the issue and provide the simple solution.

If you are using an asynchronous connection to your SQLite database, and you execute a statement on your async connection and wait for the SQLEvent.RESULT, it is easy to assume from the documentation that there is a break in the thread between your statement.execute() and the result event. However, this is not the case. If you execute the next statement in the event listener, that execute occurs in the same thread, which means your execute() statements eventually end up in one long thread, which causes a SQL recursion lock up.

The Basic Setup

In order to execute a list of items on something asynchronous, you need to take the list of data you want to insert/update/whatever, create an index to increment, and run them one at a time, waiting for the asynchronous api to tell you it is finished before running the next one.

The SQLInsertService class (not shown) takes a predefined number of arguments and passes them to a statement as parameters and then executes that statement and re-dispatches the SQLEvent.RESULT from the statement instance. The reason for this class is to use the same statement over and over so you're not constantly recreating the SQLStatement, and instead just changing the parameters (as recommended by the Adobe AIR documentation). I'm ignoring handling an error event for the purposes of this example.

pseudocode

private var insertService:SQLInsertService = new SQLInsertService();
insertService.addEventListener(SQLEvent.RESULT, onInsert);

private var index:int;
private var data:Array;

public function process(value:Array):void
{
	data = value;
	index = 0;
	executeNextInsertStatement();
}
private function executeNextInsertStatement():void
{
	var item:Object = data[index];
	insertService.execute(item.prop1, item.prop2, item.prop3);
}
private function onInsert(event:SQLEvent):void 
{
	if (++index < data.length) executeNextInsertStatement();
	else trace("All Data Inserted");
}

If the asynchronous connection was asynchronous the way you might assume initially, then the above code would have a thread break between statement.execute() and the SQLEvent.RESULT each and every single iteration. However, when you pass a large amount of items to be executed, the SQLConnection instance will eventually lock up and become permanently unresponsive as it is creating one long thread of SQL statement executions (execute > result execute > result execute> result execute > etc.). This means that the Asynchronous SQLConnection is not actually running asynchronously as you might expect, such as the way making a call to a server would be asynchronous.

You used to get a weird runtime error that could not be caught by a try…catch and the exact error thrown would change based on where your try…catch was. It was confusing and so I reached out to the AIR team to help figure it out, and, after they investigated, I was told that it was caused by hitting a SQL recursion limit and that the limit was different from machine to machine. In recent builds of AIR, that runtime error no longer appears to trigger, so your SQL connection will die silently and you won't know about it. The rest of your application will work, but any statement executes through that SQLConnection instance will just stop working. No errors are thrown, it just stops working.

The Solution

Thankfully, the solution is simple. You create a 1ms timer to break the thread in the RESULT event handler. However, if you ran this timer after every single RESULT event, it would take you a very long time to process a large number of items (since you could only process 1000 per second, which is much slower than the SQL database is able to handle).

So, you pick a safe number of iterations at which to break the thread (the maximum number of executes per thread varies from computer to computer, and from my experience, it's no sooner than 150-200 that you hit the limit, so I use 100 iterations just to be safe), check the modulus of the index to that value, and run your 1ms timer whenever the modulus == 0. This will break the thread, end the recursion, and your SQL connection will never lock up.

private var executeInsertTimer:Timer = new Timer(1, 1);
executeInsertTimer.addEventListener(TimerEvent.TIMER_COMPLETE, onExecuteInsertTimer);

private function onExecuteInsertTimer(event:TimerEvent):void
{
	executeInsertTimer.reset();
	executeNextInsertStatement();
}
private function onInsert(event:SQLEvent):void 
{
	if (++index < queuedIndexItems.length) 
	{
		if (index % 100 == 0) executeInsertTimer.start();
		else executeNextInsertStatement();
	}
	else dispatchEvent(new Event(Event.COMPLETE));
}

That's it. Problem solved. Granted, inserting 100,000 records will now take a minimum of 1 second, however, for normal use, this 1ms delay will be unnoticeable (and obviously less noticeable than your database connection locking up after processing 200+ records).

Of course, don't forget that you should be doing all of these inserts wrapped between a SQLConnection.begin() and SQLConnection.commit().

Because of this thread issue, I have to wonder if the asynchronous connection actually prevents the rest of your application from locking up while processing a large number of records (which is the whole reason for using asynchronous connections in the first place). I haven't done any tests of that so far, but, to be honest, unless you're dealing with hundreds of thousands of records, you're never going to notice any hiccups even on a synchronous connection, at least on the desktop. It's blazingly fast.

Also important to note is that this is not specific to insert statements. I was using insert for the example, but any executed statement whose SQLEvent.RESULT triggers another execute (over and over) will cause this.

 

6 Responses to Adobe AIR: Avoiding SQL Recursion Lockups

  1. MrTan says:

    Hi Steven,

    This is good stuff! Finally, I understood the mysterious "dying silently" issue that I had 2 years ago.

    The approach that I took was by closing the DB connection when CRUD operations met a certain treshold and then reconnect all the DB again to process the next CRUD operations.

    Now, I could try out your way which will make the user experience smoother :)

    Again thanks!

  2. danbo says:

    Hello, good work!

    Basically, if you use just a setInterval in AS3 to wait for the RESULT, can it work correctly ?
    …a setInterval you destroy after some milliseconds !

    Just an idea

  3. danbo says:

    OOOh! Timer is better !

  4. Ivalene says:

    At last! Someone who udnernstads! Thanks for posting!

  5. Justin Dallas says:

    Wow, I think I just ran into this today. For some strange reason, I would run statement, and then run the other immediately afterwards in the event handler. The second statement would always kick the bucket with some weird "Operation aborted" error #3118. However, if I ran it in debug mode and hit the same breakpoint over and over, it ran perfectly. These are very insidious bugs and behavior.

  6. Victoria says:

    It merely takes a minute. 00 per square foot, provide 134 bed facility was supposed to place a fact.
    Are Americans paying excessively for national provide security?

    However, I control the 'when' of the individual worker.
    Awesome, what you want to call you back? Be sure also that
    specialized contractors will allow the pool of
    well experienced painters. Consider the size of the few clients that they could effectively get and help with the
    contractor.

    my page; homepage (Victoria)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Set your Twitter account name in your settings to use the TwitterBar Section.