B4J Question sqlite db read lock with multiple users

H

Hilton

Active Member
Licensed User
Longtime User
Hi Folks,

I have been enjoying B4J a lot thanks to the creators!

I have a program that shows customers in a tableview and on selection the details are shown in textfields.
These may be changed/added/deleted etc and when one user is using the program all works well, however, with two users there is a problem.

An issue I have come across is that if you have two users changing the same file, when db.TransactionSuccessful is executed a "db locked" message comes up.

As both users were not trying to update at the same time I can only assume that a Read Lock is set and that jams the works up. Is there any way to prevent the Read Lock and only lock the db for updates? If that is not sensible is there any other way around it?

Thanks,
Hilton.
 
Sort by date Sort by votes
D

dilettante

Active Member
Licensed User
Longtime User
Don't spend time looking at the Jackcess library either.

Even though Jet (commonly and incorrectly referred to as "Access") databases were designed to support concurrency control and transactions, Jackcess is rather crude and really only for use by a single "opener."

If all openers only read data you may have some success.

Worse yet the B4A/B4J Jackcess wrapper library uses an obsolete version of the Java Jackcess. That does several things incorrectly and has fewer features than current versions, and could result in data corruption. It works, and is ok as a desperation measure. But you'd probably only use it for importing Jet data into some other DBMS.

SQLite is great for what it is for, but it is no Jet. Sadly we don't have good Jet support so that will not address your issues. Even sadder, there probably aren't any other reliable file-based DBMSs that deal properly with concurrent access and transaction isolation. Considering how fantastic Jet really is, it sure gets beat up a lot by its critics.

So the extra resource consumption and management overhead of a client-server DBMS may be your only option.
 
Upvote 0
H

Hilton

Active Member
Licensed User
Longtime User
Hi Erel,
Thank you that was the problem - scrupulous closing of resultsets.

For anyone else's benefit:

In the past when dealing with SQLite I always did my own referential and concurrency checking which I cheerfully set about doing with B4J. When I bumped into the locking problem, I decided to change the database to Postgres as an exercise and all worked well - in spite of not closing resultsets.

After your advice, I switched back to SQLite (which is my preference) and I had to copy resultsets to maps (to keep the data available for as long as I needed it) and then close the resultset immediately. Now the ri/concurrency checking and updating all work well.

I remember reading your examples including closing the resultset but it did not stick in the grey matter. Anyway, all is well that ends well.

Regards,
Hilton.
 
Upvote 0
You must log in or register to reply here.
Top