Fixed
Status Update
No update yet.
Comments
yb...@google.com
hmm, the TEMP table should not be an issue, i cannot find any information that sqlite cannot have temp in memory tables in WAL mode.
what do you mean by: "which can only be accessed in the main DB connection"
what do you mean by: "which can only be accessed in the main DB connection"
jo...@gmail.com
In framework API, enabling WAL also means enabling connection pooling. The underlying implementation will open up to 4 database connections and put them in the connection pool, one for writing (e.g. the primary, or main connection) and the others for reading. When threads want to access the database, they acquire connection from the pool, do actual work(query or execSQL) then release connection back to the pool. This allows concurrent read/write access.
Connection pool is implemented by classes below:
https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteSession.java
https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteConnectionPool.java
https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteConnection.java
TEMP tables are perfectly compatible to WAL mode, but not to multiple connections. They can only be seen by the connection that creates them. In Room library, with WAL and multiple connections enabled, the room_table_modification_log TEMP table is created by the primary connection, but accessed by all primary and non-primary connections. Non-primary connections will throw "SQLiteException: no such table: room_table_modification_log" because they don't see that TEMP table not created by themselves.
I can enable only WAL mode but not multiple connections by executing "PRAGMA journal_mode=wal;" in the builder callback. However, doing this can not bring me concurrent access to the DB, which is important to my app.
It will be good if Room supports WAL and concurrency more "gracefully".
Connection pool is implemented by classes below:
TEMP tables are perfectly compatible to WAL mode, but not to multiple connections. They can only be seen by the connection that creates them. In Room library, with WAL and multiple connections enabled, the room_table_modification_log TEMP table is created by the primary connection, but accessed by all primary and non-primary connections. Non-primary connections will throw "SQLiteException: no such table: room_table_modification_log" because they don't see that TEMP table not created by themselves.
I can enable only WAL mode but not multiple connections by executing "PRAGMA journal_mode=wal;" in the builder callback. However, doing this can not bring me concurrent access to the DB, which is important to my app.
It will be good if Room supports WAL and concurrency more "gracefully".
jo...@gmail.com
Considered:
1. The details of database connections are hidden by SQLiteDatabase and not exported to the user.
2. INSERT, UPDATE and DELETE statements always run in primary connection, which HAS room_table_modification_log table.
3. Only those statements above can trigger modifications to room_table_modification_log table.
I think the easiest way is don't touch room_table_modification_log table during read operations.
1. The details of database connections are hidden by SQLiteDatabase and not exported to the user.
2. INSERT, UPDATE and DELETE statements always run in primary connection, which HAS room_table_modification_log table.
3. Only those statements above can trigger modifications to room_table_modification_log table.
I think the easiest way is don't touch room_table_modification_log table during read operations.
cr...@gmail.com
see: https://sqlite.org/tempfiles.html
2.6. TEMP Databases
Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP TABLE" statement is originally evaluated. These TEMP tables, together with any associated indices, triggers, and views, are collectively stored in a separate temporary database file that is created as soon as the first "CREATE TEMP TABLE" statement is seen. This separate temporary database file also has an associated rollback journal. The temporary database file used to store TEMP tables is deleted automatically when the database connection is closed using sqlite3_close().
2.6. TEMP Databases
Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP TABLE" statement is originally evaluated. These TEMP tables, together with any associated indices, triggers, and views, are collectively stored in a separate temporary database file that is created as soon as the first "CREATE TEMP TABLE" statement is seen. This separate temporary database file also has an associated rollback journal. The temporary database file used to store TEMP tables is deleted automatically when the database connection is closed using sqlite3_close().
yb...@google.com
we are working on getting WAL into 1.1
ya...@google.com
gr...@gmail.com
yb...@google.com
just use 1.1.0-alpha2 which we've shipped today.
Even though it is alpha, there are no know regressions from 1.0 (though this is the first WAL enabled release so feedback would be very much welcome)
Even though it is alpha, there are no know regressions from 1.0 (though this is the first WAL enabled release so feedback would be very much welcome)
gr...@gmail.com
Is there a timeline on 1.1.0 will be released in earnest?
yb...@google.com
we are looking forward to finalize it soon. Btw, 1.1 alpha should be stable as well, we just don't promise API compatibility between alpha versions (or major versions).
xm...@gmail.com
We face the same problem, when query the large data user LiveData paging, and the other thread inserting many data, the query so slow, it's waiting for until the insert end
Description
Version used: 1.0.0-beta2
Devices/Android versions reproduced on: Nexus 5 / Android 6.0.1
- Sample project to trigger the issue.
- A screenrecord or screenshots showing the issue (if UI related).
It would be nice to support concurrent database access via WAL and connection pooling, but it seems not possible in the current version.
Connection pool can be enabled by calling SQLiteDatabase.enableWriteAheadLogging() or passing ENABLE_WRITE_AHEAD_LOGGING flag to open database in framework API. However when I tried to enable WAL for Room databases in the following codes, it failed with "java.lang.IllegalStateException: Write Ahead Logging (WAL) mode cannot be enabled or disabled while there are transactions in progress."
------------------
mAppDB = Room.databaseBuilder(this, AppDatabase.class, "app-db")
.addCallback(new RoomDatabase.Callback() {
public void onCreate(@NonNull SupportSQLiteDatabase db) {
super.onCreate(db);
db.enableWriteAheadLogging();
}
public void onOpen(@NonNull SupportSQLiteDatabase db) {
super.onOpen(db);
db.enableWriteAheadLogging();
}
})
.build();
------------------
I hacked into FrameworkSQLiteOpenHelper and called FrameworkSQLiteOpenHelper.setWriteAheadLoggingEnabled(true) to enable WAL just after creating the OpenHelper. WAL and connection pool can be successfully enabled this way, but further access to the database will cause "SQLiteException: no such table: room_table_modification_log".
This is because InvalidationTracker create a temporary table called room_table_modification_log to track modifications to tables and trigger observer events, which can only be accessed in the main DB connection.
Is there a way to deal with Observers without using temporary tables? If it's not possible, how can I disable observer notifies and InvalidationTracker?