[SGVLUG] Probably more "dev" related, but a question on databases...

[Joel Witherspoon]

On Fri, Jan 8, 2010 at 11:52 AM, Emerson, Tom (*IC) <
Tom.Emerson at wbconsultant.com> wrote:

> I've come across something I think is a bit odd for a database - every
> table has a corresponding "view" that (I presume) is little more than
> "select * from table" - end user (reporting) apps only have access to the
> "views" and not the underlying tables.  The reason I've been given for this
> is that it "avoids locking issues".
>
> >From this, I also presume that the "views" are "read only", which I would
> agree would avoid locking the underlying table, but doesn't a "view" imply
> extra processing and/or storage to support the view?  [i.e., a "temporary"
> table built and populated with a copy of the data]
>
> This seems to be a false-economy to me - but then, I "grew up" with a
> different sort of database than the current crop of RDBMS's, one in which
> "locking" wasn't a big scary issue.  (it is still a big issue, just not
> scary...)  After all, "end user apps" can be written such that any "query"
> is a "read only" query, avoiding the need to generate a temporary table and
> populate it with a copy of the data [in particular, you would need/use
> "non-repeatable" reads]
>
> For those of you who administer modern databases, does this make sense?  Is
> it true that a "view" that mirrors the structure of an existing table
> completely avoids "locking" issues with the tables, or are you robbing peter
> to pay paul?  [using more disk or processor time to present the same data]
>

Yes on one hand, maybe on the other hand. Guess which hand? (Joking)

A view is a recordset of data that exists as a virtual table in the
database. They are primarily used for rapid querying, data protection, or
obfuscation. Views are also dummy objects, they only have the same security
as the underlying table they query. If your users don't have the permissions
to access certain columns and rows in the table, they won't have access to
the columns and rows through the view. So, yes, views can be used to
alleviate locks because they -generally- aren't used to edit data.

As far as locks and cursors, you can use views to edit data but you don't
want to do that if you can avoid it. There are simply too many things to go
wrong. If you see a view that starts with the word "UPDATE" in it's code,
that's a view that can be modified. Caveats abound in using views this way.

-- 
Joel Witherspoon

LinkedIn: http://www.linkedin.com/in/jcwitherspoon

Facebook: http://www.facebook.com/joel.witherspoon

Skype: joel.witherspoon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.sgvlug.net/pipermail/sgvlug/attachments/20100108/e3654c06/attachment.html