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.

This method has an evident drawback – if we query the database and then save the link to already opened copy and use the received copy not calling SQLiteDatabase.getReadableDatabase() then this method won’t work.

Variant 2

Mandatory adding a fake reference to the database and keep it while the database is in useSQLiteClosable.acquireReference();

However here the database should be closed with preliminary cleanup of all the references manually created. Nevertheless the number of references may become zero therefore it is required to constantly track the number of references and populate them when necessary. Though this method is not quite successful.

Variant 3

Close and open the database after every query. This method is unreliable since two independent connections for writing are feasible to be created and an error will occur. If using this approach only for reading then an error won’t happen however will significantly slows down the application efficiency (especially when there is a large number of queries to the database).

Variant 4

Using ContentProvider to access the database. And it is desirable to use exactly one provider – it is simple to implement since it can be added with an unlimited number of Uri’s. The point is that ContentProvider tracks the database status. And the decision on when it is time to close the database is assigned to the OS – it will delete the old providers from the memory and return them when first required.

Please review the detailed description on how to work with ContentProvider on the official web site.

The Issue of the “Corrupted Database”

There is little space on Android phones left for applications and this space should be taken care of otherwise the user will sacrifice your application for another game. Almost any application uses a database to store data and if the database is too large then it is desirable to store it on the SD card. The older versions of Android (2.2 and earlier) do not allow creating the database on the SD card via standard tools of SQLiteOpenHelper however this can be bypassed if using AndroidConnectionSource from ORMLite.

One should bear in mind that anything that is visible to the user can be deleted. The user can delete or otherwise corrupt a database file, can take out the SD card from the device and many other things. However the user is not only one who can corrupt the database. A telephone is the device with unreliable power supply – part of data can be left unrecorded (the most vital if logging is not activated), the database can be corrupted when on download or when using a preinstalled database etc. please read more on items that can corrupt the database in the article“How To Corrupt An SQLite Database File”.

If the developer has not managed to implement the database restore mechanism then Android will create the database anew. However it happens that the database can be restored. The simplest way – query data from any available tables and insert them to the new database. However often it would be just enough to execute the“VACUUM” command – this method recreates the database and restores maximum of data.

Frequently it is required to create an application with preinstalled data. For this an available database can be taken and placed to the raw folder and when installing the application the database will be copied to the device. The file with the database would be best to be saved to the raw folder. The folder assets seems to be more proper since it can be compressed however it is impossible to get data over 1 Mb (please see [here]), and therefore the database should be split into files of 1 Mb which is rather inconvenient. It is important that the database should always be built on the emulator of the lowest of the supported versions since if the preinstalled database is built on Android 2.3 then on Android 2.2 it will give the “corrupted database” error though on devices 2.3 and above the database will work correctly.

Optimizing Queries

Speed of completing queries is made of multiple factors however optimizing the query and the database structure are the most important ones. In order to optimize queries there are many standard methods which can be easily found in the Internet so let’s just specify SQLite optimization features. For brevity let’s just arrange them as theses.

There is no need in writing queries that return over 1000 lines or data of over 1 Mb – always use the operator limit. If a query returns more than 1000 lines then a warning will be given to the log or the application will fail depending on available memory and the device. In case a long list is required to be displayed there are two solutions:

a) Query the list in parts and then merge using android.database.CursorJoiner.

b) Implement the auto updated list on the interface (the list with resumable download).

Single query is much faster than 2 separate. Join is more preferable to be used however execute 1 query. An order for restrictions to join is very important so that there is no Cartesian product when strings are selected by the operator “where”.

If anything is required to be changed in the database – do it in the transaction. This will not only guarantee the data integrity but significantly accelerates execution of the task. The thing is that at any change to the database the changes file is created next to the file. If you do 100 inserts then 100 times the changes file will be created and deleted and if the 100 inserts are in the transaction then the changes file is created only once.

If you need to create a table from already existing data then use INSERT AS SELECT (do not execute separate INSERT) which significantly facilitates the execution speed.

If you have received too much data from the file at once and such “large” query is not often repeated then clean the unnecessary memory SQLiteDatabase.releaseMemory().

First more simple conditions should be written in the operator where

SELECT * FROM tablename WHERE col1 LIKE ‘%string%’ AND col2 = 123456

Works 3-4 times slower than

SELECT * FROM tablename WHERE col2 = 123456 AND col1 LIKE ‘%string%’

Correct indexing of tables facilitates execution of queries 5-7 times. Indexing should be made for those fields that are with join and further to where search is made. And it is better to indicate the direction for the indexer for example:

CREATE INDEX index_name ON table_name (column_name ASC).

Use FTS3 for large tables with search which significantly speeds up text search by the table. Instead of LIKE use MATCH however be aware that MATCH on default works as search for a word as a whole and not for the sub-string. Please see the description of FTS3.

The Conclusion

In this article the main issues have been outlined when working with SQLite in Android. Unfortunately the API has a lot of gaps, to resolve some issues there is no documentation available as well as from time to time at work the system errors are detected. However the fact that with every new version the Android API is getting more flexible and comprehensive, errors are being fixed and documentation enhanced.

Want to benefit by our experience in mobile application development for Android? Start with your project estimation right now!Share on email