Has anyone had issues with race conditions when in...

# room

[Sergi Mascaró]

Has anyone had issues with race conditions when inserting a bunch of entities with FKs in a large transaction? Let's say I have the following structure:
• Entity A
• Entity B, has FK -> A
• Entity C, has FK -> B, and FK
Then I have a method were I insert a bunch of data in a @Transaction in the right order to satisfy the FKs. That is:
1. insertA()
2. allB.foreach { insertB() }
3. allC.foreach { insertC() }
Now, after months of debugging what is going on, I noticed when adding query logging that sometimes a C will be inserted before all Bs have been inserted and cause a foreign key violation. I found out about deffered foreign keys and I was extremely happy, until I realized that it's broken and Google is not fixing it.

Any ideas or experiences? I'm quite lost, it shouldn't be a rare scenario...

[Hristijan]

I had the same issue when inserting lots of data, I've fixed it with inserting batches

Entity A batch by 100 keep in mind if you have lots of data lower the batch
Once it finishes B and then C

Mine was only 2 fk (b, c)
Data was around 20k rows

Transactions didn't help, sadly it's a slower process but it got me through this very issue, this issue I discovered really early when I started working with Room and that was 2018, damn it hasn't been fixed, what a shame and the issue is from 2014 🫠

[Sergi Mascaró]

Interesting, thanks! Did you still run all these batches under one single transaction? If so, how did you prevent that B didn't start before all A had finished? Because I think that's still the main issue. Or, maybe I'll phrase it differently: what exactly do you mean by batches?

[Hristijan]

No transactions at all, normal insert query, just inserting a list of batches

Let's say your list is 20k items you slice it in lots of sub lists where each list has 100 items and every batch is inserted sequentially

[Sergi Mascaró]

Ahh just to clarify, do you mean @Insert queries or insert `@Query("INSERT...")`s? I'm really trying to avoid the last one as it will be a hell to maintain. But in general, if you didn't use transactions at all, how did you handle rollbacks in case there were any issue? That's my other concern 😬

[Hristijan]

@Insert

[Hristijan]

Well i didn't handle rollbacks it wasn't the requirement, but if i had to, i would not go the transaction way of start/end transaction but after every insert i would have returned the same items and checked against the receiving list

[Hristijan]

Maybe you can batch all of those inserts in a transaction, just throwing out an idea, like have a transaction that inserts two lists of items, so it'll act like batch of 2 x 100?

[Sergi Mascaró]

The problem is that Room/Sqlite doesn't allow for multiple transactions at the same time, so as soon as you start a transaction, any transaction inside like @Insert , then it is no longer atomic and there's no promise it will wait for the row to be inserted before continuing with the next one. Now I'm playing around with making each insert return the row id. It wouldn't make sense that the execution should be able to continue if you need the inserted row id. Hopefully this enforces some more sequentiality

[Sergi Mascaró]

Yeah I think the only solution (for now) is to use a single thread executor 😞

[Sergi Mascaró]

But it can be painfully slow

[Ahmed]

Why not use?

@Insert
fun insertBs(b: List<B>)
@Insert
fun insertCs(c: List<C>)

And if the list is very large, you can maybe use windowed or chunked

[Sergi Mascaró]

For anyone interested, in the end, inserting entities using @Ahmed’s suggested pattern fixed the issue.

[Ahmed]

Fwiw, you can also make these calls a single transaction.

@Transaction
fun insertAll(
  a: List<A>, 
  b: List<B>, 
  c: List<C>,
) {
  a.chunked(100).forEach{ insertA(it) }
  b.chunked(100).forEach{ insertB(it) }
  c.chunked(100).forEach{ insertC(it) }
}

This ensures the order and wraps all of the logic in a transaction.

[Sergi Mascaró]

@Ahmed that would still sequentially insert each entity. Each line would be equivalent to x.forEach{ insertX(it) } , since splitting a list in chunks doesn't make it happen in different transactions. I think the main reason why the other solution works is because then we leave it to room to execute the transaction of inserting all the entities of one type, instead of iterating ourselves.