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]

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...

[JonB]

@tovax
Yes, this is the issue. As you say, emitting a signal to ask the main thread to do a write (setData()) for your thread is not so difficult. Your problem comes on trying to synchronise read access (data()).

It is easier to maintain per-thread connections for that purpose. But that comes with other overheads, as discussed. Depends on how much of what you need to do when.

[Christian Ehrlicher]

Again: why do you think you need threads for simple reading data from the database? What heaviy computation do you do with those values?

[tovax]

@Christian-Ehrlicher
The main purpose of thread is to process time-consuming algorithms and real-time control. Reading data from the database is only to obtain the global parameters needed in the algorithm and control process.

[Christian Ehrlicher]

Then read your data in one thread, start/inform the workers that there is data and let them do the work. When you a mutex to protect read/write access to your data you don't even need to copy it. But that's plain threading stuff which you should read about before trying to actually writing something. Threading is not easy and should be avoided when not really needed for basic programmers.

[JonB]

@tovax
I was going to say as @Christian-Ehrlicher has just said. Can you not take the reading from the database outside the threads and then access some shared data only for the computations.

@Christian-Ehrlicher
Isn't the following a problem: is it not the case that for a reader accessing data() that calls rowCount() and that can cause physical call to database for fetchMore()?? Or does data()/rowCount() never do that, I can't recall?

[tovax]

@Christian-Ehrlicher

Some results of time-consuming algorithm and motion control need to be written to database, such as time, current position and so on. In other words, there will be frequent data interaction between some child threads and the database.

[KroMignon]

@tovax said in Exception of multi thread reading QSqlTableModel:

The main purpose of thread is to process time-consuming algorithms and real-time control. Reading data from the database is only to obtain the global parameters needed in the algorithm and control process.

I think you are "over-engineering" your programm.
For me, the steps your need are:

• getting computation parameters from db
• starting multiple computation operations

So I would do it like @Christian-Ehrlicher said:

• in main thread preparing data for computation ==> reading required data from DB
• start thread for computation ==> I would do it with QtConcurrent::run() and dedicated QThreadPool to avoid thread creation/destruction and to be able to wait for computation end with QFutureWatcher.

[tovax]

For example, the results generated by child thread1 need to be written to the database, and used by child thread2.

[JonB]

@tovax
Most important decision: do you really mean that thread1 must physically write to the database and thread2 must physically (re-)read the data from the database, or do you mean that thread2 can/should read the (changed) data in-memory.

[SGaist]

@tovax from the looks of it you are missing one component: the controller.

Your use case is typically one that should rather use MVC rather than just MV.

One thing that is not clear is how are you child threads triggered ?

Are you creating them on demand on are they long-lived ? In both cases, the suggestions from @KroMignon are good in terms of encapsulation of the threaded components and separation of the storage side.

@JonB The one connection per thread rule is rather a constraints of the class design: you shall not use the exact same database connection from different threads in the same spirit as you should not modify GUI object outside the GUI thread.

[KroMignon]

@tovax said in Exception of multi thread reading QSqlTableModel:

For example, the results generated by child thread1 need to be written to the database, and used by child thread2.

You can solve this with signals/slots:

• Client1: you need a signal (or more) for writing result, which can be connected to Main to store in DB and to Client2 to update his parameters.
• Client2: you need a signal for writing new position
• Main: you need a signal to setup Client1 and another to setup Client2

Maybe Client1 and Client2 needs an additional signals to request settings update.
But that don't seems to be a very difficult architecture to implement.

[JonB]

@SGaist said in Exception of multi thread reading QSqlTableModel:

@JonB The one connection per thread rule is rather a constraints of the class design: you shall not use the exact same database connection from different threads

Absolutely, as I said from the start! The question at hand is whether for this user's case he should indeed go for separate connections per thread, or whether he should work on "shared" (somehow) data.

Since @Christian-Ehrlicher has not replied to it, could you kindly answer my earlier question, at least for my information:

Isn't the following a problem: is it not the case that for a reader accessing data() that calls rowCount() and that can cause physical call to database for fetchMore()?? Or does data()/rowCount() never do that, I can't recall?

[tovax]

@JonB
First of all, the data generated by each child thread must be written to the database in order to prevent sudden power failure. Then, the data generated by each child thread can be shared with each other threads.
I just think the unified use of the database is logically simpler, thread2 can read the changed data in memory.

[tovax]

This post is deleted!

[tovax]

[tovax]

@KroMignon said in Exception of multi thread reading QSqlTableModel:

Maybe Client1 and Client2 needs an additional signals to request settings update.

Thank you for your detailed steps. The actual application is much more complicated than the block diagram, so I'm worried that this method will need to create too many signal slots.

[tovax]

Based on your discussion, I think that creating database connections for each child thread is not my best choice, because the actual situation is much more complicated than the block diagram.

[tovax]

In the main thread to create a unique database connection, other child threads interact with the main thread to achieve read/write the database. I know that the signal-slot can easily write data to the database, so what is the best way to read data?

[tovax]