SQLite not inserting record instantly

Question

I am working on an app which saves transactions in SQLite database after payment is received. As soon as the transaction is approved, it is inserted in database and after that user is shown the approval. I show the approval when I get the result from sqlitedatabase.insert() method, and check that it returns row number.

public static boolean insert(String tableName, SQLiteDatabase sqLiteDatabase, ContentValues contentValues){
        long rowId = sqLiteDatabase.insert(tableName, null, contentValues);
        return rowId != -1;
    }

The problem is, when the approval is shown to user, I shut down device by ejecting battery (I am testing out of battery case) and when I reboot device, I see that no record is inserted in the database. That makes me think that SQLite is inserting records asynchronously after returning the row id.

I am using AsyncTask and I do inserting in the doInBackground, and I show the user approval in onPostExecute() of AsyncTask.

My question is, is there any way to make sure that the record is inserted the moment I call insert method or am I doing something wrong here?

Answer 1

After some research, I realized what was causing the problem. It was SQLite's default configuration setting and synchronous setting was not FULL. According to documentation:

FULL (2) When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode.

The default configuration was not FULL and I changed it in my SQLiteOpenHelper class' onConfigure callback. And it solved the problem.

@Override
public void onConfigure(SQLiteDatabase db) {
    db.execSQL("PRAGMA synchronous = 2");
}

Although the document states that "FULL synchronous is very safe, but it is also slower.", I haven't seen a significant performance difference despite batch inserting in some cases.