[POI] Concurrent read/write issue

[wish...@gmail.com]

Hey guys,

I was wondering, whether anybody has encountered issues with concurrent read/write to the POI database?

My setup is as follows:

• one write AsyncTask, which adds POIs to the database using persistenceManager.insertPointsOfInterest(batch);
  
• another read AsyncTask populating map layer using persistenceManager.findInRect(bbox, categoryFilter, null, MAX_OBJECTS);
• both may run at the same time, sharing the same persistenceManager
  

Occasionally I see some strange exceptions as

> E/idPoiPersistenceManager: bind failed
>    jsqlite.Exception: bind failed
>    at jsqlite.Stmt.bind(Native Method)
>    at org.mapsforge.poi.android.storage.AndroidPoiPersistenceManager.insertPointsOfInterest(AndroidPoiPersistenceManager.java:375)
>    at org.openbmap.services.PoiService$AppendTask.doInBackground(PoiService.java:188)

and

> E/idPoiPersistenceManager: cannot start a transaction within a transaction
>     jsqlite.Exception: cannot start a transaction within a transaction
>     at jsqlite.Database._exec(Native Method)
>     at jsqlite.Database.exec(Database.java:177)
>     at org.mapsforge.poi.android.storage.AndroidPoiPersistenceManager.insertPointsOfInterest(AndroidPoiPersistenceManager.java:372)
>     at org.openbmap.services.PoiService$AppendTask.doInBackground(PoiService.java:188)

I was wondering whether I'm doing something wrong conceptually and if there are some work-arounds to read and write to the database at the same time?

Any hints welcome,

Cheers

Toby

[Emux]

Interesting use of the Mapsforge POI API, I have not tried it so far. :)

It may be relevant how we initialize the SQLite, i.e. with what options enabled or not, if transactions are immediate or queued, etc.

An interesting reading is this topic in SpatiaLite forum (we use SpatiaLite in Android):
https://groups.google.com/forum/#!topic/spatialite-users/pBHHPlUXHzc

Also the "cannot start a transaction within a transaction" search can return many SQLite related results, worth checking.

--
Emux

[wish...@gmail.com]

> Interesting use of the Mapsforge POI API, I have not tried it so far. :)

You're right :) A bit over-engineered.. In order to trace the issue down I started from scratch with a simple test.

I took the mapsforge POI sample and added a few lines to add POI at runtime. Adding POI via insertPointOfInterest works fine, inserting several POI at the same time via insertPointsOfInterest will give a bind exception.

Can somebody reproduce the exception when using insertPointsOfInterest? Or am I doing something obvious wrong here?

Sample test code from POISearchViewer, somewhere around line 97

@Override
protected Collection<PointOfInterest> doInBackground(BoundingBox... params) {
    PoiPersistenceManager persistenceManager = null;
    try {
        // open PersistanceManager in Write Mode
        persistenceManager = AndroidPoiPersistenceManagerFactory.getPoiPersistenceManager(POI_FILE, false);
        PoiCategoryManager categoryManager = persistenceManager.getCategoryManager();
        PoiCategoryFilter categoryFilter = new ExactMatchPoiCategoryFilter();
        categoryFilter.addCategory(categoryManager.getPoiCategoryByTitle(this.category));

        // create POIs at runtime
        PointOfInterest poi1 = new PointOfInterest(Math.abs(new Random().nextLong()),
            params[0].minLatitude + (params[0].maxLatitude - params[0].minLatitude) * new Random().nextDouble(),
            params[0].minLongitude + (params[0].maxLongitude - params[0].minLongitude) * new Random().nextDouble(),
            "new poi", categoryManager.getPoiCategoryByTitle(this.category));

        PointOfInterest poi2 = new PointOfInterest(Math.abs(new Random().nextLong()),
                params[0].minLatitude + (params[0].maxLatitude - params[0].minLatitude) * new Random().nextDouble(),
                params[0].minLongitude + (params[0].maxLongitude - params[0].minLongitude) * new Random().nextDouble(),
                "new poi", categoryManager.getPoiCategoryByTitle(this.category));

        PointOfInterest poi3 = new PointOfInterest(Math.abs(new Random().nextLong()),
                params[0].minLatitude + (params[0].maxLatitude - params[0].minLatitude) * new Random().nextDouble(),
                params[0].minLongitude + (params[0].maxLongitude - params[0].minLongitude) * new Random().nextDouble(),
                "new poi", categoryManager.getPoiCategoryByTitle(this.category));

        // WORKS
        persistenceManager.insertPointOfInterest(poi1);

        ArrayList<PointOfInterest> batch = new ArrayList<>();
        batch.add(poi2);
        batch.add(poi3);

        // DOESN'T WORK: jsqlite.Exception: bind failed
        persistenceManager.insertPointsOfInterest(batch);

        return persistenceManager.findInRect(params[0], categoryFilter, null, Integer.MAX_VALUE);
    } catch (Throwable t) {
        Log.e(SamplesApplication.TAG, t.getMessage(), t);
    } finally {
        if (persistenceManager != null) {
            persistenceManager.close();
        }
    }
    return null;
}

Cheers
Toby

[Emux]

On 18/11/2016 08:48 μμ, wish...@gmail.com wrote:

In order to trace the issue down I started from scratch with a simple test.

That's the best method to check something.
Along with unit tests!

Can somebody reproduce the exception when using insertPointsOfInterest? Or am I doing something obvious wrong here?

Thanks for searching it.

Indeed we didn't clear properly the bindings at each iteration.
According to docs in JDBC it isn't necessary, but apparently in SQLite / SpatiaLite it is.

I pushed the fix in issue #900, can you check it too?

--
Emux

[wish...@gmail.com]

Very cool, thanks Emux!!

Seems to work smoothly now..