MS SQL Views

Is there any kind of lock on a view in ms sql database?
what happens if one of my users is looking at the view and another one is adding something to the database?
Thanks.The view itself doesn't create any locks, although using the view has the potential to create locks. A view in SQL Server is simply a way to "can" a SELECT statement (with a few restrictions), so the view itself doesn't do any locking. Using the view is subject to exactly the same rules that using the SELECT statements that the view represents, and those rules are applied in exactly the same way regardless of whether you use the view or the SELECT statement.

-PatP|||Well-well-well, the usage of the view via SELECT/INSERT/UPDATE/DELETE as well as when referenced in a JOIN, DOES cause additional TAB lock to be created in syslocks. And if everything was "exactly" the same while comparing SELECT and a view, - then why did "they" come up with such a silly concept? We could just do SELECT ... FROM (SELECT ... FROM (SELECT ... FROM (SELECT ... FROM tbl) a) b) c

Instead, the usage of views (while dangerous depending who's in control) provides a mechanism of abstracting data and data access settings that equally affect the view participants.

To answer the question you need to know what isolation level is being applied while SELECTing/INSERTing from/to the view. SELECT usually produces a TAB and a PAG lock, but may escalate to EXT, while INSERT results in a PAG lock that is not yet visible to the previously invoked SELECT.|||If you want to prevent the view from escalating the lock on the table, you could always try creating another incompatible lock on the table with another spid so that it is never escalated into a table lock. It will run slower, but your select should still run. You can always watch lock escalations with the query profiler, filter the object ID and know exactly when and what is causing the escalation. When you know that, then you know what you need to tune.

Cheers,
-Kilka