Query against a view vs. query against the underlying base table

Question

I was told to query against views in our ERP system because this would prevent any locks put on the actual tables thus blocking actual work being done. After learning a bit more about SQL Server, I'm not sure this is accurate but wanted to get clarification.

Knowing that a view is just a “saved query”, it appears I'm querying the base table via the view so it would seem this wouldn't accomplish anything as far as preventing blocking.

In other words if I do the following and leave these open, am I understanding that either one would block sales table writes?

begin trans
select * from dbo.view_sales

versus

begin trans
select * from dbo.sales

Thanks!

Answer 1

It depends on the construction of the view. SELECTing from a view with NOLOCK or READUNCOMMITTED table hints should avoid blocking the underlying tables (with the risks that entails).

Indexed views (SQL Server term - more generally called materialized views) actually are stored on disk separately from their underlying tables, and are updated as the underlying tables are updated. This not only consumes more disk space, but can lead to contentions and delays if the underlying tables are frequently modified. SELECTing from the view can still block updates to the tables underneath, since when those tables are updated, the index for the view is also updated.

And, as you thought, SELECTing from a normal view can temporarily block queries that are changing the underlying tables, just like SELECTing from the tbles directly can.

Answer 2

It depends on the code inside of dbo.view_sales. If it is querying the dbo.sales table directly, then yes, there could be blocking. However, if dbo.view_sales is actually an indexed view (or, "materialized view" to the Oracle folks) then there shouldn't be any blocking on the dbo.sales table.

An indexed view will have its data written to disk, separate from the table(s) in the query that the view calls. So, you can think of an indexed view as its own table that is automatically updated whenever the data in the table(s) it calls is updated. Because of this, when an indexed view is called it will be querying its own data set and not touching the dbo.sales table, so there will not be any blocking against dbo.sales.

Answer 3

You are correct. A view does not hold data but simply references data from other database objects. Locks would still need to be placed on the underlying table data.

Answer 4

Depending on transaction level, those queries will block writes. Serializable & Repetable Read transaction isolation level will hold S locks (for the whole duration of the transaction), which are incompatible with X locks that are required for writes(inserts/updates) .

And yes it makes no difference, if you check execution plan you will see that they are exactly the same.

So in order to prevent locking and blocking implementing different kind of objects wont give you no results, but performance boost(if used stored proc). Instead you should change isolation level

Answer 5

One of our developers likes to write,

CREATE VIEW theView AS SELECT * FROM theTable WITH (NOLOCK)  -- does not place a lock

I can't decide if that is a symptom of genius, or, just a symptom.

Maybe they just prefer to not be locked out of tables when they want to use them.