Android. Handling some SQLite issues

Android. Handling some SQLite issues.

转载：?http://www.enterra-inc.com/techzone/handling_sql_issues/

?

?

Today OS Android is gaining more popularity enhancing the range of tasks that users would want to be resolved here and now. Alongside with the most pertaining tasks that we can encounter there are those that require handling large volumes of data for reasonable time like for example full text search thru the database, that is SQLite that is used on OS Android with the accessory package android.database.sqlite. However this package contains only a set of tools to work with the database which is not in fact a framework that regulates approaches to database access implementations.

Currently Google does not provide any detailed recommendations to work with the database. In official documentation there are only 2 simple examples using SQLite (“NotePad” and “SearchableDictionary”) therefore programmers find their own ways to implementing database tasks and as the result there are many methods to resolve though often incorrect.

It is rather hard to elaborate the right approach to implementation. The primary issues here are weak documentation and non-evident features in the classes of the android.database.sqlite package.

The following errors are the first evidence that it is high time to think of the architecture:

1. database is locked – shows up at multithreaded record to the database.
2. database is closed – may appear when working with the database from various parts of the program e.g. Activity and Service.
3. corrupted database – arises if the database file is corrupted either by the user or at unexpected interruption of recording to the database (phone switched off, OS error, lack of space, bad sectors on the SD card etc.)
4. low efficiency at working with the DB – may appear due to internal deadlocks, concurrent transactions, high volume of logging, absence of batch processing.

Let’s review in details the reasons of why the issues arise and possible “non-evident” demonstration of such issues as well as methods to their resolution.

The “Database Is Locked” Issue (Aka Concurrency)

Programmers often face the question like “how to better work with SQLiteOpenHelper”. Indeed since any part of the program can send a query to the data access layer (service, presenter, widget, etc.) then?SQLiteOpenHelpershould be available everywhere alongside with Context. Also there is a question if it’s worth creating a personal connection to the database for each part of the program, will then the speed of queries be increased? There are questions on concurrent access to the database and of course locking on writing.

First of all it should be noted that locking in SQLite is done on the file level which guarantees locking of changes from different threads and connections. Thus multiple threads can read the database however one can only write to it. More on locking in SQLite can be read at?SQLite documentation?but we are most interested in the API provided by OS Android.

Writing with two concurrent threads can be made both from a single and from multiple database connections. Since only one thread can write to the database then there are two variants:

1. If you write from two threads of one connection then one thread will await on the other to finish writing.
2. If you write from two threads of different connections then an error will be – all of your data will not be written to the database and the application will be interrupted with?SQLiteDatabaseLockedException.

It becomes evident that the application should always have only one copy of?SQLiteOpenHelper(just an open connection) otherwise?SQLiteDatabaseLockedException?can occur at any moment.

Different Connections At a Single SQLiteOpenHelper

Everyone is aware that?SQLiteOpenHelper?has 2 methods providing access to the database?getReadableDatabase()and?getWritableDatabase(), to read and write data respectively. However in most cases there is one real connection. Moreover it is one and the same object:

SQLiteOpenHelper.getReadableDatabase()==SQLiteOpenHelper.getWritableDatabase()

It means that there is no difference in use of the methods the data is read from. However there is another undocumented issue which is more important – inside of the class?SQLiteDatabase?there are own locks – the variable mLock. Locks for writing at the level of the object?SQLiteDatabase?and since there is only one copy ofSQLiteDatabase?for read and write then data read is also blocked. It is more prominently visible when writing a large volume of data in a transaction.

Besides the internal locks the?SQLiteDatabase?Class has another interesting feature: this class (up to API 11) allows creating transactions only in the exclusive transaction mode. Because of this holdups may occur when actively working with the database. Let’s consider an example of such an application that should download a large volume of data (approx. 7000 lines containing BLOB) in the background on first launch and save it to the database. If the data is saved inside the transaction then saving takes approx. 45 seconds but the user can not use the application since any of the reading queries are blocked. If the data is saved in small portions then the update process is dragging out for a rather lengthy period of time (10-15 minutes) but the user can use the application without any restrictions and inconvenience. “The double edge sword” – either fast or convenient. The reasons for such an issue and summary are covered in the article from Kevin Galligan “Android Sqlite Locking”.

