Description
Discussion so far
Originally reported by @svranch in brody4hire/cordova-sqlite-ext#56:
I am running in to a problem where I get this ERROR:
Unable to begin transaction: cannot start a transaction within a transaction.
It's really a bummer. 😞
My answer in brody4hire/cordova-sqlite-ext#56 (comment) with my original theory:
Considering that this is a multi-page app, my theory is that this happens if the JavaScript part starts a transaction and then the app goes to another page before the transaction finishes.
In case of a multi-page app I think the safest workaround is to execute all SELECTs and changes using db.executeSql instead of using the standard transaction mechanism.
Followup from @svranch in brody4hire/cordova-sqlite-ext#56 (comment):
But why do I seem to think you have to use the standard transaction mechanism? Do you know? Is it a rule or standard for sqlite? Or what?
Further explanation with my theory again
I tried to explain before that in case of a multi-page app you do NOT want to use the standard transaction mechanism such as:
db.transaction(function(tx) {
tx.executeSql(...);
}, ...);if your app has multiple pages and there may be a page change that happens in the middle of a transaction.
My theory is that a BEGIN statement would be executed and then no COMMIT or ROLLBACK before the app changes pages. If your app then attempts to execute another transaction the plugin would attempt to do the BEGIN and fail with the "cannot start a transaction within a transaction" error.
Data risk situation
If this situation would happen say in page 1 and then something in page 2 attempts to store data using db.executeSql or db.sqlBatch the plugin would store the data and call the success callback (if present) with the abandoned transaction still open. Once the app stops the data stored by page 2 would be lost since the transaction started by BEGIN is never closed.
Recommended workaround
In case of multi-page apps please do NOT use standard transactions (db.transaction(function(tx) { ... }, ...)). Please use single-statement transactions (db.executeSql(...)) or batch SQL (db.sqlBatch(...)) instead.
Possible alternative solutions
Alt 1: Update the native code to always open or reopen the database file upon open request from JavaScript. (The JavaScript does take care of reusing database access connections whenever possible, this should have no effect on the native side.) It may be good for the JavaScript to also send a cleanup signal upon startup.
Alt 2: Upon startup the JavaScript sends a cleanup or reset signal to the native side which should then close all existing database access connections. It would be good for the native side to open or reopen a database file connection upon request, or signal an error. This would be a variation of alt 1.
Alt 3: Whenever the JavaScript opens a new database connection it would then send a ROLLBACK SQL statement and wait for success or error callback before sending any application SQL statements for processing. This would be a good to deal with outdated platform implementations such as WP8 in Cordova-legacy-build-support.
Testing
It would be good to make a multi-page test app based on https://github.com/brodybits/cordova-sqlite-test-app (or https://github.com/brodybits/cordova-sqlite-storage-starter-app) that starts a transaction and then changes to another page that attempts db.transaction(function(tx){...},...) and then shows success or error with a message.
It should be possible for this test app to show the error with the "cannot start a transaction within a transaction" message until this issue is resolved. This test app can then be used to verify that this issue does not reappear in the future.