Read/write analysis and optimization of multi-threaded Android Databases

Document directory

• 1. multi-thread writing. Use a sqliteopenhelper. This ensures that a sqlitedatabase is used in multiple threads.
• 2. multi-threaded writing. Multiple sqliteopenhelper may cause an exception during insertion, resulting in an insertion error.
• 3. multi-thread reading
• 4. multi-thread read/write

The latest needs to optimize the database read and write for the software. Previously, no matter whether it is read or write, a sqliteopenhelper is used. getwriteabledatabase () is used to operate the database. Now multi-thread concurrent read/write is required. The project uses the 2.2 SDK.

The android database system uses SQLite, and every database of SQLite is actually. database file, and its synchronization lock is accurate to the database level. It cannot have table locks or row locks with other databases.

Therefore, if you have requirements for realism, you can use multiple database files.

Ah, this database is very good at multi-thread concurrent read/write.

Next we will analyze the performance of SQLite in different situations when operating on the same database file.

The test program runs on the 2.2 virtual phone, 4.2.1 virtual phone, and 4.2.1 real phone.

1. multi-thread writing. Use a sqliteopenhelper. This ensures that a sqlitedatabase is used in multiple threads.

First look at the relevant source code

// Sqlitedatabase. Java public long insertwithonconflict (string table, string nullcolumnhack, contentvalues initialvalues, int conflictalgorithm) {If (! Isopen () {Throw new illegalstateexception ("database not open ");}.... omit lock (); sqlitestatement Statement = NULL; try {Statement = compilestatement (SQL. tostring (); // bind the values if (entryset! = NULL) {int size = entryset. size (); iterator <map. entry <string, Object> entriesiter = entryset. iterator (); For (INT I = 0; I <size; I ++) {map. entry <string, Object> entry = entriesiter. next (); databaseutils. bindobjecttoprogram (statement, I + 1, entry. getvalue () ;}}// run the program and then cleanup statement.exe cute (); long insertedrowid = lastinsertrow (); If (insertedrowid =-1) {log. E (Ta G, "error inserting" + initialvalues + "using" + SQL);} else {If (config. logd & log. isloggable (TAG, log. verbose) {log. V (TAG, "inserting row" + insertedrowid + "from" + initialvalues + "using" + SQL) ;}return insertedrowid;} catch (sqlitedatabase+uptexception e) {on=uption (); throw E;} finally {If (statement! = NULL) {statement. Close () ;}unlock ();}}

//SQLiteDatabase.java  private final ReentrantLock mLock = new ReentrantLock(true);/* package */ void lock() {       if (!mLockingEnabled) return;              mLock.lock();              if (SQLiteDebug.DEBUG_LOCK_TIME_TRACKING) {                  if (mLock.getHoldCount() == 1) {                        // Use elapsed real-time since the CPU may sleep when waiting for IO                       mLockAcquiredWallTime = SystemClock.elapsedRealtime();                        mLockAcquiredThreadTime = Debug.threadCpuTimeNanos();                  }       } }

By using the source code, we can know that when executing the insert operation, we will request the mlock lock of the member object of the sqlitedatabase object to ensure that the insert operation will not be executed concurrently.

The test will not cause exceptions.

However, we can use multiple sqlitedatabase objects to insert at the same time to bypass this lock.

2. multi-threaded writing. Multiple sqliteopenhelper may cause an exception during insertion, resulting in an insertion error.

E/Database (1471): Android. database. SQLite. sqliteexception: Error Code 5: database is locked08-01

E/Database (1471): at Android. database. SQLite. sqlitestatement. native_execute (native method)

E/Database (1471): At android.database.sqlite.sqlitestatement.exe cute (sqlitestatement. Java: 55)

E/Database (1471): at Android. database. SQLite. sqlitedatabase. insertwithonconflict (sqlitedatabase. Java: 1549)

