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?
which means data loss to me in a real-time scenario.I dont understand what you mean by data loss ?