3

I am writing a android application using sqlite. There are many activities and one service. I use the DB from more than one thread. It works perfectly in Android 2.X, but once I run it in Android 3.X it always throws this error and Force Close:

05-04 22:17:04.815: I/SqliteDatabaseCpp(8774): sqlite returned: error code = 5, msg = database is locked, db=/data/data/xxx/databases/im
05-04 22:17:04.815: E/SqliteDatabaseCpp(8774): sqlite3_open_v2("/data/data/xxx/databases/im", &handle, 6, NULL) failed
05-04 22:17:04.835: E/SQLiteDatabase(8774): Failed to open the database. closing it.
05-04 22:17:04.835: E/SQLiteDatabase(8774): android.database.sqlite.SQLiteDatabaseLockedException: database is locked
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:983)
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:956)
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1021)
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:790)
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
05-04 22:17:04.835: E/SQLiteDatabase(8774):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:149)

Anyone knows why it happen and how to solve it?

I have researched in the internet and most people advise:

  1. Use one DB connection only for the application. How to ensure it? I want to share the DB connection for both of the Service and Activities. Should I do it by create a public static DB variable?
  2. ContentProvider - I am using complicated SQL Statement in code (Such as joining few tables, temporary table). Is it possible to run these complicated SQL statement in ContentProvider?


    Thank you everyone. Finally, (1) works fine for me. But I still wonder why Android 2.X does not have this problem.
Nikola Despotoski
  • 49,966
  • 15
  • 119
  • 148
mobile app Beginner
  • 1,651
  • 3
  • 26
  • 40
  • 1) works only if all access happens withing the same process, 2) works as long as all you need is insert/query/update/delete, but complex queries look ugly since you are limited to the http://developer.android.com/reference/android/content/ContentResolver.html interface – zapl May 04 '12 at 14:45
  • Thanks zapl, the DB is not shared with other applications. I think (1) is good for me. Do you have any idea of why Android 2.X don't have this problem? – mobile app Beginner May 04 '12 at 14:49
  • 1
    you should go on this link http://stackoverflow.com/questions/7930139/android-databse-locked – Anand Tiwari May 04 '12 at 14:50
  • @mobileappBeginner No :( – zapl May 04 '12 at 14:52
  • Thanks Anand, I have closed the database and Cursor after using it. But still get the error – mobile app Beginner May 04 '12 at 14:57
  • Here is a wrapper I wrote/use which makes it slightly easier to use SQLite well, especially with threading, in the context of android - [SqlDb for Android](https://github.com/kashifrazzaqui/sqldb) – keios Dec 31 '13 at 10:35

2 Answers2

6

Number 1 is the answer. I have several stack answers and blog posts about how exactly to do that:

What are the best practices for SQLite on Android?

http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection

Number 2 is a lot of extra work and unnecessary. ContentProvider exists so you can share data with other apps. Its used to manage database connections because people don't understand how Sqlite and Android work together.

Community
  • 1
  • 1
Kevin Galligan
  • 16,159
  • 5
  • 42
  • 62
  • Thanks Kevin Galligan, I have read your posts. I learned a lot at - http://stackoverflow.com/questions/2493331/what-is-best-practice-with-sqlite-and-android/3689883#3689883. I am trying solution (1). I will study ormlite if solution (1) fail. Thank you! – mobile app Beginner May 04 '12 at 16:26
  • 3
    ORMLite is nice, but totally not necessary for database access from multiple threads. Just have a static SQLiteOpenHelper instance, or keep one instance in an Application instance. You'll never have locked db issues if you only have one instance. – Kevin Galligan May 04 '12 at 19:24
  • Thanks Kevin, ORMLite is awesome anyway – mobile app Beginner May 05 '12 at 01:20
3

I suggest you use a ContentProvider with a LoaderManager whenever you can. It is capable of performing queries in the background automatically.

Adrian Monk
  • 1,061
  • 9
  • 17
  • Thanks for advice, but it requires many extra works and the database will not share with other applications. I think solution (1) is better for me. I will think about your suggested solution if (1) does work. Thanks – mobile app Beginner May 04 '12 at 16:20
  • 2
    I know a lot of people that prefer ContentProvider, but its totally not necessary for multi-threaded db access. If you're only using it for that, its not needed. However, if you like ContentProvider, use it. Just seems like extra work to me, though. – Kevin Galligan May 04 '12 at 19:25