The research summary of Android SQLite3 multithreading problem _android

When I recently made a project, I threw an exception when I read and write the database in multithreading, which is naturally a place where I have no understanding of SQlite3, so I have studied it carefully afterwards.

1. With regard to the true role of Getwriteabledatabase () and Getreadabledatabase ()
Getwriteabledatabase () is actually a sub method equivalent to Getreadabledatabase (), Getwriteabledatabase () is a reference that can only return a read-write Sqlitedatabase, and throws an exception if the database is not writable, such as when the database is full of disk space. The getreadabledatabase () generally defaults to calling the Getwriteabledatabase () method, which returns a reference to a sqlitedatabase that is read-only when the database is not writable, which is the most obvious difference.

The key source code is as follows:

Public synchronized Sqlitedatabase getwritabledatabase () {
  if (mdatabase!= null) {
    if (!mdatabase.isopen ()) { c3/>//darn! The user closed the database by calling Mdatabase.close ()
    mdatabase = null;
    } else if (!mdatabase.isreadonly ()) { C6/>return mdatabase; The database is already open for business
    }
  ...

Public synchronized Sqlitedatabase getreadabledatabase () {
  if (mdatabase!= null) {
    if (!mdatabase.isopen ()) {
    //darn! the user closed the database by calling Mdatabase.close ()
    mdatabase = null;
    Mdatabase; The database is already open for business
    }
  ...
  try {return
    getwritabledatabase ();
  }
... ...

2.SQLiteDatabase Sync Lock

In fact, when using only one sqlitedatabase reference, Sqlitedatabase adds a lock to the crud operation (because it is a db file, so it is accurate to the database level), which ensures that at the same time you can only do one operation, whether it is in the same thread, This leads to the "futility" of any optimization of database reads and writes if you use a single example pattern for Sqliteopenhelper in your program.

3. Multi-threaded reading database

Look carefully at the source you will find that only add,delete,update in the database operation will call Lock (), while query () is not invoked, but when loading the data, the Sqlitequery Fillwindow method is invoked. The method will still call Sqlitedatabase.lock (), so in order to truly implement a multithreaded read database, only each thread can use its own Sqliteopenhelper object for read operations, thus avoiding the synchronization lock. The key source code is as follows:

/* Package */int Fillwindow (cursorwindow window,
  int maxread, int lastpos) {
  long Timestart = Systemclock.uptimem Illis ();
  Mdatabase.lock ();
  Mdatabase.logtimestat (mSQL, Timestart, sqlitedatabase.get_lock_log_prefix);
  try {.....

4. Multi-threaded reading and writing

The key to implementing multithreaded reading and writing is the enablewriteaheadlogging attribute, which is added by API level 11, which means that more than 3.0 of the versions are basically impossible to achieve real multithreaded reading and writing. Simply put, by calling Enablewriteaheadlogging () and disablewriteaheadlogging (), you can control whether the data is run by multithreaded read-write and, if allowed, It will allow a write thread to work with multiple read threads at the same time on a sqlitedatabase. The implementation of the principle is that the write operation is actually in a separate log file, read the operation is read the original data file, is written before the beginning of the contents of the operation, and thus do not affect each other. The read operation will detect the state of the new database when the write operation is completed. Of course, the disadvantage of doing this is that it will consume more memory space.

5. Multi-threaded writing

This is not a lot to think about, SQLite does not support, if there is a need to use multiple database files.

6. Notes

(1) Have you ever wanted to sqlite the maximum number of database connections, in fact, in the official API document (Enablewriteaheadlogging () method) to give the most accurate answer: the maximum number of connections used To execute queries in parallel are dependent upon the device memory and possibly other properties. It's just to see how much memory you have, but I think it's a bit big, not ？ Ha ha.

(2) When you use only one sqlitedatabase reference in multiple threads, you need to pay special attention to the timing of your sqlitedatabase.close (), because you are using the same reference, In one thread, for example, when a single add operation closes the database connection immediately, and the other site is ready to perform the query operation, the DB is closed and then the exception is reported. At this point there are generally three solutions, ① simply rude to add a synchronized keyword to all crud; ② never closes the database connection, only the last exit is the connection closed. In fact, each time you execute the getwriteabledatabase () or Getreadabledatabase () method, if you have a database connection that you have already established, you return directly (the exception: if the old connection was opened as read-only, the new connection succeeds, Turn off old connections, so there will always be one database connection in the program (provided it is a single case), with very little resource consumption. ③ can be counted by reference, the simple example code is as follows:

Open database method public
synchronized Sqlitedatabase OpenDatabase () {
if (mopencounter.incrementandget () = 1) {
 / /Opening New Database
 try {
 mdatabase = Sinstance.getwritabledatabase ();
 } catch (Exception e) {
 Mdatabase = Sinstance.getreadabledatabase ();
 }
 }
return mdatabase;
}

Close Database method public
synchronized void CloseDatabase () {
 if (mopencounter.decrementandget () = 0) {
 // Closing database
 mdatabase.close ();
 }
 

(3) There are some good habits and common sense, such as close cursor, use Transaction,sqlite to store data in fact do not distinguish between types, and SQLite support most of the standard SQL statements, additions and deletions to check statements are universal and so on.