SQLite Forum

Why data is lost in SQLite database with WAL mode on when connection is not closed properly?
Login
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;
}
```