Re: Views in one database that lock tables in anouther 2
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/10/03
- Next message: JimT: "How to create a trusted connection"
- Previous message: Ann: "right direction"
- In reply to: Gavin Kerr: "Re: Views in one database that lock tables in anouther 2"
- Next in thread: Ray Higdon: "Views in one database that lock tables in anouther 2"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 10 Jul 2003 11:05:50 -0400
See my response in-line
> firstly the reason I have made the views in a new database
> is because I am not permitted to do anything but read in
> the 'payrollDB' database (and this will never change),
You can still have the views in the one db and just have select permissions
on them.
> From the offset I have attempted to sell the idea of
> reporting from a snapshot, but I am having allot of
> trouble convincing my users(they will not budge, and the
> customer is always right) because they need to use the
> information in the reports to amend information in the
> system, and they want to run the same report show the
> results of there actions without having to wait for the
> snapshot to re-load...
If this is true I would not recommend using NOLOCK as they will invariablty
get incorrect results at some point in time.
I am using Cognos impromptu to run
> the reports (from the views) so I am considering creating
> the 2 catalogs one reading from the snapshot tables
> (identical in structure and fill by the views) and one off
> the live data via the views them selves (report written in
> one catalog can seamlessly be run in the other)... I will
> also do either:
By snap shot I was talking about using the replication snapshot feature.
You would be sort of re-inventing the wheel.
> --- make sure there are procedures in place to prevent any
> locks caused by the views from upsetting any internal
> operational system processes (i.e. don't run reports in
> the live catalog when you so the pay run)....
Again, if your just reading then these should not be that much of an issue.
If anything the internal processes should block the views and not hte other
way around. You need to find out exactly what is causing the locking and
why before you can come to a reasonable solution.
> ---or maybe using the FOR BROWSE statement within each
> view..... Could this solve my problem and prevent any
> locking? Is this expensive (regarding space) in the tempDB
> are there any other pitfalls in using this?
This should not be considered for this issue.
> --- Or (have I got this right?) even if the view are made
> and queried with read committed isolation level, any locks
> are removed when the transaction is committed or rolled
> back. Perhaps I should look to minimise the time taken
> inside each transaction.. How could I do this? Timeouts?
> And, could I be sure that even if a payroll process is
> blocked waiting for my reporting system to release locks
> it will carry on when the transaction (of my reporting
> system) releases them
Why is the select of the view in a transaction in the first place if this is
a reporting feature? Normal selects will issue row level shared locks as
they read the data and will release each rows lock when done reading it.
Inless you have this is a transaction or a isolation level that is
preventing the release of the locks. Again, make sure the views are causing
the issues and if so then why?
>
> I take your point with the use of read uncommitted they
> don't call them dirty reads for nothing....
>
> Last point you referred to the fact that it didn't seem
> likely that it was the reports that where locking the pay
> run process. what if I said that as these were the only
> commands impromptu had submitted(I did a trace):
>
> 1. SET TRANSACTION ISOLATION LEVEL READ COMMITTED
> 2. sp_colums (for some reason)
> 3 declare @P1 int
> set @P1=NULL
> exec sp_prepare @P1 output, NULL, N'SELECT SOMETHING FROM
> A_VIEW ', 1
> select @P1.
>
>
> No mention of transactions!!!!... If the pay run process
> was running immediately after this query had run (i.e.
> strait after these commands had been issued) and contested
> for the same row (page or table, I don't know which) would
> it have been made to wait?
>
> I know I can change the behaviour of Impromptu to issue
> set implicit_transactions when it starts and it issues a
> commit after the query has run. will this help?
OK, maybe I should have read the whole thing first but I still don't see
too mcuh aof an issue here other than the fact you are issuing adhoc sql
calls. Why not use sp's? I would have to see the actual trace for all of
what is happening to get a better feel. You say that the reports are
causing issues with the regular process. What exactly are the issues? Is
it blocking? Is it high disk queues? Is it consumming all the cpu? You
have to narrow down the issues to ensure your looking in the right
direction.
>
> Cheers,
>
> Gavin Kerr
>
>
>
>
> >-----Original Message-----
> >What is the purpose of creating a whole new db when the
> only thing in it are
> >views that point to another db? I think your making
> things more complicated
> >than they need be. As for the locking issue you need to
> find out for sure
> >who is blocking who. Don't just assume it is one of the
> reporting views
> >that is doing it, that may lead you down the completely
> wrong road. If the
> >reporting views are just reading then they should not
> permanently block the
> >other users. I would be very careful about using nolock
> or read uncommitted
> >unless you know for sure that wrong results won't affect
> someone's report in
> >a negative way. If I was working for a company that used
> uncommitted reads
> >on my parole I would be pretty upset<g>. If they don't
> care about reading
> >current data then maybe you should think about using
> wither replication or
> >just a snapshot for your reporting server. But the
> bottom line is you need
> >to investigate who is blocking and why before you can do
> anything about it.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Gavin Kerr" <gavin.kerr@alfred-mcalpine.com> wrote in
> message
> >news:03df01c34642$61d6f890$a101280a@phx.gbl...
> >> Dear All,
> >>
> >> I was hoping that some one might help me...
> >>
> >> I will give some back ground
> >> . We have an SQL7 server running our payroll database
> >> . All tables for this database reside in one
> >> database called 'payrollDB'
> >> . To Solve some reporting issues I have create a
> >> second data base residing on the same server
> >> call 'ReportingViewsDB '
> >> . The ReportingViewsDB contains a series of views
> >> (no tables) pointing to the payrollDB tables or other
> >> views within the ReportingViewsDB.
> >> . My problem is that during an important process
> >> (the pay run) it appears that records in important
> tables
> >> in the 'payrollDB' database had been locked!!!!.. Almost
> >> certainly by a process that was using the views in
> >> the 'ReportingViewsDB' database at the time (e.g. I had
> a
> >> report using a view open). the pay run is a critical
> >> process running on a critical database.. I do not want
> my
> >> reporting solution/views to lock any record at any
> time!!!
> >> I understand that there are several ways to tackle the
> >> problem
> >> . Use the FOR BROWSE statement within the SELECT
> >> statement of the views pointing to the operational data
> >> in 'payrollDB'
> >> . Setting the transaction isolation level to 'Read
> >> Uncommitted'
> >> . Use the locking hints e.g. 'with(nolock)' within
> >> the SELECT statements of the views that look at the
> >> operational (ReportingViewsDB) database tables..
> >>
> >> My questions are
> >> . Within the ReportingViewsDB I have tried
> >> using 'with(nolock)' in every CREATE VIEW/SELECT that
> >> accesses a table from the 'payrollDB' database. will
> this
> >> guarantee that no other process will ever be locked
> whilst
> >> attempting to update any field in any table in any
> >> situation within the 'payrollDB'??
> >> . Are there pit falls with this method.(other than
> >> the risk of Dirty/ Non-Repeatable/ Phantom reads)..Have
> I
> >> chosen the best?
> >> . If I SET TRANSACTION LEVEL READ UNCOMMITED within
> >> the session on the 'ReportingViewsDB', will that prevent
> >> the views from doing any locking..
> >> . Will SET TRANSACTION LEVEL READ UNCOMMITED during
> >> the session in which I create the views matter?
> >> . Is there another better alternative?
> >>
> >> The reason I posted this in security too (it is also in
> >> the server newsgroup), is mabey the is a way to solve
> this
> >> with user permissions?
> >>
> >>
> >> Thanking you in advance,
> >>
> >> Gavin Kerr
> >> Enterprise Reporting Developer
> >> SNMS
> >> Alfred McAlpine UK
> >>
> >
> >
> >.
> >
- Next message: JimT: "How to create a trusted connection"
- Previous message: Ann: "right direction"
- In reply to: Gavin Kerr: "Re: Views in one database that lock tables in anouther 2"
- Next in thread: Ray Higdon: "Views in one database that lock tables in anouther 2"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
