0
I have a Stored Procedure (SQL Server) that is accessed by several applications concurrently at intervals of less than 1 minute. It was built using NOLOCK in its accesses, most likely to gain in speed. However, the reading of data not persisted is causing problems in the logical processing of the application and it will be necessary to change this Stored Procedure.
My doubt is in relation to the behavior of the numerous applications that use this Stored Procedure, when this is changed. Will there be performance drop? If the access is done while a data is still being persisted the application will be queued, wasting time I/O, until the data is persisted?
Of course, the
NOLOCKrightly serves to avoid the need to wait for persistent data.– Sorack
Yes, requests will be queued and each request will only be released when the table(s) involved(s) is((s) released(s). You will have to evaluate if this waiting time could compromise your application.
– anonimo
Okay. In this scenario, does one SELECT compete with another? I mean, while proc is executing an application request, a second application is queued waiting for the end of the first?
– Tiago
See: https://docs.microsoft.com/pt-br/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017
– anonimo