Ultralite sync on second thread preventing updates on the main thread

[Breck_Carter]

• 11.0.1.2960 MobiLink
• 11.0.1.2878 Windows Mobile .NET client

We have developed a Windows Mobile 6 solution that does an automatic, periodic UL synchronization on a separate thread. It works great as long as there is a decent wifi connection. When in parts of the facility where the wifi is weak, however, it starts having trouble. What appears to be happening is that the automatic sync starts but then it gets the database into a state where it seems to be waiting for the ability to update rows on the main thread.

[EDIT] The docs say: "During the upload phase, UltraLite applications can access UltraLite databases in a read-only fashion". So do update requests on a non-syncing thread wait for the upload phase to complete? Or are they supposed to immediately fail?

Anyway, one of three things usually happens from there:

1. the user gives up and resets the device
2. the user waits long enough with the very poor connection that eventually the sync completes successfully and the waiting updates go through on the main thread (but it's an unacceptably long time).
3. the wifi connection gets so bad that the sync terminates with ULException - SQLE_COMMUNICATIONS_ERROR and the waiting updates on the main thread then go through successfully

The automatic sync is kind of expendable for the user...at least it's more of a nicety; if it’s doesn't happen from time to time, it really doesn’t matter. If there is a poor connection, the ideal would be to not sync at all, or at least cancel the sync after 3-5 seconds of not completing the sync. Unfortunately connection based timeout for .NET is not possible according to the docs:

ConnectionTimeout property: This feature is not supported by UltraLite.NET.

But often there may not even be a timeout occurring since the sync ultimately, usually, completes if given enough time....so maybe such a timeout wouldn’t help anyway??

The auto syncs are scheduled to occur often (like every 30 seconds), so there is very little data that ever needs to sync, and they're typically of very short duration when the wifi signal is strong (like 5 seconds maybe less)....so even if some timeout that was not based on the communication layer but on the length of the sync time itself was available, that would be nice. I don't think that's possible, but is there an option along those lines to emulate....say, for example, killing the automatic sync's thread--would that be an acceptable and safe option??

So in summary I guess these are my questions:

• Is this a known behavior...the inability to write to the database when a sync on another thread is "spinning" or trying to communicate or even during a certain phase of a sync that it is stuck at because of poor communication?
• Is there a non-destructive way to cancel an "expendable" sync after a certain amount of time? or even a way to cancel the sync if we see a user is trying to write to the database on the main thread?
• Do you see any other ways to deal with this situation?

Sorry for the length of this post and thanks for reading this far and for any help you might impart.

[Breck_Carter]

The quote "During the upload phase, UltraLite applications can access UltraLite databases in a read-only fashion" is from the docs for SQL Anywhere 10.0.1.

The docs are different for SQL Anywhere 11.0.1: "During upload and download, read-write access to the database is permitted."

Your description of the symptom is somewhat vague: "What appears to be happening is that the automatic sync starts but then it gets the database into a state where it seems to be waiting for the ability to update rows on the main thread."

Do you have any error messages or other information?

Is it possible you are dealing with an isolation level issue? "For .NET, read-committed is the default isolation level for new transactions created by calling ULConnection.BeginTransaction without parameters."

[VolkerBarth]

How do you manage transactions in the normal ("user") connection - do you use auto-commit mode or explicit transactions?

[VolkerBarth]

FWIW, even the 11.0.1 docs seem to treat the upload phase differently (or ambiguous...) - from the doc page Adding synchronization to your UltraLite application:

Concurrent changes during synchronization During the upload phase, UltraLite applications can access UltraLite databases in a read-only fashion. During the download phase, read-write access is permitted, but if an application changes a row that the download then attempts to change, the download will fail and roll back. You can disable concurrent access to data during synchronization by setting the disable_concurrency synchronization parameter.

[Former Member]

I apologize. This issue took a back seat for a while as it is intermittent.

@Breck We never call BeginTransaction. @Volker We use the default auto-commit.

Another engineer researching it today described the behavior like this:

"At some point after ULConnection.Synchronize is called and before it finishes, the Windows Mobile 6 device loses/drops the wi-fi signal which causes the application's sync thread to get stuck at the Synchronize call for upwards of three or four minutes until a SQLE_COMMUNICATIONS_ERROR finally occurs. Additionally if a database update call (from a different ULConnection and on the main thread) is executed, the thread gets stuck there until the Synchronize call finally errors out and the update call finally completes. This seems to be odd behavior considering the docs say 'During upload and download, read-write access to the database is permitted' so it leaves the impression that there's possibly some sort of lock happening in the Synchronize function which doesn't get resolved due to the dropped connection? Is there some way to then cause the synchronization to timeout much faster than it does or stop it from locking the database? Since currently if this state occurs the entire app thread freezes up since the update call happens from it, thus causing the application to be frozen until the database sync finally times out."

So I feel like my original questions still seem valid....maybe?:

• Is there a phase during the Synchronize method where the database gets locked while Ultralite sets up to process the sync?
• Any way to cancel a sync (I don't care about a particular sync if the wifi signal is poor)?
• Is it bad to terminate the thread that the sync is on?
• Other ideas on how I might deal with this?

Thanks!

[Former Member]

SyncResult.StreamErrorCode is "READ_TIMEOUT", if that's helpful.

We're using version 11.0.1, but if we upgraded to 12 and called the CancelSynchronize method, would Ultralite be able to cancel successfully with no connection to the server?

[Vlad]

I do not know if this is a good solution, but what if the developer simply defines own timeout variable (in code/timer/separate thread) and simply closes the connection with the ULConnection.Close method?
After that he can inform the user that the signal is bad OR repeat the sync again.

[Former Member]

@Vlad Thanks, we tried as you suggested to close the connection of the secondary sync thread. It takes about 20 seconds for it to close, which is still too long of a period for the app to be frozen. We also saw further errors that we presume to be related to closing the connection like that(?)

[Vlad]

I didn't expect that the connection closing might take so much time. What if, you have the connection in a separate thread, will the application freeze? And if you send the "close" event to this thread, and try to start the new sync thread for the new connection in parallel (or after 1-2 seconds). How will the applictaion handle the simultaneous closing and opening of the conntection, and three (ui + close + open) threads in total?

[VolkerBarth]

Just as a slight reminder:

As nobody from the SQL Anywhere seems to have answered so far, you might go the official support route:

Open an incident with SAP Support : http://support.sap.com/

[former_SQLA_member1694876]

Not a complete answer but...

You can certainly cancel a synchronization. In .NET, you do this by returning true from the SyncProgressed method of the sync progress listener. The sync should terminate within a second using this method.

When a wireless network goes bad, the OS will not always tell us (I suppose it's still trying to recover), and the sync stops only when UL's communication timeout expires. You can adjust the 'timeout' sync option to control how long this takes. The default is 4 minutes. I think shorter timeouts can make sense for wireless networks (but don't make it too short).

It is not generally expected that a sync in this lost-network state should block concurrent calls.