Android Room database transactions

Question

With the new Room Database in Android, I have a requirement where there are two sequential operations that needs to be made:

removeRows(ids);
insertRows(ids);

If I run this, I see (on examining the db) that there are some rows missing - I assume they are being deleted after inserting. viz. the first operation is running in parallel to the second.

If I use a transaction block, such as this, then it's all fine - the first operation seems to complete before doing the second:

roomDb.beginTransaction();
removeRows(ids);
roomDb.endTransaction();

insertRows(ids);

It's also fine if I give a sleep in-between instead:

removeRows(ids);
Thread.sleep(500);

insertRows(ids);

There doesn't seem to be much documentation for Room, and was wondering if I should use the transaction block like the above when I have sequential operations to be done, or is there any better way of doing it.

EDIT: After @CommonsWare pointed out, @Query are asynchronous, while @Insert and @Delete are synchronous. In view of this, how would I get a query which deletes rows to be async:

@Query("DELETE from table WHERE id IN(:ids)")
int removeRows(List<Long> ids);

According to the build output I get Deletion methods must either return void or return int (the number of deleted rows), if I try to wrap the return type in a Flowable.

Swampland

What exactly are the implementations of removeRows() and insertRows()? If they are plain @Delete and @Insert DAO methods, then they should be serialized naturally, as those methods are executed synchronously. The only place where Room does asynchronous stuff is on @Query with a reactive return value (LiveData, Flowable, etc.). – Phaidra

@CommonsWare, yes, while insertRows() are simple @Insert, the removeRows() have @Query calls. I guess that explains it. So, the I guess the answer to my question is to subscribe to the reactive response of the Queries. – Swampland

@CommonsWare, Thanks for your help. I have edited the question with a follow-up based on your comment. How can I write a @Query that does a DELETE so that it I can observe it until completion? – Swampland

A @Query that returns an int is supposed to be synchronous. As I wrote, the only place where Room does asynchronous stuff is on @Query with a reactive return value (LiveData, Flowable, etc.). It could be that this is a bug in Room somewhere. Is there a particular reason you are using @Query rather than @Delete? @Delete already offers IN support for a list of IDs. – Phaidra

Well, I just simplified the code in the question. In actuality, removeRows is only one of two deletion operations that I do. The other one has a more involved query which, if you think it'll help, I can add to the question. Can you also tell me or point me to where @Delete offers IN support for a list of IDs? – Swampland

@Insert, @Update, and @Delete each accept a single ID, a collection of IDs, or a varargs of IDs. developer.android.com/topic/libraries/architecture/… – Phaidra

But in https://developer.android.com...Delete.html, it says "All of the parameters of the Delete method must either be classes annotated with Entity or collections/array of it.". Can you show me the syntax used for passing a collection of IDs? – Swampland

My apologies -- it's early here... :-) Yes, these methods need entities, not IDs, so the methods know what table to delete from. I had considered filing a feature request to allow us to specify the entity class in the annotation and accept IDs as parameters, though I don't think I wound up filing that one. Again, sorry for my confusion. – Phaidra

