Use same transaction in different methods

[aleripe]

Hi guys,
forst of all thanks for this great library.

I want to expose you my problem that might already have a solution, I don't know. Let's suppose I have two methods:

Future<void> methodA() async {
    await _dbContext.executeInTransaction(() async {
       // (insert, update, delete)
   }
}

Future<void> methodB() async {
    await _dbContext.executeInTransaction(() async {
       await methodA();
       // (insert, update, delete)
   }
}

executeInTransaction in methodA should be able to issue a new transaction if it is not available yet , or reuse the transaction created by methodB in the second case.

I have only found the method writeTransaction which correctly offers a transaction object but then commits it at the end of the callback, so it's not appropiate for my case as methodB with methodA inside would find it closed.

I don't know if this makes sense to you. Is there way to obtain this behavior with your library?

Thanks!

[rkistner]

That's a good question.

What I typically do is pass the transaction object along to any other methods that need it. In your example, it would look like this:

Future<void> methodA(SqliteWriteContext tx) async {
   // (insert, update, delete) on tx
}

Future<void> methodB() async {
    await _dbContext.executeInTransaction((tx) async {
       await methodA(tx);
       // (insert, update, delete)
   }
}

If you also need methodA to start a transaction, I'd split it into two:

Future<void> methodA() async {
    await _dbContext.executeInTransaction((tx) async {
       await methodAWithTx(tx);
   }
}

Future<void> methodAWithTx(SqliteWriteContext tx) async {
   // (insert, update, delete) on tx
}

Future<void> methodB() async {
    await _dbContext.executeInTransaction((tx) async {
       await methodAWithTx(tx);
       // (insert, update, delete)
   }
}

There isn't a nice way currently to automatically start a transaction when not in one yet (there may be a workaround), but I've found in most cases it's better to be explicit about whether a method is called inside a transaction, or starts its own transaction.

[rkistner]

Ok, I think that makes more sense now.

That same pattern should work with very little modification. Changes:

• db.writeTransaction instead of db.transaction
• SqliteWriteContext instead of DatabaseExecutor for both get executor and _transaction.

Are there any further issues you're running into with this?

[aleripe]

That's what I did at first but then ran into obscure errors about the database being locked or the transaction closed. I think I have to do some simpler testing to grasp the concepts of your library before going into the harder cases. Thank you so much, you have been very helpful!!

[aleripe]

Hi Ralf, I managed to get it working with your suggestions. Now the thing I'm noticing is that the app seems slower than the version using Sqflite, while I supposed it was the opposite as your library uses FFI and async calls. Where should I look in your opinion? There is some area that could be the cause? Thanks!

[rkistner]

Performance is tricky, and I can't tell why it would be slower without seeing a specific example.

If you were already using sqflite_common_ffi, many operations would have similar performance between the two libraries. I did a blog post a while back comparing performance between different libraries - that may give you a starting point for the general performance you should expect:
https://www.powersync.co/blog/flutter-database-comparison-sqlite-async-sqflite-objectbox-isar

Going off those benchmarks:

1. When using individual operations without transactions, sqlite_async is much faster due to using the WAL mode.
2. Many individual insert/update/delete operations (thousands) in a transaction may be slightly faster with sqlite_async, but you'd get much better performance with both if you batch the operations.
3. Batching large numbers of operations should have better performance with sqlite_async that sqflite.
4. Individual select queries should have similar performance.
5. If it's a small number of slow queries, you'll have no meaningful difference in performance between the two - you'd need to optimize the queries instead.
6. sqlite_async has much better support for concurrency. This is relevant if you need to do e.g. background downloads or uploads while querying data in the app.

[aleripe]

Thank you so much Ralf!! Thanks to your suggestions I was able to achieve very good performance. I'm now using a singleton class to manage my database connection, but I have yet to understand if this can be a good approach. In the meantime I'm very satisfied with the results! Thanks again

[aleripe]

Ok, I think that makes more sense now.

That same pattern should work with very little modification. Changes:

• db.writeTransaction instead of db.transaction
• SqliteWriteContext instead of DatabaseExecutor for both get executor and _transaction.

Are there any further issues you're running into with this?

Hi Ralf, is there a way to check if a transaction has been closed (for example for an automatic rollback)? Thanks!

[aleripe]

I save the current transaction to manage nested transactions, but then I have no way of verifying if the current transaction has been closed due to a rollback and then subsequent calls give me an error "the Transaction has been closed". I'd like to create the current transaction if it is not yet available or if the current has been closed. Does this make sense to you? Thanks!

[rkistner]

The best way to do that is to call this is to use finally / whenComplete on the writeTransaction / readTransaction call, for example:

await db.writeTransaction((tx) {
  currentTransaction = tx;
  try {
    // ...
  } finally {
    currentTransaction = null;
  }
});

[aleripe]

Yes, this is what I was thinking about, but was trying to see if there was something more explicit. Thanks again!

[rkistner]

Update: In v0.6.0 you can now check whether a transaction is closed by using a combination of:

1. tx.closed to check whether the lock is still held (i.e. the callback function in db.writeTransaction hasn't returned yet).
2. await tx.getAutoCommit() to check whether the transaction is still active or has been rolled back.

[aleripe]

Thanks Ralf! I'll have a look at it