Exception of multi thread reading QSqlTableModel

tovax

Hi, all
I created the QSqlTableModel in the main thread and connected it to the database correctly. I want to quickly read the data in the model in the child thread, but occasionally the wrong data will be read. However, if the data in the model is copied into memory, the data read is correct.
Occasional read error:

QVariant SysParamAxes::value(const int32_t row, const int32_t column, int32_t role)
{
    QReadLocker lock(&modelLocker);
    return tableModel->data(tableModel->index(row, column), role);
}

read correct:

SysParamAxes::SysParamAxes
{
    for (int32_t row = 0; row < tableModel->rowCount(); row++) {
        for (int32_t column = 0; column < tableModel->columnCount(); column++) {
            values[row][column] = tableModel->data(tableModel->index(row, column));
        }
    }
}

QVariant SysParamAxes::value(const int32_t row, const int32_t column, int32_t role)
{
    QReadLocker lock(&modelLocker);
    return values[row][column];
}

I'm confused why it's necessary to copy the data from the model into memory,
could you help me please?
Best regards!

JonB

@tovax

I created the QSqlTableModel in the main thread and connected it to the database correctly. I want to quickly read the data in the model in the child thread

You are not allowed to perform SQL operations in a different thread from where the connection/model were created/live.

tovax

This post is deleted!

tovax

@JonB
In other words, it is necessary to copy the data in the model into memory. Is my understanding correct please?

tovax

@JonB
Thank you very much!
Best Wishes!

JonB

@tovax said in Exception of multi thread reading QSqlTableModel:

In other words, it is necessary to copy the data in the model into memory. Is my understanding correct please?

I don't really understand what this question means? A SQL operation like fetching the data must be performed in the thread owning the SQL connection. That thread could pre-read the result set, or a child thread could send it a signal asking it to do so when it wants it. I don't know why you are using child thread(s) here, but if you do your reading in another thread you might be best putting the QSql... variables there, or moving them to that thread.

tovax

@JonB
The data of QSqlTableModel is a global parameter, which needs to be used by multiple child threads. Your reply made me know the reason. According to your help, copying the data in the model to memory should solve my problem. I am modifying the code.
It should also be a good way to reestablish a connection between the model and the database at each child thread.
Thank you again.

SGaist

Hi,

If you want multithreaded access you should create one connection per thread.

tovax

@SGaist Hi, Thanks a lot for your help!
If a child thread modifies the data in the database, how can the QSqlTableModel of other threads be updated synchronously? By a signal send from this child thread?

tovax

7d9b611a-ea89-451b-94c6-4e275ff6bb43-图片.png
Main thread: create QTableView to modify the database
Child thread1: only read database
Child thread2: read database by "tableModel->data()", and write database by "tableModel->setData()"
Child thread3: only read database
All threads must be synchronized.

tovax

In other words, different threads connect to the same database. When one thread updates the data in the database, how do other threads know that the database has been updated? The method of using Signal-Slots between threads seems troublesome.

JonB

@tovax said in Exception of multi thread reading QSqlTableModel:

The method of using Signal-Slots between threads seems troublesome.

Nonetheless this is indeed the paradigm Qt wants you to use! Why do you find it "troublesome"?

Although doubtless he is correct --- he usually is! --- I do not share @SGaist's immediate alacrity to move to separate connections per thread. It may be required, but it could be costly on resources/memory/speed/consistency. You might also consider either working on an in-memory, shared copy of the data already read in, or signals/slots to/from the main thread. It depends on what you are doing where.

Christian Ehrlicher

@JonB said in Exception of multi thread reading QSqlTableModel:

It may be required,

It is required - QSqlDatabase database connection is not thread-safe.

how do other threads know that the database has been updated?

Use signals/slots

The method of using Signal-Slots between threads seems troublesome.

Why? It's much easier than using semaphores and wait conditions.

But still don't see why you need threads in the first place.

tovax

@JonB said in Exception of multi thread reading QSqlTableModel:

gm Qt wants you to use! Why do you find it "troubles

If there are more threads that modify the database, then it is necessary to establish signal slot connections from these threads to all other threads.

Christian Ehrlicher

@tovax said in Exception of multi thread reading QSqlTableModel:

then it is necessary to establish signal slot connections from these threads to all other threads.

That's also true when you don't use signals and slots...

JonB

@Christian-Ehrlicher said in Exception of multi thread reading QSqlTableModel:

It may be required,

It is required - QSqlDatabase database connection is not thread-safe.

Yes, I was the first person to say that if you want to access a QSqlDatabase database connection you must only do so from its owning thread. If you read what I was saying, I was talking about not having multiple database connections, as an alternative.

If, for whatever reason, the user wants to have 100 threads running accessing data read from the database, having 100 separate database connections is not an advisable approach. IMHO.

tovax

@Christian-Ehrlicher said in Exception of multi thread reading QSqlTableModel:

But still don't see why you need threads in the first place.

The time-consuming trajectory algorithm and real-time motion control algorithm in the application program need to be processed by separate threads, which will use the parameter data in the database.

KroMignon

@tovax said in Exception of multi thread reading QSqlTableModel:

If there are more threads that modify the database, then it is necessary to establish signal slot connections from these threads to all other threads.

As @Christian-Ehrlicher already written, QSqlDatabase is not thread-safe, so you have 2 options:

do all DB stuff in one thread, by using signals/slots it is quit easy.
use as many QSqlDatabase connections as you have threads ==> take a look here for example: https://forum.qt.io/topic/103626/problem-with-sqlite-database-and-threads-database-is-locked/14

tovax

@JonB
Yes, I agree with you. In extreme cases, establishing separate database connections should be very resource intensive. At present, I prefer to share memory copies.

tovax

@KroMignon said in Exception of multi thread reading QSqlTableModel:

do all DB stuff in one thread, by using signals/slots it is quit easy.

It's easy to write data by using signals/slots, but is it necessary to copy a memory copy for reading data? Because DB data cannot be read directly from other threads...