Recently, when I was working on a project, I threw an exception when reading and writing databases through multi-threads. This is naturally something that I don’t have a good understanding of SQlite3, so I carefully explored it afterwards.
1. About the real role of getWriteableDataBase() and getReadableDatabase()
getWriteableDataBase() is actually a sub-method equivalent to getReadableDatabase(). getWriteableDataBase() can only return a reference to SQLiteDatabase opened in read and write mode. If the database is not writable at this time, an exception will be thrown, such as if the database disk space is full. getReadableDatabase() generally calls getWriteableDataBase() method by default. If the database is not writable, it will return a reference to SQLiteDatabase that is opened in read-only mode. This is the most obvious difference between the two.
The key source code is as follows:
public synchronized SQLiteDatabase getWritableDatabase() {
if (mDatabase != null) {
if (!()) {
// darn! the user closed the database by calling ()
mDatabase = null;
} else if (!()) {
return mDatabase; // The database is already open for business
}
}
... ...
public synchronized SQLiteDatabase getReadableDatabase() {
if (mDatabase != null) {
if (!()) {
// darn! the user closed the database by calling ()
mDatabase = null;
} else {
return mDatabase; // The database is already open for business
}
}
... ...
try {
return getWritableDatabase();
}
... ...
Synchronous lock
In fact, when using only one SQLiteDatabase reference, SQLiteDatabase will add a lock to the CRUD operation (because it is a db file, it is accurate to the database level), which ensures that you can only perform one operation at the same time, regardless of whether it is in the same thread. This leads to the fact that if you use singleton mode for SQLiteOpenHelper in your program, then any optimization operations you perform on the database read and write will be "futile".
3. Read the database through multiple threads
If you look carefully at the source code, you will find that in database operations, only add, delete, update will call lock(), while query() will not be called. However, when loading data, SQLiteQuery's fillWindow method is called, and this method will still call(). Therefore, if you want to truly implement multi-threaded database reading, you can only use your own SQLiteOpenHelper object to read, so that you can avoid synchronization locks. The key source code is as follows:
/* package */ int fillWindow(CursorWindow window,
int maxRead, int lastPos) {
long timeStart = ();
();
(mSql, timeStart, SQLiteDatabase.GET_LOCK_LOG_PREFIX);
try {... ...
4. Multi-threaded reading and writing
The key to implementing multi-threaded read and write is the enableWriteAheadLogging property. This method is basically impossible to implement true multi-threaded read and write by API Level 11, which is added to the 3.0 or above version. 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 a write thread to work on a SQLiteDatabase at the same time as multiple read threads. The implementation principle is that the write operation is actually in a separate log file. The read operation reads the original data file and is the content before the write operation begins, so that it does not affect each other. When the write operation is finished, the read operation will detect the status of the new database. Of course, the disadvantage of doing this is that it will consume more memory space.
5. Multi-threaded writing
There is no need to think about this. SQLite does not support it at all. If you really need it, you can use multiple database files.
6. Notes
(1) Have you ever thought about 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. It depends on how much memory you have, but I feel that this is a bit big, isn't it? Ha ha.
(2) When you only use one SQLiteDatabase reference in multi-threads, you need to pay special attention to the timing of your() call, because you are using the same reference. For example, in one thread, when an Add operation ends, the database connection is immediately closed, and another site is preparing to perform a query operation, but at this time, db has been closed, and an exception error will be reported. At this time, there are generally three solutions: ① Add a synchronized keyword to all CRUDs simply and roughly; ② Never close the database connection, only the end of exiting is to close the connection. In fact, every time the getWriteableDataBase() or getReadableDatabase() method is executed, if there is a database connection that has been established, 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). Therefore, there will always be only one database connection in the program (presumably a singleton), and the resource consumption is very small. ③ You can do reference counting yourself, the simple example code is as follows:
//Open the database methodpublic synchronized SQLiteDatabase openDatabase() {
if (() == 1) {
// Opening new database
try {
mDatabase = ();
} catch (Exception e) {
mDatabase = ();
}
}
return mDatabase;
}
//Close the database methodpublic synchronized void closeDatabase() {
if (() == 0) {
// Closing database
();
}
}
(3) There are also some good habits and common sense, such as closing Cursor, using Transaction, SQLite actually does not distinguish between types when storing data, and SQLite supports most standard SQL statements, and adding, deleting, modifying and searching statements are common, etc.