Applicable Products: CardExchange® Visitor

Using SQLite with multiple clients

By default an installation of CardExchange® Visitor will use a SQLite database, for stand-alone installations this is absolutely fine but what about if multiple installations need access to the database?

You can of course from the Business edition use either an SQL Server database or a MySQL database, but if you just have 2x Standard edition clients or perhaps 1x Business edition client and a Kiosk client is that necessary?

Below is an extract taken from a question on the SQLite website:
https://www.sqlite.org/faq.html#q5

Can multiple applications or multiple instances of the same application access a single database file at the same time?

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.

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

However, client/server database engines (such as SQL Server, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

So ultimately there is no right or wrong answer, in general if you have a couple of CardExchange® Visitor clients and the usage is fairly low, then the chances of both clients trying to write to the database with a few milliseconds of each other are relatively slim. So in this case you would likely be fine using a SQLite database and sharing it between CardExchange® Visitor clients.

However if you have a more demanding installation with several CardExchange® Visitor clients and medium to heavy usage, then a server based database such as SQL Server or MySQL would likely be the best choice.

From CardExchange® Visitor Business edition you can use other database types including SQL Server and MySQL, so when using multiple clients it would make sense to make use of this and use for example a MySQL database to avoid any potential issues.

Details on installing and configuring a MySQL database for use with CardExchange® Visitor can be found in the installation guide below:

https://help.cardexchangesolutions.com/visitor/en/cevisitorinstall/cevisitorinstall.html