Is there any problem on using many open connections at the same time from different threads?
From what I've read it's thread safe by default, but, can this be hurting performance rather than improving it?
Asked
Active
Viewed 4,220 times
2 Answers
2
Having multiple connection is not a problem, the only thing to keep in mind is that SQLite does not support concurrency of multiple write transactions. From the SQlite site:
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writer queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
SQLite is an "untypical" database management system: in practice it is a library that offers SQL as language to access a simple "database-in-a-file", and a few other functionalities of DBMSs. For instance, it has no real concurrency control (it uses the Operating Systems functions to lock the db file).
So, if you need concurrent insertions into a database, you should use something else, for instance PostgreSQL.
Renzo
- 26,848
- 5
- 49
- 61
-
Does this mean I can not have inserts/updates on different threads? – Stephen H. Anderson Jul 20 '15 at 12:18
-
Yes, you cannot have concurrent modification. See my updated answer. – Renzo Jul 20 '15 at 12:19
-
@Renzo, its wrong: you can write to database on different threads but not at one moment. The one way is use mutex and post shared data to database from different thread will be able. – t3ft3l--i Jul 20 '15 at 12:23
-
@t3ft3l--i, What is wrong? The SQLite manual? Or the fact that the concurrency is done at the Operating System level? I was talking about *concurrency* at the level of the DBMS. The concurrency of SQLite is not *real* concurrency at DBMS level. That's plain and simple. In “real” DBMS you can have thousand of writing transactions per seconds, in SQLite, no. – Renzo Jul 20 '15 at 12:27
-
@Renzo Concurrency at your way is operations from different threads at the same time. Of course, SQLite not support multithreading operation at the same time, but it is real to organized using lockers as mutex for shared data. And main question wasn't about SQLite multiple transaction. – t3ft3l--i Jul 20 '15 at 12:36
-
The question was: *Is there any problem on using many open connections at the same time from different threads?*. In short my answer was not, but keep in mind the simple fact that the level of concurrency of SQLite can be "low". Of course if the application has only a few concurrent insertions then it is reasonable to use SQLite, but since the OP was talking about threads, I assumed (maybe wrongly) that the application requires an "high" level of concurrency. – Renzo Jul 20 '15 at 12:42
-
So, I'm confused now. I have 5 threads, each holds an instance to a Sqlite database object. Can these threads do inserts/updates/deletes "freely" or do I have to use only one thread for this? Or have the threads call a central wrapper object which uses locks for the inserts/updates/deletes? – Stephen H. Anderson Jul 20 '15 at 12:51
-
1You can use insert etc. in your threads. Only you should know that, even if the thread operates on different data, they will be serialized. However, they are automatically serialized by the system, so you do not have to change your program. Moreover, the fact that you have only a few threads means that you will not notice any particular loss of performance from your application. – Renzo Jul 20 '15 at 12:55
1
The documentation say:
A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread is not supported.
In addition, the third party libraries used by the QSqlDrivers can impose further restrictions on using the SQL Module in a multithreaded program. Consult the manual of your database client for more information.
It is mean you have to create connection to database which will be linking with parent thread. At docs of QSqlDatabase class you can see description:
The QSqlDatabase class represents a connection to a database.
The QSqlDatabase class provides an interface for accessing a database through a connection. An instance of QSqlDatabase represents the connection. The connection provides access to the database via one of the supported database drivers, which are derived from QSqlDriver.
Create a connection (i.e., an instance of QSqlDatabase) by calling one of the static addDatabase() functions, where you specify the driver or type of driver to use (i.e., what kind of database will you access?) and a connection name.
Using static addDatabase() function is way to create connection.
But as Renzo said SQLite does not support multiple write transactions at the same time. So you need some mechanisms(wrapper) for synchronizing threads like task queue using low-level mutex or something like that. More information you can see at docs.
-
1As I have written in the comment to my answer, actually there is no need to explicitly synchronize the threads. The serializations is made automatically by SQLite. – Renzo Jul 20 '15 at 13:00
-