Unclear what the exclusive flag in Batch.commit() does

[cachapa]

The documentation isn't very clear on what the exclusive flag in Batch.commit() does:
https://pub.dev/documentation/sqflitezjl/latest/sqflite/Batch/commit.html

It seems to be related to Database.transaction() but the flag isn't explained there either:
https://pub.dev/documentation/sqflitezjl/latest/sqflite/Database/transaction.html

Bonus question: does Batch.commit() always run inside a transaction regardless of the exclusive flag?

[alextekartik]

You're pointing to the document of another package (sqflitezjl?) instead of sqflite:
https://pub.dev/documentation/sqflite_common/latest/sqlite_api/Batch/commit.html

Exclusive is a reserved SQLite keyword. See here for explanation of its meaning. https://www.sqlite.org/lang_transaction.html
In a single process application, although I don't have a full understanding, it should not be needed most of the time.(personnaly I don't use it)

[cachapa]

Thanks for the answer and sorry for linking the wrong documentation (though it seems to be the same as this package).

Can you help me understand better the relationship between batches and transactions? I see that you can start a batch directly from a Database instance, but also from a Transaction.
Is there a difference between both? From the code it seems that batches are always committed as transactions (i.e. if one of the calls fails, the entire batch fails), is that correct?

[alextekartik]

Can you help me understand better the relationship between batches and transactions?

There is always a confusion between the 2 notions, although they have a different purpose:

Transaction:

• A transaction is a SQLite concept (BEGIN TRANSACTION, COMMIT). In a transaction, you run SQL statements
  as you would do normally (i.e. you await each statement) but the changes are only effective on COMMIT
• A transaction is committed if the callback does not throw an error. If an error is thrown,
  the transaction is cancelled. So to rollback a transaction one way is to throw an exception.

Batch

• A batch is just a list of statement to execute all at once
• A batch on a database is run in a transaction (a transaction is created for you)
• A batch in a transaction is committed when the transaction terminates
• You can create/commit mutiple batches in a transaction however the changes are committed for real only when the transaction terminates (and a failure would rollback all batches)

Prefer:

• A batch if you have a list statements to execute in sequence and no query on which your behavior depends on
• Use transaction for upsert like scenario or if you want to check a query result before an insert/update or delete.
• You can use them together (i.e. a batch in a transaction.)

I see that you can start a batch directly from a Database instance, but also from a Transaction.
Is there a difference between both?

If you create a batch from a database, one transaction is created.

If you are in a transaction, you create/commit mutiple batches however the changes are committed for real only when the transaction terminates (and a failure would rollback all batches)

[cachapa]

Hey, sorry I never got back to this. I read your message while traveling and forgot to come back later when I had more time to reply.

Thanks a lot for the detailed answer, it helped a lot. Thanks also for the great package, it's making our lives so much easier.
Cheers.