SQLIte writers queue

[Xin Li]

I would like multiple process to write to SQLite database at the same time through web service. How do I insure all the write queries queue up and all of them get executed? 

In the web service VI, I am now opening the database and set a long enough busy timeout and then pass the query from web service calls to SQLite EXEC SQL. But my test showed some queries are not executed. 

So SQLite is not automatically queuing the writing queries? What is the proper way to ensure all queries queue up and get executed?

 

Edited August 3, 2020 by Xin Li

[JKSH]

Let your application log all queries to a file. Be sure to log the execution results and errors too. Are any queries missing? Do you see any errors?

[drjdpowell]

Your webservice could also respond with the result of the SQL execution, including any error that might happen.  Your problem is either in your webservice or your SQL, not in SQLite losing writes.

[Lipko]

I'm not sure I understand the problem correctly, but I had a seemingly similar situation.

The clients send two request types: "write xyz" and "status?"

The server side has two layers:

• a thin webservice:
  • for "write" requests: queues the requests (write queue) with client ip and immediately responses with "acknowledged"
  • for "status?" requests: immediately responses with the last message for that client from the reply buffer, or empty string if no replies for the client are available
• the main server program:
  1. sequentially write the elements to the database from the write queue (making sure that writing was finished for each element*)
  2. writes status (success/error/whatever with the ip of the client who initiated the write) to the reply buffer

(in the exe, these are built in one application so the reply and write buffers are simple functional globals)

Client side:

1. send write request (and wait for acknowledge)
2. keep sending "status?" request (and wait for acknowledge) until timeout or appropriate answer (for an aggressive client, appropriate may be only "success". For a fully automated program, this is required, for a user interaction based program, "error" can be an acceptable answer)

 

This may be a little over-complicated (and I hope I remember well), but this was the only way for my application to work reliably in a not-so-fast-and-stable network.
*in my case, it is a complicated matter by itself because writing means sending messages through com ports to climate chambers with VISA, and this communication is asynchronous and takes unpredictable time for the chamber to complete. So in one write, this means sending command to the machine then requesting command state of the machine and repeating this until the command state is the same as the requested state (or timeout).

Maybe in your case some things are handled automatically by SQL...

Edited August 4, 2020 by Lipko

[Xin Li]

11 hours ago, drjdpowell said:

Your webservice could also respond with the result of the SQL execution, including any error that might happen.  Your problem is either in your webservice or your SQL, not in SQLite losing writes.

Thanks. I followed https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z0000019QK9SAM&l=en-US and see no errors of web service.

Because I want to handle simultaneous web calls, I am making the web service vi reentrant. Not sure how to log all the queries when it is reentrant. Could you give me a hint how?

[Xin Li]

7 hours ago, Lipko said:

I'm not sure I understand the problem correctly, but I had a seemingly similar situation.

The clients send two request types: "write xyz" and "status?"

The server side has two layers:

• a thin webservice:
  • for "write" requests: queues the requests (write queue) with client ip and immediately responses with "acknowledged"
  • for "status?" requests: immediately responses with the last message for that client from the reply buffer, or empty string if no replies for the client are available
• the main server program:
  1. sequentially write the elements to the database from the write queue (making sure that writing was finished for each element*)
  2. writes status (success/error/whatever with the ip of the client who initiated the write) to the reply buffer

(in the exe, these are built in one application so the reply and write buffers are simple functional globals)

Client side:

1. send write request (and wait for acknowledge)
2. keep sending "status?" request (and wait for acknowledge) until timeout or appropriate answer (for an aggressive client, appropriate may be only "success". For a fully automated program, this is required, for a user interaction based program, "error" can be an acceptable answer)

 

This may be a little over-complicated (and I hope I remember well), but this was the only way for my application to work reliably in a not-so-fast-and-stable network.
*in my case, it is a complicated matter by itself because writing means sending messages through com ports to climate chambers with VISA, and this communication is asynchronous and takes unpredictable time for the chamber to complete. So in one write, this means sending command to the machine then requesting command state of the machine and repeating this until the command state is the same as the requested state (or timeout).

Maybe in your case some things are handled automatically by SQL...

Thanks for sharing your experience. Our situations are similar. We have concurrent writes and reads. I would like all reads to be executed immediately and simultaneously and all writes line up. The writes don't have to be executed immediately but I want to make sure they are all executed properly. So it will be nice if I can forward all web service calls to SQLite and use SQlite to handle concurrency and queuing automatically. If I cannot make this work, I will try what you suggested to handle the queue and priority myself.

Could you share more how you do it? If you could share some of your code, it will be great. Not sure how to make sure the read results are returned to the right client. Thanks!

[drjdpowell]

Try this: make a deliberate error in your SQL, such as spelling INSERT "INPERT", and see if the resulting error shows up in your web service error reporting thing.  If it doesn't, then you are not seeing the errors produced by your "Exec SQL" call.

[Xin Li]

1 hour ago, drjdpowell said:

Try this: make a deliberate error in your SQL, such as spelling INSERT "INPERT", and see if the resulting error shows up in your web service error reporting thing.  If it doesn't, then you are not seeing the errors produced by your "Exec SQL" call.

Thanks. When my web service is not reentrant, I can see error produced from "EXEC SQL" call. However, when web service vi is reentrant, I don't see error.

[JKSH]

On 8/5/2020 at 3:59 AM, Xin Li said:

Because I want to handle simultaneous web calls, I am making the web service vi reentrant. Not sure how to log all the queries when it is reentrant. Could you give me a hint how?

Write extra code inside your web service VIs. The code should take all the query details (query type and parameters) and take all the SQL VI outputs (results, error clusters) and log them to a text file on disk.

However, multiple VIs won't be able to open the log file simultaneously, so you should create a dedicated "consumer loop" in your application. Your web service VIs can pass the log data to the consumer loop via a queue.

 

On 8/5/2020 at 4:22 AM, Xin Li said:

it will be nice if I can forward all web service calls to SQLite and use SQlite to handle concurrency and queuing automatically.

Although text files don't support concurrent writes, SQLite does. However, it is not bulletproof and it's possible for it to fail: https://www.sqlite.org/lockingv3.html That's why we recommend that you add logging code so that you can see what's going on.

[EDIT: SQLite does not support concurrent writes]

 

On 8/5/2020 at 6:32 AM, Xin Li said:

When my web service is not reentrant, I can see error produced from "EXEC SQL" call. However, when web service vi is reentrant, I don't see error.

How do you "see" the error? You can't easily use Probes in reentrant VIs. That's why a log file is recommended.

Edited August 9, 2020 by JKSH
Wrong info

[Lipko]

Some other constrains of my use case, so we can see if it's really similar to yours:

• a client always waits for a proper reply (be it success or error) before issuing other commands to the server
• a few dozens of clients at a time
• stable communication is much more important that speed, so it's accepteable for the client to wait even for fours for a "success" reply (in case the server restarts due to an update and the IT's asdfg...), but usually reply comes in a few seconds.
• writing is even rarer, since a client has to reserve the machine first (reservation is handled by the server), so commands to a not reserved machine will result in "not reserved" response, and will not be forwarded to the machine (no write)
• very small requests and replies. Such as "ip loginname Thermotron set -50" and "set -50 act -49.5 run"

I did not make the web service reentrant, that's why I made it "thin" as possible: it only does the two things that I wrote earlier. 

I don't remember why, I have to dig up the code, but the IP of the clients were sent by the clients themselves appended to the request (more precisely in a cluster that was converted to request text). Similarly the ip is appended to the replies in the "reply buffer".

What code would you see specifically?

Edited August 5, 2020 by Lipko

[Xin Li]

On 8/4/2020 at 7:57 PM, JKSH said:

 

Thanks, JKSH. I am trying to write the web service request to a queue, but having issues of SSE2 error. Disabling SSE2 does not resolve it. I am still trying to solve the issue. I will update once I succeed.

Edited August 7, 2020 by Xin Li

[Xin Li]

On 8/5/2020 at 1:16 AM, Lipko said:

Some other constrains of my use case, so we can see if it's really similar to yours:

• a client always waits for a proper reply (be it success or error) before issuing other commands to the server
• a few dozens of clients at a time
• stable communication is much more important that speed, so it's accepteable for the client to wait even for fours for a "success" reply (in case the server restarts due to an update and the IT's asdfg...), but usually reply comes in a few seconds.
• writing is even rarer, since a client has to reserve the machine first (reservation is handled by the server), so commands to a not reserved machine will result in "not reserved" response, and will not be forwarded to the machine (no write)
• very small requests and replies. Such as "ip loginname Thermotron set -50" and "set -50 act -49.5 run"

