• Hugo Kornelis (2/13/2012)


    I didn't check it, but I fully expect an ALTER VIEW statement to require an exclusive schema lock.

    This is true, but it's not really a problem - the ALTER VIEW statement will eventually get the lock it needs, complete very quickly, and then release that lock. If you don't mind how long the ALTER VIEW blocks for before succeeding, this can work well (though a deadlock might occur).

    For the situation depicted by Craig, the best solution would probably be to use one of the SNAPSHOT isolation levels.

    RCSI doesn't help here since it provides versioning at the statement level. An RCSI query that starts after the modification starts will block waiting to acquire Sch-S. Full snapshot isolation doesn't work either: it will also block waiting to acquire Sch-S, and if the DDL change succeeds, the query will then fail with error 3961: "Snapshot isolation transaction failed in database {name} because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation."