Concurrency between PreparedStatements and synchronizations

[Former Member]

Hello again,

I have another simple question, regarding database access/modification and synchronizations.

We are using Ultralite 16.0.2193 for both Android and iOS. In our apps we can have multiple database commands being executed at the same time, as well a data synch, all of them in a separated thread, each one having its own connection.

My question is:

1. Should I synchronize a prepared statement execution and a database synch, avoiding both being executed at the same time? Even if they are processed in different connections? If so, what happens if I don't do it?
2. Should I synchronize a prepared statement execution and a connection creation and/or commit, avoiding both being executed at the same time? If so, what happens if I don't do it?
3. Should I synchronize two prepared statement executions, avoiding both being executed at the same time? Even if they are processed in different connections? If so, what happens if I don't do it?

In short, basically I would like to make sure if I'm handling sync, prepare statements, commits and multiple threads right, since we have noticed some weird Ultralite JNI crashes that may be related to it.

Thanks!

[chris_keating]

If you are seeing crashes, it would be useful to investigate those. UltraLite should not crash in the context described.

Users should not need to worry about the impact of concurrent requests to the UltraLite runtime. The runtime should manage those requests and where needed ensure operations are "synchronized".

While the runtime permits concurrent operations during synchronization, mondifications to data involved in the sync will result in the sync failing with an error - but not a crash.

As an aside, we have made improvements in concurrency performance during synchronization in more recent SA17 SPs.

[former_SQLA_member1694876 · Accepted solution]

To reiterate and perhaps expand: you don't need to thread-synchronize anything yourself. UltraLite takes care of concurrent requests properly. You don't have to be careful with commits or connects or statements or sync (except for the notes below about conflicts).

You are already following the main threading rule: only one thread can use a connection at a time. (Incidently, v17 software will detect violations of this rule.)

Because requests execute concurrently with a Mobilink sync, you must keep in mind:

1. If you modify a row that the download subsequently also wants to modify, the download will detect this and automatically roll back.
2. If you attempt to modify a row that the download has already modified, the concurrent request will get an error indicating the row is locked/modified (the sync is not affected). This also applies to foreign rows in the expected way. If this type of conflict is possible in your app, you need to code for it.
3. When the sync starts, there is some point where the committed transactions are selected for upload, and transactions after this point are not uploaded in the current sync. Modifications otherwise do not affect the upload.

[Former Member · Accepted solution]

We faced an issue in which I found it weird at the begging, but now it starts to make sense. We have an update statement which uses a where-exists clause with another table. But sometimes this statement fails with a FK violation. However, no FK column is updated in this statement. As the table that is being updated references another table that could've been deleted during a sync (and the record that is being updated itself could've been deleted by cascade as well), the FK violation may occur, may it?

[Former Member]

Your JNI crashing may need to be investigated through a support incident but you can definitely use the Java synchronized methods to at least investigate that a bit deeper. {You could also implement specific critical sections for even tighter control.} I have not found any identified crashes that might be related.

Since you seem to already be aware about Ultralite concurrency and I assume the caveats when depending upon that. Your 1st case has some conditions attached to it and you may find you'll want to avoid making concurrent changes to some tables changes while synchronizing ... which will depend on a lot of factors ... but you shouldn't need to coordinate concurrent changes from multiple UltraLite connections (other than through record locking); as in your 2nd and 3rd cases.

Others may know of something from their own experience ... but good luck with your JNI woes.

[Former Member]

Thanks @Nick Elson, I'll open an incident if I identify new crashes.