Android Database Transaction

Android SQLite Bulk Insert Using Transactions

You have already closed the db before ending the transaction, instead close it after ending.

public boolean insertQuestions(String gid, String qid, String qname) {
    SQLiteDatabase db = this.getWritableDatabase();

    boolean wasSuccess = true;
    try {
        db.beginTransaction();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_GID, gid);
        contentValues.put(KEY_QID, qid);
        contentValues.put(KEY_QNAME, qname);

        long result = db.insert(TABLE_QUESTIONS, null, contentValues);

        if (result == -1) {
            wasSuccess = false;
        } else {
            db.setTransactionSuccessful();
        }
    } finally {
        db.endTransaction(); 
        db.close();
    }
    return wasSuccess;
}

Android Firebase database transaction

You should be expecting that the initial snapshot of data available in your transaction could be null during its first execution. Note this from the documentation:

Note: Because doTransaction() is called multiple times, it must be
able to handle null data. Even if there is existing data in your
remote database, it may not be locally cached when the transaction
function is run, resulting in null for the initial value.

Also in the javadoc it says:

This method will be called, possibly multiple times, with the current
data at this location.

Expect that the first time your handler is run, it will be dealing with an empty database. Then, when the data there is known (if any), you should be prepared to handle that case also. If you don't want to do anything where there is no data (or unknown data) at the location, simply return a successful Transaction with no changes to mutableData.

Should I always use transactions in android sqlite?

No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically start a transaction if one is
not already in effect. Automatically started transactions are
committed when the last query finishes.

Transactions can be started manually using the BEGIN command. Such
transactions usually persist until the next COMMIT or ROLLBACK
command. But a transaction will also ROLLBACK if the database is
closed or if an error occurs and the ROLLBACK conflict resolution
algorithm is specified.

SQLite Query Language.

So yes, a transaction starts automatically. However, if you execute multiple queries without starting the transaction manually, multiple transactions are started and finished, which impacts performance negatively.

Furthermore, the automatic rollback mechanism only rolls back the single query when it fails.

How to make a database transaction with Room using functions from differents DAOs and using coroutines?

There is a suspend extension function for this withTransaction(). Check this out:

https://android.googlesource.com/platform/frameworks/support/+/refs/heads/androidx-master-dev/room/ktx/src/main/java/androidx/room/RoomDatabase.kt

Transaction in Room Database returning LiveData

Point 1 and 2 remain unanswered, anyway here how i bypassed the problem (as per point 3). Hope it helps someone:

fun getOrderCount(): LiveData<Int> = object : LiveData<Int>(0) {
    val observer = object : InvalidationTracker.Observer("order_table") {
        override fun onInvalidated(tables: MutableSet<String>) {
            requestOrderCount()
        }
    }

    override fun onActive() {
        super.onActive()
        val tracker =
            MyRoomDatabase.getInstance(applicationContext).invalidationTracker;
        tracker.addObserver(observer)
        requestOrderCount()
    }

    override fun onInactive() {
        super.onInactive()
        val tracker =
            MyRoomDatabase.getInstance(applicationContext).invalidationTracker;
        tracker.removeObserver(observer)
    }

    private fun requestOrderCount() {
        // better to use coroutines than executors
        Executors.newSingleThreadExecutor().execute {
            postValue(
                MyRoomDatabase.getInstance(applicationContext).OrderDao()
                    .getOrderCount_()
            )
        }
    }
}


@Transaction
private fun getOrdersCount_(): LiveData<Int>
{
    val now = LocalDateTime.now()
    val firstOfMonth = now.withDayOfMonth(1)
    subop_pruning(firstOfMonth) // query that deletes older orders
    return subop_select_count() // query that returns the number of orders
}