some doubt about view RRS feed

  • Question

  • I've created a view in db1 which union three tables in 3 different dbs.

    If I visit the view,how does the lock work?

    It will lock the three dbs one by one?

    or just lock the db1?
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    • Moved by Tom Phillips Thursday, July 16, 2009 7:51 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, July 16, 2009 6:33 PM


  • We can do a bit of guessing, but it is not about locking the view, it is about locking the row you are fetching for consistency purposes.  So it will lock rows in all three databases if it fetches rows from them.  It will also have database schema locks on db1, and it might have locks on the others to avoid you dropping the database (but it may not).

    How long the data is locked it completely dependent on which isolation level you are in/any locking hints.  In the default isolation level (read committed), you will generally lock one row at a time as it is fetched and released.  Then the next row is fetched.  Users can change rows you haven't fetched, and rows you have fetched.

    In other isolation levels (like read uncommitted), no locks will be taken on the data, just the schema.  Yet others (repeatable read  and serializable) as data is fetched it will become locked and will stay locked. You are likely to get full table locks too. 

    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    Friday, July 17, 2009 2:53 AM