0

I’ve a report which sits on top of a view. The view underlying tables are updated every 15 minutes and the update cycle takes approximately 1 -2 minutes and during this time if I run my report I’m getting wrong values on my report .Is there a way that I can apply some kind of locks on the view so that I can get the report once the update is done and avoid dirty data on my report.Please let me know if there are any other solution for this issue

Thanks, Ravi

Ravi
  • 2,470
  • 3
  • 26
  • 32

3 Answers3

1

I would consider using a different method to update the underlying tables. Instead of updating these tables for 1-2 minutes, make "shadow" tables in another schema. (And have a third schema for temporary holding.) This allows you to work on tables the users can't see, then switch them in using simply a metadata operation. Then you can do this:

  1. truncate/re-populate the shadow tables (2 minutes, or maybe less with no contention)
  2. start a transaction (sub-millisecond)
  3. move the primary table to the holding schema, using ALTER SCHEMA ... TRANSFER (sub-millisecond)
  4. move the shadow table to the dbo schema (sub-millisecond)
  5. move the primary table to the shadow schema (sub-millisecond)
  6. commit the transaction (sub-millisecond)
  7. (optional) truncate the shadow table to recover some space (sub-second)

One downside of this solution is that you will have two sets of stats, indexes etc. to maintain. For the stats you should be ok if the data is simply increasing and not changing substantially otherwise.

Adam Haines has a really thorough write-up about this method (which I showed him a few years ago) here:

http://jahaines.blogspot.com/2009/10/locking-table-while-it-is-being-loaded.html

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Hi Aaron, Thanks for solution but I don't have any kind of rights on the database and to follow this approach .Is there any other approch which can be achieved using "select statements" as I only have read access to the database.Thanks – Ravi Jul 01 '11 at 15:04
1

A similar way to Aaron Bertrand's answer but using SYNONYMs:

In this case, you have 2 synonyms: one for "table to load" and one for "table to query"

The various CREATE statements can be wrapped in a stored procedure that has EXECUTE AS OWNER to escalate permissions.

Based on your comment to Aaron's answer, you have to have some way of switching. The only way to increase concurrency is to use more than one table.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
-1

Did you consider retrieving report data in repeatable read or serialization isolation modes?

UserControl
  • 14,766
  • 20
  • 100
  • 187