Topics

Multiuser SQLite #xpa #sqlite #multiuser

tomdzio
 

Hello

I know that SQLite is rather single-user DBMS but I red here that it is possible to use in in multi-user environment - this is just the question of updating by 1 user at any instant of time as whole DB is locked on writing. However when I'm trying with Magic (XPA 3.3) it looks like first update lock DB exclusively and not releasing it after this update is finalized. Second Magic session gets just the message - "Database locked". Is it really no possible to overcome this?

regards
Tomasz

Wes Hein
 

Have you tried a DbDiscnt  after updating the value?  Just a WAG.  Is this database open all the time for default values or only opened on occasion?

Wes

tomdzio
 

Hi Wes

I've just tried and I have the message in the statusline "Failed to disconnect, datasource is open, datasource: Products". My test is very simple: I'm running simple browsing program for table Products from top menu on 2 session in Runtime. The problem starts if the program is open on both session and I'm modifying a record on one of them. Even after closing the program on the that session I'm getting "db is locked" error on the second one. On the other hand the first session is also not possible to use as when try to start browsing program again I'm getting "cannot start a transaction withing a transaction" error.

Tomasz

Wes Hein
 

What are the transaction settings for the program?

tomdzio
 

I've tried both Physical and Deferred and also different locking strategy.

tomdzio
 

I've just received the following reply from Magic helpdesk:

Hi Tomasz,

I have following update from our Product Management,

SQLite is a single user DB and the same considered by Magic.
We do not support accessing the same DB by multiple threads/engines.

So there is no way to use SQLite in XPA multi user environment even if we accept locking of whole DB in the moment of writing. It a pity. 

Tomasz

Thomas Titus
 

A while ago, I tried SQLite 100% in read only mode for a single web service and experienced occasional lock errors.  Finally, I changed to MSSQL Express and resolved the issue.
Thanks
Thomas Titus.