1. We are not accepting any new account registrations at this time.

[HELP] Trouble with UniDAC Sqlite Concurrent Writes

Discussion in 'HelpDesk' started by Sir.Dev.A.Lot, Oct 3, 2019.

  1. Sir.Dev.A.Lot

    Sir.Dev.A.Lot is a Trusted Warez PosterSir.Dev.A.Lot Shadow Moderator Staff Member Super Moderator DEV Guild Reverser Translator

    Joined:
    Oct 10, 2008
    Messages:
    21,264
    Likes Received:
    84,238
    have been having this problems.

    I have a local database SQLite3, and using UniDAC v7.0.2

    How can Delphi write from 2 different apps in the same database.

    The message is DATABASE LOCKED.

    Is there a way (function, procedure) trick, SQLite3 config to solve that.

    ?
     
  2. Skydevil

    Skydevil is a Trusted Warez PosterSkydevil Administrator Staff Member Administrator V.I.P DEV Guild

    Joined:
    Oct 4, 2008
    Messages:
    12,752
    Likes Received:
    30,760
    Sqlite was simply not design for multi user scenarios and that's why there is no server process which would handle concurrency writes. It's possible to read from multiple apps, but only one app can write at the same time. Therefore you should have short write cycles and in case the write fails, maybe try again, with a small delay, until the write succeeded. Maybe the component TClientDataset is at that point helpful, since it allows to cache write operations and handle them.

    In case you have your apps running on the same computer, you could establish a write locking with a mutex. You could also serialize write operations with a pipe. Also any kind of REST mechanic is thinkable. You could limit the access to local calls. Your apps maybe know each other, therefore it's also thinkable, that one app is always the "master" and doing all the writes, what efforts also any kind of intercommunication between processes.

    Okay, there are a few scenarios to solve that "missing server process" and all cause some work. It's unclear what you want to do or how many write operations are awaitable or if anytime three apps need to write into the same sqlite database. So, maybethe following settings are enough as workaround...
    Code:
    begin
      (...)
      FDConnection1.FetchOptions.Mode := fmAll;
      FDConnection1.Params.Values['LockingMode'] := 'Normal';
      (...)
    
    Hint: It should be also possible to keep the default setting on the connection object and change the fetch mode for the dataset with write operations.
     
  3. Sir.Dev.A.Lot

    Sir.Dev.A.Lot is a Trusted Warez PosterSir.Dev.A.Lot Shadow Moderator Staff Member Super Moderator DEV Guild Reverser Translator

    Joined:
    Oct 10, 2008
    Messages:
    21,264
    Likes Received:
    84,238
    Thanks for your quick reply @Skydevil

    My scenario is this:

    SQLite3 encryption and compression is very good compared to other databases as AbsoluteDB, TurboDB, Accuracer, EasyTable, etc . and its really fast with a lot of GBs in database size.

    The components that i use is UniDAC.

    1 exe and several DLLs writing to the same table, i have checked for opened handles and i have found some opened for the Database.

    My only hope now is to use a procedure, to check if handle is opened and close it, open and write to the database, and check at the end try... finally for opened handles, and close them.

    not all operations are made at the same time, like 2 writing operations in 1 sec or more ...

    i can not use Firebird or MySQL embeeded for the reason of data encryption...
     
  4. ce

    cepukom Registered User

    Joined:
    Nov 23, 2008
    Messages:
    230
    Likes Received:
    306
    Multi tier, REST, architecture like may help here. The server part is connect directly to SQLite3 and doing read or write to the database. The clients just using services made by the server.
     
  5. cr

    crispixel Registered User

    Joined:
    Oct 20, 2008
    Messages:
    111
    Likes Received:
    2,020
    @Sir.Dev.A.Lot I've already implemented concurrent connections/writes. Just follow this example, it really works:

    Code:
     DM.LiteConn.SpecificOptions.Add('EnableSharedCache=True');
     DM.LiteConn.SpecificOptions.Add('ReadUncommitted=True');
     DM.LiteConn.SpecificOptions.Add('BusyTimeout=3000');
     DM.LiteConn.Database := 'C:\xyz_localDB\mydatabase.sqlite3';
     DM.LiteConn.Connect;
     DM.LiteConn.ExecSQL('PRAGMA journal_mode = WAL');
     
    1 person likes this.
  6. st

    stlcours Registered User

    Joined:
    Aug 25, 2009
    Messages:
    179
    Likes Received:
    1,151
    ÿou can lock your operations yourself. I do like this and there is no problem with multi-thread.
     
  7. st

    stlcours Registered User

    Joined:
    Aug 25, 2009
    Messages:
    179
    Likes Received:
    1,151
    ÿou do everything yourself, by example, you define two functions:

    Code:
    procedure MyRead(sql : String)
    begin
      m_lock.lock; // CreateMutex or the others locks
    
      execute(sql); // select * from table1
    
      m_lock.unlock;
    
    end;
    
    procedure MyWrite(sql : String)
    begin
      m_lock.lock; // CreateMutex
    
      execute(sql); // update set ...
    
      m_lock.unlock;
    
    end;
    
    Everytime, you call these two functions to execute sql. So you don't need to know well about sqlite itself and its differnces between different versions.
    Even there still are still bugs about Unidac, it doesn't matter.
     
    Last edited by a moderator: Oct 13, 2019 at 17:16
    1 person likes this.
  8. Warax

    Warax Honorary Member Honorary Member V.I.P DEV Guild

    Joined:
    Jan 16, 2011
    Messages:
    4,680
    Likes Received:
    4,029
    If you have many users use something like Firebird or InterBase
     
  9. Sir.Dev.A.Lot

    Sir.Dev.A.Lot is a Trusted Warez PosterSir.Dev.A.Lot Shadow Moderator Staff Member Super Moderator DEV Guild Reverser Translator

    Joined:
    Oct 10, 2008
    Messages:
    21,264
    Likes Received:
    84,238
    @crispixel @stlcours I have used your code and added the Pooling:= True, and some more code, now the DATABASE IS LOCKED MESSAGE is gone.

    But i need a lot of testing of many instances to see if the technnic is good enough. until now working great :emoji_ok_hand: