• oData request time outs because there is no "WITH (NOLOCK)"

oData request time outs because there is no "WITH (NOLOCK)"

  • 154Views
  • Last Post 11 October 2022
0
votes
Niels Rossel posted this 07 September 2022

I am currently experiencing regular time outs when requesting salesorders from SQL database:

url: /orders?$filter=ordernr+eq+'22304425'&$expand=orderlines

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This happens with orders that are still "in use". Trying to request orders that are in use results in the time outs.
This could be prevented if the oData request generates a SQL query with "WITH (NOLOCK)", but this doesn't seem to be the case.

 

In the documentation I see examples where the queries are generated "WITH (NOLOCK)":
https://docs.skyvia.com/connect/odata-endpoints/supported-odata-protocol-features.html

 

But if check the Endpoint logs I don't see any "WITH (NOLOCK)" in the queries:

url: /orders?$filter=ordernr eq '22305480'

Connecting time: 4 ms

 

Executing: SELECT t.ID, t.ordernr, t.debnr, t.fakdebnr, t.verzdebnr, t.naldebnr, t.einddebnr, t.refer, t.refer1, t.refer2, t.refer3, t.orddat, t.afldat, t.afl_week, t.ordbv_afdr, t.magcode, t.selcode, t.ex_artcode,
...
 FROM dbrkrg AS t WHERE (t.ordernr = '22305480'), time: 2 ms

Why are these not generated with "WITH (NOLOCK)"? How can I enforce this?

sql endpoint
Order By: Standard | Newest | Votes
0
votes
Niels Rossel posted this 07 September 2022

I have been checking the endpoints logs of some of our other customers (they all use the same EXACT GLOBE software so they all have the same SQL tables) and I noticed that there is "WITH (NOLOCK)" in those queries:

url: /orders?$filter=ordernr eq '387038'

Connecting time: 0 ms

 

Executing: SELECT t.ID, t.ordernr, t.debnr, t.fakdebnr, t.verzdebnr, t.naldebnr, t.einddebnr, t.refer, t.refer1, t.refer2, t.refer3, t.orddat, t.afldat, t.afl_week, t.ordbv_afdr, t.magcode, t.selcode, t.ex_artcode,
...
 FROM dbrkrg AS t WITH (NOLOCK) WHERE (t.ordernr = ' 386945'), time: 8 ms

What determines if "WITH (NOLOCK)" is used or not?

0
votes
Nataliia Nikulina posted this 13 September 2022

Hello Niels,

 

Thank you for contacting Skyvia Support team.

The server version defines if "WITH (NOLOCK)" is used or not. For the servers that have a version that is earlier than 2016, such a hint is generated. For the servers that are 2016 and higher, it is not generated as it is deprecated. 

Should you have any further questions, let us know. 

 

Best regards,

Nataliia 

 

0
votes
Niels Rossel posted this 14 September 2022

Thank you for your reply.
This particular customer is using 2017 so that explains indeed why the hint is not generated.

But now my next question, what is the alternative for new servers?

if "WITH (NOLOCK)" is deprecated, there must be something new in place. But since nothing new is generated by your queries, is there is server setting to get the same effect?

So, is there a setting that has to be done in the SQL server to not retrieve LOCKED records?

0
votes
Nataliia Nikulina posted this 15 September 2022

Hello Niels,

Thank you for the update. 

The alternative will be not to use NOLOCK as it is not fully correct. It is necessary to compose those transactions that change data and block the strings to be shorter and also it is possible to increase a command timeout for the connection. 

Please follow a couple of links for your reference:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fff14d03-9efc-4afa-a8d0-c44d0521c077/nolock-removal-for-future-version?forum=transactsql

https://stackoverflow.com/questions/32950619/best-alternative-to-withnolock

Should you have any further questions, let us know.

Regards,

Nataliia

 

 

0
votes
Niels Rossel posted this 23 September 2022

Hi Natallia, I've nee reading up on this and have been doing some tests on the SQL server.

We tested with created a new order and adding some orderlines. While we editing the lines, Skyvia (and the same direct SQL query in the database) were unable to retrieve records. It would just wait till it would time out. This is not the ideal situation.
When we changed the TRANSACTION ISOLATION LEVEL to READ UNCOMMITED, we did get an immediate response back when testing with a direct query in the database.

The orderlines that were being edited gave incomplete data back (as expected) but that is not an issue because we get the full data later again whenever the customer finishes the order.

 

Currently orders stay open for up to 20 minutes before the customer finishes an order and data can be retrieved again.
So we like to have the same functionality we had on the older servers with the WITH (NOLOCK) hint.

I have been checking all our customers and 3 of them using 2016 or higher SQL servers, and all 3 of them have a large number of failed requests due to time outs.

So how can we set Skyvia to use READ UNCOMMITED for newer servers?

1
votes
Serhii Muzyka posted this 26 September 2022

Hi,

Thank you for contacting Skyvia Support.

 

We have additionally investigated your case and found out that (NOLOCK) is actually deprecated for an UPDATE or DELETE statement. Please see more details here: https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver16

We shouldn't been removing it for SELECT and will bring it back after the next update.

 

We will let you know in this topic once it's done.

 

Should you have other questions, don't hesitate to reach out.

 

Best regards, 

Serhii

Technical Support Engineer

  • Supported by
  • Niels Rossel
0
votes
Serhii Muzyka posted this 10 October 2022

Hi,

 

We hope you are well!

 

Kindly be informed that Skyvia app was updated and (NOLOCK) for SELECT is available again.

 

Should you have other questions, don't hesitate to reach out.

 

Best regards, 

Serhii

Technical Support Engineer

1
votes
Niels Rossel posted this 11 October 2022

Thanks for the update. 

I installed the update at the client's server and the "WITH (NOLOCK)" hint has indeed returned in the queries.

Thanks again for the solution

  • Supported by
  • Serhii Muzyka
New Ticket
Categories
Popular Tags
Close