Then how shall we resist the “standard” behavior? In the new versions of Android starting from API 11 Google has already fixed a part of issues related to?SQLiteDatabase?functionality as the following methods have been added:

beginTransactionNonExclusive()?– creates a transaction in the “IMMEDIATE mode”.

yieldIfContendedSafely()?– temporary seizes the transaction in order to allow completion of tasks by other threads.

isDatabaseIntegrityOk()?– checks for database integrity

Please read in more details in the?documentation.

However for the older versions of Android this functionality is required as well.

The Solution

First locking should be turned off and allow reading the data in any situation.

SQLiteDatabase.setLockingEnabled(false);?cancels using internal query locking – on the logic level of the java class (not related to locking in terms of SQLite)

SQLiteDatabase.execSQL(“PRAGMA read_uncommitted = true;”);?Allows reading data from cache. In fact, changes the level of isolation. This parameter should be set for each connection anew. If there are a number of connections then it influences only the connection that calls for this command.

SQLiteDatabase.execSQL(“PRAGMA synchronous=OFF”);?Change the writing method to the database – without “synchronization”. When activating this option the database can be damaged if the system unexpectedly fails or power supply is off. However according to the?SQLite documentation?some operations are executed 50 times faster if the option is not activated.

Unfortunately not all of PRAGMA is supported in Android e.g.?“PRAGMA locking_mode = NORMAL”?and?“PRAGMA journal_mode = OFF”?and some others are not supported. At the attempt to call PRAGMA data the application fails.

In the documentation for the method setLockingEnabled it is said that this method is recommended for using only in the case if you are sure that all the work with the database is done from a single thread. We should guarantee than at a time only one transaction is held. Also instead of the default transactions (exclusive transaction) the immediate transaction should be used. In the older versions of Android (below API 11) there is no option to create the immediate transaction thru the java wrapper however SQLite supports this functionality. To initialize a transaction in the immediate mode the following SQLite query should be executed directly to the database, – for example thru the method execSQL:

SQLiteDatabase.execSQL(“begin immediate transaction”);

Since the transaction is initialized by the direct query then it should be finished the same way:SQLiteDatabase.execSQL(“commit transaction”);

Then TransactionManager is the only thing left to be implemented which will initiate and finish transactions of the required type. The purpose of?TransactionManager?– is to guarantee that all of the queries for changes (insert, update, delete, DDL queries) originate from the same thread.

The “Database Is Closed” Issue

When working with the database from a single?Activity?thru?SQLiteOpenHelper?it is obvious that the database should be opened together with?Activity?and closed when closing?Activity. However if a number of?Activities?are concurrently working with the database as well as?Services?and part of data is shared by?ContentProvider?then there is a question: when the connection with the database should be opened and closed? If the connection is opened and closed after every query then the database query speed will fall down in times and if it is opened when the application starts and close on shutdown it is not clear when we leave the application (and if the service is still working or the provider is not used – there is only one method left –?Application.onTerminate()). However none of the methods is correct. The database connection can be automatically closed upon the following conditions:

If a number of Activities independently from each other are opening new connections then the error may occur that is described in the case “database is locked” above.

If we open a connection with the database upon the application start and close at Application.onTerminate(), the database connection can be closed on its own at another call for?Cursor.getCount()?or?Cursor.onMove(). If we carefully take a look at the source code of the respective classes then we can see that at some combination of conditions the method?SQLiteDatabase.onAllReferencesReleased()?will be finally called that will call the native method?dbclose().?Here?is a more detailed description of the issue, the succession of calls and required conditions are described?here.

Probably this is one of the reasons why “ManagedCursor” has been claimed “Deprecated”.

This issue is widely known and multiple ways have been proposed to its resolution.

Variant 1

At every call to the database one should check if the database is opened or closed and if opened then it should be reopened.

Share on email

?