CODEWITHSUNDEEP
sql-server-2005
Ravi
Ravi

Reputation: 2558

SQL Server Concurrency issue

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

Upvotes: 0

Views: 202

Answers (3)

gbn
gbn

Reputation: 432180

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.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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

Upvotes: 1

UserControl
UserControl

Reputation: 15149

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

Upvotes: -1

Related Questions