May 7, 2012 at 11:15 am
I have a process which inserts a view's data into local table, and this local table would be treated as a base data source for all the queries, it's part of my query optimization plan by inserting other database's data into local table via a view.
My questions is, I have a stored procedure to truncate local table data and re-insert view's data again, just because in case other databases have data modifications. The total rows of view may have up to 2M, so the the whole process would sometime take 1 minute to do. But during the process, I still want to grant read permission to select from local table, however, since I have transaction isolation level setup, the update will actually lock the table, which all the select queries need to wait until it's release of exclusive lock.
I tried to backup local table to another table, and create a partitioned view to union these two tables, but still, once the update process starts to truncate original table and insert new values, the partitioned view still needs to wait for read permission until it's done.
The partitioned view is like this:
Create view dbo.vwPartitioned
as
Select * from originaltable
union
Select * from backuptable
I am wondering is there a way that I could still have read permission even it's read uncommitted during update process with view?
Or other ways which I dont know.
Thanks.
May 8, 2012 at 5:50 am
I'm pretty sure a truncate will always result in a TABLOCK until the transaction is committed. Rather than using a partitioned view (even if you somehow got around the table lock, you'd potentially have inconsistent or duplicated data), why not populate a backup table, then use sp_rename to rename both the tables (this takes only a fraction of a second).
Or, have your end users access a view that points to one copy of the table, then alter the view definition to point to the other one after the load.
May 8, 2012 at 8:44 am
HowardW (5/8/2012)
I'm pretty sure a truncate will always result in a TABLOCK until the transaction is committed. Rather than using a partitioned view (even if you somehow got around the table lock, you'd potentially have inconsistent or duplicated data), why not populate a backup table, then use sp_rename to rename both the tables (this takes only a fraction of a second).Or, have your end users access a view that points to one copy of the table, then alter the view definition to point to the other one after the load.
Hi HowardW, I did already use sp_rename with a backup table for the solution, and didn't use partitioned view at all, so it's right about your suggestion!!Thanks a lot! as you said, it's only take a second, it's a great idea.
I do also need to use delete from instead of truncate table, since delete from will not have loc_s, and it's shared.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply