Single user database vs multi-user database

Question

What is the difference between single user database and multiple user database? Why I would need to use a single user database? why it was made?

If I have multiple threads accessing the same database on the same time using the same log-in credentials, do I need a multiple user database?

Answer 1

What is the difference between single user database and multiple user database? Why I would need to use a single user database? why it was made?

Single-user mode allows only one connection to a database at a given moment in time.

Since you've tagged this question as SQLite I'll base my answer on that specific engine.

SQLite's concurrency protection model is based on direct file reads and writes to the database file on disk, so obviously two different programs can't be writing to the same file on a disk at the exact same time. SQLite will force the second query to wait while changes are being made to the database file.

SQLite protects against file/database corruption using operating system file locks when INSERTS and UPDATES are being done. But it does allow multiple read-only handles at the same time (just like how you can open same the file multiple times in read-only on your desktop, but won't be able to save changes).

In other databases like SQL Server, single-user mode is often only used when the database needs maintenance - like restoring a backup file, changing the database structure, or changing global database settings.

Single-user mode is useful because you can't have clients connected and trying to read/write data while you are making these kinds of changes.

But under normal conditions, you don't want to run SQL Server in single-user mode for performance reasons. However, unless you have a high-volume webservice, SQLite will probably be able to handle the performance requirements despite changes being one at a time on disk.

If I have multiple threads accessing the same database on the same time using the same log-in credentials, do I need a multiple user database?

No, it will still work. And SQLite databases don't require authentication at all, there are no credentials for it other than file/directory permissions.