Simultaneously Transactions with web workers and main app

[fishme]

Hi Chris,

I was playing around with the web workers.

My use case needs to run sql commands in the web worker and to the same time sql commands in the main app. But this runs in a critical issue.

Maybe I do something wrong, I created a small repo with this use case: https://github.com/fishme/sqlite-cordova-webworker-test

How to reproduce: Tap fast between "Show record count" and "run webworker" The web worker insert 10.000 records.

Tested: Android 6.0 (nexus 5x)

Maybe you have an idea, how to do it? Or is that principle not possible?

Best David

[brodycj]

Thanks @fishme this is definitely a bug. The root cause is that there is a transaction queue in the main JavaScript thread and another transaction queue in the web worker.

The workaround is to use database.executeSql (for SELECT and single-statement changes) and database.sqlBatch (for multi-statement changes) instead of database.transaction.

I may would like to deprecate the existing database.transaction mechanism in this version due to this issue.

[fishme]

hi @brodybits

thanks for your feedback. Well but how is the performance if I run every single sql command into .executeSql, without a transaction? I was testing it with a simple loop and then .executeSql and it took much longer as before. For 10-20 calls it is ok, I guess, but for 10k and more? Also callback handling can be tricky.

Or do I see here something wrong?

Best David

[brodycj]

Hi David,

Please be sure to use database.sqlBatch to insert a large number of records. The code you wrote in https://github.com/fishme/sqlite-cordova-webworker-test/blob/master/www/webworker/sample-worker.js#L6 is correct. Then you should use database.executeSql to SELECT (read) data from the database.

Please report if this helps or not.

[brodycj]

Please be sure to use database.sqlBatch to insert a large number of records.

My apologies, sqlBatch is not supported by this plugin version yet. Hope to add it soon.

[brodycj]

My apologies, sqlBatch is not supported by this plugin version yet. Hope to add it soon.

sqlBatch support was rolled in by merge ref: #4

[brodycj]

sqlBatch support was rolled in by merge ref: #4

Using sqlBatch still does not solve this issue since it works in multiple batches.

Probably the best solution would be parallel database access handles, similar to the parallel database readers solution in https://github.com/brodybits/cordova-sqlite-evmax-legacy-exp-free. Issue with parallel reader/writer interference discussed in https://github.com/brodybits/cordova-sqlite-evmax-legacy-exp-free/issues/1 would need to be solved on native platform side in this plugin version due to possible access from multiple JavaScript threads.

[fishme]

Thanks for the update I will do some tests the next days

[brodycj]

@fishme this is still an issue. I am planning to resolve this issue by using parallel database access handles (#8), hopefully within the near future.