2

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!

2
  • There as to be more than that. A query like those you wrote won't cause any locks to block work on them. ps: try to check if those views are "Indexed views" Jul 13 '17 at 16:17
  • Normally select statements without a holdlock and xlock hint won't block unless the default isolation level has been changed on the db to one of the more optimistic levels. Based on the CPU/memory/IO and other factors of the server reading from the tables vs the view, if its materialized, will usually be more efficient as all of the data to fulfill the query is in the views data pages. Without the view being materialized I've never seen a difference in performance as it will just expand the view during execution. On the dev db use the expand hint with the view to see if there is a diff.
    – Aaron
    Jul 13 '17 at 21:53
2

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.

1

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.

1
  • Thanks, that's interesting info, appreciate it. Knowing this I looked at the execution plan when querying the view and it is using indexes so I guess the developers knew what they were doing :-)
    – cspell
    Jul 13 '17 at 21:01
0

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.

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.