April 22, 2011 at 7:29 am
Main Database and Database_Reporting.
All reports views pull data from Database_Reporting and come from Access/Crystal Reports. From time to time causes blocks on inserts/updates coming from ERP system.
If the database itself is set to Read Uncommitted will this not cause a shared lock on the record ?
I looking at a way to read the data with out it putting in a Shared Lock to prevent the updates. Tried NO_LOCK in statements. Rather than convert all views to SP and add read uncommitted as I cannot put this in the views , I was wondering if the database settings would force this regradless if pulling data from another database.
Thanks
April 22, 2011 at 9:36 am
While a read is occuring, you can't update the data underneath. Read uncommitted allows you to read before the write has been committed (while it's still in cache), which is not the same. Unless these reports are reading just millions of rows though, they should be clear & gone. If they're reading millions of rows, I'd concentrate on rewriting them or working on creating some pre-aggregations of the data or something along those lines.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply