Research summary of Android SQLite3 multithreading operation

Recently, when I was working on a project, I threw an exception when reading and writing the database through multithreading. Naturally, I didn't understand SQLite3 well, so I explored it carefully afterwards.

1. About the real functions of getwriteabledatabase() and getreadabledatabase(), getwriteabledatabase() is actually a sub method of getreadabledatabase(). Getwriteabledatabase() can only return a reference to sqlitedatabase opened in read-write mode. If the database is not writable at this time, exceptions will be thrown, such as when the disk space of the database is full. Getreadabledatabase () generally calls getwriteabledatabase () method by default. If the database is not writable, it will return a reference to sqlitedatabase opened in read-only mode, which is the most obvious difference between the two.

The key source codes are as follows:

2. Synchronization lock of sqlitedatabase

In fact, when only one sqlitedatabase reference is used, sqlitedatabase will add a lock to CRUD operations (because it is a DB file, so it is accurate to the database level), which ensures that you can only perform one operation at the same time, whether in the same thread or not, which leads to if you use the singleton mode for sqliteopenhelper in the program, Then any optimization of database reading and writing is "futile".

3. Multi thread database reading

If you look at the source code carefully, you will find that only add, delete and update will call lock (), but query () will not. However, when loading data, sqlitequery's fillwindow method is called, and this method will still call sqlitedatabase. Lock (). Therefore, to truly realize multi-threaded database reading, Each thread can only use its own sqliteopenhelper object for read operations, so as to avoid synchronization locks. The key source codes are as follows:

4. Multithreaded reading and writing

The key to multi-threaded reading and writing is the enablewriteaheadlogging attribute. This method is added to API level 11, that is, it is almost impossible to realize real multi-threaded reading and writing in versions above 3.0. Simply put, by calling enablewriteaheadlogging() and disablewriteaheadlogging(), you can control whether the data is read and written by multiple threads. If allowed, it will allow one write thread and multiple read threads to work on a sqlitedatabase at the same time. The implementation principle is that the write operation is actually in a separate log file, and the read operation reads the original data file, which is the content before the start of the write operation, so it does not affect each other. When the write operation ends, the read operation will detect the state of the new database. Of course, the disadvantage of this is that it will consume more memory space.

5. Multithreaded write

You don't have to think about it. SQLite doesn't support it at all. You can use multiple database files if you really need them.

6. Remarks

(1) Have you ever wondered how many database connections SQLite supports at most? In fact, the most accurate answer is given in the official API document (enablewriteaheadlogging() method): the maximum number of connections used to execute queries in parallel is dependent upon the device memory and possibly other properties, But I think it's a little big, isn't it? 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() call, because you use the same reference. For example, in a thread, when an add operation is completed, the database connection is closed immediately, and another site is preparing to perform a query operation, but the DB has been closed at this time, Then an abnormal error will be reported. At this time, there are generally three solutions: ① simply and rudely add a synchronized keyword to all cruds; ② Never close the database connection, only close the connection at the last exit. In fact, each time the getwriteabledatabase() or getreadabledatabase() method is executed, if there is an established database connection, it will be returned directly (exception: if the old connection is opened in read-only mode, the old connection will be closed on the premise that the new connection is successful), so there will always be and only one database connection in the program (provided that it is a single example), The consumption of resources is very small. ③ You can count references by yourself. The simple example code is as follows:

(3) There are also some good habits and common sense, such as closing cursor, using transaction, SQLite does not distinguish between types when storing data, and SQLite supports most standard SQL statements, and the addition, deletion, modification and query statements are common, etc.