I did not make the web service reentrant, that's why I made it "thin" as possible: it only does the two things that I wrote earlier. 

I don't remember why, I have to dig up the code, but the IP of the clients were sent by the clients themselves appended to the request (more precisely in a cluster that was converted to request text). Similarly the ip is appended to the replies in the "reply buffer".

What code would you see specifically?

Hi Lipko, I think our task priority is not exactly the same but the way how you handle the web service through queues could be helpful to me.

I feel the labview web service modules' help manual not very helpful and examples lacking. I think the web service will need to deal with hand-shaking between client and server automatically. But if I am handling the request through my own queues, I am not sure how to implement in labview and make sure the request data is send to the right client. 

I am still very new to Labview, so if you could give me some hint in more details how I can implement this or share this part of the code, it could be very helpful.

[Lipko]

50 minutes ago, Xin Li said:

Hi Lipko, I think our task priority is not exactly the same but the way how you handle the web service through queues could be helpful to me.

I feel the labview web service modules' help manual not very helpful and examples lacking. I think the web service will need to deal with hand-shaking between client and server automatically. But if I am handling the request through my own queues, I am not sure how to implement in labview and make sure the request data is send to the right client. 

I am still very new to Labview, so if you could give me some hint in more details how I can implement this or share this part of the code, it could be very helpful.

Note to codes: I'm am only a hobbyist tinker, and I don't know how nice LV code is written. And I hope I posted every relevant things. You don't have to care much about the "alive_clients" thing. It's there to make sure the reply buffer doesn't overflow if a client gets lost before the server' reply gets forwarded to the client by the webservice.

The server program that reads the request queue and writes the reply buffer is too complicated to be useful.

And to point out again: The client ip is sent by the client itself. Then you have to keep track of the ips in the server program logic.

Chamber_comm_main_webservice.vi

chamber_request_queue_FGV.vi

chamber_reply_queues_FGV.vi

chamber_server_reply_type.ctl

reply_buffer.ctl

chamber_webservice_msg_type.ctl

[ShaunR]

On 8/5/2020 at 12:57 AM, JKSH said:

Although text files don't support concurrent writes, SQLite does.

SQLite does not support concurrent writes.

Quote

Only one process at a time can hold a RESERVED lock. But other processes can continue to read the database while the RESERVED lock is held.

If the process that wants to write is unable to obtain a RESERVED lock, it must mean that another process already has a RESERVED lock. In that case, the write attempt fails and returns SQLITE_BUSY.

 

[JKSH]

On 8/8/2020 at 8:24 AM, ShaunR said:

SQLite does not support concurrent writes.

Ah, you're right. Thanks for correcting me.

That explains the why @Xin Li found that "some queries are not executed": If 2 web service VIs try to execute a write query at the same time, one of them will fail.with an SQLITE_BUSY error and the data is lost.

[drjdpowell]

OP said they set a long busy timeout, so the write queries will just block and execute in serial order. 

[Xin Li]

On 8/7/2020 at 4:27 PM, Lipko said:

Note to codes: I'm am only a hobbyist tinker, and I don't know how nice LV code is written. And I hope I posted every relevant things. You don't have to care much about the "alive_clients" thing. It's there to make sure the reply buffer doesn't overflow if a client gets lost before the server' reply gets forwarded to the client by the webservice.

The server program that reads the request queue and writes the reply buffer is too complicated to be useful.

And to point out again: The client ip is sent by the client itself. Then you have to keep track of the ips in the server program logic.

Chamber_comm_main_webservice.vi 30.68 kB · 0 downloads

chamber_request_queue_FGV.vi 43.59 kB · 0 downloads

chamber_reply_queues_FGV.vi 34.57 kB · 0 downloads

chamber_server_reply_type.ctl 4.82 kB · 0 downloads

reply_buffer.ctl 5.95 kB · 0 downloads

chamber_webservice_msg_type.ctl 18.04 kB · 0 downloads

Thanks so much for sharing, I will look at these next week!

[Xin Li]

A update for my progress. I have been able to write the calls the web service received to a queue and found out the web service is able to receive all the calls. The reason for SQLite to fail to write is because I used a time stamp as input for a variable that need to be unique, but with the simultaneous calls, that time stamp is the same.

Thanks for everyone's help here! 

[drjdpowell]

That should have produced an error.  Did you solve your problem of not seeing errors?