Qt中多线程使用数据库

女爷i 2022-05-22 07:39 226阅读 0赞

### **【写在前面】** ###

最近在多线程环境中使用数据库，结果出现了一些比较有意思的问题。

然后也找到了原因和解决的办法，这里记录、分享一下过程。

--------------------

### **【正文开始】** ###

开始我打算开两个线程，然后每个线程进行自己的数据库查询，

然后出现了一个线程能查询到数据，一个不能，甚至程序直接崩溃退出。

如图(后面直接崩溃)：

![70][]

widget代码如下：

#ifndef WIDGET_H
    #define WIDGET_H
    
    #include <QWidget>
    #include <QPushButton>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QSqlError>
    #include <QSqlDriver>
    #include <QThread>
    #include <QTextBrowser>
    #include <QVBoxLayout>
    
    class Test : public QObject
    {
        Q_OBJECT
    
    public:
        Test(QSqlDatabase database)
            :   QObject(nullptr), m_database(database)
        {
    
        }
    
    signals:
        void query_result(const QString &result);
    
    public slots:
        void test_query()
        {
            m_database.open();
    
            QString str = "Thread ID: " + QString::number((int)QThread::currentThreadId(), 16) + "  result: |";
            QSqlQuery query(m_database);
            query.exec("SELECT * FROM test;");
    
            while(query.next())
                str += QString::number(query.value(1).toInt()) + ":" + query.value(0).toString() + "|";
    
            m_database.close();
    
            emit query_result(str + "\n");
        }
    
    private:
        QSqlDatabase m_database;
    };
    
    class Widget : public QWidget
    {
        Q_OBJECT
    
    public:
        Widget(QWidget *parent = nullptr)
            :   QWidget(parent)
        {
            QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
            db.setDatabaseName("test.db");
            db.setHostName("localhost");
            db.setUserName("mps");
            db.setPassword("123456");
    
            test1 = new Test(db);
            test2 = new Test(db);
    
            test1->moveToThread(&thread1);
            test2->moveToThread(&thread2);
    
            thread1.start();
            thread2.start();
    
            QPushButton *button = new QPushButton("开始", this);
            QTextBrowser *browser = new QTextBrowser(this);
            browser->insertPlainText("UI Thread ID: " + QString::number((int)QThread::currentThreadId(), 16));
            QVBoxLayout *layout = new QVBoxLayout(this);
            layout->addWidget(button);
            layout->addWidget(browser);
            setLayout(layout);
    
            connect(button, &QPushButton::clicked, this, &Widget::start);
            connect(button, &QPushButton::clicked, this, [=]()
            {
               browser->insertPlainText("\n");
            });
            connect(&thread1, &QThread::finished, &thread1, &QThread::deleteLater);
            connect(&thread2, &QThread::finished, &thread2, &QThread::deleteLater);
            connect(test1, &Test::query_result, this, [=](const QString &result)
            {
               browser->insertPlainText(result);
            });
            connect(test2, &Test::query_result, this, [=](const QString &result)
            {
               browser->insertPlainText(result);
            });
    
            resize(500, 400);
        }
    
        ~Widget()
        {
            thread1.wait();
            thread2.wait();
        }
    
    public slots:
        void start()
        {
            QMetaObject::invokeMethod(test1, "test_query");
            QMetaObject::invokeMethod(test2, "test_query");
        }
    
    private:
        QThread thread1;
        QThread thread2;
    
        Test *test1;
        Test *test2;
    };
    
    #endif // WIDGET_H

很明显的原因是出现了竞争，然而最大的问题出在两个线程使用了同一个连接，QSqlDatabase::addDatabase 的第二个参数为 const QString &connectionName = QLatin1String(defaultConnection) ，默认值即为默认连接，问题部分如下：

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("test.db");
    db.setHostName("localhost");
    db.setUserName("mps");
    db.setPassword("123456");
    
    test1 = new Test(db);
    test2 = new Test(db);

因此只需要创建各自不同的连接，更改后的代码如下：

#ifndef WIDGET_H
    #define WIDGET_H
    #include <QWidget>
    #include <QPushButton>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QSqlError>
    #include <QSqlDriver>
    #include <QThread>
    #include <QTextBrowser>
    #include <QVBoxLayout>
    
    class Test : public QObject
    {
        Q_OBJECT
    
    public:
        Test(const QString &connectName)
            :   QObject(nullptr)
        {
            m_database = QSqlDatabase::addDatabase("QSQLITE", connectName);
            m_database.setDatabaseName("test.db");
            m_database.setHostName("localhost");
            m_database.setUserName("mps");
            m_database.setPassword("123456");
        }
    
    signals:
        void query_result(const QString &result);
    
    public slots:
        void test_query()
        {
            m_database.open();
    
            QString str = "Thread ID: " + QString::number((int)QThread::currentThreadId(), 16) + "  result: |";
            QSqlQuery query(m_database);
            query.exec("SELECT * FROM test;");
    
            while(query.next())
                str += QString::number(query.value(1).toInt()) + ":" + query.value(0).toString() + "|";
    
            m_database.close();
    
            emit query_result(str + "\n");
        }
    
    private:
        QSqlDatabase m_database;
    };
    
    class Widget : public QWidget
    {
        Q_OBJECT
    
    public:
        Widget(QWidget *parent = nullptr)
            :   QWidget(parent)
        {
            test1 = new Test("test1");
            test2 = new Test("test2");
    
            test1->moveToThread(&thread1);
            test2->moveToThread(&thread2);
    
            thread1.start();
            thread2.start();
    
            QPushButton *button = new QPushButton("开始", this);
            QTextBrowser *browser = new QTextBrowser(this);
            browser->insertPlainText("UI Thread ID: " + QString::number((int)QThread::currentThreadId(), 16));
            QVBoxLayout *layout = new QVBoxLayout(this);
            layout->addWidget(button);
            layout->addWidget(browser);
            setLayout(layout);
    
            connect(button, &QPushButton::clicked, this, &Widget::start);
            connect(button, &QPushButton::clicked, this, [=]()
            {
               browser->insertPlainText("\n");
            });
            connect(&thread1, &QThread::finished, &thread1, &QThread::deleteLater);
            connect(&thread2, &QThread::finished, &thread2, &QThread::deleteLater);
            connect(test1, &Test::query_result, this, [=](const QString &result)
            {
               browser->insertPlainText(result);
            });
            connect(test2, &Test::query_result, this, [=](const QString &result)
            {
               browser->insertPlainText(result);
            });
    
            resize(500, 400);
        }
    
        ~Widget()
        {
            thread1.wait();
            thread2.wait();
        }
    
    public slots:
        void start()
        {
            QMetaObject::invokeMethod(test1, "test_query");
            QMetaObject::invokeMethod(test2, "test_query");
        }
    
    private:
        QThread thread1;
        QThread thread2;
    
        Test *test1;
        Test *test2;
    };
    #endif // WIDGET_H

测试图如下：

![70 1][]

从结果来看起来，基本没有问题了，也没有再出现崩溃。

--------------------

### **【结语】** ###

最后，还有一些东西要注意，比如每次 sql 操作前后都使用 open / close，否则如果不 close，线程就无法退出，然后文档中说明了 addDatabase 是线程安全的，但 open 并没有指出，因此建议在 open 的部分设置 Mutex 进行保护，以保证其原子性，这样就不会出现任何问题。

[70]: /images/20220522/1632fae18f504bdd814f0b98bebcb7e3.png
[70 1]: /images/20220522/9de84c97d9e74168ab773aaf81281096.png