How to avoid a select query for holding a Sch-S lock
5
votes
3
answers
14602
views
I am looking for an option to avoid Sch-S locks when running a Select query.
I have a database which is controlled by an application written by others as well as my own application. One of the tables has millions of rows. I don't have any issue with dirty reads etc. but I don't my select query to lock the other application indexing or modification queries that have to wait due to Sch-S locks from my query.
I tried to set isolation level snapshot before calling my query but that didn't make any difference either WITH NOLOCK option that still acquires an Sch-S lock.
I am looking for an option to run a select on a table without acquiring Sch-S or any kind of lock, maybe if we can quickly have snapshot/view or temp copy (will it work as it has millions of rows of data?) of the table without acquiring any kind of lock at all.
Asked by AKS
(151 rep)
May 22, 2018, 05:56 AM
Last activity: Jan 31, 2019, 06:44 PM
Last activity: Jan 31, 2019, 06:44 PM