Multi-thread writing. Each thread uses one sqliteopenhelper, which enables each thread to use one sqlitedatabase object. Multiple Threads execute Insert at the same time, and finally call the local method sqlitestatement. native_execute

Throw an exception. It can be seen that the local method of multi-thread database writing in the android framework does not have synchronization lock protection, and concurrent writing throws an exception.

Therefore, the same sqliteopenhelper object must be used for multi-threaded writing.

3. multi-thread reading

You can see the source code of sqlitedatabase. insert, update, and execsql call lock (). At first glance, only query does not call lock (). But...

Take a closer look and find

Finally, the query result is a sqlitecursor object.

Sqlitecursor saves the query conditions, but does not execute the query immediately. Instead, it uses the lazy policy to load part of the data as needed.

When loading data, sqlitequery's fillwindow method is called, and this method still calls sqlitedatabase. Lock ()

  /**     * Reads rows into a buffer. This method acquires the database lock.     *     * @param window The window to fill into     * @return number of total rows in the query     */    /* package */ int fillWindow(CursorWindow window,            int maxRead, int lastPos) {        long timeStart = SystemClock.uptimeMillis();        mDatabase.lock();        mDatabase.logTimeStat(mSql, timeStart, SQLiteDatabase.GET_LOCK_LOG_PREFIX);        try {            acquireReference();            try {                window.acquireReference();                // if the start pos is not equal to 0, then most likely window is                // too small for the data set, loading by another thread                // is not safe in this situation. the native code will ignore maxRead                int numRows = native_fill_window(window, window.getStartPosition(), mOffsetIndex,                        maxRead, lastPos);                // Logging                if (SQLiteDebug.DEBUG_SQL_STATEMENTS) {                    Log.d(TAG, "fillWindow(): " + mSql);                }                mDatabase.logTimeStat(mSql, timeStart);                return numRows;            } catch (IllegalStateException e){                // simply ignore it                return 0;            } catch (SQLiteDatabaseCorruptException e) {                mDatabase.onCorruption();                throw e;            } finally {                window.releaseReference();            }        } finally {            releaseReference();            mDatabase.unlock();        }    }

Therefore, if you want to read multiple threads without synchronization locks between reads, each thread must use its own sqliteopenhelper object. After testing, there is no problem.

4. multi-thread read/write

What we ultimately want to achieve is multi-thread concurrent read/write.

Results are known before multi-thread writing. Only one write can be performed at a time.

Multi-thread read concurrency

Therefore, use the following policy:

One thread writes and multiple threads read at the same time. Each thread uses its own sqliteopenhelper.

In this way, in the Java layer, all threads are not locked, that is, there is no lock between writing and reading, and there is no lock between reading and reading.

An Insertion exception is found.

E/sqlitedatabase (18263): Error inserting descreption = insertthread #01375493606407
E/sqlitedatabase (18263): Android. database. SQLite. sqlitedatabaselockedexception: database is locked (Code 5)
E/sqlitedatabase (18263): at Android. database. SQLite. sqliteconnection. nativeexecuteforlastinsertedrowid (native method)

An Insert exception occurs when a database is written in a thread. An exception is thrown.

The source code can be analyzed. What sqliteopenhelper. getreadabledatabase () does not necessarily obtain is the read-only sqlitedatabase.

//  SQLiteOpenHelper.java  public synchronized SQLiteDatabase getReadableDatabase() {        if (mDatabase != null && mDatabase.isOpen()) {            return mDatabase;  // The database is already open for business        }        if (mIsInitializing) {            throw new IllegalStateException("getReadableDatabase called recursively");        }        try {            return getWritableDatabase();        } catch (SQLiteException e) {            if (mName == null) throw e;  // Can't open a temp database read-only!            Log.e(TAG, "Couldn't open " + mName + " for writing (will try read-only):", e);        }        SQLiteDatabase db = null;        try {            mIsInitializing = true;            String path = mContext.getDatabasePath(mName).getPath();            db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READONLY);            if (db.getVersion() != mNewVersion) {                throw new SQLiteException("Can't upgrade read-only database from version " +                        db.getVersion() + " to " + mNewVersion + ": " + path);            }            onOpen(db);            Log.w(TAG, "Opened " + mName + " in read-only mode");            mDatabase = db;            return mDatabase;        } finally {            mIsInitializing = false;            if (db != null && db != mDatabase) db.close();        }    }

It first checks whether there are any created sqlitedatabases. If not, it first tries to create and read sqlitedatabase. If it fails, it tries to create a read-only sqlitedatabase.

Therefore, a new method is written to obtain the read-only sqlitedatabase.

// Dbhelper. java // dbhelper extends sqliteopenhelperpublic sqlitedatabase getonlyreaddatabase () {try {getwritabledatabase (); // ensure the latest database version} catch (sqliteexception e) {log. E (TAG, "couldn't open" + mname + "for writing (will try read-only):", e) ;} sqlitedatabase DB = NULL; try {string Path = mcontext. getdatabasepath (mname ). getpath (); DB = sqlitedatabase. opendatabase (path, mfactory, sqlitedatabase. OPE N_readonly); If (db. getversion ()! = Mnewversion) {Throw new sqliteexception ("can't upgrade read-only database from version" + dB. getversion () + "to" + mnewversion + ":" + path);} onopen (db); readonlydbs. add (db); Return dB ;}finally {}}

Usage Policy: one thread writes and multiple threads read at the same time. Only one sqliteopenhelper is used. The read thread uses the getonlyreaddatabase () method written by itself to obtain read-only data.
However, after testing, an exception will still be thrown. On 2.2, only the insertion exception, and on 4.1.2, there may even be a read exception.

4.1.2 reading exception.
E/sqlitelog (18263): (5) database is locked
W/dalvikvm (18263): threadid = 21: thread exiting with uncaught exception (group = 0x41e2c300)
E/androidruntime (18263): Fatal exception: onlyreadthread #8
E/androidruntime (18263): Android. database. SQLite. sqlitedatabaselockedexception: database is locked (Code 5):, while compiling: Select * From test_t

It seems that this road is different.

In fact, sqlitedatabase has an additional attribute in API 11.Enable_write_ahead_logging.

You can call enablewriteaheadlogging () to disable disablewriteaheadlogging (). It is disabled by default.

What does this attribute mean?

Refer to the API documentation. When this attribute is disabled, it is not allowed to read and write at the same time. It is guaranteed by locking.

When enabled, it allows one write thread and multiple read threads to work on one sqlitedatabase at the same time. The principle is that the write operation is actually in a separate file, not the original database file. Therefore, the read operation is not affected when it is written during execution. The read operation reads the original data file, which is the content before the write operation starts.

After the write operation is successful, the changes are merged into the original database file. In this case, the read operation can read the modified content. However, this will consume more memory.
With it, the multi-threaded read/write problem is solved, but it is a pity that it can only be used in API 11 or above.

Therefore, you can only determine the SDK version. If the SDK version is later than 3.0, this attribute is enabled.

public DbHelper(Context context , boolean enableWAL) {this(context, DEFAULT_DB_NAME, null, DEFAULT_VERSION);if( enableWAL && Build.VERSION.SDK_INT >= 11){getWritableDatabase().enableWriteAheadLogging();}}

For details about this attribute of sqlitedatabase, refer to the API documentation. You can also refer to the description of the multi-threaded database read/write in sqlitesession. java.

Sqlitesession. Java

Conclusion

If you want multi-thread concurrent read/write, do not think about it for less than 3.0. If you want to use more than 3.0, set enablewriteaheadlogging () to OK.

If you still cannot meet the requirements, use multiple dB files.

In addition:

The company has a Samsung note2 mobile phone. All the above examples have no problems .... Very good and powerful.

Finally, attach my test program.

Https://github.com/zebulon988/SqliteTest.git

Exclusive statement. If you have any questions, please reply to me. Thank you!