QSqlDatabase using SQlite and timing of concurrent write attempts
-
Hi :-)
I'm writing a program using an SQLite database. It can either start a TCP server or client, so that all connected instances of it (that live on different computers) can broadcast changes and all databases are kept in sync (using the same central database all clients can connect to is not possible/wanted). At least, that's what it should be when I'm finished ;-)
I already implemented broadcasting all changes that are done on the server instance to all clients. This is no problem and already works.
But now, it's about how to implement a change coming from a client. I thought about not actually changing anything, but only to send a change request to the server. If everything is fine, the server applies the change and broadcasts it, as it would be done if the change originated from the server.
I think this should also be no problem, until a change request from a client actually happens the very time the server also wants to change something. And here we are at my question:
The whole communication is handled inside the normal event loop. There are no threads. So if the server triggers a change, and just in this exact moment, a change request from a client comes in, will it be either processed before or after the server change, or can it happen that the change request would collide? All changes to the database happen in transactions.
If all changes are processed one after another, I think it would be enough to check if a change is (still) possible server-side for both local and remote changes. If it's possible, it's applied, and if not, nothing is done and a message is displayed that the change has already been done (either locally or remotely).
Sorry if this should be a dumb question, but this is something I never did before, and I just don't want to implement stuff in a basically wrong way from the start.
Thanks for the clarification!
-
Hi :-)
I'm writing a program using an SQLite database. It can either start a TCP server or client, so that all connected instances of it (that live on different computers) can broadcast changes and all databases are kept in sync (using the same central database all clients can connect to is not possible/wanted). At least, that's what it should be when I'm finished ;-)
I already implemented broadcasting all changes that are done on the server instance to all clients. This is no problem and already works.
But now, it's about how to implement a change coming from a client. I thought about not actually changing anything, but only to send a change request to the server. If everything is fine, the server applies the change and broadcasts it, as it would be done if the change originated from the server.
I think this should also be no problem, until a change request from a client actually happens the very time the server also wants to change something. And here we are at my question:
The whole communication is handled inside the normal event loop. There are no threads. So if the server triggers a change, and just in this exact moment, a change request from a client comes in, will it be either processed before or after the server change, or can it happen that the change request would collide? All changes to the database happen in transactions.
If all changes are processed one after another, I think it would be enough to check if a change is (still) possible server-side for both local and remote changes. If it's possible, it's applied, and if not, nothing is done and a message is displayed that the change has already been done (either locally or remotely).
Sorry if this should be a dumb question, but this is something I never did before, and I just don't want to implement stuff in a basically wrong way from the start.
Thanks for the clarification!
@l3u_ said in QSqlDatabase using SQlite and timing of concurrent write attempts:
I thought about not actually changing anything, but only to send a change request to the server. If everything is fine, the server applies the change and broadcasts it, as it would be done if the change originated from the server.
Good idea.
There are no threads.
(I assume this means ther's a single thread) in that case all requests will be managed one after the other. You might have little control on the order of execution but you can be sure there's no chance of 2 things happening at the same time
"La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
~Napoleon BonaparteOn a crusade to banish setIndexWidget() from the holy land of Qt
-
@l3u_ said in QSqlDatabase using SQlite and timing of concurrent write attempts:
I thought about not actually changing anything, but only to send a change request to the server. If everything is fine, the server applies the change and broadcasts it, as it would be done if the change originated from the server.
Good idea.
There are no threads.
(I assume this means ther's a single thread) in that case all requests will be managed one after the other. You might have little control on the order of execution but you can be sure there's no chance of 2 things happening at the same time
@VRonin Yes, the program is single-threaded, as well as the handling of client connections. So if I can be sure that each write attempt will be mad one after another, I can simply check if I can do it before I do it. Either, it works and can be done and distributed, or it I can't do it, because it has been done remotely in the very moment the user clicked the "Save" button. This actually should be a rare event, but I definitely have to handle that case.
Thanks for the clarification :-)
-
@VRonin Yes, the program is single-threaded, as well as the handling of client connections. So if I can be sure that each write attempt will be mad one after another, I can simply check if I can do it before I do it. Either, it works and can be done and distributed, or it I can't do it, because it has been done remotely in the very moment the user clicked the "Save" button. This actually should be a rare event, but I definitely have to handle that case.
Thanks for the clarification :-)
Threading is a red herring here, you can't run
QSqlDatabasefrom different threads anyway. Your problem is how you can provide atomicity of the operation, correct? Well as it happens I'm working on something remotely similar (at lest the technical infra behind it). So I have decided how I would handle that: tokens.An object on the server (doesn't matter if there's a database behind, but let's roll with a db) is assigned a token - that is an identifier of the current state of the object (think of object in this context as a record in the db). So when the client (application) wants to edit something it requests the token for that object from the server, and issues it back alongside with its changes when ready. Now, when the object's updated successfully it's token is re-generated. The idea is that if it so happens that two clients are trying to edit some object on the server, only one of them will succeed, while the server will "know" the other's update request is based on stale data and return back an error message.
Read and abide by the Qt Code of Conduct
-
@l3u_ said in QSqlDatabase using SQlite and timing of concurrent write attempts:
I thought about not actually changing anything, but only to send a change request to the server. If everything is fine, the server applies the change and broadcasts it, as it would be done if the change originated from the server.
Good idea.
There are no threads.
(I assume this means ther's a single thread) in that case all requests will be managed one after the other. You might have little control on the order of execution but you can be sure there's no chance of 2 things happening at the same time
@VRonin said in QSqlDatabase using SQlite and timing of concurrent write attempts:
(I assume this means ther's a single thread) in that case all requests will be managed one after the other. You might have little control on the order of execution but you can be sure there's no chance of 2 things happening at the same time
That's somewhat misleading, because the network is asynchronous and (quite importantly) has latency. Not everything that is issued from the client happens instantaneously on the server (and vice versa).
-
Threading is a red herring here, you can't run
QSqlDatabasefrom different threads anyway. Your problem is how you can provide atomicity of the operation, correct? Well as it happens I'm working on something remotely similar (at lest the technical infra behind it). So I have decided how I would handle that: tokens.An object on the server (doesn't matter if there's a database behind, but let's roll with a db) is assigned a token - that is an identifier of the current state of the object (think of object in this context as a record in the db). So when the client (application) wants to edit something it requests the token for that object from the server, and issues it back alongside with its changes when ready. Now, when the object's updated successfully it's token is re-generated. The idea is that if it so happens that two clients are trying to edit some object on the server, only one of them will succeed, while the server will "know" the other's update request is based on stale data and return back an error message.
@kshegunov It's actually about atomic operations. But I think if all write attempts happen one after another, I think I'm safe by always checking if the requested change is possible, am I?
Example:
We have a name entry "foo". The server wants to change it to "bar". The client wants to change it to "whatever". And these two requests happen at the very same time. Now, there are two possibilities:
The server request happens first. I check if we have a "foo" name entry. It's there, and I change it to "bar". Then, the client request is processed. I check if there is a "foo" entry, but there is none – so I tell the client that the change is no more possible and do nothing.
Same for the other order: If the client request is processed first, "foo" is renamed to "whatever", and the server request will fail, because there's no more "foo" name.
Or am I wrong here?!
-
@kshegunov It's actually about atomic operations. But I think if all write attempts happen one after another, I think I'm safe by always checking if the requested change is possible, am I?
Example:
We have a name entry "foo". The server wants to change it to "bar". The client wants to change it to "whatever". And these two requests happen at the very same time. Now, there are two possibilities:
The server request happens first. I check if we have a "foo" name entry. It's there, and I change it to "bar". Then, the client request is processed. I check if there is a "foo" entry, but there is none – so I tell the client that the change is no more possible and do nothing.
Same for the other order: If the client request is processed first, "foo" is renamed to "whatever", and the server request will fail, because there's no more "foo" name.
Or am I wrong here?!
@l3u_ said in QSqlDatabase using SQlite and timing of concurrent write attempts:
Or am I wrong here?!
No, you are correct, and this is indeed the way to do it. I was just trying to provide additional "insight", rather clumsily. In my program the data is entered by people (users), so for me it's not enough simply to tell the user "Sorry dude, somebody beat you to it". I'd have to provide some means for the user to reload the whole piece of data, which can consist of multiple fields. As I'm sending the messages as a whole I'd want to check if any of the fields (i.e. any part of the DB record) got changed in the mean time, not only one single string, or an integer.
Read and abide by the Qt Code of Conduct
-
@l3u_ said in QSqlDatabase using SQlite and timing of concurrent write attempts:
Or am I wrong here?!
No, you are correct, and this is indeed the way to do it. I was just trying to provide additional "insight", rather clumsily. In my program the data is entered by people (users), so for me it's not enough simply to tell the user "Sorry dude, somebody beat you to it". I'd have to provide some means for the user to reload the whole piece of data, which can consist of multiple fields. As I'm sending the messages as a whole I'd want to check if any of the fields (i.e. any part of the DB record) got changed in the mean time, not only one single string, or an integer.
@kshegunov Okay, nice! Luckily, in my case, I actually can say "bad luck, dude, somebody already changed it" ;-)
-
Hi,
Out of curiosity, why not use a database system that already does that kind of things ? Like a PostgreSQL cluster
@kshegunov IIRC, you can use QSqlDatabase in a multi-threaded setup however, each thread must have its own connection to the database.
Interested in AI ? www.idiap.ch
Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct -
Hi,
Out of curiosity, why not use a database system that already does that kind of things ? Like a PostgreSQL cluster
@kshegunov IIRC, you can use QSqlDatabase in a multi-threaded setup however, each thread must have its own connection to the database.
@SGaist said in QSqlDatabase using SQlite and timing of concurrent write attempts:
@kshegunov IIRC, you can use QSqlDatabase in a multi-threaded setup however, each thread must have its own connection to the database.
Yes, this is exactly what I meant. By the way from, the point of view of the database two connections might as well be 2 separate clients.
-
Hi,
Out of curiosity, why not use a database system that already does that kind of things ? Like a PostgreSQL cluster
@kshegunov IIRC, you can use QSqlDatabase in a multi-threaded setup however, each thread must have its own connection to the database.
@SGaist It's a special case ;-)
It's about a program to manage tournaments for a card game. For a normal tournament, it's completely sufficient to have one notebook to manage the registration. For a big tournament, it's nice to have two laptops to registrate people so that the queue won't grow too long.
What I'm doing is a simple network connection so that the registration computers can talk to each other and everybody has the current list of participants.
It's important that the program stays as simple to use as it is at the moment. With SQLite as the backend, it just works out of the box. Nobody using it would want to or even be able to setup a database server before using it.
