I have a scenario in which I don't want any readers to block writers. I don't want users using table hints or isolation levels to issue shared locks, which might block writers.
Is there a way for SQL Server to force everyone to use snapshot isolation or remove the explicit hints they added?
I have read Can I force a user to use WITH NOLOCK?
It's ok if user just uses RCSI or SI. Thus no S lock. However, user might issue ad-hoc queries with some table hints (e.g. REPEATABLEREAD) or change isolation level, which can block write. For example, if one user does:
SELECT * FROM X WITH (REPEATABLEREAD)
…if table X is big, likely the table will have S lock and writes to that table will be blocked, which means data loss to me in a real-time scenario.
For AlwaysOn Readable Secondary Replicas:
All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.
I want to do similar things, that is to limit the isolation level somehow. Is that possible?
Best Answer
There is no way to do that !
If you enable RCSI using alter database then DML statements against objects stored in that database start generating row versions even when no transaction uses snapshot isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.