Android multi-threaded database reading and writing analysis and optimization "Turn"

Original: http://blog.csdn.net/lize1988/article/details/9700723

The latest need for software to do database reading and writing optimization, both read and write before, are using a sqliteopenhelper.getwriteabledatabase () to operate the database, now requires multi-threaded concurrent read and write, the project is 2.2 of the SDK.

Android database system with SQLite, SQLite each database is actually a. db file, its synchronization lock is accurate to the database level, not with other databases have a table lock, row lock.

So for realism on demand, you can use multiple database files.

Alas, this database in the multi-threaded concurrent read and write aspect itself is quite fucked.

The following analysis of different situations, in the same database file operation, SQLite performance.

The test program runs on 2.2 virtual phones, 4.2.1 virtual phones, 4.2.1 real phones.

1, multi-threaded write, use a sqliteopenhelper. It also ensures that multithreading uses a sqlitedatabase.

First look at the relevant source code

//Sqlitedatabase.java   Public Longinsertwithonconflict (String table, String nullcolumnhack, Contentvalues initialvalues,intconflictalgorithm) {          if(!IsOpen ()) {              Throw NewIllegalStateException ("Database not open"); }            ....          Omit lock (); Sqlitestatement Statement=NULL; Try{statement=compilestatement (sql.tostring ()); //Bind the values            if(EntrySet! =NULL) {                  intSize =entryset.size (); Iterator<map.entry<string, object>> entriesiter =Entryset.iterator ();  for(inti = 0; i < size; i++) {Map.entry<string, object> entry =Entriesiter.next (); Databaseutils.bindobjecttoprogram (statement, I+ 1, Entry.getvalue ()); }              }                //Run The program and then cleanupStatement.execute (); LongInsertedrowid =Lastinsertrow (); if(Insertedrowid = =-1) {log.e (TAG,"Error inserting" + initialvalues + "using" +SQL); } Else {                  if(Config.logd &&log.isloggable (TAG, Log.verbose)) {LOG.V (TAG,"Inserting Row" + Insertedrowid + "from" + Initialvalues + "using" +SQL); }              }              returnInsertedrowid; } Catch(sqlitedatabasecorruptexception e) {oncorruption (); Throwe; } finally {              if(Statement! =NULL) {statement.close ();          } unlock (); }      }  

//Sqlitedatabase.java     Private FinalReentrantlock MLock =NewReentrantlock (true); /* Package*/ voidLock () {if(!mlockingenabled)return;                  Mlock.lock (); if(sqlitedebug.debug_lock_time_tracking) {if(Mlock.getholdcount () = = 1) {                            //Use elapsed real-time since the CPU could sleep when waiting for IOMlockacquiredwalltime=Systemclock.elapsedrealtime (); Mlockacquiredthreadtime=Debug.threadcputimenanos (); }           }     }  

It is known through the source that when an insert is performed, the lock of the member object Mlock of the Sqlitedatabase object is requested to ensure that the insert does not execute concurrently.

The test does not throw an exception.

But we can bypass this lock by inserting multiple Sqlitedatabase objects at the same time.

2, multi-threaded writing, using multiple sqliteopenhelper, may throw an exception when inserting, causing an insert 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.execute (sqlitestatement.java:55)

E/database (1471): at Android.database.sqlite.SQLiteDatabase.insertWithOnConflict (sqlitedatabase.java:1549)

Multithreaded writes, each thread uses a sqliteopenhelper, which makes each thread use a Sqlitedatabase object. Multiple threads execute inserts at the same time, and last call to local method Sqlitestatement.native_execute

Throw exception, visible Android frame, multi-threaded Write database local method without synchronous lock protection, concurrent write will throw exception.

Therefore, multithreaded writing must use the same Sqliteopenhelper object.

3, multithreaded Read

See Sqlitedatabase source code can know, insert, UPDATE, EXECSQL will call Lock (), at first glance only query does not call lock (). But...

Look closely and find

Finally, the query result is a Sqlitecursor object.

Sqlitecursor saved the query condition, but did not immediately execute the query, but instead used the lazy policy to load some of the data when needed.

When the data is loaded, the Fillwindow method of Sqlitequery is called, and the method still calls Sqlitedatabase.lock ()

