document explicit behavior of sql transaction statements

[jacobg]

The README limitations section states:

User-defined savepoints are not supported and not expected to be compatible with the transaction locking mechanism used by this plugin. In addition, the use of BEGIN/COMMIT/ROLLBACK statements is not supported.
https://github.com/xpbrew/cordova-sqlite-storage#other-limitations

But there is at least one 3rd party library that uses transaction statements on this plugin, namely TypeORM (https://github.com/typeorm/typeorm/blob/master/src/driver/sqlite-abstract/AbstractSqliteQueryRunner.ts#L87). An issue will be opened also in the TypeORM repo about that.

However, it does seem perhaps that transaction statements might work as long as it's guaranteed that only one request will be made at a time, i.e., no concurrency.

We should better define exactly what the behavior is here, and the possible effects. For example, with concurrency, I've seen various errors occur due to contention:

QueryFailedError · cannot commit - no transaction is active

TransactionAlreadyStartedError · Transaction already started for the given connection, commit current transaction before starting a new one.

TransactionNotStartedError · Transaction is not started yet, start transaction before committing or rolling it back.

That is, because this plugin currently uses only one connection to the database, requests can overlap and interfere with each other. One request could begin a transaction, and then another could also try to begin one while the first request is in the middle of a transaction, causing an error and potential data corruption.

If sql transactions can never work properly, perhaps the plugin should explicitly forbid them at the api level. Or if they only work serially, perhaps the plugin should use a request queue to enforce that.

For longer term solutions, here is an issue that proposes creating a new API (possibly a new plugin) to offer better transactional control and concurrency:
#862

@brodybits

[brodycj]

Thanks @jacobg. Despite my best efforts over the years I think this is not clear enough. I also raised #865 to explicitly block the BEGIN / COMMIT / ROLLBACK transaction statements more explicitly.

I will need some time to consider alternative solutions more deeply.