Problem: data is not available to `SELECT` from new connection to SQLite DB with WAL mode (Write Ahead Log) on if previous connection hasn't been closed properly. Main question is: why data is lost and is there some way to get lost transactions? I'm trying to store data in a SQLite table with `WAL mode` on. I'll describe 3 cases: case A results in transaction loss, cases B and C - don't. Case A (quit app, don't close connection): ------- 0. Open application, open Connection 1, open Connection 2 (on the same DB) 1. Begin transaction (Connection 1) 2. `INSERT` (Connection 1) 3. End transaction (Connection 1) 4. Repeat 5 times steps 1-3 5. `SELECT` (Connection 1) // All data is present. 6. `SELECT` (Connection 2) // All data is present. 7. **Close application (without closing Connection 1)** 8. Open application, open Connection 3 9. `SELECT` (Connection 3) // **Some data is lost** - select could return 2 out of 5 inserted transactions, for example. Or 0 out of 5 transactions. Totally random. Case B (close connection before quit app): ------- 0. Open application, open Connection 1, open Connection 2 (on the same DB) 1. Begin transaction (Connection 1) 2. `INSERT` (Connection 1) 3. End transaction (Connection 1) 4. Repeat 5 times steps 1-3 5. `SELECT` (Connection 1) // All data is present. 6. `SELECT` (Connection 2) // All data is present. 7. **Close Connection 1** 8. Close application 9. Open application, open Connection 3 10. `SELECT` (Connection 3) // **All data is present.** Case C (perform checkpoint before close app + don't close connection): ---------------------------------------------------------------------- 0. Open application, open Connection 1, open Connection 2 (on the same DB) 1. Begin transaction (Connection 1) 2. `INSERT` (Connection 1) 3. End transaction (Connection 1) 4. Repeat 5 times steps 1-3 5. `SELECT` (Connection 1) // All data is present. 6. `SELECT` (Connection 2) // All data is present. 7. **Perform WAL checkpoint** 8. Close application (without closing Connection 1) 9. Open application, open Connection 3 10. `SELECT` (Connection 3) // **All data is present.** To sum up, when i `SELECT` from the table before closing app, all data is present, but after incorrect closing of the application (for example if app crashed) some of the data i inserted is missing. However if i perform checkpoint **before** closing the app (or close connection before closing app)- all the data is available. Extra info: - If i perform checkpoint **after** re-opening app (Case A), transactions don't appear (don't proceed from journal to main db file). - WAL hook: i've registered callback using `sqlite3_wal_hook` to check whether transactions are actually commited to WAL-journal file and it shows that pages being successfully written to the log file. - WAL file: i've tried to look into `-wal` file using Device File Explorer in Android Studio or by copying it to external storage from internal storage (/data/data/com.package.my/files) and most of the time it is either empty or doesn't exist. - Threadsafe: i've tried to turn on SERIALIZED threadsafe mode by using SQLITE_OPEN_FULLMUTEX flag when opening DB: `sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);` It didn't make any difference. However, it lead to problems with reading from 2nd connection, so i use `sqlite3_open` without `SQLITE_OPEN_FULLMUTEX`. - Stack: android 7 - JNI - c++ 11 - sqlite 3.27.2 Code: ``` // DB init (executed once on app start) void initDB() int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0 // check threadsafe mode int stResult = sqlite3_threadsafe(); // stResult = 1 // register WAL hook char* pointerContext = new char[4](); pointerContext[0] = 't'; pointerContext[1] = 'e'; pointerContext[2] = 's'; pointerContext[3] = 't'; sqlite3_wal_hook(handle, wal_hook, pointerContext); // turn WAL mode on int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0 } // close connection int32 close() { return sqlite3_close(handle); } // WAL checkpoint sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg); // Insert EventPtr persist(EventPtr obj) { vector<DBData*> args; int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL); try { args.push_back(&obj->eventId); // ... push more args string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);"; int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args); if(rc == SQLITE_DONE) { int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL); return obj; } } catch(...){ } } // SELECT vector<EventPtr> readAll() { string query = "select * from Events;"; ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL); vector<EventPtr> vec; for(int32_t i = 0; i < result->size(); i ++){ EventPtr event(new Event); // init event vec.push_back(EventPtr(event)); } return vec; } // executePreparedWOresult int32 executePreparedWOresult(const string& query, vector<DBData*> *args){ sqlite3_stmt *stmt; cout << query ; sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL); for(uint32 i = 0;args && i < args->size(); i ++){ switch(args->at(i)->getType()){ // statement bindings (sqlite3_bind_text, etc.) } } int32 rc = sqlite3_step(stmt); sqlite3_finalize(stmt); return rc; } // executePrepared ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){ ResultSetPtr res = ResultSetPtr(new ResultSet); sqlite3_stmt *stmt; int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL); for(uint32 i = 0;args && i < args->size(); i ++){ switch(args->at(i)->getType()){ // statement bindings (sqlite3_bind_text, etc.) } } int32 count = sqlite3_column_count(stmt); int32 rc; ResultRow row; int32 rows = 0; while((rc = sqlite3_step(stmt)) == SQLITE_ROW){ rows ++; for(int32 i = 0; i < count; i++){ // multiple row.push_back-s for all columns } res->push_back(row); row.clear(); } sqlite3_finalize(stmt); return res; } ```