/*** Reads rows into a buffer.    This method acquires the database lock. *    * @paramwindow The window to fill in *@returnNumber of total rows in the query*/    /* Package*/ intFillwindow (Cursorwindow window,intMaxread,intLastpos) {        LongTimestart =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 and then most likely windows is//too small for the data set, loading by another thread//Is isn't safe in this situation. The native code would ignore Maxread              intNumRows =Native_fill_window (window, window.getstartposition (), Moffsetindex, Maxread, Lastpos); //Logging              if(sqlitedebug.debug_sql_statements) {log.d (TAG,"Fillwindow ():" +MSQL);                } mdatabase.logtimestat (MSQL, Timestart); returnNumRows; } Catch(IllegalStateException e) {//simply ignore it              return0; } Catch(sqlitedatabasecorruptexception e) {mdatabase.oncorruption (); Throwe; } finally{window.releasereference (); }        } finally{releasereference ();        Mdatabase.unlock (); }    }  

So want to multi-threaded read, there is no sync lock between the read, and each thread has to use their own Sqliteopenhelper object, tested, no problem.

4, multi-threaded Read and write

The end goal we want to achieve is multithreading concurrent read and write

Multithreading before writing already know the result, only one write at a time.

Multithreaded reads can be concurrent

Therefore, use the following policy:

One thread writes, multiple threads read simultaneously, and each thread uses its own sqliteopenhelper.

This way, in the Java layer, there is no lock between all threads, that is, there is no lock between write and read, and no lock between read and read.

An insert exception was 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)

Inserting an exception, which indicates that the database is written when the thread is read, throws an exception.

Analysis source can know, sqliteopenhelper.getreadabledatabase () not necessarily get is read-only sqlitedatabase.

//Sqliteopenhelper.java     Public synchronizedsqlitedatabase getreadabledatabase () {if(Mdatabase! =NULL&&Mdatabase.isopen ()) {             <span style= "color: #FF0000;" >returnMdatabase;</span>//The database is already open for business        }            if(misinitializing) {Throw NewIllegalStateException ("Getreadabledatabase called recursively"); }            Try {              returngetwritabledatabase (); } Catch(sqliteexception e) {if(Mname = =NULL)ThrowE//Can ' t open a temp database read-only! LOG.E (TAG, "couldn ' t open" + Mname + "for writing (would 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 NewSqliteexception ("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; returnmdatabase; } finally{misinitializing=false; if(db! =NULL&& db! =mdatabase) db.close (); }      } 

Because it first looks at whether there are sqlitedatabase that have already been created, try to create read-write Sqlitedatabase first, and then try to create a readonly sqlitedatabase after the failure.

So I wrote a new way to get read-only sqlitedatabase.

//Dbhelper.java//DBHelper extends Sqliteopenhelper Publicsqlitedatabase getonlyreaddatabase () {Try{getwritabledatabase ();//Keep the database version up to date}Catch(sqliteexception e) {log.e (TAG,"Couldn ' t open" + Mname + "for writing (would try Read-only):", E); } sqlitedatabase DB=NULL; Try{String path=Mcontext.getdatabasepath (mname). GetPath (); DB=sqlitedatabase.opendatabase (Path, mfactory, sqlitedatabase.open_readonly); if(Db.getversion ()! =mnewversion) {                  Throw NewSqliteexception ("Can ' t upgrade read-only database from version" +db.getversion ()+ "to" + Mnewversion + ":" +path);              } onOpen (db);              Readonlydbs.add (DB); returnDB; } finally {          }  }  

Use policy: One thread writes, multiple threads read at the same time, with only one sqliteopenhelper, and the read thread uses its own write Getonlyreaddatabase () method to get read-only.
However, after testing, the exception is thrown, only the insertion exception on 2.2, and even the read exception on 4.1.2.

4.1.2 on the test, read the 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 the road is different.

in fact, Sqlitedatabase in API 11 more than one attribute Enable_write_ahead_logging.

Can be hit, enablewriteaheadlogging (), can turn off disablewriteaheadlogging (), default is off.

What does this attribute mean?

Reference API documentation, this property is closed, not allowed to read, write at the same time, through the lock to ensure.

When opened, it allows a write thread to function with multiple read threads at the same time on a sqlitedatabase. The implementation principle is that the write operation is actually in a separate file, not the original database file. So writing at execution time does not affect the read operation, the read operation is read the original data file, is the content before the start of the write operation.

After the successful execution of the write operation, the changes will be merged into the original database file. The read operation is now able to read the modified content. However, this will cost more memory.
With it, multi-threaded read-write problem solved, but only in the API 11 use.

So you can only judge the SDK version, if more than 3.0, open this property

 Public Boolean Enablewal) {          thisnull, default_version);           if (Enablewal && Build.VERSION.SDK_INT >=) {              getwritabledatabase (). enablewriteaheadlogging ();          }  }  

For this property of Sqlitedatabase, refer to the API documentation, or see the description of the Sqlitesession.java in the multithreaded database.

Sqlitesession.java

Conclusion

Want to multi-threaded concurrent read and write, 3.0 below do not think, more than 3.0, directly set enablewriteaheadlogging () on OK.

If you are still not up to the requirements, use multiple DB files.

Other:

The unit has a Samsung Note2 mobile phone, all of the above examples run up with no problem .... Very good and very powerful.

Finally, attach my test procedure.

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

Android multi-threaded database reading and writing analysis and optimization "Turn"