multiple instances of application concurrently use SQLite?

[tatamata]

Is it possible to set (sqldb, sqlite3 connection) that multiple instances of application can read and write the same SQLite file concurrently?
If yes, how?

[rvk]

According to the documentation, it's NOT.

Most SQL database engines are client/server based. Of those that are serverless, SQLite is the only one known to this author that allows multiple applications to access the same database at the same time.

https://www.sqlite.org/serverless.html

But:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

https://www.sqlite.org/faq.html#q5

So only one application can write to the file at the same time.
Multiple application can read at the same time.

Locking of the database during write is done automatically so you don't need to do anything. Just write to the database. At the time of writing other application get a signal that the database is in use. If you write a lot to the database with multiple application, then SQLite is not for you.

(Also read http://www.sqlite.org/whentouse.html)

[tatamata]

Ok, maybe I wrote it incorrectly.
I don't actually want more than one instance write at the exact same time, but that they don't lock SQLite file generally.

My application (which uses caCommitRetaining transaction) completely locks the SQLite file for other instances, so they are read-only all the time, not only during actual write.

I was wondering if multiple instances can use the same SQLite file, locking only during the writing, not the whole time?

[rvk]

Well, that shouldn't be a problem.

BUT:

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

https://www.sqlite.org/faq.html#q5
(note NFS <> NTFS)

On what kind of drive (FAT, NTFS, Network drive) is your database?
Under what operating system and what version?
It all matters.

[LuizAmérico]

I was wondering if multiple instances can use the same SQLite file, locking only during the writing, not the whole time?

As long as all process accessing the database is in the same system as the datafile you are safe to go.

If the datafile is in a network share there's potential problems related to file locking, thus not recommended.

But i have two Lazarus applications with multiples process reading and writing (few writings) accessing a datafile in a NTFS network share without problems. One app is running for 8 years the other for 3 years.

In other to scale i'm migrating to a HTTP server (cgi/fastcgi) architecture where i can safely scale the concurrency with Sqlite.

[tatamata]

I was wondering if multiple instances can use the same SQLite file, locking only during the writing, not the whole time?

As long as all process accessing the database is in the same system as the datafile you are safe to go.

If the datafile is in a network share there's potential problems related to file locking, thus not recommended.

But i have two Lazarus applications with multiples process reading and writing (few writings) accessing a datafile in a NTFS network share without problems. One app is running for 8 years the other for 3 years.

In other to scale i'm migrating to a HTTP server (cgi/fastcgi) architecture where i can safely scale the concurrency with Sqlite.

Using sqldb? How do you prevent locking the whole database? In my case, maybe bacuse of caCommitRetaining transaction (?) the whole database is whole time locked for writing for all other instances, available only for reading...

[LuizAmérico]

I forked the sqlite3connection to fit my needs. Among other changes i removed the calls to 'BEGIN' 'COMMIT', effectively making start/endtransaction a no-op. If i need transaction i issue directly 'BEGIN' 'COMMIT'

But even without this change there was not a issue since the at each access, the cgi process is started, opens the database, execute the commands and close the database. Only for sometimes the db is locked, not affecting performance. Be warned that a busy timeout must be specified.

[LuizAmérico]

The above setup ifs for the HTTP server. For the access through network share i use TSqlite3Dataset

[tatamata]

I forked the sqlite3connection to fit my needs. Among other changes i removed the calls to 'BEGIN' 'COMMIT', effectively making start/endtransaction a no-op. If i need transaction i issue directly 'BEGIN' 'COMMIT'

But even without this change there was not a issue since the at each access, the cgi process is started, opens the database, execute the commands and close the database. Only for sometimes the db is locked, not affecting performance. Be warned that a busy timeout must be specified.

My application is desktop application with dbaware controls, mostly dbgrids...

[rvk]

Using sqldb? How do you prevent locking the whole database? In my case, maybe bacuse of caCommitRetaining transaction (?) the whole database is whole time locked for writing for all other instances, available only for reading...

Your problem is not so much the caCommitRetaining but the SELECT of your second program.

Read this page: https://www.sqlite.org/lockingv3.html
At the bottom you'll see that when a program issues a SELECT it will obtain a SHARED-lock on the database. Multiple programs van READ the database but NONE can write to it as long as there are SHARED-locks on the database. (No EXCLUSIVE-locks can be obtained as long as there are SHARED locks)

You can check this by just running your program on one end... (don't enter edit yet) start it a second time... you can view both at the same time... but none can insert any records until the other exists or closes the database.

So you would need to program around this (closing the database etc) or change the component like LuizAmérico has done (but even if you do that you can't have a program with an open database like DBGrid, and edit it with another program).

So if access from multiple instances is really needed (with DBGrids etc.) I would suggest going for a "real" client/server database (like Firebird).

[engkin]

Read this page: https://www.sqlite.org/lockingv3.html

From the top of that page:

The document only describes locking for the older rollback-mode transaction mechanism. Locking for the newer write-ahead log or WAL mode is described separately.

From WAL mode page:

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

WAL mode is activated by:

PRAGMA journal_mode=WAL;

[LuizAmérico]

About wal, from http://www.sqlite.org/wal.html:

But there are also disadvantages:

    WAL normally requires that the VFS support shared-memory primitives. (Exception: WAL without shared memory) The built-in unix and windows VFSes support this but third-party extension VFSes for custom operating systems might not.
    All processes using a database must be on the same host computer; WAL does not work over a network filesystem.

[LuizAmérico]

Did you try to set a busy timeout?

[runs]

I solved with WAL mode. I can write from two instances of the same program and not locks:

Code: Pascal  [Select][+][-]

1. uses
2.   db, sqlite3conn, sqldb;
3. type
4.   TSQLite3Connection = class(sqlite3conn.TSQLite3Connection)
5.   protected
6.     procedure DoInternalConnect; override;
7.   end;  

Code: Pascal  [Select][+][-]

1. procedure TSQLite3Connection.DoInternalConnect;
2. begin
3.   inherited;
4.   execsql('PRAGMA journal_mode = WAL;');
5. end;

And I set a busy_timeout:

Code: Pascal  [Select][+][-]

1. type
2.   { TDataModule1 }
3.   TDataMod = class(TDataModule)
4.     procedure ConnectionAfterConnect(Sender: TObject);
5.   private
6.     { private declarations }
7.   public
8.     { public declarations }
9.   end;          

           

Code: Pascal  [Select][+][-]

1. procedure TDataMod.ConnectionAfterConnect(Sender: TObject);
2. begin
3.   Connection.ExecuteDirect('PRAGMA busy_timeout = 1000;');
4. end;

But I face a problem, the changes do not update (really update in db but the another user cannot see) till Disconnect/Reconnect Database. How to know/refresh if the other user modified the database?