That's alright. So, it does seem like there's no straightforward solution (unless I @Query all the entities and pass them to @Delete, but that'll be a hit on the performance. Maybe that feature request is a good idea :-) – Swampland

Well, again, @Query returning int should be synchronous, as that is not a reactive return value. Try putting a breakpoint where you are calling removeRows(), then step through the generated code and see if the query is being executed synchronously or asynchronously. You could do the same with your insertRows(). If one or the other is asynchronous (which they shouldn't be), then that would help explain your symptoms. – Phaidra

If both are being executed synchronously, then something is seriously messed up somewhere to explain your prior results, and we'd need a reproducible test case to get to the bottom of it. – Phaidra

Sure. I'll check this and get back. – Swampland

@CommonsWare, you're right - I had a @Query call embedded in my code, based on which I had to do some computation and delete some of the rows. This former call was async, and was causing issues. The @Delete and @Insert calls themselves ARE synchronous. Thanks a bunch for you help. – Swampland

@Rajath: So the '@Query' call is running asynchronously by default or you are running it explicitly inside a background thread? AFAIK, since '@Query' is not returning any observable it will not run on background thread by default. – Magus

@rajath I think @Delete (and also @Update) searches rows based on primary key. So if you set your ID as @PrimaryKey, then you create a dummy object with desired ID as key and then pass it to delete function. – Weichsel

Yes, it works developer.android.com/training/data-storage/room/… – Weichsel

Answer 1

As pointed out on documentation for Transaction, you can do following:

 @Dao
 public abstract class ProductDao {
    @Insert
    public abstract void insert(Product product);

    @Delete
    public abstract void delete(Product product);

    @Transaction
    public void insertAndDeleteInTransaction(Product newProduct, Product oldProduct) {
         // Anything inside this method runs in a single transaction.
         insert(newProduct);
         delete(oldProduct);
     }
 }
 

Boggers

what if i am using interfaces? – Technology

@Technology I was using interfaces and it was not hard to switch to abstract classes. alternatively you might try this ugly trick on interface @Transaction @Query("DELETE FROM products; INSERT INTO products VALUES(x,y,z)") over a method. – Boggers

How would you test this in Espresso, if you are calling @Query right after Insert? – Ferrer

@Boggers why would that trick be ugly? That is plain SQL syntax, I think it's readable and maintainable, absolutely fine. – Asset

What about the situation when there is a relation between two objects and second object is in other DAO? – Chevaldefrise

@Chevaldefrise I think for this situation, there is a method on db itself. it should be like db.startTransaction(); aDao.do(); bDao.do(); db.endTransaction() or some kotlin style blocks. but you have to do it outside of a dao. but you have to be careful about async queries. – Boggers

insert update and delete annotations already run inside a transaction, wrapping these in another transaction doesn't add any value to it. – Legra

but they are in different transactions, wrapping them makes them run in single transaction no? – Boggers

Answer 2

As @CommonsWare pointed out, @Query are asynchronous , while @Insert , @Delete , @Update are synchronous.

If you want to execute multiple queries in single transaction , Room also provides a method for that as mentioned below.

roomDB.runInTransaction(new Runnable() {
        @Override
        public void run() {
            removeRows(ids);
            insertRows(ids);
        }
    });

I hope this will solve your problem.

Lucullus

PCMIIW: Query are not always asynchronous. Query is asynchronous only when you are returning an observable e.g. Flowable or LiveData. Since in the question Query is used for removing element, return value is int and hence it will run synchronously. – Magus

For anyone wondering: runInTransaction: "[...]The transaction will be marked as successful unless an exception is thrown in the Runnable." – Lael

Answer 3

For Room transactions in Kotlin you can use:

• Interface with implemented method, like:

@Dao 
interface Dao {

    @Insert 
    fun insert(item: Item)

    @Delete 
    fun delete(item: Item)

    @Transaction
    fun replace(oldItem: Item, newItem: Item){
        delete(oldItem)
        insert(newItem)
    }

}

• Or use open function, like:

@Dao 
abstract class Dao {

    @Insert 
    abstract fun insert(item: Item)

    @Delete 
    abstract fun delete(item: Item)

    @Transaction
    open fun replace(oldItem: Item, newItem: Item){
        delete(oldItem)
        insert(newItem)
    }

}

You'll get error: Method annotated with @Transaction must not be private, final, or abstract. without open modifier.

Schonfeld

Answer 4

I believe when we are using DAO interfaces, still we can perform transaction using default interface methods. We need to add the annotation @JvmDefault and @Transaction and we can perform any operation inside that, which belong to single transaction.

@Dao
interface TestDao {
    @Insert
    fun insert(dataObj: DataType)

    @Update
    fun update(dataObj: DataType): Completable

    @Delete
    fun delete(dataObj: DataType): Completable

    @Query("DELETE FROM $TABLE_NAME")
    fun deleteAllData()

    @Query("SELECT * FROM $TABLE_NAME ORDER BY id DESC")
    fun getAllData(): Single<List<DataType>>

    @JvmDefault
    @Transaction
    fun singleTransaction(dataList: List<DataType>) {
        deleteAllData()
        dataList.forEach {
            insert(it)
        }
    }
}

Ascender

interface method with body!! – Redden

It gives me an error when I add @JvmDefault as you did in your example. – Ignace

Can you please elaborate what error you are getting? – Ascender

Answer 5

here's the solution to this problem:

@Query("SELECT * FROM friend WHERE id = :id")
Friend getFriendByID(int id);
@Delete
void delete(Friend friend);

---

Friend friendToBeDeleted = friendDAO.getFriendByID(id);
friendDAO.delete(friendToBeDeleted);

You have to go through two steps!

Talapoin

Those are two independent transactions. – Wamsley