Transaction locking in Sql Server
We have an old version of Cognos 7 running on Sql Server 2000 Enterprise.
It is issuing very badly constructed sql commands that are creating many locks which are escalating and blocking the server.
The targeted database is built once a day and then only used for selection.
As the Cognos queries can't be changed (short of upgrading to Cog 10), what can I do to improve this situation?
If I mark the database Read Only will this prevent the locks?
Locking does not happen in read-only databases, so this would (probably) help, assuming that locks are the only cause.
If you can issue a new query at the start of a session you could also change the transaction isolation level to read uncommitted, which would cause selects to ignore locks.