Database Queue for android SQLite

Question

I have an Android Application that holds a fairly large Database which is accessed from multiple threads. I am starting to get Database locked Exceptions. My question, is there a way of creating a database Queue (similar to FMDataBase.Queue in iOS)?

I have done some research and I don't want to create a helper class as a lot of my database queries and inserts are different so creating a method for each query is not feasible.

I could put

if(!db.isLocked)
{
    //exec(BLAH)
}
 else
{
  //try{thread.sleep(1000);}... then some retry code
}

on every database function but there must be a better way than this.

moreover if the thread is locked after is has slept for 1000ms, it will still crash and if the db is locked for 10ms I will be waiting for 990ms for the query to run, which is not great for user experience.

Is is possible to create a Queue so that any commands sent to the db will be executed once the the db becomes unlocked?

Any advice would be greatly appreciated

CommonsWare · Accepted Answer · 2013-11-03 16:50:50Z

1

My question, is there a way of creating a database Queue

Move all your database writes to an IntentService, a regular service hosting a LinkedBlockingQueue, a LinkedBlockingQueue held by your singleton SQLiteOpenHelper, etc.

a lot of my database queries and inserts are different so creating a method for each query is not feasible.

The overhead to wrap a database I/O in a method not especially much, less than what you were proposing doing in your question.

if the db is locked for 10ms I will be waiting for 990ms for the query to run, which is not great for user experience

Tune your database access (e.g., EXPLAIN keyword, then set up appropriate indices). Use Traceview to determine exactly where your problems lie. If your problems are in a database transaction (e.g., you open your own transaction and do a ton of work inside of it), consider using yieldIfContendedSafely().

answered Nov 3, 2013 at 16:50

CommonsWare

1.0m195 gold badges2.4k silver badges2.7k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mooing Duck Over a year ago

IntentService is no longer viable on Android O :(

CommonsWare Over a year ago

@MooingDuck: IntentService is still viable; it just needs to be in the foreground. We also have other options nowadays: RxJava-based queues, WorkManager, etc., in addition to the ones that I mentioned in the answer.

Collectives™ on Stack Overflow

Database Queue for android SQLite

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related