Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Thread safe of sqlite with different connection name
Forum Updated to NodeBB v4.3 + New Features

Thread safe of sqlite with different connection name

Scheduled Pinned Locked Moved Unsolved General and Desktop
12 Posts 4 Posters 3.3k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • thamT Offline
    thamT Offline
    tham
    wrote on last edited by
    #1

    If I open two db of sqlite with different connection names, could I treat them as two independent database, or I need to lock the db if I call them in different threads?Thanks

    QSqlDatabase alert_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), "alert_db");
    QSqlDatabase visit_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), "visit_event_db");
    
    JonBJ 1 Reply Last reply
    0
    • thamT tham

      If I open two db of sqlite with different connection names, could I treat them as two independent database, or I need to lock the db if I call them in different threads?Thanks

      QSqlDatabase alert_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), "alert_db");
      QSqlDatabase visit_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), "visit_event_db");
      
      JonBJ Online
      JonBJ Online
      JonB
      wrote on last edited by JonB
      #2

      @tham

      or I need to lock the db if I call them in different threads

      Are you aware that you must not perform database operations in threads other than the one where addDatabase() was called?

      1 Reply Last reply
      4
      • thamT Offline
        thamT Offline
        tham
        wrote on last edited by tham
        #3

        Are you aware that you must not perform database operations in threads other than the one where addDatabase() was called?

        No, thanks for your information, this help a lot.

        JonBJ 1 Reply Last reply
        0
        • thamT tham

          Are you aware that you must not perform database operations in threads other than the one where addDatabase() was called?

          No, thanks for your information, this help a lot.

          JonBJ Online
          JonBJ Online
          JonB
          wrote on last edited by
          #4

          @tham
          I don't know what you were planning to do across threads, but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

          thamT 1 Reply Last reply
          1
          • JonBJ JonB

            @tham
            I don't know what you were planning to do across threads, but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

            thamT Offline
            thamT Offline
            tham
            wrote on last edited by
            #5

            @JonB

            but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

            This is what I need. From the Qt doc, I think the safe way to access the same sqlite table is

            1. create different connection in different thread
            2. use mutex to lock the non-thread safe operation of sqlite(ex : transaction)

            If you want to use it with QtConcurrent::run, is following solution work?

            lock_guaryd<mutex>  guard(mutex_);
            return QtConccurrent::run([=]()
            {
                  {
                      auto db = QSqlDatabase::addDatabase("QSQLITE", conName);
                      db.setDatabaseName("alert");
                     //open db, transaction, insert data blah blah blah
                 }
                QSqlDatabase::removeDatabase(conName);
            });
            

            This solution need to create new connection, open db, remove connection blah blah every time when I need to insert data into the sqlite(only insert when cache is big enough or closing the app), looks a bit clumsy.

            jsulmJ JonBJ 2 Replies Last reply
            0
            • thamT tham

              @JonB

              but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

              This is what I need. From the Qt doc, I think the safe way to access the same sqlite table is

              1. create different connection in different thread
              2. use mutex to lock the non-thread safe operation of sqlite(ex : transaction)

              If you want to use it with QtConcurrent::run, is following solution work?

              lock_guaryd<mutex>  guard(mutex_);
              return QtConccurrent::run([=]()
              {
                    {
                        auto db = QSqlDatabase::addDatabase("QSQLITE", conName);
                        db.setDatabaseName("alert");
                       //open db, transaction, insert data blah blah blah
                   }
                  QSqlDatabase::removeDatabase(conName);
              });
              

              This solution need to create new connection, open db, remove connection blah blah every time when I need to insert data into the sqlite(only insert when cache is big enough or closing the app), looks a bit clumsy.

              jsulmJ Online
              jsulmJ Online
              jsulm
              Lifetime Qt Champion
              wrote on last edited by
              #6

              @tham Why do you actually want to access the database from different threads? If you anyway synchronize the access with mutexes you will not gain any performance improvements, but complicate your app. You should access the db from one thread. Other threads can send data (or get data) to/from db using signals.

              https://forum.qt.io/topic/113070/qt-code-of-conduct

              1 Reply Last reply
              2
              • thamT tham

                @JonB

                but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

                This is what I need. From the Qt doc, I think the safe way to access the same sqlite table is

                1. create different connection in different thread
                2. use mutex to lock the non-thread safe operation of sqlite(ex : transaction)

                If you want to use it with QtConcurrent::run, is following solution work?

                lock_guaryd<mutex>  guard(mutex_);
                return QtConccurrent::run([=]()
                {
                      {
                          auto db = QSqlDatabase::addDatabase("QSQLITE", conName);
                          db.setDatabaseName("alert");
                         //open db, transaction, insert data blah blah blah
                     }
                    QSqlDatabase::removeDatabase(conName);
                });
                

                This solution need to create new connection, open db, remove connection blah blah every time when I need to insert data into the sqlite(only insert when cache is big enough or closing the app), looks a bit clumsy.

                JonBJ Online
                JonBJ Online
                JonB
                wrote on last edited by JonB
                #7

                @tham said in Thread safe of sqlite with different connection name:

                This is what I need. From the Qt doc, I think the safe way to access the same sqlite table is

                create different connection in different thread
                use mutex to lock the non-thread safe operation of sqlite(ex : transaction)

                Could you refer me to where in the documentation you find this suggestion?

                This solution need to create new connection, open db, remove connection blah blah every time when I need to insert data into the sqlite

                It is "unusual" (and costly) to have to add/open remove/close a database each time you want to insert data?

                Exactly as @jsulm has just posted: one begins to wonder why in the world you want threads at all, wouldn't you be better/simpler and even possibly quicker to do it all single-threaded and not have to do this mutexing and especially opening/closing each time??

                1 Reply Last reply
                1
                • thamT Offline
                  thamT Offline
                  tham
                  wrote on last edited by tham
                  #8

                  @jsulm

                  If you anyway synchronize the access with mutexes you will not gain any performance improvements, but complicate your app. You should access the db from one thread. Other threads can send data (or get data) to/from db using signals.

                  Because the main purpose is not increase the throughput, but prevent the main gui freeze, everytime I insert the data(after I call exec), the frame freeze, although this is not frequently happen, customers don't like it. That is why I want to use QtConcurrent to insert the data, since it is easier to use(don't need to create a worker class and move it to QThread etc)

                  @JonB

                  Could you refer me to where in the documentation you find this suggestion?

                  No, that is the solution I come up, not sure if it is correct.

                  It is "unusual" (and costly) to have to add/open remove/close a database each time you want to insert data?

                  As I though, but this do not happen frequently, I only insert the data into db after cache is full.

                  wouldn't you be better/simpler and even possibly quicker to do it all single-threaded and not have to do this mutexing and especially opening/closing each time??

                  Tried it before, but the frame freeze every time when I insert the data

                  JonBJ 1 Reply Last reply
                  0
                  • thamT tham

                    @jsulm

                    If you anyway synchronize the access with mutexes you will not gain any performance improvements, but complicate your app. You should access the db from one thread. Other threads can send data (or get data) to/from db using signals.

                    Because the main purpose is not increase the throughput, but prevent the main gui freeze, everytime I insert the data(after I call exec), the frame freeze, although this is not frequently happen, customers don't like it. That is why I want to use QtConcurrent to insert the data, since it is easier to use(don't need to create a worker class and move it to QThread etc)

                    @JonB

                    Could you refer me to where in the documentation you find this suggestion?

                    No, that is the solution I come up, not sure if it is correct.

                    It is "unusual" (and costly) to have to add/open remove/close a database each time you want to insert data?

                    As I though, but this do not happen frequently, I only insert the data into db after cache is full.

                    wouldn't you be better/simpler and even possibly quicker to do it all single-threaded and not have to do this mutexing and especially opening/closing each time??

                    Tried it before, but the frame freeze every time when I insert the data

                    JonBJ Online
                    JonBJ Online
                    JonB
                    wrote on last edited by JonB
                    #9

                    @tham

                    Because the main purpose is not increase the throughput, but prevent the main gui freeze,

                    Even if this threading is your solution, why do you want two, separate threads for your two databases? If you put both (all) of them in a single thread dedicated for database interaction, you would avoid all this nasty mutexing problem, wouldn't you??

                    thamT 1 Reply Last reply
                    3
                    • JonBJ JonB

                      @tham

                      Because the main purpose is not increase the throughput, but prevent the main gui freeze,

                      Even if this threading is your solution, why do you want two, separate threads for your two databases? If you put both (all) of them in a single thread dedicated for database interaction, you would avoid all this nasty mutexing problem, wouldn't you??

                      thamT Offline
                      thamT Offline
                      tham
                      wrote on last edited by
                      #10

                      @JonB said in Thread safe of sqlite with different connection name:

                      Even if this threading is your solution, why do you want two, separate threads for your two databases? If you put both (all) of them in a single thread dedicated for database interaction, you would avoid all this nasty mutexing problem, wouldn't you??

                      Because I am too lazy to create a worker class for them, so I use QtConcurrent instead of that, the price is the behavior looks unusual. Maybe I would move them into a worker class in the future, but not now.

                      Anyway, thanks for your suggestions, they help me a lot.

                      kshegunovK 1 Reply Last reply
                      0
                      • thamT tham

                        @JonB said in Thread safe of sqlite with different connection name:

                        Even if this threading is your solution, why do you want two, separate threads for your two databases? If you put both (all) of them in a single thread dedicated for database interaction, you would avoid all this nasty mutexing problem, wouldn't you??

                        Because I am too lazy to create a worker class for them, so I use QtConcurrent instead of that, the price is the behavior looks unusual. Maybe I would move them into a worker class in the future, but not now.

                        Anyway, thanks for your suggestions, they help me a lot.

                        kshegunovK Offline
                        kshegunovK Offline
                        kshegunov
                        Moderators
                        wrote on last edited by kshegunov
                        #11

                        You may not be able to access them in separate threads at all, mutexes or no mutexes. The Qt API gives no guarantees that the SQL driver would keep reentrancy! So this question would be answered by actually looking at the SQL driver and/or finding a person that is familiar with the implementation. The answer is implementation (and database) specific.

                        Read and abide by the Qt Code of Conduct

                        thamT 1 Reply Last reply
                        4
                        • kshegunovK kshegunov

                          You may not be able to access them in separate threads at all, mutexes or no mutexes. The Qt API gives no guarantees that the SQL driver would keep reentrancy! So this question would be answered by actually looking at the SQL driver and/or finding a person that is familiar with the implementation. The answer is implementation (and database) specific.

                          thamT Offline
                          thamT Offline
                          tham
                          wrote on last edited by tham
                          #12

                          @kshegunov Thanks for your advice. After checking, I am sure in my case, the functions related to the database do not need to be reentrant, I make sure they work sequential with the help of lock(same lock for the same table of sqlite3). Do I miss some important points which may cause the program unstable(undefined behavior etc)?

                          1 Reply Last reply
                          0

                          • Login

                          • Login or register to search.
                          • First post
                            Last post
                          0
                          • Categories
                          • Recent
                          • Tags
                          • Popular
                          • Users
                          • Groups
                          • Search
                          • Get Qt Extensions
                          • Unsolved