Results 1 to 4 of 4

Thread: MS SQL Views

  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2004
    Posts
    11
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •