2

I am attempting to use a SQLite DB with an AIR project and there seem to be a few questions that I just can't seem to find the answers to on the internet, especially ones concerning best practices.

  1. The first is about having multiple connections in the same application instance. Some application processes require the use of a db for a split second, and would better be served by a synchronous type code flow (try{}catch(){}). Some processes may take awhile and would be better served by an asynchronous code flow with an AsyncResponder.

    Because of this, I have thought about having a ConnectionPoolManager which would have multiple instances of async and one instance of my sync SQLConnection classes. Is this a decent, good or absolutely atrocious idea ? This leads me to my next point.

  2. Is there any issue of having multiple async connections running their async statements in parallel ? I've seen some people complain about the db being locked, and I've read that only one statement can perform a write at a time.

    What happens if a write statement gets called to execute(new Async...) while another one is busy ? Will an error be thrown, or will it wait for a timeout ?

Any clarification about this issue would be greatly appreciated. I keep running into sources that kind of answer one-off questions but not my question in particular. Usually I would go test it myself, but I'm worried I may not be able to reproduce the pitfall error that will only manifest itself at heavier usage levels.

EDIT: Here is some documentation concerning the issue I found after wasting half the day. Adobe Doc

Black Dynamite
  • 4,067
  • 5
  • 40
  • 75

1 Answers1

2

SQLite has no write concurrency; a writer will block all other readers and writers.

When such a conflict happens, an SQLError with ID 3119 will be thrown. In this case, the application should wait and retry. SQLite has a built-in setting to do such a wait automatically, but this is not exposed by Air. You would have to do this manually in your code, which is not easy.

Therefore, it would be easier to have a single connection to SQLite. If you'd want to use it asynchronously, you'd have to manage the accesses with a Mutex.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks man. This is kind of depressing, as it requires MUCH greater complexity when working with the db. I think I'm going to post this on the Adobe forums to see if anyone has any design tips on this issue as I know I am not the first one to run into this. I wish there was a way to expose the timeout parameter. – Black Dynamite Oct 18 '12 at 13:32