I want to use opportunistic on a case by case basis. I do not want to make it a server wide or database wide default until I have an opportunity to experiment with it slowly.
I know that it can be done on a case by case basis because SQL Server Management Studio uses it on a single query when connected to my target database on my target server but I do not know how to specify that I want to use it. Lots of google searches over several months and lots of reading Microsoft Knowledge Base entries has not been fruitful.
I have learned how to turn it on as the standard (default?) for an entire server and in fact have done that on a development server but have not learned how to do it on a query by query basis.
Is there a difference in how you would request it on a select query vs how you would specify it on an update statement? I do not think that I need it on the update but I do need it on the select. I ask just to gain a more comprehensive understanding.
I googled many variations of "Specify SQL Server optimistic locking on a single query" changing the order of the terms and dropping some to see if it makes a difference.
Google, trying to be helpful conflated optimistic locking, optimized locking, opportunistic locking and brought in many cases that had nothing to do with SQL Server such as file locking. I could find nothing on point.
I was hoping to find a KB article containing set options or lock hints to accomplish what I want to do. I am faced with lockouts when a very simple update of a single column in a single row using the primary (clustered, integer key) is deadlocking against a full table scan that can not be indexed. For example lets say we want to update the customer balance of one customer (who has a valid zip code) using the customer number (int) as key and it runs against a query where we are checking all customers where the country is USA and the zip code is not numeric. The example is contrived, the point is I can not or do not want to index zip code to speed up the query. My belief is that with optimistic locking this scenario would not deadlock.
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }
will change the isolation level for the duration of the connection or until changed by a subsequent set command. In my case, lowering the isolation level to snapshot using this method achieved the desired result.
This is documented https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16